Pagination

The four shapes — offset, cursor/keyset, numbered pages, infinite-scroll — their performance characteristics, and what forge-orm exposes for each. Cursor pagination is the right default for almost everything; this page also covers total-count strategies, Relay/REST response shapes, and per-dialect behavior under load.

Queries deep-dive sketches the cursor API in two paragraphs. This doc is the full reference: every shape with its failure mode, the per-dialect emit forge actually produces, how cursors interact with sort keys / filters / soft-delete, and the response envelopes you wrap them in at the HTTP and GraphQL boundaries.

Contents

• The four shapes — at a glance
• Offset (take + skip) — and why it stops scaling
• Cursor — forge's cursor + take
• Keyset — the raw SQL form
• Numbered pages — when you actually need them
• Total count strategies
• Bidirectional cursors — take: -N is not supported
• Sort + cursor — stable keys and tie-breakers
• cursorAll vs cursorSnapshot — consistency under writes
• GraphQL Relay connection mapping
• REST response shapes — body, headers, Link
• Infinite-scroll UX with TanStack Query
• Pagination + filters — invalidation and filter-hashed cursors
• Pagination + soft-delete
• Per-dialect pagination
• Three worked examples
• Common bugs
• See also

---

The four shapes — at a glance

Shape	API surface	Cost on page N	Stable under writes	Random access	Best for
Offset	take + skip	O(N × pageSize)	No	Yes	Stable historical data, admin reports
Cursor (keyset)	cursor + take	O(pageSize)	Yes	No	Feeds, infinite scroll, realtime lists
Numbered pages	take + skip + count	Offset cost plus COUNT(*)	No	Yes — jump-to-page	Small admin tables (< ~100k rows)
Infinite-scroll / load-more	Cursor under a UI veneer	O(pageSize) per fetchNextPage	Yes	No	Mobile feeds, search results

"Best default" is cursor. The rest are special cases.

The performance gap is not theoretical. On Postgres the planner reads and discards every skipped row before returning the first row of the page — OFFSET 10000 LIMIT 20 reads 10020 rows. The same query under a keyset cursor reads 20 rows. By page 500 the offset form is 500× slower than the cursor form on the same index, and the difference is wall-clock time the user is staring at.

---

Offset (take + skip) — and why it stops scaling

The classic form. LIMIT + OFFSET plus an ORDER BY to make the boundaries deterministic.

await db.post.findMany({
  where:   { status: 'PUBLISHED' },
  orderBy: { created_at: 'desc' },
  take:    20,
  skip:    40,                         // page 3 of 20-row pages
});

Per-dialect emit:

Dialect	Emit
Postgres	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
MySQL	... ORDER BY `created_at` DESC LIMIT 20 OFFSET 40
SQLite	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
DuckDB	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
MSSQL	... ORDER BY "created_at" DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
Mongo	.find(filter).sort([['created_at', -1]]).skip(40).limit(20)

Two distinct failure modes. Cost scales with OFFSET — every engine implements OFFSET as "read the first N rows from the ordered result and throw them away". Two thousand reads per page is fine; twenty thousand is the whole latency budget. On a 100k-row table users will hit pages where the response takes seconds. Non-stable under concurrent writes — offset addresses rows by position, not identity. A new row arriving between page loads bumps every offset by one and the user sees the last row of page 2 again at the top of page 3. Or a row gets skipped entirely.

Use offset when the result set is stable for the session (admin reports against historical data), the dataset is small enough that OFFSET 1000 is not a latency problem, you need jump-to-page and the cost is acceptable, or duplicates and skips are acceptable (audit logs the user is scrolling, not selecting). For anything else, reach for cursor.

---

Cursor — forge's cursor + take

Cursor pagination addresses the boundary by row identity, not position. The next page is "rows whose ordered key is after this row's ordered key", which compiles to an index range scan — sargable on every dialect.

const page1 = await db.post.findMany({
  where: { status: 'PUBLISHED' }, orderBy: { id: 'asc' }, take: 20,
});
const page2 = await db.post.findMany({
  where: { status: 'PUBLISHED' }, orderBy: { id: 'asc' }, take: 20,
  cursor: { id: page1.at(-1)!.id },             // exclusive — no skip needed
});

The IR emits a half-open inequality on the cursor key. On PG with orderBy: { id: 'asc' }:

SELECT … FROM "post"
WHERE "status" = $1 AND "id" > $2
ORDER BY "id" ASC LIMIT 20;

That "id" > $2 is sargable on the primary key index. The page is O(pageSize) regardless of how deep the user has scrolled — no skipped rows, no boundary drift under writes, no COUNT(*) round-trip.

The trade-off is no random access — no "page 47" because there is no notion of position. The UI either pages forward / back from a known cursor, or it gives up the requirement.

The cursor is exclusive — do not add skip: 1

This is the one divergence from Prisma that will bite you, because the habit transfers silently.

Forge emits a strict inequality: > for an ascending key, < for a descending one, never >=. The cursor row is therefore already excluded from the next page. Prisma's cursor is inclusive, which is why Prisma code pairs cursor with skip: 1 — and on forge that skip: 1 throws away the first row you asked for:

// rows p0 … p5 ordered ascending, cursor on p2
cursor: { created_at: p2.created_at }              // → p3, p4, p5
cursor: { created_at: p2.created_at }, skip: 1     // → p4, p5     ← p3 is gone

Nothing errors. The row simply never appears on any page, in either direction, and the bug shows up much later as "a record is missing from the list". Do not claim or assume Prisma parity here: port the query, drop the skip.

The flip side of an exclusive cursor is that you must hold a real boundary value. You cannot resume from a cursor whose row has been deleted unless the cursor carries the values rather than just the id — see Sort + cursor.

Nothing checks that the cursor key is unique

cursor is typed as { id?: string } or any { [key]: value }, and the builder simply flattens whatever you pass into a set of field/value pairs. There is no compile-time narrowing to unique fields and no runtime validation — a cursor on a non-unique column compiles and runs, and duplicates or skips rows at every tie.

Uniqueness is your responsibility. The rule is in Sort + cursor, and it is worth reading before you ship a feed.

---

Keyset — the raw SQL form

"Cursor pagination" and "keyset pagination" are the same idea — the forge cursor API is a typed wrapper on the keyset technique. When you drop to $queryRaw (recursive CTEs, window functions), the pattern is:

