SQL Through Your Own Endpoint

An endpoint you already run in front of Postgres, MySQL, SQL Server or SQLite can answer a grid directly: turn the request createPushdownSource hands your adapter into one parameterised statement, and only the matching page comes back. No ORM, no query builder, and no value is ever written into a string.

The code

From the backend recipes repository, the Node and Postgres recipe. translate/postgres.js is a pure function: no connection, no pg import, so it is unit-tested against recorded SQL shapes with no database reachable at all. Quoted in full, not shortened:

translate/postgres.js

/**
 * Translate a wire-contract request into Postgres SQL (`$1`, `$2`, ...
 * placeholders, `ILIKE` for a case-insensitive `contains`).
 *
 * A pure function - no connection, no `pg` import - so it is unit-tested
 * against recorded shapes with no database reachable, which is exactly the
 * situation this recipe is in on a box with no Postgres installed. Point
 * `server.js` at a real one (`DATABASE_URL`) to see it actually run; see this
 * repo's README for why the proof script instead runs the SQLite recipe's
 * server for its headless count-equality check.
 */

const SQL_OP = {
  eq: '=', neq: '!=', gt: '>', gte: '>=', lt: '<', lte: '<=', contains: 'ILIKE',
};

/**
 * Render one filter node, appending `$n` placeholders as it goes.
 * @param {object|null} node the filter node
 * @param {unknown[]} params collected in place
 * @returns {string} the SQL fragment
 */
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}" ILIKE $${params.length}`;
  }
  params.push(node.value);
  return `"${node.field}" ${op} $${params.length}`;
}

/**
 * Build the SELECT and matching COUNT(*), both parameterised for `pg`.
 * @param {{filters?: object|null, sort?: Array<{col: string, dir: string}>,
 *   range?: {start: number, end: number}, quick?: string}} request the request
 * @returns {{select: {text: string, values: unknown[]},
 *   count: {text: string, values: unknown[]}}} both statements
 */
export function translate(request) {
  const { filters = null, sort = [], range = null, quick = '' } = request || {};
  const params = [];
  let where = renderFilter(filters, params);
  if (quick) {
    const cols = ['customer', 'region', 'status'];
    const parts = cols.map((c) => { params.push(`%${quick}%`); return `"${c}" ILIKE $${params.length}`; });
    where = `${where} AND (${parts.join(' OR ')})`;
  }
  const countValues = [...params];
  let text = `SELECT * FROM orders WHERE ${where}`;
  if (sort.length) {
    text += ` ORDER BY ${sort.map((s) => `"${s.col}" ${s.dir === 'desc' ? 'DESC' : 'ASC'}`).join(', ')}`;
  }
  if (range) {
    params.push(range.end - range.start, range.start);
    text += ` LIMIT $${params.length - 1} OFFSET $${params.length}`;
  }
  return {
    select: { text, values: params },
    count: { text: `SELECT COUNT(*)::int AS total FROM orders WHERE ${where}`, values: countValues },
  };
}

server.js

#!/usr/bin/env node
/**
 * The Node + Postgres recipe server.
 *
 * **Run this against your own Postgres instance**: set `DATABASE_URL` and
 * `translate/postgres.js`'s real SQL runs through `pg`. Without it, this
 * serves the fixed dataset from an in-memory SQLite copy instead - the
 * "tiny reference server over a SQLite file" this repo's README explains -
 * so there is still something to point the grid at on a box with no
 * Postgres installed, and the conformance/headless proof below still has a
 * server to run against. The `translate/postgres.js` module itself is
 * unit-tested separately (`test/translate.test.js`) against recorded SQL
 * shapes, with no database at all.
 */
import { createServer } from 'node:http';
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';
import { translate as translatePostgres } from './translate/postgres.js';
import { translate as translateReference } from '../node-sqlite/translate/sqlite.js';

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

let pgPool = null;
if (process.env.DATABASE_URL) {
  try {
    const { Pool } = await import('pg');
    pgPool = new Pool({ connectionString: process.env.DATABASE_URL });
    console.log('[node-postgres] DATABASE_URL set - running translate/postgres.js against a real Postgres');
  } catch (error) {
    console.error(`[node-postgres] DATABASE_URL is set but the "pg" package is not installed (${error.message}); `
      + 'falling back to reference mode. Run `npm install pg` in this recipe to use a real instance.');
  }
}

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);
if (!pgPool) {
  console.log(`[node-postgres] reference mode: serving ${dataset.length} rows from an embedded SQLite copy of `
    + 'the fixture dataset. Set DATABASE_URL to run translate/postgres.js against your own instance.');
}

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

