Cursor pagination with Drizzle

For Drizzle cursor pagination, call findByCursor(filters, { limit, sortBy, cursor }). It returns { data, limit, nextCursor }; pass nextCursor back as cursor to get the next page, until it is null. Each page is a WHERE on the sort columns of the last row instead of an OFFSET, so page 500 costs the same as page 1, and a row inserted on an earlier page does not shift the next one. This is also called keyset pagination.

How do I walk a list with findByCursor?Copy Anchor Link

Start without a cursor, then pass each page's nextCursor until it comes back null.

let cursor: string | null = null;
do {
  const page = await this.postsService.findByCursor(
    { status: 'published' },
    { sortBy: ['-published_at'], limit: 50, cursor },
  );
  await handle(page.data);
  cursor = page.nextCursor;
} while (cursor);

Filters, options.search, select, relations, counts, the tenant scope, soft delete and row locks all work as they do in findAll.

What does findByCursor return?Copy Anchor Link

The rows, the limit that was used, and the cursor of the next page.

interface CursorPage<T> {
  data: T[];
  limit: number;
  nextCursor: string | null; // null on the last page
  total?: number;            // only with count: true
}

There is no page number: a cursor is a position, not an index. The count query is skipped unless you pass count: true, because counting is the slow part of a large list. With count: true, total is the number of rows that match the filters, not the number left after the cursor.

Which options does findByCursor take?Copy Anchor Link

The second argument takes these fields, all optional.

How does the cursor work?Copy Anchor Link

The cursor holds the sort values of the last row on the page, primary key last, plus the sort it was made for. The next query asks for rows that come after that position. With sortBy: ['views'] on a not-null column:

WHERE (views > $1) OR (views = $1 AND id > $2)
ORDER BY views ASC, id ASC
LIMIT 51

How each part keeps pages correct:

The cursor itself is base64url JSON, built in src/core/utils/cursor.ts. The cursor eyJzIjoibmFtZTphc2MsaWQ6YXNjIiwidiI6WyJDaGFkIiw1MF19 decodes to {"s":"name:asc,id:asc","v":["Chad",50]}: the sort signature and the last row's name and id.

What happens with a bad cursor?Copy Anchor Link

It is a 400 (BadRequestException). These all fail:

Changing the sort mid-walk breaks the position, so start again from the first page.

How do I use cursor pagination over HTTP?Copy Anchor Link

With parseQuery, a cursor (or after) key in the query string starts a keyset walk and sets isCursor. An empty ?cursor= means the first page.

GET /countries?limit=20&cursor=
GET /countries?limit=20&cursor=eyJzIjoibmFtZTphc2MsaWQ6YXNjIiwidiI6WyJDaGFkIiw1MF19
GET /countries?limit=20&cursor=&count=true

One controller method can serve both kinds of list:

@Get()
findAll(@Query() query: Record<string, unknown>) {
  const q = this.countries.parseQuery(query);
  return q.isCursor
    ? this.countries.findByCursor(q.filters, q.pagination, q.options)
    : this.countries.findAll(q.filters, q.pagination, q.options);
}

When should I use offset pagination instead?Copy Anchor Link

When the UI shows page numbers or jumps to a given page. Keyset pages only go forward from a known position. For infinite scroll, exports, sync jobs and any list that grows while people read it, use the cursor.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link