The SQL stack

Cruft embeds two of its own database engines, the SQLite-compatible CQuilite and the PostgreSQL engine postcrust, over one shared relational core. Both run inside the runtime with no external library and no wire protocol. This page covers that three-layer architecture and how the shared executor handles joins and NULLs.

Node gives you a database one of two ways. npm install better-sqlite3 bundles the SQLite C library and compiles it as a native addon, or you run PostgreSQL as a separate process and talk to it over a socket with pg. Either way the database lives outside the runtime: a bundled C library, or a server across a wire protocol.

Cruft embeds both. It ships two of its own database engines, a SQLite-compatible engine and a PostgreSQL engine, built over one shared relational core. There is no libsqlite, no libpq, and no wire protocol: both run inside the runtime, in Rust the project owns. This page covers the three-layer architecture. The CQuilite and Postcrust pages cover each in depth, and the ORM sits on top of the stack.

One core, two dialects

Relational algebra is dialect-independent. Scan, filter, project, join, sort, limit, aggregate: the logical-plan vocabulary is the same whether the surface syntax is SQLite's or PostgreSQL's. What differs is the type system and expression semantics, SQLite's column affinity against Postgres's OID type catalog and strict coercion, and each dialect's function library.

The stack splits exactly there:

Two dialects, one core
CQuilitethe SQLite dialect and column affinity — tokenizer, parser, functions
postcrustthe Postgres dialect and OID types — types, expressions, catalog, statements
sql-core planboth dialects lower here — the dialect-agnostic plan algebra and row executor

sql-core owns the plan node set and the row executor, and nothing else. It never learns a dialect's value rules: each front end hands it evaluated scalar, predicate, and key closures over rows, so the core orchestrates operators without knowing whether 1 + '1' is 2 (SQLite affinity) or an error (Postgres strictness). The shared value set is small, Null, Int, Real, Text, and Blob, with Postgres's richer types carried as validated canonical text on top.

Both engines are thin dialect layers over this one core. An executor improvement lands once and both engines get it, and the two dialects share the join, collation, and equality-index machinery beneath them.

The three layers

  • sql-core is the dialect-agnostic relational plan and row executor: the operators (scan, filter, project, join, sort, group, aggregate, limit), the join kinds, collation, and equality-index access paths.
  • CQuilite is the SQLite dialect: tokenizer, parser, type affinity, the SQLite function library, and the real sqlite3 on-disk file format, read and written. It is reached through cruft:sqlite, bun:sqlite, and the ORM's openSqlite.
  • postcrust is the PostgreSQL engine: the OID type system, strict coercion, per-tuple MVCC, PL/pgSQL, catalog introspection, a large Postgres function catalog, and a durable single-writer file format. It is reached today through the ORM's openPostgres.

Why embed the databases

  • One trust story. A runtime whose SQL was libsqlite or libpq would carry a second, separate dependency on the most sensitive data path. Owning the engines keeps that path under the same roof as the rest of the runtime.
  • The boundary is a function call. Because rows never cross a socket, the ORM can check every row against the live schema as it comes back. That soundness check, the feature that defines the ORM, is only affordable when the database is in the same process.
  • Data is a capability. A database handle is an ordinary endowable value. Which compartment can reach which data is governed by the same capability machinery as files and sockets.

The shared core and NULLs

The dialect-agnostic executor handles the semantics where relational algebra usually goes wrong. The classic NULL-comparison trap, using one equality for both WHERE and grouping, is avoided: WHERE runs a three-valued predicate that drops UNKNOWN rows, while GROUP BY uses a separate comparison in which two NULLs are equal, so all-NULL keys collapse to one group and WHERE col = NULL matches nothing. Outer-join null-extension pads the correct side, and a NULL join key never matches.

Two edges to know: the numeric comparator treats NaN as equal to every number, so a NaN row can group with unrelated numbers; and the default NULL ordering for ORDER BY follows SQLite's, so the Postgres front end passes its own ordering explicitly.