@neutron-build/sql

Drizzle-shaped TypeScript SQL ORM for Postgres. Schema in code, no codegen, one readable SQL statement per query, toSQL() on everything. Zero runtime dependencies — bring postgres or pg (or both) as optional peers.

Alpha — contained, not production-ready. Verified on PostgreSQL 15, 16, 17 and 18 with both drivers (changing a generated column's expression needs 17+); Nucleus and other Postgres-wire engines are not claimed. See the support matrix and, when coming from an earlier build, Upgrading.

Quick start

npm install @neutron-build/sql pg     # or: postgres

Requires Node.js 22 or later. pg and postgres are optional peers; install the one you use.

// schema.ts
import { pgTable, serial, integer, text, timestamp, relations } from "@neutron-build/sql";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({ posts: many(posts) }));
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.userId], references: [users.id] }),
}));

Create the tables with the migration workflow (export the schema, neutron migrate generate, neutron migrate), then query them:

// app.ts
import { createDatabase, eq } from "@neutron-build/sql";
import { users, posts, usersRelations, postsRelations } from "./schema.js";

const db = await createDatabase({
  url: process.env.DATABASE_URL!,
  tables: { users, posts },
  relations: { users: usersRelations, posts: postsRelations },
  logger: true, // redacted JSON-lines events in dev — never parameter values
});

// SQL builder — typed CRUD
const inserted = await db.insert(users).values({ email: "[email protected]" }).returning();
await db.update(users).set({ email: "[email protected]" }).where(eq(users.id, inserted[0].id!));
const rows = await db.select().from(users).where(eq(users.email, "[email protected]")).limit(10);

// Relational reads — one statement, typed nesting
const author = await db.query.users.findFirst({
  where: eq(users.email, "[email protected]"),
  with: { posts: true },
});
// author.posts: typed array; empty relations are [], never [null]
// Unknown relation names in `with` are compile errors

// Inspect without executing
console.log(db.select().from(users).toSQL());

// Transactions
await db.transaction(async (tx) => {
  await tx.insert(posts).values({ userId: inserted[0].id!, title: "hello" });
});

await db.close();

Types come from the schema (typeof users.$inferSelect / $inferInsert); there is no codegen step and db never types as unknown.

Migrations

Export the schema as JSON with a small module next to your compiled schema (tsc emits schema.js from schema.ts):

// export-schema.mjs
import { writeFileSync } from "node:fs";
import { exportSchemaV2, canonicalSchemaJson } from "@neutron-build/sql";
import { users, posts } from "./schema.js";

writeFileSync("neutron.schema.json", canonicalSchemaJson(exportSchemaV2({ users, posts })));

# 1. export the schema as JSON
node export-schema.mjs

# 2. point the CLI at a disposable database (env or --url)
export DATABASE_URL=postgres://user@localhost:5432/mydb

# prototype: push straight to the database
neutron db push --schema neutron.schema.json

# real migrations: reviewable SQL files, rename-aware
neutron migrate generate --schema neutron.schema.json --name add_users
neutron migrate generate --rename 'users.name>users.full_name'   # quote it: > is a shell redirect
neutron migrate          # apply (history table: _neutron_migrations)
neutron migrate status

Two export formats exist. exportSchema() emits the legacy version-1 shape; the CLI planning commands (db push, migrate generate, schema check) accept both. exportSchemaV2() emits the cross-language schema contract v2 (contracts/data/schema-v2.json) and canonicalSchemaJson() serializes it deterministically: identical bytes and SHA-256 for the same schema on every machine, input key order and process timezone irrelevant — the Go CLI and the contracts/data reference consumer reproduce the same canonical bytes (CI-pinned by the golden fixture contracts/data/golden/valid/exported-v2.json). readSchemaDocumentV1() is the explicit compatibility reader: it upgrades legacy v1 export documents to v2 under the same rules as the Go upgrade reader (contracts/data/CANONICAL.md §6) and reports every ambiguity ([ambiguous-default]) instead of guessing literal vs expression. Columns keep declaration order (PostgreSQL attnum semantics); tables, constraints and indexes normalize as sorted sets in the canonical form. Schemas the v2 document cannot represent faithfully — a serial column with an explicit default, a foreign key to a column outside the export or not covered by a primary-key/unique constraint — fail at export time with the contract error code, never as a silently invalid document.

migrate generate diffs the exported schema against the live database by default (unless neutron.toml sets [migrations] snapshots = true) and writes {version}_{name}.up.sql / .down.sql pairs; --mode snapshot plans offline from the last accepted snapshot instead (see neutron migrate generate --help). When a column is added and another with the same type is dropped, it prints a suggestion — confirm intent with --rename 'users.old>users.new' (the > must be quoted in the shell; without quotes the shell reads it as a redirect and the flag never reaches the CLI). A rename alone plans only RENAME COLUMN in db push and migrate generate --mode live: PostgreSQL rewrites the generated columns, checks and indexes that name the column, and the planner compares against that; the down file reverts the rename, including changed keys, foreign keys and indexes on the column. --mode snapshot has no database to compare against: a rename that no generated column, check or index expression names plans the same way, and one that such an expression names is refused with the elements listed and a two-migration path (keep the old name while changing them, then rename). A live plan is refused the same way when the planner cannot copy the table to compare it (for example a check with a whole-row reference); the message gives the ALTER TABLE ... RENAME COLUMN to run by hand first. Statement order: foreign keys drop before the keys they reference and are added after them, in every table; constraints and indexes drop before the columns they name change type or are dropped, and are re-created after; a generated column drops before a column it reads; a new table's foreign key onto a column or key the plan adds, renames, retypes or changes is added after that change; tables the plan drops go last, after the defaults and columns that use their sequences, with only their foreign keys onto keys the plan drops removed earlier; declared views are dropped dependents first and created bases first (live, in the order the catalog records; offline, from the relations each definition names after FROM and JOIN, falling back to declaration order if that is ambiguous); an index whose operator class changes (for example text_pattern_ops under a type change) is rebuilt; and a down file re-creates dropped columns before their indexes, and dropped tables (with the sequences they owned) before the foreign keys between them, so these plans apply and their down files revert. A foreign key of a table the plan keeps but does not manage (left in place, declared with managed: false, or in a schema the document does not declare) onto a key or table the plan drops refuses the plan, naming the foreign key: declare the table as managed, or let --allow-destructive drop a left-in-place one, or drop the foreign key by hand. Changing the type of a column a stored generated column reads drops the generated column before the change and adds it back after it, with its indexes, constraints and the foreign keys onto it (PostgreSQL cannot change the type under it): the plan needs --allow-destructive, the values are recomputed, and the column is placed last; its down file adds it back last too, so the original column order is not restored. A refusal that suggests --allow-destructive lists every other object the flag would drop. The flag drops everything in the managed schemas that the schema does not declare as managed: tables (including ones declared with managed: false), columns, indexes, views and enums. The list and the plan use the same rules. Objects that depend on a column whose type changes or that is dropped, and that the plan does not handle, refuse the plan by name. db push and migrate generate --mode live read them from the catalog: views and materialized views (including a view over a function that returns the table's rows), functions with a BEGIN ATOMIC body, policies, triggers with a column list, rules, publication row filters and column lists, and the columns of tables, partitioned tables and materialized views that store the table's row type directly, in an array, or through a composite type or domain (foreign tables are not checked, as in PostgreSQL). For a declared view the plan drops and re-creates, they also read what depends on the view or on its row type (another view, a function returning SETOF the view, a column storing its row type). Declare such a view, so the planner drops and re-creates it around the change, or drop it (--allow-destructive drops undeclared views in the managed schemas); drop the other objects by hand before the change and re-create them after. --mode snapshot has no catalog: it checks the views the chain records by their text, which is conservative (a view naming a same-named column of another table is refused too), and objects the chain does not record still fail at apply and roll back.

