Multi-dialect strategy

The planner produces ONE MigrationPlan regardless of dialect. The applier executes it per-dialect, dispatching through sql.onDialectOrElse for every emit + falling back to runtime probes when behaviour diverges. The same voltro db apply invocation against the same schema produces structurally identical results on every backend.

What ISN'T uniform: transactional DDL semantics.

Dialect	Plan applied atomically	Advisory-lock mechanism	Recovery after a mid-plan crash
Postgres	✓ one transaction	pg_advisory_lock(KEY)	nothing to recover — rolled back
MSSQL	✓ one transaction	sp_getapplock	nothing to recover — rolled back
MySQL / MariaDB	✗ implicit commit per DDL	GET_LOCK('voltro_migration', N)	resume ledger
SQLite / Turso	✗ per statement	process-local mutex	resume ledger

This is the operationally heaviest cross-dialect difference. The rest of the page covers what changes.

SQLite is on the non-atomic side, and the reason is Turso. SQLite the engine can do transactional DDL. The applier does not use it, because SQLite and Turso share one dialect token and Turso rejects DDL inside its default transaction — the applier cannot wrap one without wrapping the other. Both therefore take the per-statement path and the resume ledger below.

On Postgres one class of operation is still not covered by the transaction: online-required ops (CREATE INDEX CONCURRENTLY, the shadow-column type swap) are rejected inside a transaction, so they run after the commit. They are ledgered like a MySQL plan.

#Postgres / MSSQL — transactional happy path

A multi-step plan runs inside one BEGIN ... COMMIT. Mid-flight failure rolls EVERYTHING back; the next plan diff is identical to the pre-apply one. There's nothing to resume — re-running the apply re-runs the plan from scratch.

The advisory-lock variants serialise concurrent applies — two operators running voltro db apply against the same DB at the same time go through serially.

The lock is scoped to your configured schema. With DB_SCHEMA set, the lock key (Postgres) / lock name (MySQL, MSSQL) is derived from the schema, so two apps sharing one database in different schemas do not serialise — or defer — each other's migrations and trigger repairs. Without DB_SCHEMA (or with DB_SCHEMA=public) every instance takes one stable framework-wide key, which is what makes a rolling deploy safe: old and new replicas contend on the same lock. On MySQL/MariaDB GET_LOCK is server-wide; setting DB_SCHEMA to your database name un-shares the lock between two apps on one server.

Transient DDL failures are retried, per the dialect's own predicate. A CREATE TABLE IF NOT EXISTS that meets SQLite/Turso's schema lock (database is locked / SQLITE_BUSY), a MySQL lock-wait timeout, or a Postgres/MSSQL deadlock victim during the boot auto-migrate is retried with bounded attempts and exponential backoff instead of failing the boot on the first attempt — only statements that are safe to re-run, and on Postgres/MSSQL as a fresh transaction (their deadlock classes roll the whole transaction back). VOLTRO_MIGRATION_DDL_RETRIES moves the retry count (default 4; 0 disables).

#MySQL / MariaDB / SQLite / Turso — the resume ledger

Every DDL statement implicitly commits. A 5-op plan on MySQL is effectively 5 separate "atomic statements" with the prior ones already committed when a later one fails. If op 5 of 5 fails, ops 1–4 stay applied:

plan applying (mysql, env=prod):
  1. create-table audit_logs               ✓  42ms
  2. add-column users.email                 ✓  18ms
  3. backfill users.email                   ✓  12s
  4. alter-column users.email SET NOT NULL  ✓  8ms
  5. add-index audit_logs(actorId)          ✗  ER_DUP_KEYNAME

The _voltro_migration_plans row is still written only on a fully successful apply — that row means "this schema is live", and a half-applied plan is not. What the applier DOES write as it goes is a per-operation resume ledger, _voltro_migration_ops:

• every operation that will run outside a transaction is inserted pending before any DDL runs, so a crash on operation 1 still leaves the whole intended sequence on disk;
• each row flips to started immediately before its statement and applied immediately after;
• the rows are deleted once the apply converges and the plan row lands. The ledger is a work queue, not a history — the history is _voltro_migration_plans.operations.

There is still no --resume / --abort flag, because there is nothing to choose. Recovery is to re-run the apply once the cause is fixed:

voltro db apply

The next apply finds the ledger, says so in the log, and continues the interrupted run:

[voltro:migrate] migration resume: found an interrupted run (plan_msocz71h_x0qq5d) — 4 of 5 operation(s) completed, in flight: add-index index:audit_logs.audit_logs_actorId_idx
[voltro:migrate] migration resume: continuing run plan_msocz71h_x0qq5d — replaying 1 operation(s), 4 already applied

Three things are worth knowing about how it decides.

The ledger cannot be atomic with the DDL it records — on MySQL the statement commits itself, so there is always a window where the DDL landed and the applied flip did not. That window is not eliminated, it is BOUNDED: the ledger is written strictly sequentially, so at most one operation can be started, and it is the only one whose outcome is unknown. That one is resolved by asking the planner — the fresh diff was computed against the live database moments ago, so an operation it no longer mentions has already taken effect. Completed operations are never re-attempted.

If you edited the schema in response to the failure, the recorded plan is dropped and the freshly-diffed one is applied instead — the old plan aims at a target nobody wants any more. The interruption is still logged, and the artefacts below are still reconciled first.

