Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

SQLite Driver

SQLite migration storage, table reconstruction, and provider limits

SQLite migrations use transactional table reconstruction when a requested change cannot be expressed safely by native ALTER TABLE. Introspection reads declared types, writer-owned checks, indexes, and foreign keys from SQLite’s catalog.

Physical types

Declaration Physical storage
s.string() TEXT
s.int() / s.bigInt() INTEGER
s.number() REAL
s.decimal({ precision, scale }) Checked scaled INTEGER coefficient
s.decimal({ precision, scale }).array() Checked TEXT coefficient-string JSON
s.boolean() INTEGER (0/1)
s.dateTime() Canonical UTC timestamp TEXT
s.dateTime(SQLITE.DATETIME.INTEGER) Epoch-millisecond INTEGER
s.dateTime(SQLITE.DATETIME.REAL) Julian-day REAL
s.json() / supported scalar lists JSON storage
s.blob() BLOB
s.enum(["active", "inactive"]) TEXT plus value CHECK
s.point() Reserved VIBORM_GEO_TEXT plus exact carrier CHECK

Decimal checks prove integer storage and the declared coefficient range. Lists store coefficient strings so large values remain exact. An unmarked TEXT or REAL decimal is refused during adoption; values do not establish a column’s scale. Typed queries apply logical codecs; raw SQL exposes physical storage.

Adopting decimal columns from another tool

A schema VibORM pushed or migrated is correct by construction, so typed queries never inspect storage: a read sends exactly its own statement. Bring a table another tool created under VibORM through viborm push, viborm migrate or a baseline, which refuse a decimal column without VibORM’s checked scaled-integer storage (V8001) until a reviewed conversion stores coefficients, or audit it first with viborm check --db, which reports each such column and exits nonzero. Until then, a foreign decimal column is unprotected. Typed queries read every value SQLite stores as an integer as a scaled coefficient: in a DECIMAL(10,2) column, whose NUMERIC affinity stores whole numbers as integers, a stored 3 reads back as 0.03 with no error. Filters, aggregates and updateMany/deleteMany compare coefficients with foreign values, so they can return wrong answers or change the wrong rows. Only a row holding another stored type, such as any value of a REAL column, fails the read with V2006; a write stores a coefficient before its returned row fails to decode.

Defaults and reconstruction

import { s } from "viborm";

const user = s.model({
  id: s.int().id().increment(),
  status: s.enum(["active", "inactive"]).default("active"),
  createdAt: s.dateTime().now(),
}).map("users");

Date .now() uses UTC YYYY-MM-DD text and Time .now() uses UTC HH:mm:ss.sss text. DateTime text .now() uses strftime('%Y-%m-%dT%H:%M:%fZ','now'). Numeric DateTime forms use their own physical default expressions. Adding a computed default to a populated table reconstructs and backfills the table rather than issuing an unsupported ADD COLUMN ... DEFAULT.

Reconstruction creates the destination table, copies admitted values, replaces the source, and restores indexes. Renames preserve source-column identity; new columns receive their defaults. A required new column without a default refuses reconstruction. Foreign-key, primary-key, type, nullability, and enum CHECK changes use this same owner.

Contiguous alterations of distinct columns on the same table and with the same risk class share one reconstruction, including their inverse. Other operations retain their original order; native libSQL paths keep their provider semantics.

Enum removal mappings run in the copy SELECT, under the destination CHECK. A mapping may target a newly added value; nullable removal still requires explicit useNull() or mapValues(). DateTime storage conversions validate and convert source values rather than reinterpreting physical bytes.

Reconstruction drops the source table. SQLite drops that table’s triggers with it, and a view or another table’s trigger that reads the table breaks the swap. So before any statement runs, apply, down and push refuse a migration that rebuilds a table any trigger or view depends on, and the error (V11009) names each table and dependent. The check reads the stored SQL, together with the triggers and views the same migration path creates or drops before the rebuild. String literals and comments are ignored, and so is a bare word SQLite never accepts as an unquoted name (such as ORDER or GROUP), so a table named order is not refused for an unrelated ORDER BY. Any other identifier spelled like the table, a column included, counts as a reference. To proceed, drop the dependents before the change and recreate them after it. A SQL step earlier on the same migration path counts when it is a DROP TRIGGER or DROP VIEW statement (leading comments and a main. qualifier are fine).