-- pageSize = 20, lastSeen = (createdAt, id) of the previous page's last row
SELECT id, title, created_at
FROM post
WHERE status = $1
  AND (created_at, id) < ($lastCreatedAt, $lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The composite tuple (created_at, id) is the keyset; the comparison is the boundary. The trailing id is the tie-breaker — created_at alone is not unique, so without id the page boundary is ambiguous when two posts share a timestamp.

Per-dialect emit of the tuple form:

Dialect	Emit
Postgres	("created_at", "id") < ($1, $2) — true row-value compare
SQLite	("created_at", "id") < (?, ?) — supported since 3.15
DuckDB	("created_at", "id") < ($1, $2)
MySQL	("created_at", "id") < (?, ?) — supported since 5.7
MSSQL	Not supported. Expanded to ("created_at" < @p1 OR ("created_at" = @p1 AND "id" < @p2))
Mongo	Not supported. Expanded to { $or: [{ created_at: { $lt: a } }, { $and: [{ created_at: a }, { id: { $lt: b } }] }] }

The expanded form is more verbose but the planner sees the same range scan on every dialect — provided you have a compound index on (created_at, id). Without it the OR-of-AND form falls back to a sequential scan and the page-N pattern is as slow as offset. See INDEXES for the declaration:

const post = model('post', { /* … */ }, {
  indexes: [{ keys: { created_at: 1, id: 1 } }],
});

Leading column matches the leading orderBy key. Putting id first would not help — the range scan needs created_at first to be selective.

---

Numbered pages — when you actually need them

Some surfaces genuinely require numbered pagination — admin tables where "page 47 of 312" is a legitimate affordance, exports where the user needs a deterministic position.

const PAGE_SIZE = 25;

async function adminUsersPage(pageNum: number) {
  const [rows, total] = await Promise.all([
    db.user.findMany({
      orderBy: { created_at: 'desc' },
      take:    PAGE_SIZE,
      skip:    (pageNum - 1) * PAGE_SIZE,
    }),
    db.user.count(),
  ]);

  return {
    rows,
    pageInfo: {
      page:        pageNum,
      pageSize:    PAGE_SIZE,
      totalRows:   total,
      totalPages:  Math.ceil(total / PAGE_SIZE),
      hasPrevPage: pageNum > 1,
      hasNextPage: pageNum * PAGE_SIZE < total,
    },
  };
}

Works when:

• The total is small enough that OFFSET stays sub-millisecond (rule of thumb: under 50k rows, or under 5k with a heavy projection).
• The user genuinely needs to know how many pages there are.
• Drift between page loads is acceptable.

Past those limits, switch to cursor and drop the page numbers.

---

Total count strategies

The COUNT(*) query that fills the "of ???" half of "page 47 of ???" is its own performance story. Four strategies, in order of preference.

1. Exact count via db.x.count().

const total = await db.user.count({ where: { active: true } });

PG / MySQL / SQLite / DuckDB / MSSQL emit SELECT COUNT(*) FROM "user" WHERE …. Mongo uses countDocuments. Cost scales with the result set; on a 10M row table the count itself is the slow request. Always run count and findMany in parallel — most ORMs serialise them and double latency:

const [rows, total] = await Promise.all([
  db.user.findMany({ where, take: 25, skip: page * 25 }),
  db.user.count({ where }),
]);

2. Estimated count from pg_class.reltuples (Postgres). For unfiltered or very common-filter counts on huge tables, the planner estimate is the difference between "loads in 2 ms" and "loads in 800 ms".

const [{ estimate }] = await db.$queryRaw<{ estimate: number }[]>`
  SELECT reltuples::bigint AS estimate
  FROM pg_class WHERE relname = 'user';
`;

Updated by autovacuum + ANALYZE; wrong by up to a few percent on a busy table. For "about 12,000 users" precision is more than enough.

DuckDB exposes duckdb_tables; MySQL has INFORMATION_SCHEMA.TABLES.TABLE_ROWS (also estimated); SQLite has no equivalent. For filtered PG estimates wrap the query in EXPLAIN (FORMAT JSON) and read the Plan Rows field — the trick GitLab and Discourse use for huge filtered counts.

3. Cached count. For totals that change slowly relative to the read rate, cache and invalidate on writes. For tenanted counts, hook into model events (EVENTS) and increment / decrement:

db.$on('user.created',     ({ org_id }) => cache.incr(`count:user:${org_id}`));
db.$on('user.softDeleted', ({ org_id }) => cache.decr(`count:user:${org_id}`));

The cached value drifts under cache-miss scenarios — refresh on a schedule to bound the drift. Billing surfaces should always do the live count.

4. The "is there a next page?" shortcut. Cursor pages do not need a total — hasNextPage only needs to know whether any row exists beyond the current page. Read one extra row past the page size:

const PAGE_SIZE = 20;
const rows = await db.post.findMany({
  where,
  orderBy: { id: 'asc' },
  take:    PAGE_SIZE + 1,              // ask for one extra
  ...(cursor ? { cursor: { id: cursor } } : {}),
});

const hasNextPage = rows.length > PAGE_SIZE;
const visible     = rows.slice(0, PAGE_SIZE);
const nextCursor  = hasNextPage ? visible[PAGE_SIZE - 1].id : null;

That extra row is one index read; COUNT(*) over the same filter is potentially every index read.

---

Bidirectional cursors — take: -N is not supported

Retracted. This page used to describe a negative take as "the previous page relative to the cursor", implemented by flipping the predicate, flipping the ORDER BY and re-reversing the result array. None of that exists. The value is passed straight through as the limit, so take: -20 becomes LIMIT -20 — which SQLite reads as no limit at all, Postgres and MySQL reject, and Mongo reads as "one batch of at most 20". Do not use it.

Page backwards by reversing the sort yourself and reversing the array after, which is what the flip would have done:

// "previous page", ending just before firstCursor
const rows = await db.post.findMany({
  orderBy: { created_at: 'desc' },        // opposite of the forward sort
  take:    PAGE_SIZE + 1,                 // the extra row answers hasPrevPage
  cursor:  { created_at: firstCursor },   // exclusive, so no skip
});
const hasPrev = rows.length > PAGE_SIZE;
const page    = rows.slice(0, PAGE_SIZE).reverse();   // back into display order

For a feed with up and down arrows, carry two cursors — firstCursor and lastCursor. "Next page" keeps the forward orderBy and anchors on lastCursor; "previous page" is the block above. hasNextPage and hasPrevPage both come from the extra-row trick.

The cursor keys must match the orderBy you use in each direction — see Sort + cursor.

---

Sort + cursor — stable keys and tie-breakers

The single most common cursor bug is "the sort key is not unique and the cursor is not stable". A cursor on created_at alone duplicates or skips rows whenever two rows share a timestamp — and on a busy table they will.

The rule: the cursor keys are the orderBy keys, in the same order. Every cursor key is compared against the column of the same name, and the direction for that comparison is read from the matching orderBy entry. So the cursor has to describe the same ordered position the sort does. If the visible sort key is not unique, add a unique tie-breaker (almost always id) to both:

const last = page1.at(-1)!;

await db.post.findMany({
  orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
  take:    20,
  cursor:  { created_at: last.created_at, id: last.id },
});

Carry the last row's values, not just its id. A cursor on id alone while sorting by created_at is the trap this section exists for: it compiles, it runs, and it compares ids while the rows come back ordered by timestamp — so it drops and repeats rows more or less at random. A cursor key with no matching orderBy entry is treated as ascending, which is the same assumption the ORDER BY builder makes for a bare field.

How a composite cursor compiles

Uniform direction — every key sorts the same way — becomes a row-value comparison, one predicate an index on those columns satisfies with a single range scan:

-- orderBy: [{ created_at: 'desc' }, { id: 'desc' }]
WHERE ("posts"."created_at", "posts"."id") < ($1, $2)

Mixed directions cannot be a row comparison at all — (a, b) > (x, y) applies one operator to the whole tuple, and "score descending, id ascending" needs two. It expands lexicographically instead:

-- orderBy: [{ score: 'desc' }, { id: 'asc' }]
WHERE (("posts"."score" < $1)
    OR ("posts"."score" = $2 AND "posts"."id" > $3))

Each occurrence of a cursor value gets its own placeholder — the compiler pushes a parameter per use rather than reusing one — so a three-key mixed cursor is six parameters, not three.

MSSQL always takes the expansion, uniform or not: T-SQL has no row-value comparison, so there is no tuple form to emit. This is the Dialect.rowValueComparison capability flag, which is false only there. The same uniform-descending cursor as above becomes

WHERE (([posts].[created_at] < @p1)
    OR ([posts].[created_at] = @p2 AND [posts].[id] < @p3))

The expansion is correct either way; it is just more predicates for the planner, and it needs the compound index to stay sargable.

Mongo uses the same lexicographic expansion with $or:

{ $or: [
  { score: { $lt: 5 } },
  { $and: [ { score: 5 }, { _id: { $gt: 'abc' } } ] },
] }

Before 2.18.0 Mongo built { $and: [ { a: { $gt: x } }, { b: { $gt: y } } ] }, which is not a tuple comparison: after (5, 9) the next row is (5, 10), and that row has b > 9 but a = 5, which is not > 5, so the $and rejected it. Every row sharing the cursor's leading value was skipped.

Compound-unique keys. When the key is itself compound — (user_id, video_id) on a watched table — you can pass the synthetic @@unique name and forge flattens it, or pass the fields directly. These are the same cursor:

cursor: { user_id_video_id: { user_id: 'u1', video_id: 'v9' } }
cursor: { user_id: 'u1', video_id: 'v9' }

Nothing checks the name against a declared unique — the flattening is purely mechanical. And note that the synthetic-name form works only for cursor. In a where it is not supported and throws [forge] unknown operator 'user_id' on '<model>.user_id_video_id'; write the fields out instead (where: { user_id: 'u1', video_id: 'v9' }).

Sorting on a relation field. orderBy: { author: { name: 'asc' } } is documented at the README surface but the IR builder silently drops it today (same gap as in QUERIES). Denormalise the sort key onto the row or fall back to $queryRaw.

---

cursorAll vs cursorSnapshot — consistency under writes

Cursor pagination is stable under writes for the boundary. The question is what guarantee you want for the rows between boundaries.

cursorAll — see writes as they happen. The default. Each page is a fresh findMany against current data. Rows inserted between page 1 and page 2 will appear on subsequent visits to page 1, but not retroactively on page 2. Use for feeds, search, anything where freshness is the desired behaviour.

cursorSnapshot — fix the result set at session start. Some surfaces (data exports, paginated batch jobs, "back" navigation on search results) want the page set fixed at the moment the first page is fetched. Capture a timestamp on first request, round-trip it through each subsequent page (in the URL, query params, or cursor blob), and apply it as a filter:

const snapshotAt = new Date();
const where = { created_at: { lte: snapshotAt } };

const p1 = await db.post.findMany({ where, orderBy, take: 20 });
const p2 = await db.post.findMany({
  where, orderBy, take: 20,
  cursor: { id: p1.at(-1)!.id },
});

The created_at <= snapshotAt filter freezes the result set against inserts. For delete stability, soft delete is the answer (see Pagination + soft-delete). Hard-deleted rows simply disappear and there is no per-row way to keep them in a snapshot. Forge does not expose a built-in cursorSnapshot mode — it is a pattern over findMany, not a separate call.

---

GraphQL Relay connection mapping

The Relay cursor connection spec is the canonical GraphQL pagination shape — edges { cursor node }, pageInfo { hasNextPage hasPreviousPage startCursor endCursor }, and the first / after / last / before argument quartet. It maps cleanly onto forge cursors.

async function postsResolver(_: unknown, args: {
  first?: number; after?:  string;
  last?:  number; before?: string;
}) {
  const PAGE_SIZE = args.first ?? args.last ?? 20;
  const forward   = args.last == null;
  const opaque    = forward ? args.after : args.before;
  const cursor    = opaque ? decodeCursor(opaque) : undefined;

  const rows = await db.post.findMany({
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    forward ? PAGE_SIZE + 1 : -(PAGE_SIZE + 1),
    ...(cursor ? { cursor: { id: cursor } } : {}),
  });

  const hasMore = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);
  return {
    edges: visible.map(p => ({ cursor: encodeCursor(p.id), node: p })),
    pageInfo: {
      hasNextPage:     forward  ? hasMore : Boolean(args.after),
      hasPreviousPage: !forward ? hasMore : Boolean(args.before),
      startCursor: visible.length ? encodeCursor(visible[0].id)        : null,
      endCursor:   visible.length ? encodeCursor(visible.at(-1)!.id)   : null,
    },
  };
}