In --mode snapshot, removing a schema from the document stops the chain tracking its objects, and declaring it again plans create table for tables that still exist, which fails (42P07). Keep a schema declared while its tables exist; if it happens, recover by re-baselining (step 4 below), not with neutron migrate resolve --mark-applied, which would record a migration that never ran. Live mode and db push compare against the database and are not affected.

Enum value additions: PostgreSQL cannot use an enum value inside the transaction that adds it (SQLSTATE 55P04). When a plan adds enum values and also changes anything else, migrate generate writes the additions as their own earlier migration ({version}_{name}_enum_values), and db push applies them in their own reported transaction before the rest (--dry-run shows the boundary; if the second transaction fails, the added values stay and a re-run converges). Changing a generated column's expression uses ALTER COLUMN ... SET EXPRESSION, which needs PostgreSQL 17+: db push and migrate generate --mode live refuse it on older servers with the alternative named, and --mode snapshot records minServerMajor in the plan so neutron migrate refuses older servers before running anything. Changing a column's type to an enum while a check or index compares it with text drops the check and index before the type change and re-creates them after it.

Safety semantics: generated SQL never drops _neutron_* metadata tables, extension-owned objects, or anything absent from the schema unless --allow-destructive is passed as an explicit acknowledgement. The _neutron_ table prefix is reserved: such tables cannot be declared in a schema, and schema pull and schema baseline leave them out. In --mode snapshot, what a plan leaves in place is recorded in its target snapshot, so the chain keeps matching the database; its drops stay listed as pending until a plan with --allow-destructive drops it or the schema declares it again. Column order is informational: PostgreSQL appends added columns and cannot reorder existing ones without rebuilding the table, so a schema that declares another order plans nothing, and the next plan notes the difference (insert with column lists, not by position). Catalog structures the diff cannot represent faithfully are rejected with an error instead of producing a migration that falsely claims synchronization. With a version-2 document the planner covers schema-qualified tables, composite primary-key/unique/check/foreign-key constraints, indexes with methods, predicates, expressions and INCLUDE, enums, one-dimensional arrays, identity and stored generated columns, and views. What the contract cannot represent — virtual generated columns, multi-dimensional arrays or arrays of temporal/bytea/vector elements, NULLS NOT DISTINCT, non-default operator classes or collations on index keys, foreign keys to views, and types outside its vocabulary such as domains, citext, ranges and composite types — fails at definition or export time in TypeScript, and is introspected as opaque and blocks the affected plan in the CLI. Version-1 documents keep the older single-column surface.

Drivers

postgres (postgres.js) and pg (node-postgres) are both supported and live-tested; with no explicit choice, postgres is tried first and pg is the fallback only when the postgres.js module is genuinely not installed — a loaded driver's connection or URL failure propagates instead of silently switching libraries under you. Nucleus speaks the same wire protocol, but this package has no test coverage against it — treat any non-Postgres engine as unsupported until a conformance leg exists.

Driver choice and pool tuning: createDatabase({ url, driverOptions: { driver: "pg", max: 10 } }) (driverOptions replaces the pre-0.1 driver option, which now injects an adapter — see below).

Runtime support (Node.js)

This package runs on Node.js (engines: ">= 22"; CI runs Node 22 and 24); the pg and postgres.js adapters use Node sockets. createDatabase and loadDriver detect the runtime positively (process.versions.node — never typeof window inference) and, on a runtime without Node compatibility, fail with one precise error before any driver import is attempted. In a bundler that externalizes node: builtins (Vite-style shims), that call-time error is what you get; in a runtime with hard no-Node module resolution, the import itself fails at node:crypto (used for statement ids). There is no edge/browser transport adapter — none will be advertised until one exists with real transport fixtures for every claimed runtime. A runtime that genuinely provides Node compatibility passes the guard but stays outside the tested matrix.

Relationship to @neutron-build/data (Drizzle interop)

Two documented database paths exist, separately named. This package is the first-party SQL path (schema, compiler, transactions, migrations feed). @neutron-build/data's createDrizzleDatabase — import it from @neutron-build/data/drizzle — is the explicit Drizzle interop path: it returns genuine drizzle-orm databases (PostgresJsDatabase / LibSQLDatabase, typed through your schema) for existing Drizzle users and for Nucleus multi-model access. See that package's README for the interop contract and its supported peer versions.

Injected adapters and ownership

