SQL Compatibility & Mapping — PostgreSQL / SQLite → Twill DB

Purpose & framing

Twill DB speaks a deliberately small, hand-parsed SQL subset (spec 02; user-facing surface in docs/sql-reference). The request behind this spec — "map every SQL that works in PostgreSQL and SQLite to Twill, then specify how to implement it" — is answered in two halves:

1. The mapping (§"Feature mapping"): every major area of the PostgreSQL and SQLite grammar, classified against Twill's current engine and given a mapping strategy. "Every SQL" is, taken literally, unbounded (PostgreSQL alone is hundreds of statement forms, thousands of built-in functions, and an extensible type system); this spec is exhaustive at the level of language area and representative-but-not-line-complete at the level of individual built-in functions, which it handles by policy rather than enumeration.
2. The implementation (§"Implementation spec"): a staged plan (Phase 6A–6E) that grows the frontend to cover the common, OLTP-shaped majority of that surface, with the AST / module / test changes for each stage and the invariants every stage must preserve.

Twill is an OLTP engine with a pluggable storage seam, not a PostgreSQL re-implementation. The goal of Phase 6 is "the SQL an application actually issues against an OLTP database parses and runs the same on Twill", not byte-for-byte dialect parity. Where PostgreSQL and SQLite disagree (typing, quoting, boolean handling), Twill picks one documented behaviour and shims the other on the dialect boundary (server mode reports as PostgreSQL; the embedded path leans SQLite).

Invariants this spec must not break

The mapping and every implementation stage are bound by the project's load-bearing rules (.claude/rules/storage-seam.md, rust.md):

• The Storage trait does not change. SQL surface is entirely a frontend concern: lexer/parser (sql.rs), AST, executor (exec.rs), and the row/value model (value.rs, store.rs). No new feature here may add a method to trait Storage or leak a backend concept into it. STORAGE_TRAIT_VERSION stays 2, the C1–C8 conformance suite stays green, branching/scale-to-zero are unaffected.
• The C ABI stays frozen unless a symbol is genuinely needed. SQL flows through the existing engine_prepare/engine_exec/result-set path; new statement forms and richer result shapes do not add C symbols. ENGINE_ABI_VERSION stays 3 unless a stage introduces a new export or changes an existing export's contract (called out per stage).
• MVCC, single-writer, group commit, durability are untouched. New read shapes (joins, grouping, subqueries) run against the same snapshot; new write shapes (RETURNING, upsert, INSERT … SELECT) emit the same WalOp batch and publish at the same commit LSN. No statement may ack before durable.
• Reject, never mis-parse. Anything still unsupported after a stage must return ENGINE_ERR_SQL with a clear message — never silently ignored or mis-executed (the existing contract; cf. OR REPLACE being parsed-and-ignored is itself a documented hazard this spec tightens).

Status legend

Feature mapping

Data types & storage classes

Twill stores five dynamic storage classes — Value::{Null,Int(i64),Real(f64),Text,Blob,Vector} (value.rs) — with SQLite-style name-based affinity (ColumnType::from_sql). Declared column types are advisory affinities, not hard constraints (the SQLite model), except vector(N), whose dimension is enforced. The map below is therefore "declared type → affinity bucket".

DDL — data definition

DML — data manipulation

Queries — SELECT

Expressions & operators

Functions

Policy, not enumeration. PostgreSQL ships thousands of built-ins and SQLite a few hundred; Twill will implement a curated OLTP-common set as native scalar/aggregate functions and reject the rest with ENGINE_ERR_SQL. Scalar function calls are not parsed at all today (a bare word becomes a column reference; only the five aggregates are special-cased), so this is a clean addition: a Expr::Call{name, args} node plus a dispatch table in exec.rs.

Transactions & session

Dialect-specific quirks (PostgreSQL vs SQLite)

Implementation spec

The gaps cluster into five stages, ordered by value/risk. They are additive frontend changes: each grows the Stmt/Expr AST (sql.rs), the parser, and the executor (exec.rs), plus the catalog (catalog.rs) and transaction manager (conn.rs) for the few stateful items. None touches trait Storage; the WAL op set (wal.rs) only grows if a stage adds a new durable catalog fact (called out). Proposed as Phase 6 — SQL Surface Completeness in the roadmap (spec 13); ship the stages as independent, individually-testable increments.

Stage 6A — Expression & single-table completeness (low risk, high value) Implemented

The cheapest, highest-coverage wins; no new execution model, only richer expressions and result shapes over the existing single-table scan.

