Egon ORM

Appearance

Pagination ​

Two ways to read a large list a page at a time: by page number (offset), or after the last row seen (keyset).

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { createRepo, from, Schema, t, tuple } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

class Post extends Schema("posts", {
  id: t.integer().primaryKey().dbGenerated(),
  title: t.string(),
  createdAt: t.timestamp(),
}) {}

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("posts", (table) => {
      table.id({ type: "integer" });
      table.text("title").notNull();
      table.timestamptz("created_at").notNull();
    });
    // The order of the pages; Postgres reads it backwards for created_at desc, id desc.
    m.createIndex("posts", ["created_at", "id"]);
  },
  down(m) {
    m.dropTable("posts");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021000901, name: "pagination_tables", migration: tables }],
  }).up();
  await admin.close();
}

By page number ​

ts
export async function page(number: number, perPage = 20) {
  const base = from(Post, "p").orderBy(({ p }) => [p.createdAt.desc(), p.id.desc()]);

  const [rows, total] = await Promise.all([
    repo.all(base.limit(perPage).offset((number - 1) * perPage)),
    repo.count(base), // the order is dropped for the count
  ]);
  return { rows, total, pages: Math.ceil(total / perPage) };
}

limit and offset are bound parameters, so every page shares one query plan. repo.count(base) counts the rows of the same query; the order does not matter to it.

Offset pagination is simple and lets a reader jump to page 7, but the database still reads and skips the rows before the page, and a row inserted while someone pages through moves the rest by one.

After the last row seen ​

Keyset (or cursor) pagination asks for the rows after the last one of the previous page. It reads only the page, whatever its depth, and is stable while rows are added.

ts
type Cursor = { createdAt: Date; id: number };

export async function after(cursor: Cursor | null, perPage = 20) {
  let query = from(Post, "p")
    .orderBy(({ p }) => [p.createdAt.desc(), p.id.desc()]) // the same columns as the where
    .limit(perPage);
  // The first page has no cursor, so no condition.
  if (cursor !== null) {
    const { createdAt, id } = cursor;
    query = query.where(({ p }) => tuple(p.createdAt, p.id).lt(tuple(createdAt, id)));
  }
  const rows = await repo.all(query);
  const last = rows.at(-1);
  // A short page is the last one: no cursor, so the caller does not ask for an empty page.
  const next =
    last === undefined || rows.length < perPage ? null : { createdAt: last.createdAt, id: last.id };
  return { rows, next };
}

The examples below start from these rows, after creating the tables:

ts
await createTables();
const start = Date.UTC(2026, 0, 1);
await repo.insertAll(
  Post,
  Array.from({ length: 45 }, (_, i) => ({
    title: `Post ${i + 1}`,
    createdAt: new Date(start + (i % 9) * 60_000),
  })),
);
ts
let cursor: Cursor | null = null;
let seen = 0;
let queries = 0;
do {
  const batch = await after(cursor);
  queries += 1;
  seen += batch.rows.length;
  cursor = batch.next;
} while (cursor !== null);
({ seen, queries }); // => { seen: 45, queries: 3 }
  • The first page has no cursor, so after adds the where only when there is one: a query is a value, and query.where(...) returns a new one with the condition added.
  • The cursor holds the values of the order columns of the last row. tuple(a, b).lt(...) compares them as a row: (created_at, id) < ($1, $2).
  • A page shorter than perPage is the last one, so after returns no cursor and the loop stops: 45 rows take three queries (20, 20, 5). When the total is an exact multiple of perPage, the last full page still returns a cursor, and one more query comes back empty. To avoid it, ask for perPage + 1 rows and return a cursor only when the extra row came back.
  • Order by a unique combination: createdAt alone may repeat, so id breaks ties.
  • The where and the orderBy use the same columns in the same direction. An index on (created_at, id) serves both: Postgres reads it backwards for a descending order.
  • To send the cursor to a client, encode it (for example as base64 JSON) and decode it into a Date and a number again on the next request.

Good to know

  • limit and offset count rows, not parents. A query that fills a to-many association from one of its own joins (preload({ posts: "p" }), see From a join) repeats each parent once per child, so limit and offset are refused there. Preload the association with a separate query (preload("posts")) instead.

See also ​