Pagination

paginate takes a page, an offset window or an after cursor. Prefer cursors: the cost of a page number grows with the offset, because Postgres reads and discards every row before it, and a row inserted while someone pages shifts the rest by one. Page numbers stay for tables where people jump to page 7 and the total matters.

Page numbersCopy Anchor Link

const { items, page } = await db.customers
  .paginate({ page: 2, size: 25, count: "exact", orderBy: { name: "asc" } })
  .orThrow();
// page: { number: 2, size: 25, total: 81, pages: 4, hasMore: true }

Without count, total and pages are null. hasMore comes from fetching one extra row, so it never needs a count. Without orderBy, pages are ordered by the primary key, so they never overlap.

Offset and limitCopy Anchor Link

When the caller already speaks in offsets (a data grid, a REST API with offset and limit parameters), pass offset and limit instead of page and size. The rows and the total come back in one request.

const { items, page } = await db.customers
  .paginate({ offset: 40, limit: 20, count: "exact", orderBy: { name: "asc" } })
  .orThrow();
// page: { number: 3, size: 20, total: 81, pages: 5, hasMore: true }

The result has the same shape as a numbered page: size is the limit, and number is the page the offset falls on (floor(offset / limit) + 1). An offset past the last row returns no items and the total on both executors, although PostgREST itself answers that read with a 416. Passing offset together with page or size is an invalid_request error.

CursorsCopy Anchor Link

const first = await db.customers
  .paginate({ after: null, size: 25, orderBy: { name: "asc" } })
  .orThrow();

const next = await db.customers
  .paginate({ after: first.nextCursor, size: 25, orderBy: { name: "asc" } })
  .orThrow();

Cursors use keyset pagination: the next page is "rows after the last one", not an offset, so inserts do not shift pages and deep pages stay fast. The primary key is added as a tiebreaker, so rows with equal sort values are never skipped or repeated.

Nullable sort columns work too: the next page follows where Postgres puts nulls (last for asc, first for desc, or what nulls says), so no row with a null sort value is skipped. Give the order an index that matches it, for example create index on customers (name, id), and each page is one index range scan however deep it is.

On the better-supabase/postgres executor the cursor compiles to a row comparison, (name, id) > ($1, $2), when every column sorts the same way and none is nullable.

A cursor is an opaque base64url string. Pass it back with the same orderBy and where. The cursor records the table and the sort it continues, so a cursor passed with another orderBy fails with an invalid_request error instead of returning the wrong page; start the new order with after: null.

Lists and REST resources page by cursor with pagination: 'cursor' (see list queries); their size stays capped by maxPageSize.

Row capsCopy Anchor Link

PostgREST returns at most db-max-rows rows for one read (1000 on hosted projects, [api] max_rows in config.toml) and drops the rest without an error. A findMany without limit can therefore look complete when it isn't.

export const betterSupabase = defineSupabase(schema, { maxRows: 1000 });

betterSupabase.on("query", ({ table, truncated }) => {
  if (truncated) metrics.increment(`db.${table}.truncated`);
});

• maxRows tells better-supabase the cap. It defaults to 1000; set it to what your project uses.
• A read without limit that returns exactly maxRows rows sets truncated: true on the query event and logs a warning once per table. The result itself is unchanged.
• The fix is limit for a bounded list, paginate for everything else, or an aggregate when you only need a number.

Default orderCopy Anchor Link

findMany without orderBy orders by the primary key. Postgres has no default row order, so without it two identical reads can return rows in a different order after an update or a VACUUM, and limit picks arbitrary rows. Pass orderBy to choose another order. Views and tables without a primary key keep the database's order.

Sorting by a related rowCopy Anchor Link

orderBy takes a to-one relation with columns of the related table, one level deep:

await db.invoices
  .paginate({
    page: 1,
    size: 25,
    orderBy: [{ organization: { name: "asc" } }, { number: "desc" }],
  })
  .orThrow();

Over PostgREST this sends order=_bs1(name).asc with an empty embed of the relation, or reuses the relation's include when you include it. The SQL executor sorts by a scalar subquery. Rows without a related row sort as null. To-many relations, cursor pages, aggregates, sorts inside an include and SQLite return an error, since none of them can sort by a joined row.

LintCopy Anchor Link

The unbounded-read rule flags findMany without limit on the tables gen marked as large in supabase/snapshot.json, before they reach production.