• Lexer: add || (concat) and :: (cast) tokens; keep the existing <-disambiguation discipline (vector ops bind first) in lex_lt.
• AST / parser: Expr::Case, Expr::Cast{expr, ty}, Expr::InList{expr, list, negated}, Expr::Between{expr, lo, hi, negated}, Expr::Call{name, args}, and BinOp::Concat. Add SelectStmt.offset: Option<i64> and per-key NULLS FIRST/LAST. Add LIKE … ESCAPE.
• Executor: evaluator arms for the new nodes (CASE short-circuits; CAST routes through ColumnType::coerce + explicit text↔number parsing; || is text concat with NULL propagation; IN/BETWEEN desugar). A scalar-function dispatch table seeded with the conditional/null group (coalesce, nullif, ifnull, iif, greatest, least) — string/math/date land in 6C against the same table.
• RETURNING: run_insert/run_update/run_delete already build the affected new row versions; thread an optional projection list through and return a ResultSet instead of just a row count. The C ABI already exposes a result-set channel from engine_exec, so this is shape-only — no ABI bump.
• Upsert: implement ON CONFLICT (col) DO NOTHING|UPDATE SET … and SQLite OR IGNORE/OR REPLACE against the existing PK conflict path in run_insert (it already detects committed vs. concurrent-pending clashes). Stop silently ignoring OR REPLACE/OR IGNORE.
• INSERT … SELECT: let the insert source be a parsed SelectStmt evaluated to rows, fed into the same staging/validation loop.
• Tests: extend crates/engine/tests/engine for each operator/function; tests/ffi.rs for RETURNING over the C ABI; a bun test pass after a release rebuild.
• Versions: ENGINE_ABI_VERSION stays 3; STORAGE_TRAIT_VERSION stays 2.

Stage 6B — Multi-table & aggregation (the big one) Implemented

The structural jump: more than one row source, qualified names, grouping. This is where the executor gains a small relational layer above the per-table scan, staying brute-force (nested-loop, hash-group) — appropriate for an in-memory MVCC OLTP store.

• AST: generalize SelectStmt.from from Option<String> to a FromClause tree: base tables with aliases, JOIN nodes (Inner/Left/Right/Full/Cross) with ON/USING, and derived tables ((SELECT …) AS a). Add group_by: Vec<Expr>, having: Option<Expr>, distinct: bool, and a set_op wrapper (UNION/INTERSECT/EXCEPT [ALL]). Add Expr::Subquery (scalar) and Expr::Exists/Expr::InSubquery.
• Name resolution: replace the "drop the table qualifier, keep the column" shortcut in primary() with a real binder: resolve alias.col against the active FromClause's column namespace; raise ENGINE_ERR_SQL on ambiguous/unknown.
• Executor: a row-source iterator model — scan, nested-loop join (with LEFT/RIGHT/FULL null-extension), then a grouping operator (hash by the GROUP BY key, fold aggregates per group, filter by HAVING), then DISTINCT, then order/limit/offset. All over the same MVCC snapshot; visibility is still resolved per base-row exactly as today. Correlated subqueries re-evaluate per outer row (documented as O(n·m), fine for OLTP cardinalities).
• Grouped aggregates: generalize compute_aggregate to run per group; add group_concat/string_agg and COUNT(DISTINCT …).
• Views & CTEs: non-recursive WITH and CREATE VIEW become named derived tables (the view body is a stored, re-bound SelectStmt; a CreateView WAL op carries the text — a new durable catalog fact, so this sub-item alone touches wal.rs and the catalog, still not the storage trait).
• Tests: a dedicated tests/joins.rs and tests/grouping.rs with MVCC-visibility cases across joins; parity checks for set ops.
• Versions: STORAGE_TRAIT_VERSION stays 2. ENGINE_ABI_VERSION stays 3 (still text-in/result-out). The CreateView/(optional) catalog WAL ops are additive WAL records, replayed like any other.

Stage 6C — Function library Implemented

• Populate the 6A scalar-dispatch table with the string, numeric/math, date/time, UUID, and minimal-JSON groups from the functions table. Date/time follows the SQLite model (text/epoch + strftime/date), avoiding a native temporal type and keeping the value model unchanged.
• Determinism & durability: non-deterministic functions (now(), random(), gen_random_uuid()) are evaluated once at execution time and the resulting concrete value is what lands in the WalOp — so replay reproduces the stored value exactly (never re-rolls). This is the same discipline that keeps the vector index replay-deterministic.
• Keep dependencies minimal (rust.md): hand-roll or use the existing base64-style in-house helpers; no new crates for date parsing or JSON unless justified.
• Tests: per-function unit coverage; an explicit replay-determinism test for the non-deterministic functions.