Pass an adapter you created instead of a URL: wrap an existing pg pool or postgres.js client with wrapPgPool(pool) / wrapPostgresJs(client). Wrapped resources are borrowed by default — db.close() (and the driver's close()) never ends them; the pool/client you injected stays yours and functional. Pass { ownership: "owned" } to transfer disposal instead. Adapters this package creates from url are owned: close() terminates them exactly once, and repeated or concurrent close() calls are idempotent (no double-end). Every driver exposes driver.lifecycle ({ ownership, terminated, terminate() }) so callers can inspect and drive the lifecycle explicitly. Custom adapters passed via driver: must expose the same lifecycle and close() — wrap an existing pool/client with wrapPgPool/wrapPostgresJs to get a conforming adapter. The injected postgres.js surface must include reserve() (every postgres.js 3.x client has it) — transactions pin their connection through it.

Transactions, cancellation and observability (I02)

db.transaction(fn, options?) runs the callback on one pinned connection (pg: a checked-out pool client; postgres.js: sql.reserve()), driven by one shared runner on both drivers: BEGIN with validated modes, real savepoints for nesting, COMMIT/ROLLBACK, and honest failure classification.

await db.transaction(
  async (tx) => {
    await tx.insert(posts).values({ userId: 1, title: "hello" });
    // Nested transaction = a real SAVEPOINT: inner failure rolls back and
    // releases only the savepoint; the outer transaction stays usable.
    await tx.transaction(async (tx2) => { /* ... */ });
    // Or explicit control: rollbackTo keeps the savepoint, release drops it.
    const sp = await tx.savepoint("stage1");
    await sp.rollbackTo();
    await sp.release();
  },
  {
    isolation: "serializable",        // "read-committed" | "repeatable-read" | "serializable"
    readOnly: true,                   // BEGIN READ ONLY
    deferrable: true,                 // only with serializable + readOnly — see below
    retry: { maxAttempts: 3, backoffMs: (n) => n * 50, idempotent: true },
  },
);

• Isolation maps to what PostgreSQL actually supports — the three real levels (there is no read-uncommitted: it aliases READ COMMITTED and is not offered). Modes are observable: the server's current_setting('transaction_isolation') reflects them.
• deferrable is only accepted where it is real: serializable + readOnly: true. PostgreSQL accepts DEFERRABLE with any mode but it has no effect outside serializable read-only transactions; that silent no-op is rejected up front instead.
• Retry is opt-in and bounded. Only SQLSTATE 40001 (serialization failure, including ones surfacing at COMMIT) and 40P01 (deadlock) are retried, up to maxAttempts total executions with backoff between attempts. Enabling retry requires idempotent: true: a retried attempt re-executes the whole callback from the top, including any external side effects. Non-retriable errors surface after one attempt.
• A commit with an unknown outcome is its own error state. If the connection fails while COMMIT is in flight (transport failure, FATAL 57P01/57P02/57P03, or a cancellation), the transaction throws CommitAmbiguityError with the cause and guidance: the transaction may or may not be durably committed, and it is never replayed automatically — not even with retry armed. Inspect the database state (or an idempotency record) before re-submitting. An SQL error at COMMIT (e.g. 40001) is the server's definite rollback answer, never ambiguous.

Query deadlines and AbortSignal — cancellation reaches the database

// any statement, both drivers:
await db.driver.query("select ...", [id], { deadlineMs: 250 });
const ac = new AbortController();
req.on("close", () => ac.abort());
await db.select().from(users).execute({ signal: ac.signal });   // builders take options
await db.query.users.findMany({ with: { posts: true } }, { deadlineMs: 250 }); // relational too

The cancel is delivered to the server, not just the caller's promise: the pg leg runs pg_cancel_backend(pid) on a second pooled connection (the backend pid is read from the connection running the query — one extra round trip on cancellation-armed statements; the side channel needs spare pool capacity, so a max: 1 pool cannot service it); postgres.js uses its native Query.cancel() (a dedicated cancel connection managed by the driver — installed 3.4.x has no AbortSignal support of its own). The canceled statement fails with QueryCanceledError (reason: "deadline" | "signal", dispatched, SQLSTATE 57014 — it is a ServerSqlError, so SQLSTATE survives wrappers). The pooled connection stays usable: the server answers 57014 and the session returns clean — verified live on both drivers by canceling pg_sleep mid-flight and running subsequent queries on the same pool. A 57014 you did not request (server-side statement_timeout, an administrator cancel) stays a plain ServerSqlError. A pre-aborted signal rejects immediately with dispatched: false — nothing is sent.

One pg caveat fixed along the way: a connection dying inside a transaction used to surface as an unhandled 'error' event on the checked-out client (pg-pool detaches its listener while checked out) and could crash the process. The transaction pin now owns that hazard, and fatally-lost connections are removed from the pool instead of returning to the idle set.

Structured events (redacted by default)

logger: true prints one JSON line per event; a custom function receives the same SqlEvent. Kinds: query-begin, query-end, query-error, tx-begin, tx-commit, tx-rollback, savepoint, cancel — with durations, 16-hex statement ids (sha256 of the SQL text), transaction ids and savepoint names. Parameter values never appear: the params field exists only when the process sets NEUTRON_SQL_LOG_PARAMS=1, an explicit redaction-free mode — the emitted values are then visible in whatever sink receives events, so do not enable it where logs are shared. Connection strings and passwords are never logged (events carry no connection information at all). Server error messages pass through verbatim in error.message — PostgreSQL itself may echo values there (e.g. duplicate-key details); that is the server's wording, not this package's emission. The pre-I02 logger (plain SQL lines that printed parameter values) is gone; LogEvent remains as a deprecated alias of SqlEvent.

postgres.js 3.4.8 defects worked around (documented)

Reproduced on raw postgres.js during I02 (see the private evidence record): its own sql.begin() crashes the process when the backend is terminated mid-transaction (rollback written to the already-nulled socket), and releasing a reservation whose socket died reopens the dead connection into the pool (next pooled query crashes the same way; one stale 57P01 can also be delivered to an innocent later query). This package's transaction runner pins through reserve() but never rolls back onto a fatally-lost connection and never releases a dead reservation — the terminate-mid-transaction storms in the live suite survive where the driver's own path crashes. The residual upstream behavior (a stale one-shot 57P01 after a killed reservation) is sequenced around, not hidden: statements never ran, so a retry is safe.

One more upstream behavior, documented rather than worked around: repeated killed reservations can stall postgres.js's reconnect backoff. After ~7 consecutive terminations of reserved backends, the next reserve() (or the BEGIN that follows it) can fail to settle for tens of seconds (observed once at ~40 s on a max: 6 client; a 6-kill run recovered instantly). The stalled slot is a connection mid-reconnect inside the driver (upstream closed() → backoff(retries) lifecycle), not a checked-out connection: once the backoff expires the pool serves normally again (plain queries, fresh reserves) — no permanent leak, no crash, no data risk. Recovery is automatic; nothing here needs to intervene.

Errors and capabilities

Driver errors surface as a stable taxonomy instead of message matching: MissingDriverError (the npm package is absent), ConnectionFailedError (transport: refused/timeout/socket died/pool ended — with code, address, port when present), ServerSqlError (the server answered with an SQL error; sqlstate retained verbatim, plus severity/detail/hint/position, original driver error as cause), QueryCanceledError (a ServerSqlError for a cancellation this package dispatched — 57014, with the deadline/signal reason), and CommitAmbiguityError (a COMMIT whose outcome the connection failure left unknown — never retried automatically). getSqlState(err) walks wrapper chains; isRetriableTransactionError(err) answers the 40001/40P01 question the retry contract asks.

db.engine() returns the connected engine's identity (one SELECT VERSION(), memoized; Nucleus detection per the framework contract — an unrecognized server is unknown, never assumed Postgres). db.capability(name) resolves capability status as supported / unsupported / unknown with the evidence that produced it: documented PostgreSQL version facts, or a side-effect-free probe on engines without them. Compiled statements carry their capability requirements; an unknown requirement fails closed with CapabilityRequirementError before any SQL runs — unknown is never all-enabled. Detection runs through whichever driver you brought; a plain Postgres connection gains no Nucleus/model dependency.

Vectors and search (X01)

Vector columns and full-text search live in OPTIONAL capability modules — the SQL-only root never loads them; importing the entry point is the explicit opt-in.

pgvector (@neutron-build/sql/pgvector)

import { pgVector, l2Distance, cosineDistance, pgvectorExtension } from "@neutron-build/sql/pgvector";

const docs = pgTable("docs", {
  id: serial("id").primaryKey(),
  body: text("body").notNull(),
  embedding: pgVector("embedding", 3).notNull(),
}, (t) => [
  // Index metadata: method, operator class and access-method parameters.
  index("docs_emb_hnsw").using("hnsw").on(t.embedding)
    .opclass(t.embedding, "vector_cosine_ops")
    .with({ m: 16, ef_construction: 64 }),
]);

const nearest = await db
  .select({ id: docs.id, d: l2Distance(docs.embedding, [1, 0, 0]) })
  .from(docs)
  .orderBy(asc(l2Distance(docs.embedding, [1, 0, 0])))
  .limit(5);

• The declared dimension is a contract, validated at definition, at write time and on every distance bind — a wrong-dimension vector is a precise client-side error before any SQL runs, and pgvector enforces the same contract server-side.
• Distance/similarity operators (<-> L2, <#> inner product, <=> cosine, <+> L1) are typed expressions carrying capability requirements: engines without the extension — including Nucleus, whose vector model has no proven SQL-column semantics — reject the statement BEFORE any SQL runs. Unknown fails closed (I01).
• pgvectorExtension(driver) reports the extension state per database (installed with version / available / absent) from pg_extension and pg_available_extensions — PostgreSQL extension detection, separate from engine capabilities. Migrations that need pgvector fail loudly when the extension is missing; they never skip a queried column.
• The root vector() export remains as a deprecated alias.

Full-text search (@neutron-build/sql/fts)

Core PostgreSQL (no extension): tsvector columns, to_tsvector, websearch_to_tsquery / plainto / phraseto / to_tsquery, the @@ match predicate and tsRank / tsRankCd ranking — exact server semantics, configurations bound as regconfig parameters.

import { tsvector, toTsvector, websearchToTsquery, tsRank, matches } from "@neutron-build/sql/fts";

const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  body: text("body").notNull(),
  keywords: tsvector("keywords"),
}, (t) => [index("posts_kw_gin").using("gin").on(t.keywords)]);

