Skip to main content

Atomic migrations

A migration is a batch of schema and seed changes that must land all or nothing — the developer writes CREATE TABLE and its INSERT together, not as separate steps. xcon-db has no transactional DDL, so it gets atomicity a different way: the whole batch runs against a shadow copy of the database, and only a fully successful run is swapped in with a single atomic rename. A failure discards the shadow; the live database was never touched.

BEGIN MIGRATION;
  CREATE ROWS TABLE devices (name TEXT);
  INSERT INTO devices (name) VALUES ('alpha');
  RECORD REVISION 1 SCRIPT '01-init.sql' HASH 'a3f9…';
COMMIT MIGRATION;
  • BEGIN MIGRATION checkpoints the bound database, copies it aside (a copy-on-write reflink where the filesystem allows, so a large database shadows near-instantly) and points the session at the shadow. Every statement after it runs on the shadow — DDL, writes and journal entries alike.
  • COMMIT MIGRATION seals the shadow and swaps it in: one rename, atomic.
  • ABORT MIGRATION, any statement error, or a client that stops short of COMMIT discards the shadow and leaves the live database exactly as it was.

Because a failure leaves no partial state, there is nothing to resume — fix the script and run the migration again.

The maintenance window​

While a database migrates it is frozen: other sessions opening it get database is migrating until the run commits or aborts. This is the price of the shadow — writes that landed on the live database during the copy would be lost at the swap, so they are refused instead. Migrations are a deploy-time operation; run them in a quiet window.

Crash safety​

The swap is a single rename, so a crash never leaves a half-swapped database. On restart the engine tidies up: an orphaned shadow (the swap never happened) is discarded, and a rename interrupted mid-swap restores the original — an uncommitted migration simply did not happen.

The publish orchestration — which scripts are pending, assembling the batch, rollback over .down.sql twins — lives in the studio, not the engine. The engine offers the three boundaries and the atomic guarantee; the journal (SHOW REVISIONS) records what a committed run applied.