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_keysrun 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 outsidesqlite_*is the application’s.exclusion: "database-write-lock": every transaction the driver opens begins withBEGIN IMMEDIATE."frame": the driver implementsopenMigrationFrame(), one platform transaction per frame, opened where the lock would sendBEGIN IMMEDIATEand settled where it would sendCOMMITorROLLBACK; 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.