const ranked = await db
  .select({ id: posts.id, rank: tsRank(toTsvector("english", posts.body), websearchToTsquery("english", "search")) })
  .from(posts)
  .where(matches(toTsvector("english", posts.body), websearchToTsquery("english", "search or ranking")))
  .orderBy(desc(sql`${tsRank(toTsvector("english", posts.body), websearchToTsquery("english", "search"))}`))
  .limit(10);

Hybrid search composes the two: order by ts_rank(...) desc, embedding <=> $1 — text relevance first, vector distance as the tie-breaker, one statement.

LISTEN/NOTIFY (@neutron-build/sql/listen-notify)

PostgreSQL's asynchronous notification channel, on ONE dedicated connection per listener. LISTEN is session-scoped, so the listener never holds a pool checkout: pgListener({ url }) owns its connection end to end, pgListener({ client }) borrows an already-dedicated pg client (closed listeners unlisten and detach but never end it), and postgresJsListener({ client }) uses postgres.js's native listen() (which manages its own subscription connection).

import { pgListener } from "@neutron-build/sql/listen-notify";

const listener = await pgListener({ url: DATABASE_URL, signal: controller.signal });
await listener.listen(["orders"], (n) => console.log(n.channel, n.payload));
await listener.notify("orders", "order-17 ready"); // payload, quoted + escaped, <=8000 bytes
await listener.close(); // or abort controller.signal — UNLISTEN, detach, end (owned only)

Delivery semantics are PostgreSQL's own: notifications fire at COMMIT (a rolled-back NOTIFY is never delivered), arrive in commit order, only while the listening session is connected, and are not persisted — a disconnect is a gap and the pg listeners do NOT reconnect (postgres.js re-listens on reconnect; messages emitted during the gap are still lost). Nucleus delivers pending notifications around the listener's own statement traffic — pass pollIntervalMs to flush an otherwise-idle listener. Channel names are rendered as case-preserving double-quoted identifiers on both engines and reported back normalized.

Vector columns in the v1 workflow (breaking, X01)

The legacy v1 skip behavior (-- NUCLEUS-ONLY (skipped on Postgres)) is withdrawn: a skipped column is later queried but nonexistent. v1 documents with vector columns now fail with a pointer to schema document v2 (whose capabilities: ["pgvector"] is detected per database at apply time).

Time-series and columnar workflows (X03)

Time-series workflows on the SQL substrate live in an OPTIONAL capability module — the SQL-only root never loads it; importing the entry point is the explicit opt-in. Everything is core PostgreSQL: no extension, no retention daemon, no server-side materialization.

Time series (@neutron-build/sql/timeseries)

import { timeBucket, tsBetween, timeSeries, hypertableSupport } from "@neutron-build/sql/timeseries";

const metrics = pgTable("metrics", {
  id: serial("id").primaryKey(),
  sensor: text("sensor").notNull(),
  ts: timestamptz("ts").notNull(),
  value: double("value").notNull(),
});

const m = timeSeries(db, metrics, {
  timestamp: metrics.ts,
  value: metrics.value,
  retention: { keepSeconds: 86_400 },                        // metadata only — nothing deletes on its own
  downsampling: [{ name: "hourly_avg", bucket: "hour", fn: "avg" }],
});

// bucketed aggregate: one statement, hand-oracle-verifiable
await m.bucketQuery({ start: "2026-09-24T00:00:00Z", end: "2026-09-25T00:00:00Z" }, { unit: "hour" });

// rolling window over the bucket sequence (Q08 composability)
const b = timeBucket(metrics.ts, "minute");
const bucketed = cteTable("minute_buckets", db.select({ b, v: avg(metrics.value) }).from(metrics).groupBy(b));
await db.select({ b: bucketed.b, prev: over(lag(bucketed.v), { orderBy: [asc(bucketed.b)] }) }).from(bucketed);

// retention: ONE delete with an exact boundary — a row exactly at the
// cutoff survives, anything 1µs older drops; pin `asOf` for determinism
await m.applyRetention();                 // reads the server clock once, then one DELETE
await m.applyRetention("2026-09-24T12:00:00Z"); // pinned: exactly one statement