const encodeCursor = (id: string) => Buffer.from(id).toString('base64');
const decodeCursor = (c:  string) => Buffer.from(c, 'base64').toString();

The cursor is an opaque base64 string from the client's perspective — the Relay convention. If you ever need to switch from id-cursor to a compound (created_at, id)-cursor, encode a JSON blob inside the base64 and clients see the same opaque string. totalCount is optional in the Relay spec; set it only when the UI needs it.

---

REST response shapes — body, headers, Link

Three patterns are common; pick one and stick to it across the API.

1. Cursor in the body (recommended default). Self-describing, no header inspection, works through proxies that strip headers. Mirrors the GraphQL connection shape so client code is uniform.

{ "data": [ ... ], "pageInfo": { "hasNextPage": true, "hasPreviousPage": false, "nextCursor": "abc123", "prevCursor": null } }

async function listPosts(req: Request, res: Response) {
  const PAGE_SIZE = 20;
  const after = req.query.after as string | undefined;

  const rows = await db.post.findMany({
    where:   { status: 'PUBLISHED' },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE + 1,
    ...(after ? { cursor: { id: after } } : {}),
  });

  const hasNext = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);

  res.json({
    data: visible,
    pageInfo: {
      hasNextPage: hasNext,
      hasPreviousPage: Boolean(after),
      nextCursor: hasNext ? visible.at(-1)!.id : null,
      prevCursor: after   ? visible[0]?.id     : null,
    },
  });
}

