nlp2sql

Ask your Postgres questions in plain English, from inside your own application.

import { NL2SQL, anthropic, catalogFromDir, postgres } from "nlp2sql";

const engine = new NL2SQL({
  catalog: await catalogFromDir("./catalog/acme"),
  db: postgres({ connectionString: process.env.DATABASE_URL }),
  model: anthropic(),
});

const answer = await engine.ask("revenue by store last quarter", { tenant: session.orgId });

answer.sql     // the SELECT it ran, parsed and allowlisted before execution
answer.rows    // [{ store_name: "Camden", revenue_gbp: "48210.00" }, ...]
answer.chart   // { kind: "bar", x: "store_name", y: ["revenue_gbp"], unit: "gbp" }

No sidecar, no VPS, no Docker, no second database. It is a library: your connection and your model key stay in your process, and no row of your data ever leaves your infrastructure — not to us, and not to the model provider.

The model never sees your schema. It sees a catalog: a handful of curated reporting views with declared grain, units, synonyms and forbidden combinations. Every query it writes is parsed with the real PostgreSQL grammar, checked against an allowlist, cost-gated, and executed read-only inside a tenant-scoped transaction.

Status

Not published. Phases 1–7 of the plan are built and green — 173 tests, clean typecheck, a working dist build. The package name is settled (nlp2sql, free on npm); what remains is that "private": true is still set in package.json, because removing it is the act of publishing and that needs a human.

Everything else — exports, files, types, the prepublishOnly check — is in place. npm run check typechecks, tests and builds.

Note the bare nl2sql on npm is a different, actively maintained project. The exported class here is still NL2SQL; only the package name differs.

Install

npm i nlp2sql pg                        # a connection string
npm i nlp2sql @aws-sdk/client-rds-data  # SST/Aurora, no DSN at all
npm i nlp2sql                           # Supabase needs no extra package

Node 20+. ESM only. TypeScript types are first-class, not an afterthought.

Two ways to use it

Both supported, both documented, neither the escape hatch.

Managed — the library makes the model call

const answer = await engine.ask(question, { tenant });
if (answer.ok) render(answer.rows, answer.chart);
else if (answer.status === "abstained") say("I can't answer that.");

You get retrieval, the repair-once loop, cost gating, rate limits and tracing.

Primitives — you make the model call

const retrieval = engine.context(question);       // what to tell the model
if (retrieval.abstain) return refuse(retrieval.abstainReason);

const draft = await myModel(retrieval.context);   // your provider, your tracing
const safe  = await engine.validateSql(draft.sql);
const rows  = await engine.runQuery(safe, { tenant });
const chart = engine.chart(rows);

Use this for streaming, your own observability, a provider we do not ship, prompt caching you control, or — the big one — more than one query per question. We are not shipping an agent framework; you already have a for loop, and it is better than a framework we invent.

runQuery accepts only a SafeSQL, which only validateSql can mint. A model's raw string cannot reach your database by mistake, and the compiler is what says so.

See examples/.

Answer

type Status = "answered" | "abstained" | "rejected" | "error" | "rate_limited";

| Status | Means | You should | |---|---|---| | answered | validated SQL ran | render | | abstained | outside the catalog. No model call was made | say "I can't answer that" — not "error" | | rejected | the model's SQL was refused, or too expensive | log it; this is a catalog gap | | error | Postgres refused, after one repair attempt | log; error carries the message | | rate_limited | a Limits counter tripped | back off |

ask() never throws for anything a user can cause. Programmer errors — a malformed catalog, no model configured — still throw.

answer.assumptions is the honesty channel: when a question is underspecified the model must declare what it assumed ("unit=gbp", "year=2026"), and your UI should show it.

Adapters

postgres({ connectionString })                   // the base case
postgres({ pool: app.db })                       // the pool you already have
rdsData({ resourceArn, secretArn, database })    // SST/Aurora: no DSN at all
supabase({ url, serviceKey })                    // no DSN, no database password

Each declares what it can actually do rather than pretending:

| | explain | readOnlyTx | setLocal | |---|---|---|---| | pg | yes | yes | yes | | rds-data | yes (5 round trips per query) | yes | yes | | supabase | no cost gate | inside the RPC | inside the RPC |

postgres({ pool }) is the quiet best answer to "we don't keep a DATABASE_URL around": hand over the pool you use for everything else. It is safe because the transaction is BEGIN READ ONLY — a write is refused by Postgres itself even on a connection that could perform one.