• timeBucket(col, unit, { timeZone }) builds date_trunc(...) as a structural node carrying the ts-bucketing capability (probe-resolved with semantic controls, including the timezone argument — Nucleus 1.0.2 resolves unsupported and every bucket statement fails closed before any SQL runs). Bucket results follow the lossless temporal wire form: microsecond precision survives, bucket labels are exact instants. The unit/zone render as validated inline literals so the same expression is byte-identical in the projection, GROUP BY and ORDER BY (PostgreSQL matches them as one expression; $n placeholders renumber per site and would break the match). Zone-less date_trunc on timestamptz truncates in the SESSION timezone by PostgreSQL design — pass an explicit timeZone when bucket boundaries must be session-independent.
• tsBetween(col, { start, end }) is the canonical half-open range [start, end): start included, end excluded, boundaries validated at microsecond resolution and bound through the column codec (canonical strings keep all six fraction digits; Dates are millisecond exact).
• applyRetention computes the boundary in exact arithmetic (asOf − keepSeconds) and binds it as a parameter — no interval arithmetic in SQL, no wall-clock nondeterminism inside the statement.
• continuousAggregate() is disabled with a reason: PostgreSQL core has no continuous aggregates and no automatic downsampling materialization. downsampleQuery runs on demand instead (exact, one statement). hypertableSupport(driver) reports the timescaledb extension state honestly (installed / available / absent / unknown) and never claims integration — integrated is false until an engine integration is actually built and proven.
• Retention/downsampling live as typed metadata plus executable plans; PostgreSQL has no retention daemon, so retention happens only when applyRetention runs (documented, honest).

Columnar inspection (@neutron-build/sql/columnar)

import { inspectColumnarStorage } from "@neutron-build/sql/columnar";

const report = await inspectColumnarStorage(db.driver);
// PostgreSQL core: { status: "heap-only", accessMethods: [{ name: "heap", columnar: false }], integrated: false }

Read-only pg_am inspection with an honest conclusion: core PostgreSQL stores tables heap-only; a columnar table access method exists only when an extension provides one, and this ORM integrates none (integrated is always false — inspection only, never a columnar-execution claim). Engines whose catalogs cannot answer report unknown with the server's own reason — never a silent "absent".

Nucleus time-series and columnar model clients

The @neutron-build/nucleus clients (timeseries, columnar plugins) talk to the engine's own TS_* / COLUMNAR_* model surface — a different thing from the SQL substrate above. X03 gates the surfaces the X00 capability report records no evidence for with lazily-memoized semantic probes (probeTimeSeriesModel): TS_RANGE and TIME_BUCKET are proven real on the live engine (exact range points, real floor bucketing) before query()/timeBucket() trust them; a fake implementation fails closed with the probe evidence. The engine's retention is global, destructive, retroactive and irreversible — retention(days) documents exactly that, and per-series retention is not offered because the engine has no such surface. Columnar insert() binds numbers with an explicit cast (untyped binds are stored as text and the engine's numeric aggregates then silently answer 0/NULL — an upstream defect recorded with a reproducer); the store refuses inserts inside explicit transactions and is append-only. Engine evidence: conformance/live/orm/x03-nucleus-leg.mjs.

Property mapping, NULL and required keys

All select, projections, returning (insert/update/delete) and relational reads return rows keyed by the declared JS property names (firstName), never by physical SQL names (first_name). Projections use the requested key: db.select({ displayName: people.firstName }) returns rows keyed by displayName. Predicates and assignments always compile to physical qualified identifiers: .set({ firstName: "Ada" }) emits set "first_name" = $1. Mapping is a schema-metadata lookup, never a name-spelling transform — two properties whose names differ only by case style (firstName -> first_name and first_name -> "firstName") each map to their own physical column.

Table metadata (name, column map, indexes) lives in a symbol-keyed internal record, not in plain properties, so user columns named columns, tableName or indexes are ordinary columns and cannot clobber it — CRUD, returning, relational reads, DDL and schema export all work on such tables. Read metadata through the exported helpers getTableName(table), getTableColumns(table) and getTableIndexes(table); the pre-alpha plain table.tableName / table.columns properties are gone. Insert and select types are exact: required insert keys are NOT NULL-without-default columns (primary keys imply NOT NULL; serial implies a server default), nullable columns accept null, and invalid value types, unknown keys and unknown relations are compile errors as well as runtime errors.

• Nullable columns accept null: .set({ nick: null }) binds SQL NULL; the empty string stays an empty string. undefined in .set() is ignored, and an update whose .set() leaves no assignments is an error.
• Required insert keys are enforced at runtime before execution. Required = NOT NULL without default; primary keys imply NOT NULL; serial columns imply a server default. Explicit null for a NOT NULL column is rejected before execution on insert and update.
• Invalid mutation values are rejected before execution with column context: nested plain objects, arrays for non-array columns, Date for non-temporal columns, symbols and functions. Without this, the drivers silently corrupt data (pg JSON-stringifies objects into text columns; postgres.js stores "[object Object]"). json/jsonb columns accept JSON-representable values (recursively checked — no bigint, symbol, undefined or non-finite numbers anywhere inside). sql template expressions remain supported in .set() assignments on every column type except json/jsonb, where object values always bind as values.

One compiler, structural fragments

Every statement this package emits — select/insert/update/delete, projections, returning, relational aggregation — is built as a frozen AST and rendered by a single one-traversal compiler (compileStatement). Placeholders and the params array are produced in the same pass, so they can never disagree; nothing on this path ever scans or renumbers SQL text. The old regex-splicing assembly was deleted in F04, which also fixed a real precedence hole: chained .where() calls are now individually parenthesized (where (a or b) and (c or d)) instead of raw-joined with and.

Grouping guarantee (F04 rework): every operator the compiler applies — and/or/not, comparisons, the where-list join — parenthesizes its fragment and trustSql operands, so a top-level or/and/not inside spliced text can never escape the operator it was passed to (and(eq(a), sqlb or c) compiles to ((a = $1) and (b or c))). Call-form arguments need no wrap: the call's own parentheses and commas delimit each argument. Fragments nested inside fragment text compose verbatim — the author of the outer template owns that text; the compiler only delimits what it applies itself.

• sql\…`is the structural template: interpolated **values bind as parameters** (never spliced), while columns, tables, other nodes andtrustSql(text, TRUSTED_SQL_ACK)segments splice structurally. A literal"$1"inside a value or a dollar-quoted string can never collide with placeholder numbering. (This wassqlAstduring F01–F03; the names collapsed in F04 andsqlAst` remains as a deprecated alias.)
• Raw parameterized text survives only through the explicit direct-execution escape hatch: raw(text, params) executed via driver.query/driver.execute. Its $n placeholders are never interpolated into other statements — that would require scanning raw SQL. Passing a legacy {sql, params} fragment into ANY builder slot (where, orderBy, set, projections, connective arguments, insert values, relational args, template interpolation) throws the same fail-closed rejection with instructions to rebuild it with sql, bind a plain value, or run it directly via raw() + driver.query.
• Builders are immutable: every fluent call (.where(), .set(), .values(), .limit(), …) returns a new frozen builder. Reusing a base query in two requests cannot leak filters between them, and .toSQL() is pure — the same builder state compiles to byte-identical SQL.
• Compiled statements carry their projection decode plans (built from the serializable per-column ColumnCodec) and their engine capability requirements (statements using to_jsonb acquisition or json_agg relational aggregation carry jsonb-functions; engines without those functions must reject them).
• The compiler reserves the __q<number> alias namespace for generated derived-table aliases; user aliases in that namespace are rejected.

