SQLite Dialect

SQLite Dialect Overview SQLite is an embedded, serverless, zero-configuration SQL database engine that stores an entire database (schema, tables, indexes, data) in a single file on disk — or entirely in memory. There is no separate server process: the database runs in-process with your application. This ORM's SQLiteDialect (in src/dialects/sqlite/index.ts) is built on the synchronous better-sqlite3 driver. Because the engine is embedded, several operations that are network round trips in client/server dialects are pure in-process calls here — most notably streaming, which uses better-sqlite3's native Statement#iterate() cursor rather than a paginated LIMIT/OFFSET fallback. Key facts (from src/dialects/sqlite/index.ts): Dialect name is 'sqlite'; the underlying driver library is 'better-sqlite3'. Identifiers are double-quoted: escapeId('col') → "col", with embedded " doubled (""). Foreign key enforcement is off by default in SQLite, so connect() explicitly runs PRAGMA foreignkeys = ON on every new connection. SQLite's dynamic type system means the ORM's type mapping produces column affinities rather than strictly-enforced types (unless STRICT tables are used — see below). Connection SQLiteDialectOptions extends DialectOptions and adds SQLite-specific fields. The top-level PrormOptions exposes dialect and storage; the remaining driver options (readonly, fileMustExist, timeout, verbose) pass through dialectOptions. Notes: storage defaults to ':memory:'. An in-memory database is discarded when the connection closes. storage may also point at an attached-database path or :memory: for use with attachDatabase(). On connect(), the constructed better-sqlite3 options are { readonly, fileMustExist, timeout, verbose }, and PRAGMA foreignkeys = ON is issued immediately. Type mapping getDataTypeSql(dataType, columnName?) maps the ORM's type descriptors to SQLite column types. Because SQLite uses type affinity, several logical types collapse onto TEXT, INTEGER, or BLOB. ORM type descriptor | Generated SQL | --------------------------------------------- | -------------------------------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'CHAR', length: 4 } | CHAR(4) | { key: 'TEXT' } | TEXT | { key: 'INTEGER' } | INTEGER | { key: 'INTEGER', unsigned: true } | INTEGER UNSIGNED | { key: 'BIGINT' } | BIGINT | { key: 'FLOAT' } | FLOAT(10,2) | { key: 'DOUBLE' } | DOUBLE(10,2) | { key: 'DECIMAL', precision: 12, scale: 4 } | DECIMAL(12,4) | { key: 'BOOLEAN' } | INTEGER (SQLite has no boolean type) | { key: 'DATE' } | DATETIME | { key: 'DATEONLY' } | DATE | { key: 'TIME' } | TIME | { key: 'BLOB' } | BLOB | { key: 'JSON' } / { key: 'JSONB' } | TEXT (JSON stored as text) | { key: 'UUID' } | TEXT | { key: 'GEOMETRY' } | BLOB | { key: 'ENUM', values: ['a','b'] } | TEXT CHECK("col" IN ('a','b')) | { key: 'VIRTUAL' } | '' (no column emitted) | 'TEXT' (raw string) | TEXT (passed through verbatim) | Booleans have no dedicated SQLite type, so BOOLEAN maps to INTEGER, and the escape()/bind-coercion paths convert JS true/false to 1/0. Date values are serialized to ISO-8601 strings; plain objects/arrays are JSON.stringify-ed; Buffer values are emitted as X'...' blob literals. ENUM emulated as TEXT + CHECK SQLite has no native ENUM. The object form { key: 'ENUM', values: [...] } and the raw string form ENUM('a','b') both compile to a TEXT column with a CHECK(... IN (...)) constraint that references the actual column being defined (via the columnName argument), so the constraint validates the correct column: SQL generation & dialect features DDL — CREATE TABLE, STRICT, WITHOUT ROWID createTable() builds a standard CREATE TABLE, optionally with IF NOT EXISTS. Two SQLite-specific table modifiers are supported after the column list and can be combined as ) STRICT, WITHOUT ROWID: strict: true → appends STRICT (SQLite 3.37+ rigid type checking). withoutRowid: true → appends WITHOUT ROWID (stores rows keyed by the PRIMARY KEY instead of the implicit rowid). AUTOINCREMENT is emitted only when a column is both primaryKey and autoIncrement and its resolved type is exactly INTEGER — this mirrors SQLite's rule that AUTOINCREMENT is valid only on an INTEGER PRIMARY KEY. Options that other dialects honor but SQLite cannot are ignored with a console.warn: engine, charset, table-level collate, rowFormat, and initialAutoIncrement. uniqueKeys and indexes are realized as separate CREATE [UNIQUE] INDEX statements after the table is created. Indexes addIndex() emits CREATE [UNIQUE] INDEX IF NOT EXISTS, and supports partial indexes (where, SQLite 3.8+) and expression indexes (expression, SQLite 3.9+, wrapped as ((expr))). INSERT and upsert (ON CONFLICT) buildInsertQuery() produces a parameterized INSERT, inlining Literal values directly rather than binding them. With no columns it degrades to INSERT INTO ... DEFAULT VALUES. Upserts use SQLite's ON CONFLICT syntax: ON CONFLICT ... DO UPDATE requires an explicit conflictFields target (SQLite cannot infer it), and the dialect throws a clear error if it is missing: DO NOTHING is the one form that may omit a target: buildUpsertQuery() behaves the same way, additionally honoring updateOnDuplicate (restrict the SET list) and returning (SQLite 3.35+ RETURNING or a column list). Chained multi-target conflicts SQLite allows several ON CONFLICT clauses in one INSERT, each targeting a different constraint and evaluated in order. Pass conflictTargets (a ConflictTarget[]), which takes precedence over conflictFields/doNothing: Each ConflictTarget supports fields (required), action ('DO NOTHING' | 'DO UPDATE', default DO UPDATE), updateColumns (defaults to all inserted columns), and a raw where restricting when the DO UPDATE applies. UPDATE / DELETE / RETURNING buildUpdateQuery() and buildDeleteQuery() build parameterized statements with an optional LIMIT and an optional RETURNING clause (SQLite 3.35+). Literal values in an UPDATE set are inlined. WHERE-clause operators buildWhereClause() produces parameterized SQL and supports both string keys ($gt) and Symbol operators (normalized via operatorToWhereKey). Sibling $and/$or/$not are AND-ed with plain field conditions rather than replacing them, and multiple operators on one field (e.g. { age: { $gte, $lte } }) are split and AND-ed. Operator | SQL | -------------------------------------------- | ----------------------------------------------- | $eq / $ne | = ? / != ? | $gt / $gte / $lt / $lte | > ? / >= ? / < ? / <= ? | $like / $notLike | LIKE ? / NOT LIKE ? | $iLike / $notILike | LIKE ? / NOT LIKE ? (LIKE is ASCII-case-insensitive) | $startsWith / $endsWith / $substring | LIKE ? with % padding on the bound value | $in / $notIn | IN (...) / NOT IN (...) | $between / $notBetween | BETWEEN ? AND ? / NOT BETWEEN ? AND ? | $isNull / $isNotNull / $is / $not | IS NULL / IS NOT NULL / IS ? / != ? | $contains | jsoneach(...) subquery counting matched elements | $col | "key" = "otherColumn" (column-to-column) | A bare array value is treated as an IN: JSON (JSON1) support SQLite stores JSON as TEXT and operates on it via the JSON1 extension. The dialect exposes builders that emit JSON1 functions: buildJsonExtract(col, path, asText?) → jsonextract("col", '$.path'), wrapped in CAST(... AS TEXT) when asText (the default) is set. buildJsonContains, buildJsonHasKey, buildJsonPathQuery — containment / key-existence / path comparisons. buildJsonGroupArray, buildJsonGroupObject — the jsongrouparray() / jsongroupobject() aggregates for rolling child rows into nested JSON. buildJsonPatch(col, patch) → jsonpatch("col", json(?)) (RFC 7396 merge patch; keys set to null are removed). Transactions & savepoints The outer transaction runs a real BEGIN TRANSACTION / COMMIT / ROLLBACK; nested transactions are emulated with SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT. Standalone savepoint SQL is available via createSavepointSQL, releaseSavepointSQL, and rollbackToSavepointSQL. Retry on lock contention query() supports retry options and, by default when retry is enabled, retries on SQLITEBUSY, SQLITELOCKED, database is locked, and database table is locked — the transient errors that arise when multiple connections contend for the single database file. Quirks & limitations No ALTER TABLE ADD CONSTRAINT / DROP COLUMN / ALTER COLUMN. SQLite's ALTER TABLE is limited, so these are implemented via the documented table-rebuild dance: - removeColumn() and changeColumn() recreate the table without / with the modified column. - addForeignKey() runs the full 12-step rebuild: read the existing CREATE TABLE from sqlitemaster, splice the FOREIGN KEY (...) REFERENCES ... clause in before the closing paren, then (with PRAGMA foreignkeys=OFF) RENAME the old table aside, recreate it, INSERT INTO new SELECT FROM old, drop the temp, and restore PRAGMA foreignkeys=ON in a finally. DROP TABLE ... CASCADE is unsupported. SQLite has no CASCADE on DROP TABLE; cascade: true is treated as a no-op (dropping a table simply removes its schema entry) rather than emitting invalid syntax. This was needed for sync({ force: true }) to work at all. No schemas. SQLite has only the main schema (plus attached databases). createSchema/dropSchema are no-ops, showAllSchemas()/listSchemas() return ['main'], and quoteTable(name, schema) treats the schema as a prefix ("schema"."table") for attached databases. No users, roles, grants, DDL sequences, or identity columns. createUser, createRole, grant, revoke, etc. all throw "... not supported by SQLite"; identity columns throw and direct you to AUTOINCREMENT. Sequences are simulated with an INTEGER PRIMARY KEY AUTOINCREMENT table. No native partitioning. addPartition() throws; the other partition helpers (createPartitionedTable, createPartition, attach/detach) are a metadata-table + UNION ALL view emulation, not real partitions. Table/column comments are not supported — comment options are accepted but stored nowhere (documented no-op). CREATE DATABASE / DROP DATABASE generate CREATE DATABASE "name" / DROP DATABASE "name" strings for interface parity, but the SQLite way to add a database is attachDatabase(path, alias) (a real ATTACH DATABASE ? AS alias), with detachDatabase(alias) to remove it. Runnable examples Caveats / not-yet-verified The build methods shown here are pure/synchronous string builders; the SQL they emit is grounded in src/dialects/sqlite/index.ts. Live execution behavior (e.g. that a rebuilt foreign key enforces referential integrity, or that STRICT rejects a mistyped value) depends on the running SQLite version and is exercised only where the SQLite test suites cover it (tests/dialects/sqlite-.test.ts). Several RETURNING, STRICT, expression-index, and partial-index features require reasonably recent SQLite versions (3.35 / 3.37 / 3.9 / 3.8 respectively); better-sqlite3 bundles a modern SQLite, but a system SQLite behind these versions would reject them. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL