Shared SQL compiler

@zeruxjs/db-sql-core#

Shared parameterized SQL compiler and connector executor for PostgreSQL, MySQL and SQLite. It is an internal library; applications should normally use @zeruxjs/db plus the appropriate connector.

createSqlConnector(dialect, options) compiles the normalized JSON operation into dialect-specific SQL and normalizes returned row counts and insert IDs. Insert IDs are emitted only for actual insert operations, not DDL or updates; SQLite bigint IDs are converted to strings where necessary to preserve precision.

Trust boundary: Query values are parameterized. Column/table identifiers are validated upstream by @zeruxjs/db and quoted by the dialect. Do not expose unrestricted schema operations or raw SQL to untrusted callers, and use database credentials with least privilege. Each SQL operation executes separately unless the application explicitly manages a native transaction.

Safety contract: compiler validates identifiers and runtime operator/function/join/order/index enums independently of TypeScript; SQL CHECK text strings are rejected, use a validated structured expression. Column lengths, integer limits and INSERT row shapes are checked. Unsupported RETURNING (notably MySQL) throws rather than being silently ignored. PostgreSQL write results expose affectedCount from driver rowCount, including zero-row CAS misses. MySQL and SQLite index/drop syntax and SQLite integer primary key autoincrement use engine-specific rules.

Structured connector capability metadata now advertises dialect returning and schema/index subsets. Transactions and cancellation remain unsupported by this generic executor, even when a native pool has those APIs. Normalized DML counters retain exact engine semantics (counts.affectedMeaning: 'engine'), including zero; no affected count is inferred from returned rows. See @zeruxjs/db for pending/deadline policy and keyset contracts. These capabilities do not imply every schema action is supported by every dialect.

Direct compiler boundary#

compileSql(dialect, operation) returns {statements, params} without opening a connection. createSqlConnector uses the same validation boundary, independently of @zeruxjs/db normalization. Input must be own-property JSON data: getters, cycles, prototypes, functions, undefined, nonfinite numbers and unsafe property names are rejected. Inputs are bounded to 8 MiB, depth 32, 20000 nodes and 10000 array entries. Unknown operation/expression/clause fields fail rather than disappearing; in particular upsert/replace/ignore/conflict clauses are deliberately unavailable. Query arrays belong to the database manager, not this single-operation compiler.

Identifiers are validated here and quoted by the trusted dialect callback; aliases/DDL column names are flat identifiers. Operators, function names/arity, joins, ordering, foreign actions, booleans and index methods are checked at runtime. Raw identifier/expression/CHECK text cannot bypass this boundary. Dialect callbacks remain trusted executable code, not a sandbox. The factory snapshots dialect data so later mutation does not change its advertised capabilities. The factory never opens or closes an injected client/pool; the owning connector controls its resources and failure cleanup.

import { compileSql, type SqlDialectDefinition } from '@zeruxjs/db-sql-core';

const dialect: SqlDialectDefinition = {
  kind: 'pgsql', quoteIdentifier: name => `"${name}"`,
  placeholder: index => `$${index}`, functions: { LEN: 'LENGTH' },
  supportsReturning: true, supportsFetch: true,
  autoIncrementKeyword: 'GENERATED BY DEFAULT AS IDENTITY'
};
const compiled = compileSql(dialect, {
  op: 'update', table: 'jobs', values: { state: 'claimed' },
  where: { and: [
    { field: 'id', operator: 'eq', value: 'example-job' },
    { field: 'revision', operator: 'eq', value: 1 }
  ] }, returning: ['id']
});
// SQL uses $1, $2, $3; values remain in compiled.params[0].
console.log(compiled.statements[0]);

Do not log params or return complete compilations/results to clients. SqlCompilation is owned here; shared operations, structured CHECK predicate types and counts remain owned by @zeruxjs/db.

Deliberate dialect limits#

• RETURNING requires supportsReturning: true; MySQL remains unsupported. supportsDdl: false rejects all schema/index operations and reports them unavailable. supportsGeneratedKeys: false rejects auto-increment definitions. Omitted DDL/generated-key flags retain the existing dialect defaults. Generated-key support refers to schema generation, not a promise to retrieve all generated IDs. Driver/server versions and permissions must still support the declared dialect; injected executors do not certify backend features.
• No upsert API or conflict policy is implemented. Unknown upsert, onConflict, duplicate-key, ignore and replace clauses reject before execution. No transaction/cancellation/native aggregation capability is added by this compiler.
• MySQL index guards (IF NOT EXISTS), NULLS ordering, FULL JOIN, ILIKE, expression indexes in this subset, non-BTREE/HASH methods and foreign SET DEFAULT are rejected. Binary concat is rejected there because || normally means logical OR; use the structured CONCAT function. PostgreSQL/SQLite binary concat uses ||.
• SQLite RIGHT/FULL joins, ANY/ALL, ILIKE, USING index methods and database create/drop/switch are deliberately unavailable even when a newer SQLite version offers some of them. PostgreSQL database switching and CREATE DATABASE IF NOT EXISTS are unavailable. Database force-drop and unsupported cascade/restart-identity clauses reject. MODIFY COLUMN remains MySQL-only; there is no implied cross-dialect schema migration.
• SQLite's historical truncate compatibility is compiled as DELETE FROM, with its real affected-row count; it does not reset identity or implement PostgreSQL TRUNCATE semantics. Trigger/foreign-key behavior follows DELETE. Restart/cascade flags reject.
• Index guards cannot be silently omitted. FETCH count translates to LIMIT only when no WITH TIES semantics are requested. Supported FETCH WITH TIES requires ordering and emits ROWS WITH TIES, following PostgreSQL SELECT. Conflicting limit/top/fetch specifications reject. Limits include zero (maximum 100000); offsets are 0–1000000, and MySQL/SQLite require a limit for a positive offset. Compound selects with per-arm ordering/pagination are deliberately rejected; no implicit rewrite changes their meaning.
• YEAR/MONTH/DAY and two-argument DATE_DIFF are MySQL-only in this subset. DATE_ADD is unavailable because its interval contract is not defined. SQLite CONCAT and PostgreSQL/SQLite DATE_DIFF are rejected rather than mapped to semantically different functions. CURRENT_DATE/CURRENT_TIMESTAMP are zero-argument SQL keywords; SQLite NOW maps to CURRENT_TIMESTAMP. Supported functions use bounded arity, not arbitrary executable SQL.