Numerics. pg returns numeric and bigint as strings, the Data API returns typed fields, Supabase returns JSON numbers. Silently turning 120.50 into 120.5 is the exact failure this project exists to prevent, so the default is exact ("string") and numeric: "number" is an explicit opt-in.

Training data

The reason to build a library rather than a service. Correcting a wrong answer is four calls, not a pull request:

await engine.feedback(answer.id, "dislike", { expected: "should exclude refunds", tenant });

const [candidate] = await engine.examples.candidates();
await engine.examples.promote(candidate, { sql: correctedSql, reviewedBy: "you" });

const after = await engine.evaluate({ tenant });
after.regressions(before);          // did fixing that break three others?

await engine.examples.export("./catalog/acme/gold.yaml");   // git, when you want it

A promoted example is live for the very next question. Promotion requires a human to supply the correct SQL — promote() takes sql and reviewedBy and there is no overload that omits them. That gate is kept; what changed is that passing through it takes one call instead of a release cycle.

Stores: MemoryStore (default — answers and forgets, which is correct for a first evaluation), sqliteStore(path), postgresStore({ pool, schema }).

The recommendation is your own database in a dedicated nl2sql schema — your data, your backups, queryable beside your application tables. Note it needs a writable connection while the query path is read-only: two connections, not one, which costs nothing when your app already has a writable pool.

Traces never keep your data. No result rows, ever. The tenant is hashed with the engine name. SQL keeps its shape and loses its literals — WHERE email = '[email protected]' becomes WHERE email = ? — and SQL we could not parse is dropped rather than stored raw, because literals we cannot find are literals we cannot promise are gone.

Security

| Layer | Guarantee | |---|---| | Catalog | the model never sees raw tables, raw columns, or deny: true columns | | Validator | real PostgreSQL parse; one statement; SELECT only; view, column and function allowlists; no cross-view joins; LIMIT injected into the tree | | SafeSQL | runQuery cannot be handed a raw string — the compiler refuses | | Read-only transaction | BEGIN READ ONLY; a write is refused by Postgres itself | | Timeouts | statement_timeout and idle_in_transaction_session_timeout, both SET LOCAL | | Cost gate | EXPLAIN estimate checked before execution, where the adapter supports it | | Tenant | bound as a query parameter by trusted code, per transaction. Never in the prompt | | Caps | row count and byte size, enforced after fetch |

Result rows are never sent to the model. It sees the question, the catalog, the retrieved examples, and — on a repair — the Postgres error string.

What this does not defend against, stated plainly: authorization within a tenant is yours (deny: true removes a column entirely; there is no per-role visibility); a prompt-injected question can make the model write WHERE 1=1, which cannot escape the tenant predicate or reach a view outside the catalog but does control filters inside it; and your model provider sees the question text.

The validator

Built on pgsql-parser, the actual PostgreSQL C parser compiled to WebAssembly, so "would Postgres read this the way we think?" is answered by Postgres. Among the things it refuses that a first-keyword check would not: WITH ins AS (INSERT ... RETURNING ...) SELECT ..., which writes to your database from a statement beginning with SELECT.

validate() is async because the WASM module loads on first use — call engine.warmup() at startup to move that off the first question. The parser is ~3.5 MB installed (1.1 MB WASM): fine server-side, not something for a browser.

Development

npm test          # 173 tests
npm run typecheck
npm run build
npm run check     # all three

The executor and store tests need a real server, because "a write is refused" is not a claim you can unit-test. They look for NL2SQL_TEST_DSN, then initdb in NL2SQL_PG_BINDIR / the Python virtualenv / on PATH. Finding neither, they skip loudly rather than passing quietly. Locally there is nothing to install — pgserver, already a Python dev dependency of this repository, ships real Postgres binaries and the harness starts a throwaway cluster on a Unix socket.

Why it lives in the Python repository, for now

../spec is the contract both runtimes are held to, and it is not published yet. Reading it from the same checkout keeps the loop tight: change the engine, regenerate the spec, and this suite fails immediately if the two have diverged. Extraction is one git mv at publish time. Nothing here imports anything Python.

The pyrepr.ts wart

The Python reference renders a view's default_rules into the prompt with an f-string, so what reaches the model is a Python dict repr: {'unit': 'gbp', 'year': 2026}. Emitting JSON here would send the model different bytes, and two runtimes sending different prompts cannot share an accuracy result. So it is reproduced exactly, kept in one labelled file, and recorded as a divergence in ../spec/README.md to be normalised later — behind an eval run, because changing the prompt changes answers.