2. Link header (RFC 5988, GitHub-style). Link: <…?after=abc123>; rel="next", <…?before=xyz789>; rel="prev" plus X-Total-Count: 12483. HATEOAS — the client follows hrefs without knowing the cursor format. Downsides: header parsing is annoying, some proxies strip non-standard headers, many clients ignore Link and read the body anyway.

3. Page number in the body (numbered pages). { data, pageInfo: { page, pageSize, totalRows, totalPages } }. Only when you really need numbered pages.

Cursor opacity. Raw id is simplest but anyone who knows your id format can synthesise cursors. Base64 JSON is opaque and easy to evolve (add fields). Signed JWT is tamper-proof — use when the cursor encodes filter state the client must not modify.

---

Infinite-scroll UX with TanStack Query

useInfiniteQuery from TanStack Query is the canonical client-side shape. The forge cursor maps to pageParam:

'use client';
import { useInfiniteQuery } from '@tanstack/react-query';
import { getDb } from '@/db';

export function PostFeed({ orgId }: { orgId: string }) {
  const q = useInfiniteQuery({
    queryKey: [orgId, 'post', 'feed'],
    initialPageParam: undefined as string | undefined,
    queryFn: async ({ pageParam }) => {
      const db = await getDb();
      const rows = await db.post.findMany({
        where:   { org_id: orgId, status: 'PUBLISHED', deleted_at: null },
        orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
        take:    21,                     // 20 + 1 for hasNext
        ...(pageParam ? { cursor: { id: pageParam } } : {}),
      });
      const hasNext = rows.length > 20;
      const visible = rows.slice(0, 20);
      return { rows: visible, nextCursor: hasNext ? visible[19].id : undefined };
    },
    getNextPageParam: (p) => p.nextCursor,
  });

  return (
    <>
      {q.data?.pages.flatMap(p => p.rows).map(p => <PostCard key={p.id} post={p} />)}
      {q.hasNextPage && (
        <button onClick={() => q.fetchNextPage()} disabled={q.isFetchingNextPage}>
          {q.isFetchingNextPage ? 'Loading…' : 'Load more'}
        </button>
      )}
    </>
  );
}

