Pagination That Stays Constant

Twenty thousand rows exist and none of them have been on screen. This lesson puts ten of them there, adds sortable columns, and pages through the rest — while keeping the amount of data crossing the worker boundary fixed at exactly one page, no matter what is selected. Along the way, one parameter refuses to be bound and one column type arrives in a shape you did not ask for. Checkpoint: engine-06.

Let the database do the paging

There are two places to paginate, and only one of them scales. You can fetch every matching row and slice it in JavaScript, or you can ask for the slice:

const rows = await runPrepared(
    `SELECT date, item, amount FROM sales WHERE ${filterClause(fruits)}
     ORDER BY ${orderBy} ${direction}, date ASC LIMIT ? OFFSET ?`,
    [startISO, endISO, ...fruits, limit, offset],
);

LIMIT and OFFSET bind like any other value, so a page of ten costs ten rows across the boundary whether the filter matches twenty thousand or two hundred. The engine sorts and slices where the data already is.

The trailing , date ASC is a tiebreaker, and it is doing quiet work. Sorting by item leaves thousands of rows tied, and SQL makes no promise about the order of tied rows — page two could contain a row you already saw on page one, because nothing forces a stable order between the two queries. A tiebreaker on a column with many distinct values makes pagination deterministic. Skip it and you get a bug that only appears when a user pages through a sorted view, which is to say the bug appears in front of a user and never in front of you.

The parameter you cannot bind

The obvious next move is to bind the sort column too, and it does not work:

ORDER BY ? ASC        -- sorts every row by the constant string 'date'

A prepared statement is planned before its values arrive, and the planner has to know which column it is ordering by to make a plan at all. A bound value is data; a column name is structure. Passing 'date' there gives the planner a string constant, identical for every row, and a sort that does nothing.

So the column name must be part of the SQL text — which means it must never be the raw string the client sent. A lookup table is the whole defence:

// Sort columns are interpolated into SQL, so they pass through a whitelist —
// never the raw client string.
const SORT_COLUMNS: Record<SortColumn, string> = { date: 'date', item: 'item', amount: 'amount' };

const orderBy = SORT_COLUMNS[sortColumn] ?? 'date';
const direction = sortDirection === 'desc' ? 'DESC' : 'ASC';

The type SortColumn = 'date' | 'item' | 'amount' makes wrong values a compile error, and the ?? 'date' makes them harmless at runtime too, because types are erased at the boundary — a hand-written postMessage or a JSON.parse can put anything in that slot. Direction gets the same treatment as an explicit ternary rather than a passthrough: the only two strings that can reach the SQL are ones this file contains.

The rule generalizes. Values bind; identifiers get whitelisted. Every “dynamic SQL” injection you have read about is someone discovering the first half and improvising the second.

Arrow hands back what it wants

Query results arrive as Apache Arrow record batches — a columnar format, which is why a columnar engine is fast at handing them over. Converting to JavaScript is where the surprises live:

// Arrow hands DATE columns back as Date objects or epoch numbers depending on
// the bundle; normalize to an ISO date string at the boundary.
function toISODate(value: unknown): string {
    if (typeof value === 'string') return value;
    const date = value instanceof Date ? value : new Date(Number(value));
    return date.toISOString().split('T')[0];
}

A DATE column does not come back as '2025-03-14'. Depending on the bundle and version it is a Date, or a number of days since the epoch, or occasionally a string. Writing the code that works on your machine and shipping it means shipping a bug that appears on someone else’s browser — the mvp bundle you never test on, because your laptop always gets eh.

Normalizing at the boundary means the rest of the application handles one type:

return rows.map((r): SalesRow => [toISODate(r.date), String(r.item), Number(r.amount)]);

SalesRow is a labelled tuple, [date: string, item: string, amount: number]. A tuple rather than an object because ten thousand of these cross a worker boundary in the dashboard course, and three array slots clone faster than three keyed properties — with the labels keeping it readable at the call site.

String() and Number() around the other two are the same defensive move as toISODate: the values are typed on paper, and the paper is on the far side of a message channel.

Build it

File	Action	What goes in it
`src/types.ts`	modify	`SalesRow`, `SortColumn`, `SortDirection`; add `queryPaginated` to `DbWorkerApi`
`src/db-worker.ts`	modify	`toISODate`, `SORT_COLUMNS`, `queryPaginated` with `LIMIT`/`OFFSET` and the tiebreaker; expose it
`index.html`	modify	A table with three `scope="col"` headers, each wrapping a `data-sort` button, plus prev/next controls and a page-info element
`src/index.ts`	modify	Local page/sort state, a `renderPage()` that queries and fills the tbody, and handlers for the pager and sort buttons
`src/style.css`	modify	Plain table, button and pager styles

Done when: the table shows ten rows, the page indicator reads Page 1 of 2,000 against 20,000 rows, paging forward and back works, and sorting by amount puts 5 at the top ascending and 45 at the top descending — the exact bounds randInt(5, 45) seeded.

Answer key: engine-05...engine-06.

Challenge: delete the tiebreaker

Remove , date ASC from the ORDER BY, sort by item, and page through the first several pages while writing down the rows you see. With eight items and thousands of ties, repeats and omissions across page boundaries are likely. Then put it back. Non-deterministic pagination is a genuinely hard bug to diagnose from a report that says only “some rows are missing sometimes”.

Next lesson: aggregates, and a window function that looks like a typo.