REST API Paging for a Data Grid

A grid never needs a special server to page, sort, filter or search: a plain HTTP endpoint that answers one JSON shape is enough. createPushdownSource hands your adapter the window the grid is showing, the sort it wants and whatever filter is active; your adapter POSTs that as JSON and hands back { rows, total }. Everything else, the paging control, the column headers, the scroll position, is the grid's own.

What a request carries

The adapter below sends four fields, straight from the pushed half of the plan: range (a half-open row window, { start, end }, both ends counted from zero), sort (an array of { col, dir }, outermost first), filters (a single condition or a nested and/or group) and quick (the free-text box, as a plain string). The server turns those four fields into one parameterised query and one count, and answers with exactly the rows for that window, never the whole table.

The code

This is the zero-setup recipe from the backend recipes repository: Node 22's own node:sqlite and node:http, nothing to install. It is one of four store recipes there, all built from the same two files: a pure translate function and a thin server. Every line below is the recipe's own, not a shortened stand-in for it.

translate/sqlite.js

/**
 * Translate a wire-contract request into SQLite SQL.
 *
 * A pure function: no connection, no I/O, so it is unit-testable against
 * recorded shapes with nothing running. `server.js` is the only thing that
 * executes what this returns.
 */

/** @type {Record<string, string>} the wire operator to its SQL form. */
const SQL_OP = {
  eq: '=', neq: '!=', gt: '>', gte: '>=', lt: '<', lte: '<=', contains: 'LIKE',
};

/**
 * Render one filter node as SQL, collecting bound parameters as it goes.
 * @param {object|null} node the filter node (a condition or `and`/`or` group)
 * @param {unknown[]} params collected in place
 * @returns {string} the SQL fragment, or `'1=1'` for no filter
 */
function renderFilter(node, params) {
  if (!node) return '1=1';
  if (node.op === 'and' || node.op === 'or') {
    const parts = node.conditions.map((c) => renderFilter(c, params));
    return `(${parts.join(node.op === 'and' ? ' AND ' : ' OR ')})`;
  }
  const op = SQL_OP[node.op];
  if (!op) throw new Error(`unknown operator "${node.op}"`);
  if (node.op === 'contains') {
    params.push(`%${node.value}%`);
    return `"${node.field}" LIKE ?`;
  }
  params.push(node.value);
  return `"${node.field}" ${op} ?`;
}

/**
 * Build the SELECT and the matching COUNT(*), both parameterised.
 * @param {{filters?: object|null, sort?: Array<{col: string, dir: string}>,
 *   range?: {start: number, end: number}, quick?: string}} request the request
 * @returns {{select: {sql: string, params: unknown[]},
 *   count: {sql: string, params: unknown[]}}} both statements
 */
export function translate(request) {
  const { filters = null, sort = [], range = null, quick = '' } = request || {};
  const params = [];
  let where = renderFilter(filters, params);
  if (quick) {
    // The reference evaluator searches every column; this recipe's table has
    // five, and OR-ing a LIKE per column is the direct SQL equivalent.
    const cols = ['customer', 'region', 'status', 'created_at'];
    const parts = cols.map((c) => { params.push(`%${quick}%`); return `CAST("${c}" AS TEXT) LIKE ?`; });
    params.push(`%${quick}%`);
    parts.push('CAST("amount" AS TEXT) LIKE ?');
    where = `${where} AND (${parts.join(' OR ')})`;
  }
  const countParams = [...params];
  let sql = `SELECT * FROM orders WHERE ${where}`;
  if (sort.length) {
    sql += ` ORDER BY ${sort.map((s) => `"${s.col}" ${s.dir === 'desc' ? 'DESC' : 'ASC'}`).join(', ')}`;
  }
  if (range) {
    sql += ' LIMIT ? OFFSET ?';
    params.push(range.end - range.start, range.start);
  }
  return {
    select: { sql, params },
    count: { sql: `SELECT COUNT(*) AS total FROM orders WHERE ${where}`, params: countParams },
  };
}

server.js

#!/usr/bin/env node
/**
 * The zero-setup recipe: a custom Lattice adapter backed by SQLite, using
 * only what Node 22 ships (`node:http`, `node:sqlite`). No install step, no
 * external database - `node server.js` and there is something to point the
 * grid at.
 */
import { createServer } from 'node:http';
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';
import { translate } from './translate/sqlite.js';