See React patterns for the search-as-you-type variant with useDeferredValue + useTransition.

For "auto-load on scroll-to-bottom", wire an intersection observer to fetchNextPage and throttle aggressively — without it, fast scrolls trigger multiple page loads before the first one resolves.

---

Pagination + filters — invalidation and filter-hashed cursors

Cursors are stable under writes. They are not stable under filter changes — a cursor anchored at row abc in the unfiltered list points nowhere meaningful in the filtered list, because the filtered list may not contain abc at all.

Reset to head on filter change. Simplest and almost always right. When the filter changes, drop the cursor and refetch from the first page. With TanStack Query, putting the filter in the queryKey does this for free — changing the filter mounts a fresh query starting at initialPageParam: undefined.

const [filter, setFilter] = useState('all');
const q = useInfiniteQuery({
  queryKey: [orgId, 'post', 'feed', filter],   // filter in the key
  // …
});

Filter hash inside the cursor. When the cursor must round-trip through stateless URLs (a paginated public list with shareable links), encode the filter state inside the cursor so the server can reject mismatched filter + cursor combinations:

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

function decodeCursor(c: string, expectedFilterHash: string) {
  const { id, f } = JSON.parse(Buffer.from(c, 'base64url').toString());
  if (f !== expectedFilterHash) throw new Error('Filter mismatch — cursor invalid');
  return id;
}

The hash is a stable digest of the filter object. The server recomputes it and rejects mismatched pairs, so a user who keeps a cursor in a bookmark and changes the filter on the page gets a 400 rather than a confusing result. For signed cursors, swap the hash for a server-signed JWT and reject unsigned cursors.

---

Pagination + soft-delete

Two ways soft-deleted rows interact with cursor pagination.

The cursor row gets deleted. Fine — the cursor is the boundary, not a member of the result. The next page starts at the first row whose key is strictly greater than the cursor row, independent of whether the cursor row still exists. Forge's default WHERE "deleted_at" IS NULL filters out deleted rows and the cursor inequality is applied independently.

The compound index that backs the cursor should be partial on deleted_at IS NULL — otherwise the index includes deleted rows, the scan reads them, and the filter discards them downstream:

const post = model('post', { /* … */ }, {
  indexes: [{ keys: { created_at: 1, id: 1 }, where: 'deleted_at IS NULL' }],
});

See Soft delete partial-filter indexes for the per-dialect emit. PG/SQLite/Mongo support partial indexes natively; MySQL needs the generated-column workaround.

---

Per-dialect pagination

The pagination predicates and limits compile differently on each engine. Behaviour is uniform from the caller's perspective; the wire-level cost and edge cases differ.

Postgres. LIMIT 20 OFFSET 40 for offset; WHERE "id" > $1 ORDER BY "id" LIMIT 20 for cursor; row-value tuple ("created_at", "id") < ($1, $2) for the compound form. ROW_NUMBER() over a window is sometimes proposed as an offset workaround — it is not faster, the planner still materialises every row up to the offset.

