SQL Engine (glove-sql)

glove-sql is a zero-dependency, pure-JS Postgres-subset SQL engine. Tables are built at runtime from whatever data you ingest — there is no fixed schema — and a small engine (tokenizer → recursive-descent parser → evaluator) runs a defined subset of Postgres over them. The whole store serialises to bytes and back ("computation as a value"), with none of a real database's data-dir overhead.

It is the default backend for glove-scratchpad, extracted into its own package so the SQL surface can be tested and grown independently. A consumer can also bring its own backend (real Postgres, SQLite, PGlite) that speaks the same subset.

Quick start

Install the package:

pnpm add glove-sql

Create a backend, run DDL and DML, then query with $n parameters. Window functions, CTEs, joins, and the rest of the subset behave as you'd expect from Postgres:

import { MemoryBackend } from "glove-sql";

const db = await MemoryBackend.create();
await db.exec(`CREATE TABLE "t" ("id" bigint, "name" text, "score" double precision)`);
await db.query(`INSERT INTO "t" VALUES ($1,$2,$3),($4,$5,$6)`, [1, "Ada", 9.5, 2, "Linus", 8.0]);

const { rows } = await db.query(
  `SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM "t"`,
);
// → [{ name: "Ada", rank: 1 }, { name: "Linus", rank: 2 }]

const bytes = await db.dump();                      // serialise…
const restored = await MemoryBackend.create({ load: bytes }); // …and restore

dump() serialises the entire store to a Uint8Array; create({ load }) rebuilds it. The same engine state moves across processes, workers, or a network as a plain byte array — computation as a value, with no data-dir to manage.

What it covers

The engine speaks a defined Postgres subset. Each area below is exercised by the package's test suite:

Anything outside the subset throws a clear error rather than silently mis-answering — you get an exception, never a wrong answer. For the long tail (recursive CTEs, GROUPING SETS, DISTINCT ON, explicit window frames, …), PgliteBackend (glove-scratchpad/pglite) — a real Postgres compiled to WASM that implements the same SqlBackend contract — is the escape hatch. Swap the backend and keep the rest of your code unchanged.

API

The surface is two interfaces and one class. SqlBackend is the minimal contract an embedded engine exposes; SqlResult is what a query returns (rows plus the output field names, in order); MemoryBackend is the zero-dependency pure-JS implementation.

interface SqlResult {
  rows: Record<string, unknown>[];
  fields: { name: string }[];
}

interface SqlBackend {
  query(sql: string, params?: unknown[]): Promise<SqlResult>;
  exec(sql: string): Promise<void>;
  dump(): Promise<Uint8Array>;
  close(): Promise<void>;
}

interface MemoryBackendOptions {
  load?: Uint8Array;
}

class MemoryBackend implements SqlBackend {
  static create(opts?: MemoryBackendOptions): Promise<MemoryBackend>;
}

Known limitations

The engine's correctness is tracked by an adversarial audit (AUDIT.md in the package). Two limitations are won't-fix by design, both rooted in value-level type erasure:

• Integer division returns a float. The engine erases the int-vs-float distinction at the value level, so it can't replicate Postgres's int / int → int truncation.
• SUM over bigint loses precision above 2^53. Values are JS numbers, so large-magnitude bigint sums drift past the safe-integer boundary.

Beyond these, the audit tracks a graded backlog of follow-ups (set-op precedence, ORDER BY … NULLS FIRST/LAST, string_agg / array_agg, JOIN … USING (col), window frame clauses, …) under High / Medium / Low headings. See AUDIT.md for the full list; the fixed items are pinned by the regression suite tests/sql-fixes.test.ts (pnpm --filter glove-sql test).