const PORT = Number(process.env.PORT) || 8804;
const dataset = JSON.parse(readFileSync(fileURLToPath(new URL('../../fixtures/dataset.json', import.meta.url)), 'utf8'));

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE orders (id INTEGER, customer TEXT, region TEXT, amount REAL, status TEXT, created_at TEXT)');
const insert = db.prepare('INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)');
for (const row of dataset) {
  insert.run(row.id, row.customer, row.region, row.amount, row.status, row.created_at);
}
console.log(`[node-sqlite] loaded ${dataset.length} rows into an in-memory SQLite database`);

const indexHtml = readFileSync(fileURLToPath(new URL('./index.html', import.meta.url)));

const server = createServer((req, res) => {
  if (req.method === 'GET' && req.url === '/') {
    res.writeHead(200, { 'content-type': 'text/html; charset=utf-8' });
    res.end(indexHtml);
    return;
  }
  if (req.method === 'POST' && req.url === '/query') {
    let body = '';
    req.on('data', (chunk) => { body += chunk; });
    req.on('end', () => {
      try {
        const request = body ? JSON.parse(body) : {};
        const { select, count } = translate(request);
        const rows = db.prepare(select.sql).all(...select.params);
        const total = db.prepare(count.sql).get(...count.params).total;
        res.writeHead(200, { 'content-type': 'application/json' });
        res.end(JSON.stringify({ rows, total }));
      } catch (error) {
        res.writeHead(400, { 'content-type': 'application/json' });
        res.end(JSON.stringify({ error: String(error && error.message ? error.message : error) }));
      }
    });
    return;
  }
  res.writeHead(404);
  res.end();
});

server.listen(PORT, () => {
  console.log(`[node-sqlite] listening on http://localhost:${PORT}`);
});

process.on('SIGTERM', () => server.close(() => process.exit(0)));

The page itself is a real createGrid, pointed at that server:

const adapter = {
  name: 'node-sqlite-recipe',
  capabilities: {
    filter: 'tree', operators: ['eq', 'neq', 'gt', 'gte', 'lt', 'lte', 'contains'],
    sort: 'multi', quick: true, range: true, total: true,
  },
  async execute(query, request) {
    const res = await fetch('/query', {
      method: 'POST',
      headers: { 'content-type': 'application/json' },
      body: JSON.stringify({
        filters: query.filters, sort: query.sort, range: query.range, quick: query.quick,
      }),
      signal: request && request.signal,
    });
    return res.json();
  },
};

const grid = createGrid(document.getElementById('grid'), {
  columns: [
    { field: 'id', title: 'ID', type: 'number' },
    { field: 'customer', title: 'Customer' },
    { field: 'region', title: 'Region' },
    { field: 'amount', title: 'Amount', type: 'number', format: 'currency:USD:2' },
    { field: 'status', title: 'Status' },
    { field: 'created_at', title: 'Created', type: 'date' },
  ],
  rowKey: 'id',
  source: createPushdownSource({ adapter }),
});

Run it: cd recipes/node-sqlite && node server.js, then open http://localhost:8804. No database to install, no environment variable to set; the server seeds an in-memory table on the way up.

What is pushed, what stays in the browser

The adapter declares every capability it implements: a full filter tree with seven operators, a multi-column sort, the quick box, the row window and the exact total. Because the declaration matches what translate/sqlite.js actually does, every interaction the grid offers, typing into a filter box, clicking a column header, scrolling, becomes one SQL statement rather than a client-side pass over rows already in the browser. Nothing beyond the current page ever crosses the network.

Widen operators without widening translate to match, or drop a capability the grid still expects, and the split breaks: a request that cannot be pushed is finished over whatever the adapter already returned instead of the true matching set, which is the wrong rows silently rather than merely slow rows. The fix is always to keep the two in step.

The warning a host sees

Remove a declared capability, quick: true say, without removing the quick box from the grid's own configuration, and the source falls back to finishing that filter over whatever window it already holds rather than the whole matching set. It says so once, by name:

the node-sqlite-recipe adapter cannot push quick, so every matching row is fetched and the rest is applied in the browser. Correct, and slower than it needs to be: widening the adapter is the fix.

That is the grid's own residual warning, keyed to the adapter's name and the exact part it could not push, so the fix it names is never a guess.

Ports wanted

This shape, one translate file plus one thin server, is deliberately small so a port to a language this repository does not yet cover is one afternoon's work, checked against the same 216-request conformance script every recipe here already passes. PHP, .NET and Java are open; see the repository's own "Ports wanted" section for what a new one needs to answer.

See the pushdown developer guide for the full adapter surface, or the SQL through your own endpoint guide for the same shape over Postgres, with a dialect appendix for MySQL, SQLite and SQL Server.