MySQL. Same LIMIT … OFFSET … shape, and the same row-value tuple as Postgres for a uniform-direction compound cursor — forge has no MySQL version detection, so it always emits the tuple. MySQL has supported row-value comparison since 5.7; on 5.6 the tuple form is a syntax error and there is no fallback. The deep-offset problem is worse than on PG — InnoDB secondary indexes carry the primary key as the leaf value, and OFFSET over a secondary index walks both. Switch to cursor sooner.

SQLite. Same LIMIT … OFFSET … shape. Tuple compare since 3.15 (2016), so every supported runtime including the sqlite-wasm browser adapter has it. OFFSET on SQLite is cheaper than PG/MySQL (in-process b-tree walk) but the scan cost is still O(skip + take).

DuckDB. DuckDB is a column store; OFFSET on a sorted column is sub-millisecond even at depth, because the engine seeks into the sorted chunk directly. Cursor still wins for stable boundaries under writes, but the deep-offset concern is less severe.

MSSQL. OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY — ORDER BY is mandatory; MSSQL refuses without it. T-SQL has no row-value comparison at all, which is what the Dialect.rowValueComparison: false flag records, so forge expands every compound cursor to the OR-of-AND form — including the uniform-direction case that every other SQL dialect gets as a tuple. The planner handles it well when the compound index is declared, and badly when it is not.

Mongo. .find(filter).sort(...).skip(40).limit(20) for offset; { _id: { $gt: lastId } } filter for a single-key cursor; $or lexicographic expansion for a compound cursor ($lt on descending keys, $gt on ascending). .skip() has the same trap as SQL OFFSET — and slower per row because of BSON deserialisation. hint() is sometimes necessary to force the index (the planner occasionally picks a covered sort over the cursor index); the forge adapter accepts a hint option on findMany. See MONGO.

---

Three worked examples

(a) Timeline cursor with (created_at, id)

Forward-paged feed with a non-unique sort key and a unique tie-breaker — the pattern under almost every "list of recent things".

async function postTimeline(input: { orgId: string; cursor?: string; limit?: number }) {
  const PAGE_SIZE = input.limit ?? 20;

  const rows = await db.post.findMany({
    where:   { org_id: input.orgId, status: 'PUBLISHED', deleted_at: null },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE + 1,
    ...(input.cursor ? { cursor: { id: input.cursor } } : {}),
    select: { id: true, title: true, created_at: true, author_id: true },
  });

  const hasNextPage = rows.length > PAGE_SIZE;
  const visible     = rows.slice(0, PAGE_SIZE);
  return {
    data: visible,
    pageInfo: { hasNextPage, nextCursor: hasNextPage ? visible.at(-1)!.id : null },
  };
}

Backing index — leading column is the multi-tenant scope, then the in-filter equality, then the sort tuple:

const post = model('post', { /* … */ }, {
  indexes: [{
    keys:  { org_id: 1, status: 1, created_at: 1, id: 1 },
    where: 'deleted_at IS NULL',
  }],
});

PG and SQLite emit a true partial index; MySQL falls back to the generated-column workaround (SOFT-DELETE).

(b) Admin table with numbered pages + count cache

Numbered pagination over a slowly-growing table. Count cached with event-driven invalidation.

import { lru } from 'tiny-lru';
const userCountCache = lru<number>(1000, 60_000);

db.$on('user.created',     (u) => userCountCache.set(`count:${u.org_id}`,
  (userCountCache.get(`count:${u.org_id}`) ?? 0) + 1));
db.$on('user.softDeleted', (u) => userCountCache.set(`count:${u.org_id}`,
  Math.max(0, (userCountCache.get(`count:${u.org_id}`) ?? 0) - 1)));

async function adminUsersPage(orgId: string, page: number) {
  const PAGE_SIZE = 25;

  let total = userCountCache.get(`count:${orgId}`);
  if (total == null) {
    total = await db.user.count({ where: { org_id: orgId } });
    userCountCache.set(`count:${orgId}`, total);
  }

  const rows = await db.user.findMany({
    where:   { org_id: orgId },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE,
    skip:    (page - 1) * PAGE_SIZE,
  });

  return {
    data: rows,
    pageInfo: {
      page, pageSize: PAGE_SIZE,
      totalRows:  total,
      totalPages: Math.ceil(total / PAGE_SIZE),
    },
  };
}

