Pagination

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.

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): an order that ends on a unique column, then a limit.

TSYou write Open in playground

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);

SQLPostgres runs

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

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

Page 1: Small Hours at Calder Bay (2019, id 1003), then The Cartographer's Silence (2008, id 1002).

• End the order 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).
• limit is sent as a parameter, LIMIT $4.
• Name the 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:

TSYou write Open in playground

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);

SQLPostgres runs

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

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

Page 2: A Lantern for Edith (1991, id 1001), then The Salt Orchard (1974, id 1000).

• Past the last page, select() gives [].
• Postgres still computes the rows it skips, so page 500 costs more than page 1 (LIMIT and OFFSET).
• 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.

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 fragment that compares the order's columns, in its order, with those of last, each value through db.param(). Here last is the one kept above, past its guard, 2008 and 1002:

TSYou write Open in playground

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);

SQLPostgres runs

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

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

The same page 2 as with offset: A Lantern for Edith (1991, id 1001), then The Salt Orchard (1974, id 1000).

• ("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). < 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, as the value of the order's first column, release_year: there db.self stands for that column, and the Whereable joins it to the other keys with AND.

TSYou write Open in playground

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);

SQLPostgres runs

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

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

Of studio 1000's films after 2008 and 1002: The Salt Orchard (1974, id 1000) alone.

When the directions differ

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

TSYou write Open in playground

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);

SQLPostgres runs

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

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

Page 2 again: A Lantern for Edith (1991, id 1001), then The Salt Orchard (1974, id 1000).

• 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:

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 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:

TSYou write Open in playground

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

SQLPostgres runs

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

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

From page 2 back to page 1, once reversed: Small Hours at Calder Bay (2019, id 1003), then The Cartographer's Silence (2008, id 1002).

• > with ASC gives the rows just before first in the page's order, the nearest first; 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 does for after, keep size of them and reverse.

Count the total

Two ways, by where you want the number:

	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()

TSYou write Open in playground

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

SQLPostgres runs

SELECT count(*) AS result
FROM "films"

values []

::TypeScript knows

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).
• order by a column: Postgres refuses the query (42803).

With a window in extras

A window function in extras counts the rows before LIMIT cuts them, and gives the total on each row:

TSYou write Open in playground

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);

SQLPostgres runs

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

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), 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 and returns a Response, both global in Node; encodeCursor() and decodeCursor() are those of Send the cursor to the client.

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:

{
  "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: filter, then the comparison when a cursor came. 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(): every option and the SQL it writes; count() and its options.
• SQL template, self and param(), for the comparison.
• Why pgcast: why a query stays the SQL you see.