A type change that moves a column’s SQLite affinity into INTEGER, REAL or TEXT copies each value through CAST, and keeps the result only if it compares equal to the source under SQLite’s own rules. Numeric text such as '12' becomes 12. Anything else aborts the reconstruction inside its transaction and leaves the table unchanged, with V2005 (out of range). That includes 'twelve', '1.5' or '' into INTEGER, a fractional REAL into INTEGER, an integer that REAL cannot hold exactly, a BLOB into TEXT, and an infinite REAL into TEXT (VibORM never writes one; only raw SQL can). To find the values that block a change to INTEGER, run SELECT rowid, "c" FROM "t" WHERE "c" IS NOT NULL AND CAST("c" AS INTEGER) <> "c" (use REAL or TEXT for those targets). Migrations generated before 1.2.0 keep the copy they were generated with.

A fixed-decimal column stores its scaled integer coefficient (1.23 at scale 2 is 123), so a change that drops the decimal domain divides it back out. Only an INTEGER target can hold the result: a value with a fraction, or a stored value that is not a coefficient, aborts the reconstruction and leaves the table unchanged, and a TEXT, REAL or list target is refused before any statement runs (V8001).

Dropping a table that other tables reference fires their ON DELETE actions unless foreign keys are really off, so every reconstruction is verified. PRAGMA foreign_keys is turned off between two transactions and must then read 0, or the change is refused before any statement runs (V11021). Inside the transaction, each DROP TABLE runs between two counts of every table that references the dropped one: each must keep its row count and the number of non-null values in each referencing column, or the change rolls back (V11001). Writes the migration makes to those tables before or after the drop are not affected. A DROP TABLE in such a migration whose table name VibORM cannot read (anything but "name" or a plain identifier) is rolled back the same way. A driver without transactions (supportsTransactions: false) cannot prove any of this, and a reconstruction is refused on it before any statement runs (V11021). A driver that must flush pending writes before the pragma can take effect implements the optional beforeForeignKeysOff() hook, which runs just before it, outside any transaction: a Durable Object driver calls await ctx.storage.sync() there. If PRAGMA foreign_keys does not read 1 after it is turned back on, the change has committed but the command reports it (V11021): do not reuse that connection.

Scope and providers

An explicit migrations.tables list selects exact physical tables and refuses cross-boundary foreign keys. Reset previews list the tables actually selected.

Concurrent commands

SQLite has no advisory lock: the write lock of BEGIN IMMEDIATE is the migration lock. Every effectful command runs in transactions that open with it, reads the marker and the unfinished attempts first, and runs the first group of its path in the transaction that read them. So two processes applying one migration apply it once: the second waits for the write lock (the command’s limits.lockWait, 10 s by default, set on the connection’s busy timeout for that statement only; past it the command refuses with V11005 before any effect), then reads the first one’s marker and reports noop, or refuses with V11015. Within one process, the driver’s connection queue runs one migration command at a time.

A table rebuild commits that transaction to switch PRAGMA foreign_keys between two; its group then locks again and re-reads the marker before it acts.

A manual transition or rollback declared execution: "stepwise" is refused (V11021, meta.feature: "stepwise execution"): by generate, before it is published, and by apply, down and reset, before any of the path runs, for one 1.1.0 published. Every SQLite statement can run in a transaction, and only a transaction holds the lock. Declare manual transitions execution: "transactional". A stepwise transition published with 1.1.0 that some database still needs is applied with 1.1.0 before upgrading. One that no database has applied can instead be removed from migration storage (its states/<id>.json file) and generated again, from the same parent with the same SQL, as "transactional". Generating it again without removing it would leave two children of one state, and the next generate refuses to merge them. An attempt 1.1.0 left unfinished on such a transition is still settled by resolve, whose retry runs inside the locked transaction.

Driver admission

A SQLite driver declares what its platform guarantees in sqliteMigrationCapability, as one of three whole declarations:

Declaration foreignKeys reservedTablePrefixes exclusion Declared by Admits
SQLITE_MIGRATION_CAPABILITY "pragma" "none" "database-write-lock" sqlite3, bun-sqlite every command
Durable Object "pragma" "_cf_" "frame" durable-object every command
D1 "none" "_cf_" "none" d1 read-only commands