For a 50k-user tenant the OFFSET 1225 on page 50 is sub-millisecond on PG. Past ~500k rows per tenant, switch to cursor and drop the "page 50" affordance.

(c) Relay-style GraphQL connection

The resolver from GraphQL Relay connection mapping extended with multi-tenant scope and soft-delete:

posts: async (_, args: {
  first?: number; after?:  string;
  last?:  number; before?: string;
  orgId:  string;
}) => {
  const PAGE_SIZE = args.first ?? args.last ?? 20;
  const forward   = args.last == null;
  const opaque    = forward ? args.after : args.before;
  const cursor    = opaque ? decodeCursor(opaque) : undefined;

  const rows = await db.post.findMany({
    where:   { org_id: args.orgId, status: 'PUBLISHED', deleted_at: null },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    forward ? PAGE_SIZE + 1 : -(PAGE_SIZE + 1),
    ...(cursor ? { cursor: { id: cursor } } : {}),
  });

  const hasMore = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);
  return {
    edges: visible.map(p => ({ cursor: encodeCursor(p.id), node: p })),
    pageInfo: {
      hasNextPage:     forward  ? hasMore : Boolean(args.after),
      hasPreviousPage: !forward ? hasMore : Boolean(args.before),
      startCursor: visible.length ? encodeCursor(visible[0].id)        : null,
      endCursor:   visible.length ? encodeCursor(visible.at(-1)!.id)   : null,
    },
  };
};

The cursor is the raw row id, base64-encoded. Swap to a JSON blob if you need a compound cursor — clients see the same opaque string.

---

Common bugs

Cursor on a non-unique column. cursor: { created_at: lastSeen } on a non-unique created_at does not throw — nothing validates the cursor key. It runs, and duplicates or skips rows at every tie. Fix: sort by (created_at, id) and cursor on both.

Cursor keys that are not the orderBy keys. cursor: { id } with orderBy: { created_at: 'desc' } compares ids while the rows come back ordered by timestamp. It compiles and runs, and the pages are wrong. Cursor on the same columns you sort on, in the same order.

take + skip for "jump to page 1000". By page 50 the response triples; by page 1000 it is unusable. There is no fix for jump-to-page on a large table — accept the cost (small table), denormalise a page_number column at write time (awkward, fragile under deletes), or drop random-access and switch to cursor.

Cursor reused after filter change. A cursor anchored under filter A points nowhere under filter B. The visible bug is "I changed the filter and the page is empty or in the wrong order". Either reset to head on filter change (client-side queryKey trick) or include a filter hash in the cursor and reject mismatches server-side.

count + findMany serialised. Both queries hit the same pool — run them concurrently with Promise.all. Most ORMs serialise the two and double the latency by default.

Mongo .skip() at depth. Reads and discards every skipped document, slower per row than SQL OFFSET because of BSON deserialisation. skip: 10000 on a few-million-doc collection is hundreds of milliseconds. Cursor is the only workable answer at scale.

Adding skip: 1 to a cursor. Forge's cursor is exclusive, so the cursor row is already gone. skip: 1 on top of it silently drops the first row of every page — a row that then appears on no page at all. Prisma's cursor is inclusive and needs the skip; forge's does not. See The cursor is exclusive.

A backwards page with take: -N. Not implemented. take: -20 becomes LIMIT -20, which SQLite reads as no limit at all. Reverse the orderBy and reverse the array instead — see Bidirectional cursors.

Tuple compare on MSSQL/Mongo without the compound index. The expanded OR-of-AND form runs without a compound index, but the planner falls back to a sequential scan and the page load is O(n). Always declare the compound index on tables you paginate.

Offset + soft-delete double-cost. Without a partial index filtered on deleted_at IS NULL, the engine reads every skipped row including deleted ones, then filters them out post-scan. Declare the partial index from SOFT-DELETE — or switch to cursor.

---

See also

• Queries deep-dive — findMany operator reference, where / orderBy shapes, and the original cursor sketch.
• React patterns — TanStack Query useInfiniteQuery with debounced search.
• Backend patterns — HTTP envelopes, ETag, idempotency.
• Indexes — compound and partial indexes that back stable cursor pagination.
• Soft delete — partial indexes filtered on deleted_at IS NULL.
• Streaming — findManyStream for "paginate forever".
• Mongo — hint() and cursor-stage notes.
• MSSQL — OFFSET FETCH NEXT and tuple-compare expansion.