Migration note (F04, pre-1.0 breaking changes). The sql template now returns a structural fragment node instead of {sql, params}; destructured .sql/.params accesses on eq()/and() results no longer exist (inspect conditions through .toSQL()/.toCompiled()). asc()/desc() return order specs (still accepted by orderBy), as do the explicit null-ordering variants ascNullsLast()/ascNullsFirst()/descNullsLast()/ descNullsFirst() (required on keyset terms). Update/delete where clauses and returning lists are now uniformly parenthesized/qualified — semantics unchanged, byte-level SQL text shifted. Legacy fragments are raw() + direct execution only.

Lossless value codecs (and the corrected pre-1.0 types)

Values never silently lose precision. Schema-known columns are acquired as lossless text in SQL (to_jsonb(col)::text temporals, ::text int8/numeric inside JSON aggregations) or as driver-native values that are already exact (int8/numeric strings, bytea buffers), then decoded per column mode. No global driver parser is ever mutated, and lossless acquisition happens before any driver Date parsing or JSON.parse — a decoder cannot recover microseconds a Date already dropped or digits a double already rounded.

| SQL type | Default TS value | Notes | |---|---|---| | int2/int4/serial | number | integer + range validated on write | | int8 (bigint) | bigint | never passes through JS Number; mode: "string" keeps the exact string; mode: "number" is a checked safe-number mode that rejects values outside ±(2^53−1) | | numeric | string | exact decimal string (scale and trailing zeros preserved); optional per-column decoder — a decoder that narrows to number owns the precision loss | | float4/float8 | number | non-finite numbers (NaN/±Infinity) are rejected on write | | date | string (YYYY-MM-DD) | no timezone interpretation; Date values are rejected on write | | timestamp (without tz) | string (YYYY-MM-DDTHH:MM:SS[.ffffff]) | timezone-free canonical string, microseconds preserved; explicit mode: "date" interprets the wall clock as UTC and truncates to milliseconds | | timestamptz | string (…Z, canonical UTC) | microseconds preserved, independent of process and server timezones; explicit mode: "date" gives the exact instant at millisecond precision | | boolean/text/uuid | boolean/string/string | uuid format validated on write | | bytea | Uint8Array | drivers hand back buffers | | json/jsonb | unknown | SQL NULL vs JSON null distinguished on writes (below) |

Temporal writes take canonical strings (microsecond-exact) or Date values. A Date carries only an instant at millisecond precision: it stores its UTC wall clock into timestamp columns — the same Date stores the same value on every machine and process timezone — and its exact instant into timestamptz. infinity/-infinity temporal strings pass through; Date mode rejects them. Canonical write strings are validated with column context (timestamps reject offsets; timestamptz requires Z or ±HH:MM; dates must be exactly YYYY-MM-DD).

SQL NULL vs JSON null (writes). For json/jsonb columns the JS value is the JSON value: null binds SQL NULL, the exported jsonNull sentinel writes the JSON null value, and every other JSON-representable value (including strings, booleans and nested arrays) is JSON-encoded by the codec — "null" the string stores the JSON string "null", never JSON null. Timestamp/json parameters bind as canonical text at explicitly text-typed sites ($n::text::jsonb), which both drivers pass through untouched: postgres.js otherwise re-encodes server-typed date/json parameters through new Date(...).toISOString() (timezone shift + millisecond truncation) and double-JSON.stringifys strings. On read, SQL NULL and JSON null both arrive as JS null — the distinction is preserved for writes and queryable with sql fragments (data is null vs data = 'null'::jsonb).

Predicate values (eq/ne/lt/lte/gt/gte/inArray) run through the same codec: validated with column context and bound at the same safe sites. Values interpolated into a raw sql\…`` template bind as plain parameters — no column codec touches them (use the typed predicates when you want validation).

Migration note (corrected pre-1.0 types). Before this change: bigint columns read as string, timestamp/timestamptz/date columns read as Date (driver-parsed — microseconds truncated, timestamp reads shifted by the process timezone, date reads disagreed between drivers), relation-child bytea leaves read as \x hex strings, and json string writes were interpreted as JSON text. Now int8 reads bigint by default (parse with a safe-integer check, or declare mode: "string"), temporals read canonical microsecond-exact strings (declare mode: "date" where a Date is worth the millisecond truncation), temporal writes accept canonical strings as well as Dates, date writes take YYYY-MM-DD strings only, and json writes encode the JS value. Code that relied on number for int8 or Date for temporals must switch to the new types or opt into the explicit modes.

Joins and aliases

All five join forms — innerJoin, leftJoin, rightJoin, fullJoin, crossJoin — are available on the typed select builder. Joins take alias() handles, not raw tables: const p = alias(posts, "p"), then .innerJoin(p, sql${p.userId} = ${users.id}). The alias is the table's identity inside the statement — every reference (p.title in projections, eq(p.title, …) in predicates, asc(p.id) in order specs) compiles to the alias-qualified "p"."title", which is what makes self joins and same-name tables unambiguous. Two joins may not share an alias, and a join alias may not collide with the from table's name — both fail before any SQL runs.

• ON conditions are conditions (the same AST values where takes): typed predicates (eq(p.authorId, 7)), sql fragments, and and/or/ not combinators all work. The grouping guarantee applies: fragments passed to an operator are delimited, so and(sqla or b, eq(…)) keeps the intended grouping. Column-to-column comparisons are sql templates interpolating two columns (sql${p.userId} = ${users.id}``). Cross joins take no ON.
• Outer-join nullability is typed. A column from the nullable side of a left/right/full join reads as its declared type | null — including NOT NULL columns, which the outer join can still null out. leftJoin nulls the joined alias; rightJoin nulls the from side; fullJoin nulls both; innerJoin/crossJoin add no nulls. Nulls decode through the same codecs as everything else: a left-joined timestamp cell that is SQL NULL reads null, never an epoch string, and int8/numeric NULLs stay null.
• Output mapping rule. With an explicit projection the row keys are exactly the projection keys — JavaScript object keys are unique, so two same-named columns (from two schemas, or a self join) simply need two projection keys ({ pub: users.email, alt: au.email }). Without an explicit projection the row is the from table's columns only — joined tables never leak into the default projection, so property keys cannot collide.
• Parameter order is deterministic: projections first, then each join's ON condition in call order, then where, then order by — $1..$n follow that traversal; limit/offset render inline.
• Expression projections over joined columns (sql${o.qty} * ${2}``) type as unknown — there is no column codec behind an arbitrary expression.
• Builders stay immutable: every .join…() call forks, so a base query can be reused with different joins without leakage, and .toSQL() is pure.

