Cursor Pagination

findManyPage() reads one page of rows past a cursor. An offset ($skip) makes the engine read and discard every earlier row, so page 10,000 of 50 reads 500,000 rows, and an insert shifts every later page. A cursor page costs the same at any depth, and inserts shift nothing.

import { Post } from './shared/models/index.js';
import { pool } from './uql.config.js';

const query = {
  $select: { title: true },
  $sort: { createdAt: 'desc', id: 'asc' },
  $limit: 20,
} as const;

const first = await pool.findManyPage(Post, query);
// { items, startCursor, endCursor, hasNextPage, hasPrevPage }

const next = await pool.findManyPage(Post, {
  ...query,
  $after: first.endCursor,
});
const back = await pool.findManyPage(Post, {
  ...query,
  $before: next.startCursor,
});

It takes the same query as findMany, with $limit as the page size and no $skip, and types the items the same way. hasPrevPage only says whether the page was read past a cursor, and nothing is counted: call count with the same $where for a total.

The sort must be total

Two rows that tie on every sort value can be skipped or read twice, so $sort must include something unique: the primary key, a unique field declared nullable: false, or all columns of such a unique index. { createdAt: 'desc' } alone throws and names the fix; { createdAt: 'desc', id: 'asc' } works. Directions can be mixed.

Only the entity’s own fields can be sort keys: a relation’s field, a JSON path, a relevance, a vector distance, an aggregate, or a JSON, vector or binary column is refused. So is a raw $select, since the cursor is read off the selected fields.

Page or stream?

	findManyPage	findManyStream
Reads	one page per call, one statement each	every row, from one statement
Between reads	holds nothing: the cursor is a string you keep	holds a connection for the whole loop
Consistency	per page; a row whose sort value changes may repeat or be missed	one snapshot of the whole result
Query	a total sort, on the entity’s own fields	any query
Resumes	yes, from any cursor, in another request	no
Over HTTP, hooks	yes (GET /page), afterLoad fires	no; afterLoad fires on each row

Page what a person scrolls or an API client requests; stream what one process reads once, such as an export. On libSQL, D1, and Bun SQL on MySQL or MariaDB a stream buffers the whole result, so page there when it does not fit in memory.

Nullable sort keys

A nullable sort key pages correctly wherever the engine or a placement puts its nulls, but it costs the index: a deep page reads as much as an offset would. On Postgres with 200,000 rows, a mid-table page took 15.4 ms with a nullable leading key and 0.05 ms with the same key declared nullable: false. So declare nullable: false on sort keys that never hold null, the leading one above all. On MySQL, MariaDB and SQL Server, page by non-null keys without a placement, since a placement is an expression no index serves.

The cursor

A cursor is an opaque string holding the page’s sort values, Date and bigint included, exactly, plus a fingerprint of the sort: passed to a query sorted another way, it throws. A timestamp key holds milliseconds, as a Date does, so a field declared with a higher precision cannot be a key.

A cursor survives inserts and deletes, but a row whose sort value changes between two pages can appear twice or not at all.

Cursors are encoded, not signed

Anyone holding a cursor can decode its sort values. Over HTTP, a client sorting by a field reads that field’s values from the cursor, even if a post hook strips it from the rows. Keep sensitive fields out of $sort with a preFilter hook.

Over HTTP

The HTTP handler serves pages at GET /<entity>/page, and the browser client returns { data: page }:

import { HttpQuerier } from 'uql-orm/browser';

const querier = new HttpQuerier('/api');
const query = { $sort: { id: 'asc' }, $limit: 20 } as const;

const { data: first } = await querier.findManyPage(Post, query);
const { data: next } = await querier.findManyPage(Post, {
  ...query,
  $after: first.endCursor,
});