---
title: SQLite Driver
description: SQLite migration storage, table reconstruction, and provider limits
icon: file-box
---

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

```ts
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`](/docs/drivers/sqlite/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:

```ts
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](/docs/migration/drivers).

## 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.

```ts title="migrations/fill-shipped-at.ts"
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:

```ts
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:

```ts title="repair-temporal.ts"
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()`](/docs/schema/model#rawindexname-templates). 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.