Schema-qualified tables

pgSchema("legacy").table("users", { … }) declares legacy.users. The query layer supports them end to end: CRUD renders qualified targets (insert into "legacy"."users"), alias joins render qualified join targets, and predicates/projections reference schema-qualified columns — public users and legacy users can be joined in one statement under distinct aliases. Schema export v2 (exportSchemaV2) exports them under their schema. The legacy paths — schemaToDDL and the version-1 exportSchema — and relational reads (db.query, the tables/relations inputs) reject schema-declared tables with explicit errors: they would otherwise address or export the wrong (search-path) identity.

Subqueries, CTEs, aggregates and set operations

• Grouping and aggregates. .groupBy(col | expr, …) sets the grouping terms; .having(cond) takes the same AST values .where() takes (aggregates, sql fragments, and/or/not — fragments are delimited, the grouping guarantee applies); .distinct() emits plain select distinct. Typed aggregates — count(), count(col), countDistinct(col), sum, avg, min, max, stringAgg(col, sep), boolAnd, boolOr — project and order (desc(count())) with result types that follow PostgreSQL exactly: count returns bigint through the int8 codec and is never null (0 on empty input); sum over integer columns returns bigint, over int8/numeric columns exact decimal strings, over floats numbers; avg returns exact strings except over floats; and sum/avg/min/max/string_agg/bool_and/bool_or are null on empty input. min/max mirror their argument column's codec (temporal modes honored; int8 modes honored) — over a derived/CTE temporal column the aggregate parses the materialized canonical text back to the temporal type first (min(col::timestamp)), so composed timestamptz never consults the session timezone. A grouped query over an empty table returns zero rows — no groups exist.
• Derived tables and CTEs. derivedTable(name, source) and cteTable(name, source) turn any select builder into a table-like handle; the row type derives from the source's projection outputs. derivedTable inlines (select …) as "name" at each reference (from or join); cteTable references the bare name and auto-registers with "name" as (…) on the consuming statement — referencing the same handle twice registers one CTE, and two different statements under one name fail closed. Handles join under their own name (no alias() wrap; re-aliasing a handle is rejected because it would lose the subquery), and outer joins over a handle null its columns exactly like alias handles. Source capability requirements merge into the consuming statement. Temporal columns compose losslessly: a derived/CTE level materializes the canonical text wire form, and the outer level re-acquires it with the naive pass-through to_jsonb(col::timestamp)::text — for timestamptz too (the canonical text IS the UTC wall clock; re-parsing with ::timestamptz would consult the session timezone and shift the value per composition level). The acquisition is idempotent, so values stay byte-exact through any depth in any session timezone.
• CTE references inside sql fragments resolve or fail closed. Interpolating a cteTable handle (or one of its columns) into a sql template renders the bare name, branded with the handle's CTE identity; compilation fails when the consuming statement does not carry that exact CTE in scope — an unregistered reference, or a same-name CTE built from a different statement that would silently capture it, is a compile-time error rather than a silent bind (or a missing-relation database error). Reference the handle from from/joins (its CTE registers) or register the same source with withCte. Interpolating a whole derivedTable handle into a fragment is rejected outright — a derived table exists only at its inline from/join site. Hand-typed ident/qual references stay trusted text (never scanned).
• Recursive CTEs. with recursive is computed structurally: a CTE whose statement references its own name (as a from/join target or qualified reference) renders recursively. Reference the working table through ident("name") / qual("name", "col") nodes in the recursive branch — a self-reference hand-typed inside sql fragment text is not scanned (this compiler never parses SQL text) and fails at the database instead. Cycle safety is the SQL contract: traverse cyclic graphs with union (deduplicating) or an explicit depth bound on union all.
• Correlated subqueries. sql templates interpolate subqueries and builder .subquery() nodes anywhere a value goes — scalar subqueries in projections, in (select …) lists, ON conditions — with parameters binding in traversal order. exists(source) builds the exists predicate.
• Set operations. .union/.unionAll/.intersect/.intersectAll/ .except/.exceptAll(other) on a select builder (and on the resulting compound) compose left-associatively in call order — branches render parenthesized, so SQL's intersect-over-union precedence never reorders your chain. Result rows are typed by (and decode through) the first branch — PostgreSQL takes output column names from it, so branch projections must be structurally aligned. orderBy/limit/offset after a set operation apply to the whole compound; a first branch that already carries them fails closed instead of silently rebinding them.
• Parameter and alias composition is deterministic through three levels: outer projections bind first, then derived-table/CTE bodies, then where/group/having, then set-op branches, then order by — $1..$n follow that traversal in every composed shape.
• Derived/CTE handles are query-surface identities: mutations, DDL, schema export and db.query registration reject them.

Conflict handling and upserts

db.insert(table).values(...) chains into PostgreSQL's ON CONFLICT clauses (Q03). Both actions compile through the same one-traversal AST — placeholders bind values first, then do update set assignments, then predicates, so $1..$n are deterministic in every shape.

// ignore unique violations: conflicted rows are simply not inserted
await db.insert(members).values(rows).onConflictDoNothing();

// partial unique index: the target carries the index predicate
await db.insert(soft).values(row).onConflictDoNothing({
  target: [soft.email],
  where: sql`${soft.deletedAt} is null`,
});

// the upsert: excluded(col) references the row proposed for insertion
await db.insert(members).values(row)
  .onConflictUpdate({
    target: members.email,                    // a column, a composite array,
                                              // { constraint: "name" } for
                                              // ON CONSTRAINT, or
                                              // { columns, where } for a
                                              // partial unique index
    set: { hits: sql`${excluded(members.hits)} + 1`, score: "2.50" },
    setWhere: sql`${members.hits} < ${10}`,   // conditional update (optional)
  })
  .returning(["email", "hits"]);              // selected subset, losslessly
                                             // decoded like every projection

