# Pagination

> Page numbers or offset windows with totals, or keyset cursors for infinite lists.

Source: https://bettersupabase.com/docs/repository/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 numbers [#page-numbers]

```ts
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 limit [#offset-and-limit]

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.

```ts
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.

## Cursors [#cursors]

```ts
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](/docs/platform/list#cursor-pagination)); their `size` stays capped
by `maxPageSize`.

## Row caps [#row-caps]

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.

```ts
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](/docs/extending/events) 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](/docs/repository/aggregates) when you only need a number.

## Default order [#default-order]

`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 row [#sorting-by-a-related-row]

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

```ts
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.

## Lint [#lint]

The [`unbounded-read`](/docs/plugins/lint#unbounded-read) rule flags
`findMany` without `limit` on the tables `gen` marked as large in
`supabase/snapshot.json`, before they reach production.