Cursor pagination
Keyset (cursor) pagination asks for "the rows after this one" instead of "skip
N rows". It stays fast on large tables and doesn't skip or repeat rows when
someone inserts while a client is paging. This guide builds a list endpoint
that returns a page of rows plus a nextCursor the client sends back.
The loop
- Read
sizeand theaftercursor from the request. cursorPaginate()turns them into alimit, awhereand anorder.- Run the query for
limit + 1rows. The extra row tells you whether there is a next page. - Keep the first
limitrows andbuildCursor()the last one you keep. - Return the rows and the cursor. The client passes it back as
after.
Build the query
import { cursorPaginate } from '@rtorcato/db-common'
const query = cursorPaginate({ size: 20, column: 'id', after: params.get('after') ?? undefined })
// first page: { limit: 20, where: [], order: [{ col: 'id', dir: 'asc' }] }
// later pages: { limit: 20, where: [{ col: 'id', op: 'gt', val: 42 }], order: [...] }
after comes straight from the query string, so cursorPaginate() never
throws on it. A cursor that doesn't decode, or that decodes to anything but a
string, number or boolean, counts as no cursor and you get the first page.
size is clamped to at least 1 and floored. It is not capped, so put your own
ceiling on it before you pass it in:
const size = Math.min(Number(params.get('size')) || 20, 100)
Run it and build the next cursor
With Drizzle, toDrizzleCursor() maps the query for you:
import { buildCursor, cursorPaginate } from '@rtorcato/db-common'
import { toDrizzleCursor } from '@rtorcato/db-drizzle'
import { users } from './schema'
export async function listUsers(params: URLSearchParams) {
const size = Math.min(Number(params.get('size')) || 20, 100)
const query = cursorPaginate({ size, column: 'id', after: params.get('after') ?? undefined })
const { limit, where, orderBy } = toDrizzleCursor(query, { id: users.id })
const rows = await db
.select()
.from(users)
.where(where)
.orderBy(...orderBy)
.limit(limit + 1)
const hasNext = rows.length > limit
const data = hasNext ? rows.slice(0, limit) : rows
const nextCursor = hasNext ? buildCursor(data[data.length - 1], 'id') : null
return { data, nextCursor }
}
On another driver, map query.where and query.order yourself. Each
condition is a plain { col, op, val } and each sort is { col, dir }.
Pick the right column
- Unique. The cursor is the last row's value in one column. If two rows
share that value, the page boundary can skip or repeat one of them.
idis the safe default. - It is the sort.
cursorPaginate()sets theorderitself, by that column. Don't sort by something else and keyset onid. - Direction. Pass
dir: 'desc'for newest-first. The predicate flips toltto match.
cursorPaginate({ size: 20, column: 'id', dir: 'desc', after })
// later pages: where: [{ col: 'id', op: 'lt', val: 42 }]
buildCursor(row, ['createdAt', 'id']) already encodes a composite cursor, but
cursorPaginate() only reads a single value today. Paging on a non-unique
column such as createdAt needs that support first. Until then, keyset on a
unique column.
Combine with filters
The keyset predicate is a Condition[], the same shape where() returns. To
page through a filtered list, concatenate the two before you map them:
import { toDrizzleOrderBy, toDrizzleWhere } from '@rtorcato/db-drizzle'
const filters = allow(where(filterInput), ['status'])
const query = cursorPaginate({ size, column: 'id', after })
const cols = { id: users.id, status: users.status }
const rows = await db
.select()
.from(users)
.where(toDrizzleWhere([...filters, ...query.where], cols))
.orderBy(...toDrizzleOrderBy(query.order, cols))
.limit(query.limit + 1)
Keep the filters the same on every page. A cursor from one filter set means
nothing under another. See Filters from query strings
for building filterInput.