Any other combination is no declaration. libSQL declares none. A driver without an effectful declaration keeps offline generation, check and read-only previews, while effectful commands are refused with V8002; D1’s refusal says why: it can neither switch foreign keys off for a table recreation nor hold a migration lock.

The claims:

  • foreignKeys: "pragma": PRAGMA foreign_keys run between transactions on its connection takes effect and reads back.
  • reservedTablePrefixes: "_cf_": Cloudflare owns the _cf_* tables and refuses their PRAGMAs; introspection never reads them. "none": every table outside sqlite_* is the application’s.
  • exclusion: "database-write-lock": every transaction the driver opens begins with BEGIN IMMEDIATE. "frame": the driver implements openMigrationFrame(), one platform transaction per frame, opened where the lock would send BEGIN IMMEDIATE and settled where it would send COMMIT or ROLLBACK; the platform has one connection, the application one driver, and the migration holds that driver’s queue for its whole run.

A custom SQLite driver binds to migrations only by declaring one, after confirming its claims:

import { Driver, SQLITE_MIGRATION_CAPABILITY } from "viborm/driver";

class MySQLiteDriver extends Driver<Client, Client> {
  readonly sqliteMigrationCapability = SQLITE_MIGRATION_CAPABILITY;
  // ...
}

A declaring driver always runs on the sqlite3 migration implementation, which alters columns by table recreation, whatever its name. Subclassing LibSQLDriver with the declaration therefore never uses libSQL’s native ALTER COLUMN, which does not validate existing rows. An undeclared custom SQLite driver has no migration binding: every migration command refuses it, generation included.

LibSQL / Turso

LibSQL and D1 share parts of SQLite’s DDL vocabulary, but do not inherit its migration guarantees. V1 refuses effectful migration commands on transports whose lock, constraint validation, execution boundary, and final proof are not proven. Offline generation/check and supported read-only previews remain available. Consult driver capabilities.

GeoPoint carrier upgrades

The writer uses SQLite’s 17-digit alternate printf spelling to preserve each finite binary64 coordinate. Introspection recognizes exactly the previous 15-digit writer CHECK and the current CHECK. Pushing the current declaration reconstructs a legacy carrier once and records the new encoding in schema proofs. The conversion preserves the double still stored in the legacy table; it cannot recover digits that an earlier writer already rounded away.

Timestamps written by SQL

VibORM writes canonical text: DateTime as YYYY-MM-DDTHH:mm:ss.sssZ, Time as HH:mm:ss.sss. Typed filters, ordering and unique lookups compare that stored text, so they assume it is canonical.

Stored DateTime text may also use SQLite’s own zone-less spelling, YYYY-MM-DD HH:MM:SS with an optional fraction of up to three digits, which datetime('now'), datetime('now', 'subsec') and CURRENT_TIMESTAMP write. SQLite defines it as UTC, and so does VibORM. Query input is unchanged: a DateTime string passed to the typed client still needs T and a zone.

After manual SQL runs, the program that ran it rewrites its destination state’s text DateTime and Time columns into canonical text, inside its transaction and before its checks and marker move. That program is apply of a transition with manual SQL, down through a manual rollback, resolve({ outcome: "complete" }) of an unfinished manual transition, and reset replaying a manual transition:

  • each scalar column with one UPDATE … SET col = <canonical> WHERE NOT <canonical>;
  • each DateTime or Time list member by member, rewriting its JSON array in order;
  • each decimal list member by member, checked against the column’s scale.

Zoned spellings are converted to UTC, zone-less ones are read as UTC, and Time is padded to milliseconds. A row that cannot be rewritten aborts the program with V11014, naming the column and SQLite’s reason: nothing of a transactional program is kept and the marker does not move. That is a value outside the grammar ('next tuesday', a T without a zone, 25:00, a non-text list member, a decimal-list member that is not a coefficient string of the column’s scale), or a rewrite that meets a constraint, such as two spellings of one instant in a unique column (UNIQUE constraint failed).

Every row of each column is checked, so a malformed value stored before the transition aborts it too. Find DateTime and Time values first with viborm check --db or auditStorage. Neither inspects decimal-list members: the abort names such a column; rewrite the row’s list through the typed client (update({ where, data: { prices } })), which stores canonical members.