Stage 6D — Constraints, schema evolution & savepoints Implemented

• Catalog: store DEFAULT expressions, CHECK predicates, secondary UNIQUE, and composite/table-level PRIMARY KEY in TableSchema. Enforce in the insert/update validation pass (which already validates fully before mutating — atomic per statement). Secondary UNIQUE reuses the pk_keys machinery generalized to N key sets.
• AUTOINCREMENT/SERIAL: a monotonic per-table counter in the catalog, advanced and WAL-logged on insert (a new durable fact → additive WAL op).
• ALTER TABLE: ADD COLUMN (null/default backfill), DROP COLUMN, RENAME as catalog mutations replayed from new WAL ops; existing in-memory row versions reshape on replay.
• SAVEPOINT/ROLLBACK TO: tag pending row versions with a savepoint depth in conn.rs; ROLLBACK TO tombstones pending versions above the mark (reuses Store::rollback_pending). No durable change — savepoints live entirely in the in-flight writer's pending set.
• Scalar (B-tree) secondary indexes: optional. Like the HNSW index, a derived in-memory structure rebuilt on replay (rebuild_indexes); a CreateIndex-style WAL op already exists as the model. Default plan remains a full MVCC scan, so this is a latency optimization, not a correctness requirement.
• Versions: new WAL ops are additive; STORAGE_TRAIT_VERSION unchanged. DDL remains autocommit-only (spec 02 boundary preserved).

Stage 6E — Dialect shims (compatibility surface) Implemented

Make real PostgreSQL/SQLite clients and ORMs connect without surprises, concentrated at the boundary so the core stays one dialect.

• Placeholder forms $1 and :name (normalize to the existing positional model); identifier quoting [x]/`x`; LIKE vs ILIKE split (case-sensitive default for PG mode); boolean reported as bool on the wire.
• Accept-and-pin SET TRANSACTION ISOLATION LEVEL to SI; accept-and-no-op the common session SET/SHOW/PRAGMA/VACUUM/ANALYZE that ORMs emit on connect, rejecting unknown ones (never silently swallow semantics-bearing statements).
• EXPLAIN returns the single chosen plan as text.
• Most of 6E lives in crates/server (the pgwire boundary, spec 07) and the Bun wrapper, keeping dialect translation out of the embedded engine core where it can.

Deliberate non-goals (kept rejected)

• OLAP-shaped query features — window functions, GROUPING SETS/ROLLUP/CUBE, LATERAL, recursive CTEs. Composed via DuckDB over shared storage (spec 12), not built into the OLTP core.
• Procedural & extensibility surface — PL/pgSQL, CREATE FUNCTION/TRIGGER/EXTENSION/SCHEMA, roles/GRANT, LISTEN/NOTIFY, full-text search. Auth and services are composed around the engine, not welded in.
• Exact-decimal / arbitrary-precision numerics — NUMERIC(p,s) maps to f64. A true decimal type would extend the value model (spec 02) and is out of scope here.
• PostgreSQL's structured/extensible type system — arrays, composite types, ranges, enums, geometric/network types, hstore. Encode app-side into Text/Blob.
• Storage-engine-conflicting statements — ATTACH/DETACH (backend is chosen by URL scheme, not at runtime), tablespaces, physical storage parameters.

Everything in this list stays a clean ENGINE_ERR_SQL rejection — the "reject, never mis-parse" contract is part of the engine's correctness story.

Acceptance & verification

• Each stage ships with engine tests that would fail without it (testing.md), the C-ABI surface re-checked in tests/ffi.rs, and — after a cargo build -p twill-engine --release — a green bun test.
• The C1–C8 storage conformance suite, the Phase-4 branching battery, and the Experiment-4 crash gate must stay green at every stage — they cannot regress, because no stage touches the storage seam or the commit/recovery path.
• Each shipped stage updates docs/sql-reference (the user-facing surface) and this spec's status marks in lock-step (git-workflow.md: specs and docs track behaviour).
• A compatibility corpus — a checked-in set of representative PostgreSQL- and SQLite-flavoured statements with expected results — runs as the regression gate for "the SQL an application actually issues", growing as stages land.

Related