Values, CHECKs and result precision#

eq null/ne null compile to IS NULL/IS NOT NULL without a parameter. Other direct NULL comparisons reject; use explicit null predicates. IN and NOT IN require a nonempty array (maximum 10000); empty membership remains a validation error. NULL inside a nonempty list keeps native SQL three-valued behavior. Multi-row INSERT requires identical nonempty own column sets; no columns are silently discarded. Aggregate aliases and nested expressions use the same validated identifier boundary. Placeholder numbering follows lexical SQL order across projection values, joins, subqueries, filters, unions and composite keyset predicates; each statement has its own parameter list.

CHECK accepts a structured predicate such as {field:'amount',operator:'gte',value:0}, or the existing structured expression/boolean subset. No raw CHECK string or CHECK subquery is accepted. Aggregates and current-time functions are disallowed in CHECK/index expressions. DDL cannot portably bind ordinary query placeholders, so validated CHECK/default/index literals are encoded inline and never discarded from a parameter list. DDL string literals reject control characters/backslashes and double embedded quotes. Query values continue to be bound, including hostile text. These restrictions follow the limited contract, not every engine's expression grammar; SQLite CHECK also forbids subqueries. Defaults referencing columns, contradictory default/auto-increment definitions and unused column-size options reject. PostgreSQL binary/blob/datetime/double types map to BYTEA/TIMESTAMP/DOUBLE PRECISION; MySQL UUID maps to CHAR(36).

Zero affected counts stay zero. Native integer counters must be safe nonnegative integers. Insert IDs are emitted only for inserts; bigint SQLite IDs become strings, and unsafe numeric generated IDs reject with UNSAFE_GENERATED_KEY because lost precision cannot be reconstructed. An error discovered after a write does not roll back that write. RETURNING arrays expose returned rows/count, without inventing affected rows or generated IDs. This is not a guarantee of arbitrary NUMERIC/DECIMAL, timestamp or timezone round-trip precision; those remain driver/application policies.

Focused checks include direct compiler failures, executor outage, injected pool ownership, nested/keyset placeholders and disposable native SQLite CHECK/RETURNING/DELETE behavior when node:sqlite is available. PostgreSQL/MySQL backend versions, native transaction behavior and numeric/time precision need separately configured live acceptance; local compilation and injected results do not prove them.

Migration note: callers that relied on MySQL silently discarding index guards must choose an explicit owning migration policy. Analytics already fails closed for numeric backfill when its unique index cannot be verified; its existing indexes:false,numericUniqueIndexManagedExternally:true policy is appropriate only after the operator provisions that unique constraint. Optional read-index failures retain their existing degraded status. The compiler never automatically retries with an unguarded CREATE INDEX.

SqlConnectorOptions.synchronous:true is an explicit owner contract for synchronous executors. Single- and multi-statement execution rejects thenables without creating an async continuation that could issue later DDL outside a native transaction. SQLite uses this path for its separate transactionSync API; it does not enable the shared async transaction capability.

Implementation and ownership map#

src/validation.ts owns bounded own-data snapshots and exact clause/list/flag validation. It rejects accessors before reading values and never invokes toJSON. src/index.ts owns a fresh per-operation compiler, lexical parameter allocation, explicit DDL literal mode, dialect support guards and native result normalization. Shared operation/count types remain in @zeruxjs/db; concrete adapters own pool/session/cancellation behavior.

The compiler snapshots dialect metadata, but quoting/placeholders and executor functions remain trusted executable host code. Root runtime imports require Node. compileSql opens nothing, and createSqlConnector borrows its native executor without supplying disconnect, transaction or cancellation ownership. Do not add a fake close path or treat it as a tenant authorization boundary. Native results, generated SQL, parameters and original causes remain private until the host sanitizes a response.

The focused suite preserves exact SQL/parameter fixtures and uses disposable native in-memory SQLite for CHECK/RETURNING/keyset/delete behavior. PostgreSQL/MySQL consumers also exercise their injected pinned contracts. These checks do not qualify live engine versions, transactional DDL across dialects or application-specific parser/precision policy.