How to Paginate Results in Supabase

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  process.env.NEXT_PUBLIC_SUPABASE_URL!,
5  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
6)
7
8const PAGE_SIZE = 10
9
10async function fetchPage(page: number) {
11  const from = page * PAGE_SIZE
12  const to = from + PAGE_SIZE - 1
13
14  const { data, error, count } = await supabase
15    .from('products')
16    .select('*', { count: 'exact' }) // Returns total count
17    .order('created_at', { ascending: false })
18    .range(from, to)
19
20  return { data, error, count }
21}
22
23// Page 0: rows 0-9
24// Page 1: rows 10-19
25// Page 2: rows 20-29

1// Exact count (accurate but slower on very large tables)
2const { data, count } = await supabase
3  .from('products')
4  .select('*', { count: 'exact' })
5  .eq('category', 'electronics')
6  .order('created_at', { ascending: false })
7  .range(0, 9)
8
9const totalPages = Math.ceil((count ?? 0) / PAGE_SIZE)
10console.log(`Page 1 of ${totalPages} (${count} total items)`)
11
12// Head-only count (no data returned, just the count)
13const { count: totalRows } = await supabase
14  .from('products')
15  .select('*', { count: 'exact', head: true })
16  .eq('category', 'electronics')

1async function fetchNextPage(cursor?: string) {
2  let query = supabase
3    .from('products')
4    .select('*')
5    .order('created_at', { ascending: false })
6    .limit(PAGE_SIZE)
7
8  if (cursor) {
9    // Fetch rows with created_at before the cursor
10    query = query.lt('created_at', cursor)
11  }
12
13  const { data, error } = await query
14
15  // The next cursor is the last row's created_at
16  const nextCursor = data && data.length === PAGE_SIZE
17    ? data[data.length - 1].created_at
18    : null
19
20  return { data, error, nextCursor }
21}
22
23// Usage:
24// First page: fetchNextPage()
25// Next page: fetchNextPage('2024-03-15T10:30:00Z')
26// No more pages when nextCursor is null

1async function fetchFilteredPage(
2  page: number,
3  category?: string,
4  search?: string
5) {
6  const from = page * PAGE_SIZE
7  const to = from + PAGE_SIZE - 1
8
9  let query = supabase
10    .from('products')
11    .select('*', { count: 'exact' })
12    .order('price', { ascending: true })
13    .range(from, to)
14
15  // Apply optional filters
16  if (category) {
17    query = query.eq('category', category)
18  }
19  if (search) {
20    query = query.ilike('name', `%${search}%`)
21  }
22
23  const { data, error, count } = await query
24  return {
25    data,
26    error,
27    totalPages: Math.ceil((count ?? 0) / PAGE_SIZE),
28  }
29}

1import { useState, useEffect } from 'react'
2
3function PaginatedList() {
4  const [page, setPage] = useState(0)
5  const [data, setData] = useState<any[]>([])
6  const [totalPages, setTotalPages] = useState(0)
7
8  useEffect(() => {
9    fetchPage(page).then(({ data, count }) => {
10      if (data) setData(data)
11      if (count !== null) setTotalPages(Math.ceil(count / PAGE_SIZE))
12    })
13  }, [page])
14
15  return (
16    <div>
17      {data.map((item) => (
18        <div key={item.id}>{item.name}</div>
19      ))}
20      <div>
21        <button
22          onClick={() => setPage((p) => p - 1)}
23          disabled={page === 0}
24        >
25          Previous
26        </button>
27        <span>Page {page + 1} of {totalPages}</span>
28        <button
29          onClick={() => setPage((p) => p + 1)}
30          disabled={page >= totalPages - 1}
31        >
32          Next
33        </button>
34      </div>
35    </div>
36  )
37}