• Targets. A single column, a composite list, { columns, where } with the index predicate (partial unique index inference), or { constraint: "name" } for on conflict on constraint. DO UPDATE requires a target (PostgreSQL rejects targetless DO UPDATE); DO NOTHING without one lets PostgreSQL arbitrate over any unique index. Target columns must belong to the inserted table; an index predicate is only valid with a column-list target.
• excluded(col) renders excluded."col" — the proposed row — in SET assignments and DO UPDATE WHERE predicates. This scope is enforced at the compile choke point on every statement kind: excluded() anywhere else — a select's where/having/order/projections, an update's sets/where/returning, a delete's where/returning, an insert's RETURNING, the conflict target's index predicate, or inside any subquery — fails before SQL with one message instead of as a database error (those positions cannot see the pseudo-relation; PostgreSQL rejects them at execution with "missing FROM-clause entry" / "invalid reference to FROM-clause entry"). References naming a column the table does not have fail before SQL the same way.
• INSERT ... SELECT is not implemented. .values() accepts plain row objects only (an array or single object); handing it anything else — e.g. a select builder — surfaces the unknown-column/row-shape rejection rather than composing a set-returning insert. Composing inserts from queries is a later card; use explicit SQL through the driver in the meantime.
• SET values follow update .set() semantics: literals run through the column codec (validated, canonically encoded), sql fragments and expression/excluded/subquery nodes splice structurally on non-json columns, and null respects NOT NULL.
• Returning arity is honest. Upsert returning yields one row per input row that was inserted or updated (0..n). DO NOTHING omits conflicted rows from returning; a DO UPDATE ... setWhere that rejects a conflicted row means it is neither updated nor returned. The non-returning insert resolves to the affected-row count (0 when everything conflicted away).
• Returning subsets. .returning(["email", "hits"]) on insert and update builders projects exactly those property keys — same lossless codec path as full returning (int8/numeric exact, temporal microseconds, bytea bytes). Unknown or duplicate keys are rejected before SQL.
• Batch atomicity is statement atomicity. One .values([...]) call compiles to ONE multi-row insert statement: it either lands whole or fails whole (a mid-batch unique violation without on conflict leaves nothing behind). Splitting large imports into chunks is a caller policy — chunk boundaries, per-chunk error handling and resume are deliberately not implemented here and belong to future import tooling.
• Duplicate assignments error deterministically. Assigning the same physical column twice — two .set() calls, two property keys mapping to one physical column in a .set()/insert row/on-conflict SET map, a duplicated conflict-target list — throws before SQL with one message regardless of object key or call order. (Overlap between the insert's columns and DO UPDATE SET is legal upsert semantics, not a conflict.) Two rows in one batch that conflict with each other under DO UPDATE surface PostgreSQL's own error — ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000) — unchanged, with the statement atomic (nothing lands).

Keyset pagination

keyset() builds a cursor (seek) pager over an explicitly ordered keyset:

import { keyset, ascNullsLast, descNullsFirst } from "@neutron-build/sql";

const pager = keyset(events, [ascNullsLast(events.occurredAt), descNullsFirst(events.rank)], { perPage: 20 });

const page1 = await pager.page(db.select().from(events), undefined, 20);
// page1.rows: up to 20 rows; page1.nextCursor: opaque string, or null after the last page
if (page1.nextCursor !== null) {
  const page2 = await pager.page(db.select().from(events), page1.nextCursor, 20);
}

• Unique ordering is enforced. The keyset must contain a schema-declared unique key (single-column PK/unique, the composite PK, or a unique index). When it does not, a unique tie-breaker column is auto-appended (asc nulls last — the single-column PK first, then any unique column, then a composite key) and exposed as pager.columns[...].autoAppended; with { autoAppendTiebreaker: false } a non-unique keyset errors instead. A table with no unique key at all always errors — ties would duplicate/omit rows at page boundaries.
• Null ordering is explicit and required. Every term must come from ascNullsLast()/ascNullsFirst()/descNullsLast()/descNullsFirst() — those helpers also work in plain .orderBy(). nulls last places NULL at the END of the ordering in both directions, nulls first at the start; the seek predicate decomposes mixed directions with tie comparisons (IS NOT DISTINCT FROM on nullable columns, = on NOT NULL ones).
• Cursors are opaque, versioned and strictly validated. base64url(version byte + JSON payload). Values are tagged and precision-exact: int8 rides exact decimal strings (never through JS Number), numerics their exact decimal text, temporals the canonical microsecond text, NULL positions explicit. A cursor that is not valid base64url, carries an unknown version byte, malformed JSON/values, values whose tags disagree with the column types, unknown envelope or value fields, or was built for a DIFFERENT keyset ordering fails with CursorError before any SQL runs. Cursors are JSON-safe strings (safe to embed in URLs/bodies) and deterministic.
• Precision limits, honestly: temporal keyset columns must use the default string mode (mode: "date" truncates to milliseconds and would break boundaries — rejected), numeric keyset columns must not carry a user decoder (decoded values cannot re-encode losslessly — rejected). int8 columns work in any read mode; float columns round-trip through shortest-round-trip decimal text (exact for IEEE doubles).
• Page mechanics. page() fetches perPage + 1 rows so hasMore is known without a count query, returns the first perPage rows, and derives nextCursor from the page's last included row. apply() composes seek + order + limit without mutating the source builder — and the builder must be fresh: one that already carries ORDER BY terms or an OFFSET is rejected with an error before any SQL runs (composed silently, a leading user order term or a persisting offset duplicates and omits rows — the seek predicate is only correct when the keyset ordering is the statement's total ORDER BY). To combine, filter a fresh builder — db.select().from(t).where(...) — and let the pager apply ordering and limit exclusively; to keep your own ordering, paginate the builder directly with .limit()/.offset() instead of the pager.
• Concurrency (each page is its own statement, default READ COMMITTED): rows inserted between pages appear exactly when they sort after the then-current cursor — before-cursor inserts never reappear, so the pre-existing snapshot is never duplicated or omitted; rows sorting after the cursor appear on the first page query executed after their insert. Rows deleted/updated across their own boundary can be missed or moved — that is inherent to seeking. For one immutable snapshot across a whole walk, wrap it in a single REPEATABLE READ transaction (set transaction isolation level repeatable read as the transaction's first statement) — pinned live in the Q04 suite.

Prepared execution

driver.prepare(sql) (optional Driver member — the capability gate) returns a PreparedStatement bound to that adapter; preparedStatement(driver, sql) is the fail-closed accessor for custom adapters (absent prepare errors clearly rather than silently running unnamed execution).

const stmt = db.driver.prepare!("select id from users where email = $1");
const rows = await stmt.query<{ id: number }>(["[email protected]"]); // re-parses per connection only once
const affected = await db.driver.prepare!("update users set seen = true where email = $1").execute(["[email protected]"]);

• **Prepared statements are connection-scoped (session-scoped on the server); the adapters' semantics differ and both are