Cursor Pagination

findManyPage() reads one page of rows past a cursor. With an offset ($skip) the engine reads and discards 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 and types the items the same way, with these differences:

• $limit is the page size, a positive whole number.
• $after or $before names the cursor, never both.
• $skip and $distinct are refused.

hasPrevPage only says whether the page was read past a cursor. 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 every column of a unique, non-partial index over such fields. { 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, and a JSON, vector or binary column are 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	yes (GET /page)	no

Both run @AfterLoad 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 loses 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. Declare nullable: false on sort keys that never hold null, the leading one above all. On MySQL, MariaDB and SQL Server, also leave out the placement, since it is an expression no index serves.

The cursor

A cursor is an opaque string holding the page’s sort values exactly, Date and bigint included, plus a fingerprint of the sort: passed to a query sorted another way, it throws. A cursor holds a timestamp to the millisecond, 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,
});