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	Transactional DDL	Advisory-lock mechanism	Backfill in tx with DDL
Postgres	✓ all DDL atomic	pg_advisory_lock(KEY)	✓
MSSQL	✓ all DDL atomic	sp_getapplock	✓
SQLite	✓ all DDL atomic	process-local mutex	✓
MySQL / MariaDB	✗ implicit commit per DDL	GET_LOCK('voltro_migration', N)	✓ but SEPARATE from DDL

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

#Postgres / MSSQL / SQLite — 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.

#MySQL / MariaDB — re-diff-to-recover territory

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

There is NO --resume / --abort flag and NO per-op partial-status row — the applier records a _voltro_migration_plans row only on a fully successful apply. Recovery is to re-run the apply once the cause is fixed:

voltro db apply

voltro db apply re-introspects the live DB and diffs the declared schema against the (half-applied) live shape, so it emits ONLY the ops still missing — ops 1–4 are already in the DB and don't reappear in the diff. If someone finished op 5 out of band (psql, a corrective hot-fix), the re-diff sees it as present and skips it too. If the failed op is no longer the right answer because the schema was edited in response, the next voltro db plan already reflects the new declared shape — nothing to abort.

#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 to finish):

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