# Pagination

Source: https://pgcast.dev/paginate

Show a long list a page at a time: here the films, newest first, two to a page. The last section puts the steps together in a request handler. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).

**At a glance**

- A page is an `order` that never ties, then a `limit`.
- To move on, an `offset` for page *n*, or the last row's keys for the next page.
- A total, if the page shows one: `count()`, or a window in `extras`.

## Order, then limit

Ask for the first page of all the films ([`db.all`](https://pgcast.dev/sql-template#all)): an `order` that ends on a unique column, then a `limit`.

You write:

```ts
const page = await db.select('films', db.all, {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'DESC' },
  ],
  limit: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  ORDER BY "release_year" DESC, "id" DESC
  LIMIT $4
) AS "sq_films"
```

Values: `["id", "title", "release_year", 2]`

TypeScript knows:

```ts
const page: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const page: {
  id: number;
  release_year: number;
  title: string;
}[]
```

- End the [`order`](https://pgcast.dev/select#order-limit-offset-and-withties) on a unique column, `id` here. Two films of the same year can otherwise come back in either order, and from one request to the next a film can turn up on two pages or on none: Postgres promises no order among ties ([LIMIT and OFFSET](https://www.postgresql.org/docs/current/queries-limit.html)).
- `limit` is sent as a parameter, `LIMIT $4`.
- Name the [`columns`](https://pgcast.dev/select#columns) the page shows, and keep the `order`'s among them if you'll fetch the next page from the last row (below).

Then pick how the reader moves on:

| | `offset` | The last row's keys |
|---|---|---|
| Go to page *n* | yes | no: the next page, from the one before |
| A deep page | Postgres computes every row it skips | Postgres starts at the last row, with an index that matches the `order` |
| A film added or removed meanwhile | shifts every later page by one | the next page still starts after the last row shown |
| It needs | an `order` that never ties | an `order` that never ties, and its columns in `columns` |

## Go to page n with offset

Skip `(n - 1) × size` rows with [`offset`](https://pgcast.dev/select#order-limit-offset-and-withties):

You write:

```ts
const page = await db.select('films', db.all, {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'DESC' },
  ],
  limit: 2,
  offset: 2,  // page 2: (2 - 1) × 2 rows
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  ORDER BY "release_year" DESC, "id" DESC
  LIMIT $4
  OFFSET $5
) AS "sq_films"
```

Values: `["id", "title", "release_year", 2, 2]`

TypeScript knows:

```ts
const page: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const page: {
  id: number;
  release_year: number;
  title: string;
}[]
```

- Past the last page, `select()` gives `[]`.
- Postgres still computes the rows it skips, so page 500 costs more than page 1 ([LIMIT and OFFSET](https://www.postgresql.org/docs/current/queries-limit.html)).
- A newer film added between two requests pushes the last film of page 1 onto page 2, where the reader sees it twice. For a long list that changes, start from the last row.

## Fetch the next page from the last row

This is keyset pagination: ask for the rows that come after the last one you showed, in the same `order`.

### Keep the last row

The last row of the page holds its keys, or is `undefined` when the page is empty: then there is no next page.

```ts
const last = page.at(-1);
if (last === undefined) throw new Error('No next page');
```

Hover `last` to see its type: a row of the `columns` you asked for, or `undefined`, so the keys must be among them. With `strict` on, the compiler holds you to both:

- Without the guard, `last.id` does not compile: `'last' is possibly 'undefined'` (TS18048).
- Without `id` in `columns`, it does not compile either (TS2339).

With `strict` off, both compile: a missing key goes as `NULL`, and the next page skips the rest of that year.

### Ask for the rows after it

Give the `where` as a [`sql`](https://pgcast.dev/sql-template#sql) fragment that compares the `order`'s columns, in its order, with those of `last`, each value through [`db.param()`](https://pgcast.dev/sql-template#param). Here `last` is the one kept above, past its guard, 2008 and 1002:

You write:

```ts
const nextPage = await db.select('films',
  db.sql<s.films.SQL>`(${'release_year'}, ${'id'}) < (${db.param(last.release_year)}, ${db.param(last.id)})`, {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'DESC' },
  ],
  limit: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  WHERE ("release_year", "id") < ($4, $5)
  ORDER BY "release_year" DESC, "id" DESC
  LIMIT $6
) AS "sq_films"
```

Values: `["id", "title", "release_year", 2008, 1002, 2]`

TypeScript knows:

```ts
const nextPage: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const nextPage: {
  id: number;
  release_year: number;
  title: string;
}[]
```

- `("release_year", "id") < ($4, $5)` keeps the rows before 2008 and 1002 in the order: an earlier year, or 2008 and a smaller `id` ([row-wise comparison](https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON)). `<` goes with `DESC`; for an `ASC` order, write `>`.
- With an index on `(release_year, id)`, Postgres starts reading at the last row. On 300,000 films, after the last row (1960, 150000), `EXPLAIN (ANALYZE)` shows an `Index Scan Backward` with `Index Cond: (ROW(release_year, id) < ROW(1960, 150000))`: it reads the 2 rows it returns, and none to skip.
- Keep the key columns `NOT NULL`, as these are: where the comparison reaches a `NULL`, it gives `NULL`, and that row never shows up.
- `s.films.SQL` lets the fragment name only the columns of `films`.

### With a filter

To keep a filter beside the comparison, give the comparison in a [Whereable](https://pgcast.dev/sql-template#whereable), as the value of the `order`'s first column, `release_year`: there [`db.self`](https://pgcast.dev/sql-template#self) stands for that column, and the Whereable joins it to the other keys with `AND`.

You write:

```ts
const nextPage = await db.select('films', {
  studio_id: 1000,
  release_year: db.sql`(${db.self}, ${'id'}) < (${db.param(last.release_year)}, ${db.param(last.id)})`,
}, {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'DESC' },
  ],
  limit: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  WHERE ((("release_year", "id") < ($4, $5)) AND "studio_id" = $6)
  ORDER BY "release_year" DESC, "id" DESC
  LIMIT $7
) AS "sq_films"
```

Values: `["id", "title", "release_year", 2008, 1002, 1000, 2]`

TypeScript knows:

```ts
const nextPage: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const nextPage: {
  id: number;
  release_year: number;
  title: string;
}[]
```

### When the directions differ

A row comparison goes one way. With `release_year` `DESC` and `id` `ASC`, spell it out with [`conditions.or`](https://pgcast.dev/conditions#conditions.or): an earlier year ([`conditions.lt`](https://pgcast.dev/conditions#conditions.lt)), or the same year and a larger `id` ([`conditions.gt`](https://pgcast.dev/conditions#conditions.gt)). Then bound it on the first key, with [`conditions.and`](https://pgcast.dev/conditions#conditions.and) and [`conditions.lte`](https://pgcast.dev/conditions#conditions.lte): that year or an earlier one.

You write:

```ts
const nextPage = await db.select('films', db.conditions.and(
  { release_year: db.conditions.lte(last.release_year) },
  db.conditions.or(
    { release_year: db.conditions.lt(last.release_year) },
    { release_year: last.release_year, id: db.conditions.gt(last.id) },
  ),
), {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'ASC' },
  ],
  limit: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  WHERE (((("release_year" <= $4))) AND (((("release_year" < $5)) OR (("id" > $6) AND "release_year" = $7))))
  ORDER BY "release_year" DESC, "id" ASC
  LIMIT $8
) AS "sq_films"
```

Values: `["id", "title", "release_year", 2008, 2008, 1002, 2008, 2]`

TypeScript knows:

```ts
const nextPage: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const nextPage: {
  id: number;
  release_year: number;
  title: string;
}[]
```

- Create the index in the `order`'s directions, `(release_year DESC, id ASC)`. Postgres starts reading at the last row's year: on 300,000 films, after the last row (1960, 150000), `EXPLAIN (ANALYZE)` shows `Index Cond: (release_year <= 1960)`, then a `Filter:` that removes the rest of that year, 1,167 rows.
- Without the bound, the `OR` is only a `Filter:`: Postgres reads and throws away every row before the last one, 148,671 here, as for an `offset`.

### Send the cursor to the client

A client that asks for the next page sends the last row's keys back. Give them to it as one string, a cursor, and check what comes back before it goes into a query:

```ts
type Cursor = { release_year: number; id: number };

function encodeCursor({ release_year, id }: Cursor): string {
  return Buffer.from(JSON.stringify({ release_year, id })).toString('base64url');
}

function decodeCursor(cursor: string): Cursor | undefined {
  try {
    const { release_year, id } = JSON.parse(Buffer.from(cursor, 'base64url').toString());
    const isKey = (key: number) => Number.isInteger(key) && Math.abs(key) < 2 ** 31;  // within Postgres's integer
    const made = encodeCursor({ release_year, id }) === cursor;  // no stray character, no other key
    if (isKey(release_year) && isKey(id) && made) return { release_year, id };
  } catch {}  // not base64url JSON
  return undefined;
}
```

- For 2008 and 1002, the cursor is `eyJyZWxlYXNlX3llYXIiOjIwMDgsImlkIjoxMDAyfQ`: JSON in base64url, which a URL takes as it is.
- `decodeCursor()` gives `undefined` for a string it did not make: `nonsense`, an empty string, JSON that is not an object, keys that are not integers (`"2008"`, `2008.5`) or out of Postgres's `integer` (`99999999999`, `1e300`). Answer 400, as the [handler](https://pgcast.dev/paginate#a-handler-from-request-to-response) does.
- It refuses a key more, standard base64's `==` and a stray `!` too: base64url decoding skips the last two, and destructuring drops a key more, so it encodes the two keys again and compares.
- A cursor is encoded, not signed: the client can read it and change it. It holds only where the page starts, and each key goes to Postgres as a parameter: a changed cursor moves the page, nothing more.

## Go back a page

Start from the first row you showed, and read the other way: flip the comparison and the `order`'s directions, then put the rows back in the page's order. Here `first` is page 2's first row, A Lantern for Edith (1991, id 1001), kept as `last` was, with `page.at(0)` and its guard:

You write:

```ts
const previousPage = await db.select('films',
  db.sql<s.films.SQL>`(${'release_year'}, ${'id'}) > (${db.param(first.release_year)}, ${db.param(first.id)})`, {
  columns: ['id', 'title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'ASC' },
    { by: 'id', direction: 'ASC' },
  ],
  limit: 2,
}).run(pool);
previousPage.reverse();  // back to newest first
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") AS result
  FROM "films"
  WHERE ("release_year", "id") > ($4, $5)
  ORDER BY "release_year" ASC, "id" ASC
  LIMIT $6
) AS "sq_films"
```

Values: `["id", "title", "release_year", 1991, 1001, 2]`

TypeScript knows:

```ts
const previousPage: db.JSONOnlyColsForTable<"films", ("id" | "release_year" | "title")[]>[]
// that is
const previousPage: {
  id: number;
  release_year: number;
  title: string;
}[]
```

- `>` with `ASC` gives the rows just before `first` in the page's order, the nearest first; [`reverse()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/reverse), in place, puts them newest first again.
- Fewer rows than `limit`, `[]` included: you reached the start of the list, so show the first page instead. A film added since page 1 was shown can leave one row there, not `[]`.
- The index on `(release_year, id)` serves both ways. On 300,000 films, before the first row (1960, 150000), `EXPLAIN (ANALYZE)` shows an `Index Scan`, forwards this time, with `Index Cond: (ROW(release_year, id) > ROW(1960, 150000))`: it reads the 2 rows it returns.
- To let a client come back, send `encodeCursor(first)` as a second cursor, `before`, beside `after`. To know whether a page comes before, fetch `size + 1` rows the other way, as the [handler](https://pgcast.dev/paginate#a-handler-from-request-to-response) does for `after`, keep `size` of them and reverse.

## Count the total

Two ways, by where you want the number:

| | [`count()`](https://pgcast.dev/select#count) | `count(*) OVER ()` in `extras` |
|---|---|---|
| Queries | one more | none, but it reads every row of the `where`, as `count()` does |
| The total | a `number` | a key on each row |
| An empty page | still the total | no row, so no total |
| With the last row's keys | the list's total, given the list's filter | the rows left: use `count()` |

Both count the rows of the `where`. The window's `where` is the page's own: with the last row's comparison in it, it counts the rows left, not the whole list.

### With `count()`

You write:

```ts
const total = await db.count('films', db.all).run(pool);
```

Postgres runs:

```sql
SELECT count(*) AS result
FROM "films"
```

Values: `[]`

TypeScript knows:

```ts
const total: number
```

Give it the list's `where`, and none of the page's options:

- `offset` leaves the one row of the count behind, and `.run()` rejects with `TypeError: Cannot read properties of undefined (reading 'result')` ([#163](https://github.com/AlexandreDemartin/pgcast/issues/163)).
- `order` by a column: Postgres refuses the query (42803).

### With a window in `extras`

A window function in [`extras`](https://pgcast.dev/select#extras) counts the rows before `LIMIT` cuts them, and gives the total on each row:

You write:

```ts
const page = await db.select('films', db.all, {
  columns: ['id', 'title', 'release_year'],
  extras: { total: db.sql<s.films.SQL, number>`count(*) OVER ()` },
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'id', direction: 'DESC' },
  ],
  limit: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id", $2::text, "title", $3::text, "release_year") ||
    jsonb_build_object($4::text, count(*) OVER ()) AS result
  FROM "films"
  ORDER BY "release_year" DESC, "id" DESC
  LIMIT $5
) AS "sq_films"
```

Values: `["id", "title", "release_year", "total", 2]`

TypeScript knows:

```ts
const page: db.ReturningTypeForTable<"films", ("id" | "release_year" | "title")[], {
  total: db.SQLFragment<number, never>;
}>[]
// that is
const page: {
  id: number;
  release_year: number;
  title: string;
  total: number;
}[]
```

- Each row has `total: 4`. The type is the fragment's second type argument, `number`; Postgres's `bigint` arrives as a JSON number.
- On an empty page, past the last one, there is no row to carry it: `page[0]?.total` is `undefined`, not the total.
- The window makes the page's query read and sort every row of the `where`. On 300,000 films, `EXPLAIN (ANALYZE)` shows a `Seq Scan` and a `WindowAgg` over all of them, where the page alone is an `Index Scan Backward` that stops at its 2 rows.
- With `strict` off, `total` is not in the type ([Options](https://pgcast.dev/select#options)), and `page[0].total` does not compile (TS2339).

## A handler, from request to response

The steps above in one function: the filter from the query string, the cursor, whether there is a next page, the total, and the JSON reply. `listFilms()` takes the Fetch API's [`Request`](https://developer.mozilla.org/en-US/docs/Web/API/Request) and returns a [`Response`](https://developer.mozilla.org/en-US/docs/Web/API/Response), both global in Node; `encodeCursor()` and `decodeCursor()` are those of [Send the cursor to the client](https://pgcast.dev/paginate#send-the-cursor-to-the-client).

```ts
const size = 2;

async function listFilms(request: Request): Promise<Response> {
  const params = new URL(request.url).searchParams;
  const studio = params.get('studio'), after = params.get('after');
  const cursor = after === null ? undefined : decodeCursor(after);
  const badStudio = studio !== null && !/^\d{1,9}$/.test(studio);  // 1 to 9 digits: within Postgres's integer
  const badCursor = after !== null && cursor === undefined;
  if (badStudio || badCursor) return Response.json({ error: 'Bad studio or cursor' }, { status: 400 });

  const filter: s.films.Whereable = studio === null ? {} : { studio_id: Number(studio) };
  const rows = await db.select('films', {
    ...filter,
    ...(cursor && {
      release_year: db.sql`(${db.self}, ${'id'}) < (${db.param(cursor.release_year)}, ${db.param(cursor.id)})`,
    }),
  }, {
    columns: ['id', 'title', 'release_year'],
    order: [
      { by: 'release_year', direction: 'DESC' },
      { by: 'id', direction: 'DESC' },
    ],
    limit: size + 1,  // one more than the page: if it comes back, there is a next page
  }).run(pool);

  const films = rows.slice(0, size), last = films.at(-1);
  const nextCursor = rows.length > size && last !== undefined ? encodeCursor(last) : null;
  const total = await db.count('films', filter).run(pool);  // the list's where, without the cursor
  return Response.json({ films, nextCursor, total });
}
```

On the docs schema, `GET /films` replies:

```json
{
  "films": [
    { "id": 1003, "title": "Small Hours at Calder Bay", "release_year": 2019 },
    { "id": 1002, "title": "The Cartographer's Silence", "release_year": 2008 }
  ],
  "nextCursor": "eyJyZWxlYXNlX3llYXIiOjIwMDgsImlkIjoxMDAyfQ",
  "total": 4
}
```

- `limit: size + 1` asks for one row more than the page. If it comes back, there is a next page, and the cursor is the page's last row, not the extra one; `GET /films?after=eyJyZWxlYXNlX3llYXIiOjIwMDgsImlkIjoxMDAyfQ` gives 1001 and 1000, then `"nextCursor": null`. With `limit: size`, a full last page would send a cursor to an empty one.
- The page's `where` is [the one with a filter](https://pgcast.dev/paginate#with-a-filter): `filter`, then the comparison when a cursor came. [`count()`](https://pgcast.dev/paginate#with-count) takes `filter` alone, so each page has the list's total: `GET /films?studio=1000` gives 1002 and 1000, and `"total": 2`. Leave it out, and its query, if the page shows no total.
- A `studio` that is not 1 to 9 digits, or an `after` that `decodeCursor()` refuses, gets a 400 before any query: `Number()` alone would read `''` as 0, `0x3e8` as 1000, and let `99999999999` reach Postgres, which refuses it.

## See also

- [`select()`](https://pgcast.dev/select#select): every option and the SQL it writes; [`count()`](https://pgcast.dev/select#count) and its options.
- [SQL template](https://pgcast.dev/sql-template#sql), [`self`](https://pgcast.dev/sql-template#self) and [`param()`](https://pgcast.dev/sql-template#param), for the comparison.
- [Why pgcast](https://pgcast.dev/why): why a query stays the SQL you see.