Two operations are repaired rather than re-diffed. The Postgres online type change (shadow-column swap) and the SQLite table rebuild both build a temporary object and swap it into place, and interrupted mid-swap they leave a live schema that means something ELSE to a differ — a half-finished shadow swap looks like a missing column, and the plan a blind re-diff produces for that is add-column, which succeeds and loses the data sitting in <col>__old. The applier reconciles <col>__shadow / <col>__old and <table>__voltro_rebuild from their observable state before anything else reads the schema. If a shadow-swap state cannot be classified, the apply refuses, changes nothing, and names the three columns to inspect.

What has not changed: convergence still gates the fingerprint. After the DDL — resumed or not — the applier re-plans against the live schema and refuses to record a fingerprint while anything remains. A resumed run is held to exactly the same standard as a fresh one, and an apply that does not converge KEEPS its ledger, because an unfinished run's record is the only thing that tells the next boot it is looking at a half-migrated schema.

If someone finished the failed op out of band (a mysql shell, a corrective hot-fix), the fresh diff sees it as present and it is skipped.

#SQLite — table rewrite mechanic

SQLite lacks ALTER COLUMN. For type changes, the applier auto-emits the standard pattern:

1. CREATE TABLE <name>_new (<new column definitions>)
2. INSERT INTO <name>_new SELECT (with cast) FROM <name>
3. DROP TABLE <name>
4. ALTER TABLE <name>_new RENAME TO <name>
5. Recreate every index + every FK the old table had

The plan output flags this as "rewrite table" so you know what's happening:

⊕ ALTER TABLE users ALTER COLUMN status TYPE varchar(20)  # rewrite table (SQLite has no ALTER COLUMN)
  → recreates 3 indexes, 2 incoming FKs

Caveats:

• Foreign keys referencing the rewritten table get dropped and recreated. If they declared ON DELETE CASCADE, the recreate restores it; ordering matters internally.
• Multi-rewrite plans on SQLite are brittle. A plan that rewrites 3 related tables in one apply may have intermediate states where a FK temporarily references a non-existent table. The framework orders them topologically; mixed rename + rewrite within one plan can hit edge cases. If a SQLite multi-rewrite refuses, file-based migrations let you control ordering manually.

#Per-dialect DDL idioms hidden from you

The framework emits the right dialect-native idiom for every concept. You don't write these by hand:

Concept	Postgres	MySQL / MariaDB	MSSQL	SQLite
Identifier quoting	"name"	`name`	[name]	"name"
CREATE TABLE IF NOT EXISTS	native	native	IF NOT EXISTS (SELECT * FROM sys.tables...) EXEC(...)	native
Auto-increment id	BIGSERIAL	BIGINT AUTO_INCREMENT	BIGINT IDENTITY(1,1)	INTEGER PRIMARY KEY AUTOINCREMENT
Booleans	native boolean	tinyint(1) (0/1)	bit (0/1)	integer (0/1)
JSON column	jsonb	json	nvarchar(max)	text
Timestamp with tz	timestamptz	datetime(6)	datetime2	datetime
now() default	now()	CURRENT_TIMESTAMP(6)	SYSUTCDATETIME()	current_timestamp
RETURNING * on INSERT	native	NOT available (separate SELECT)	OUTPUT INSERTED.*	native
LIMIT N OFFSET M	native	native	OFFSET M ROWS FETCH NEXT N ROWS ONLY	native
FK with cascade	native	native	native	native (must PRAGMA foreign_keys = ON)

The DDL emitter under @voltro/database/src/migrate.ts is one of the densest cross-dialect dispatch files in the codebase. Bug reports for "X doesn't work on dialect Y" usually trace to a missing branch there.

#Boot-log shape per dialect

voltro dev prints a one-line dialect summary during the auto-migrate phase:

[voltro:dev] auto-migrate: planning schema dialect=postgres env=dev tables=22
[voltro:dev] auto-migrate: applied 3 op(s) in 412ms [safe=3 needs-default=0 needs-backfill=0 rename=0 lossy=0] fingerprint=8f507ba1e1aadad5

For MySQL the line notes the non-atomic-DDL constraint (a failed op leaves earlier ops committed; re-run apply and the resume ledger continues from there):

[voltro:dev] auto-migrate: planning schema dialect=mysql env=dev tables=22 (implicit-commit DDL — re-run apply after a mid-plan failure)

For SQLite the line notes the single-process-only constraint:

[voltro:dev] auto-migrate: planning schema dialect=sqlite env=dev tables=22 (single-process — no concurrent appliers possible)

#Replication caveats during apply

If read-replicas are configured (DB_REPLICA_URLS set), the applier ALWAYS targets the primary. Replicas catch up via their normal replication stream. There's a window after apply where the replica fingerprint differs from primary — visible in voltro db drift if it's run against the replica URL during that window.

For multi-region deploys, time the apply against primary's region + accept the inter-region replication lag as the propagation time. Drift is measured against the primary fingerprint — the canonical schema authority that the applier always targets; replicas converge to it through their replication stream, so a transient post-apply mismatch on a replica is replication lag, not drift. To check a specific replica during that window, run voltro db drift against its URL.

#When the dialect rejects something the planner emitted

This shouldn't happen — the framework's per-dialect DDL emitter is the test surface for every code path. If you see a dialect-side error during an apply that looks like the framework emitted invalid SQL:

1. Capture the failing SQL from voltro logs --trace <plan-id>.
2. File an issue with: the schema diff, the dialect + version, the exact error message.
3. Workaround: drop down to a file-based migration with hand-written DDL for the affected op.

The framework can't auto-fix every dialect's pathological cases (MariaDB's RETURNING gap on UPDATE, MySQL's strict-mode rejection of TEXT defaults, MSSQL's optimizer quirks with FILTER + RAISERROR). Where the test suite has caught those, the emitter has the right branch. Where it hasn't, file the report — the matrix grows from real failures.