/**
 * Answer one query, from Postgres when connected, or the SQLite reference
 * copy otherwise.
 * @param {object} request the wire-contract request
 * @returns {Promise<{rows: unknown[], total: number}>} the answer
 */
async function answer(request) {
  if (pgPool) {
    const { select, count } = translatePostgres(request);
    const rowsResult = await pgPool.query(select.text, select.values);
    const countResult = await pgPool.query(count.text, count.values);
    return { rows: rowsResult.rows, total: countResult.rows[0].total };
  }
  const { select, count } = translateReference(request);
  const rows = db.prepare(select.sql).all(...select.params);
  const total = db.prepare(count.sql).get(...count.params).total;
  return { rows, total };
}

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', async () => {
      try {
        const request = body ? JSON.parse(body) : {};
        res.writeHead(200, { 'content-type': 'application/json' });
        res.end(JSON.stringify(await answer(request)));
      } 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-postgres] listening on http://localhost:${PORT}`));
process.on('SIGTERM', () => server.close(() => process.exit(0)));

Run it against a real instance: cd recipes/node-postgres, export DATABASE_URL=postgres://user:pass@host/db, npm install pg, then node server.js. Without a DATABASE_URL the same server answers from an embedded SQLite copy of the fixture data instead, so the page still runs with nothing installed; translate/postgres.js itself never touches that fallback.

What is pushed, what stays in the browser

This translate function pushes the filter tree (six comparisons and a case-insensitive contains), a multi-column sort, the row window and an exact COUNT(*). It does not push a group or an aggregate: there is no GROUP BY branch in the SQL above, and the recipe's own adapter declares nothing for group or aggregates. Group a column in the grid against this server and the source has to hold the whole matching set to group it in the browser instead of asking Postgres for one grouped level at a time.

Extending the recipe to push a group is the same shape again: read query.groupBy (the columns to group by) and query.totalFns (which statistic sums, averages or counts each totalled column) and add a GROUP BY clause and the matching aggregate expressions to the same parameterised builder, answered through executeGroupLevel rather than execute. Nothing about the wire shape changes; only how much of it this particular translate function currently answers.

The warning a host sees

Group a column against this server as it ships, and the source says so once, by name:

the node-postgres-recipe adapter cannot push group, 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.

Dialect appendix: MySQL, SQLite and SQL Server

The shape, one translate function building a parameterised statement, does not change between databases; the syntax it emits does. This repository's own SQLite recipe is a second real dialect built the same way, and the two together show exactly where a port to MySQL or SQL Server differs:

Dialect	Placeholder	Case-insensitive contains	Paging
Postgres (above)	$1, $2, ...	ILIKE	LIMIT $n OFFSET $n
SQLite	?	LIKE (case-insensitive on ASCII by default)	LIMIT ? OFFSET ?
MySQL	?	LIKE, case-insensitive under the common *_ci collations	LIMIT ? OFFSET ?
SQL Server	@p1, @p2, ... (named, via the mssql driver)	LIKE, case-insensitive under the common case-insensitive collations	ORDER BY ... OFFSET @start ROWS FETCH NEXT @count ROWS ONLY

A MySQL port is the closest to the SQLite recipe already in this repository: swap the placeholder style for ? and keep LIKE, since MySQL's common collations are already case-insensitive. A SQL Server port keeps the same filter tree walk but renders named @p parameters instead of positional ones, and replaces LIMIT/OFFSET with OFFSET ... FETCH NEXT ... ROWS ONLY, which needs an explicit ORDER BY to be valid syntax at all. Neither port ships in the repository today; both follow the same pure-function shape as the Postgres and SQLite recipes already there, checked against the same conformance script.

Ports wanted

Contributions are welcome for PHP, .NET (C#) and Java, and for the MySQL and SQL Server dialects above, following the repository's own "Ports wanted" section: a translate module, a thin server answering POST /query with { rows, total }, and a green run of the shared conformance script.

See REST API paging for the same wire shape over the zero-setup SQLite recipe, or the pushdown developer guide for how a grouped or pivoted level is answered once an adapter does push it.