A stepwise transition left unfinished by VibORM 1.1.0 (1.2.0 refuses to apply one on SQLite) had no enclosing transaction: its statements stay applied and the attempt stays unfinished. Repair the value, then resolve({ outcome: "complete" }) canonicalizes and moves the marker (it needs destination checks, as for any manual transition). resolve never retries manual SQL.

The snapshot marks scalar DateTime and decimal columns itself. Time columns and DateTime lists are taken from the client’s current models, matched by table, column and physical type. On a path through an older state where a column of the same name and type held plain strings, that column is checked as temporal too; run such a path with a client whose models match it.

import { sql } from "viborm";
import type { ManualMigrationInput } from "viborm/migrations";

export default (parents: readonly (string | null)[]): ManualMigrationInput => ({
  transitions: parents.map((from) => ({
    from,
    execution: "transactional",
    // Stored as '2026-10-10 12:34:56'; apply rewrites it to '2026-10-10T12:34:56.000Z'.
    up: [sql.raw(`UPDATE "orders" SET "shipped_at" = datetime('now') WHERE "shipped_at" IS NULL`)],
    rollback: { kind: "irreversible", reason: "Backfill" },
  })),
});

Repairing foreign timestamp and Time text

Text written outside migrations, by raw SQL, another writer, or a release candidate that stored string input verbatim (2024-01-15T10:30:00Z, a +00:00 offset, datetime('now')), still reads back as the right instant, but comparisons can miss or misorder its rows. Typed queries do not inspect storage per statement. Run viborm check --db to count noncanonical values per column; it exits nonzero when any exist and names the repair for each. It audits a pushed schema: one catalog read per table reports an audited column missing from its table, or every audited column of a missing table, and fails the audit, so run viborm push or apply migrations first.

The same audit is a function, for runtime and Durable Object callers that have no CLI. It reads SQLite storage only and returns an empty list on other dialects:

import { auditStorage } from "viborm/migrations";

const audits = await auditStorage(client);
const dirty = audits.filter((audit) =>
  audit.type === "decimal" ? !audit.checked : audit.noncanonical !== 0
);

Each entry names table and column. A DateTime or Time entry carries list and noncanonical, the count of rows outside canonical text; a decimal entry carries declared, the catalog’s type, and checked. A null count or declaration means the column is missing.

The manual migration helpers quote a physical column name and return a SQLite expression. DateTime conversion uses the same calendar, instant range and wide-offset rules as the migration converter, including year zero and offsets up to ±23:59, and reads zone-less text as UTC. NULL remains NULL; malformed or out-of-range values abort the statement. Time padding preserves its clock value and refuses an invalid clock. A database managed by migrations is repaired by any manual transition, since every column is canonicalized after its SQL; a pushed database can run the helpers directly:

import {
  sqliteCanonicalDateTimeExpression,
  sqliteCanonicalTimeExpression,
} from "viborm/migrations";

await client.$executeRawUnsafe(
  `UPDATE "events" SET "created_at" = ${sqliteCanonicalDateTimeExpression("created_at")}, "start_time" = ${sqliteCanonicalTimeExpression("start_time")}`
);

push adopting a plain TEXT column as a TEXT DateTime validates every row against this grammar and keeps its spelling: an offset or zone-less value stays as written, and check --db counts it. Adoption into INTEGER or REAL storage converts each value, zone-less text as UTC.

Outside migrations, a DateTime or Time list is a JSON array: read it through the typed client and write it back with update, which stores canonical members. D1 refuses the timestamp patterns these expressions and the audit use (LIKE or GLOB pattern too complex); audit and repair a D1 database through an exported SQLite copy. Back up the database before a repair, then rerun viborm check --db.

Native indexes

Introspection uses index_xinfo to observe expressions, key order and collation. An expression, descending or non-BINARY key refuses synchronization before effects because the current index declaration cannot recreate it faithfully. This includes primary/unique backing indexes. The existing index and rows remain untouched. An explicitly excluded table does not block a closed managed-table scope.

Such an index is declared with .rawIndex(). SQLite keeps an index’s text as written, so VibORM ends it with a /* viborm:raw:… */ comment and recognizes the index by it; a table rebuild re-creates it from its text, and a renamed column re-creates it with its marker. A hand-made index of the same name carries no marker and still refuses: drop it, and declare it.

Was this page helpful?