Migration Runner

Migrations are run by JavaScript, over the SqlDriver, through one runner that the app and the dev tooling share, tracked in one table. Drizzle-kit only generates the SQL, and the SQL plugin’s own migrator (sqlx’s add_migrations) is not used. The runner decides what runs and hands each migration to the driver’s batch, which on the desktop is a Rust command that runs it on a connection of its own.

One runner rather than one per environment, because a migration’s record must mean the same thing to everything that opens the file. Two runners with separate tracking disagree about a file the other built: one finds the tables without its own records and runs the first migration again, which fails with table … already exists and leaves the profile unopenable.

Pieces

• runMigrations(driver, migrations) in packages/core/src/migrator.ts. It follows drizzle’s own scheme: a __drizzle_migrations table (id, hash, created_at), with a migration pending when its journal timestamp when is later than the last one recorded. Migrations are sorted by when before they run, so the array’s order does not matter. The stored hash is a record of the source and decides nothing. It returns what it applied, or that it adopted an existing schema.
• migrations.generated.ts in packages/core/src: the migrations inlined as TypeScript, each with its tag, when, sha256 hash and statements. scripts/db/gen-migrations.ts builds it from packages/core/drizzle/*.sql and meta/_journal.json, splitting each file on --> statement-breakpoint. They are inlined because the WebView has no file system and Vite’s ?raw imports cannot be shared with Node. pnpm run db:generate regenerates the SQL from the schema and then this module (DB Dev Commands).
• In the app, apps/renderer/src/db/driver.ts runs the migrations when the first query arrives, each as a batch (below), and every query waits for them. The whole run is retried up to six times, 150 ms apart, so a first connection that races the Tauri bridge still migrates, and a failure that outlasts the retries is not kept, so a later query tries again. After the migrations, ensureCapture reinstalls sync’s triggers for a linked profile and ensureInbox makes sure the profile has its Inbox.
• In the tooling, applyMigrations() in scripts/db/lib.ts runs the same function over node:sqlite, so db:migrate, db:seed and db:reset build files the app opens as they are.

Each profile is its own file and therefore has its own migration state; a newly opened file migrates itself (Profiles).

The history

The migrations begin with one baseline, 0000_text_ids_baseline, which creates the whole schema with text ids, and every later migration builds on it. Every table’s id is TEXT PRIMARY KEY NOT NULL (Synced Databases says how ids are made).

A file from an unknown history is refused

A file whose last recorded migration is not in the running build’s set has a history this build does not know. Left alone, the runner would take the baseline for pending and replay its CREATE TABLEs over existing tables, stopping halfway. So it refuses with UnknownMigrationHistoryError, in words meant for the screen: a file older than the text-id baseline cannot be upgraded and is to be deleted and made again, and a file saved by a newer build needs the app updated.

migrationRefusal(e) finds that refusal by name rather than by instanceof, since a bundler can load the module twice, and follows the cause chain, since Drizzle rethrows a driver’s error as its own “Failed query” with the original as the cause. The trackable list shows the refusal’s sentence in place of its generic load error.

Adoption

A file that already has the schema but no records is adopted: every migration is recorded as applied, without running its DDL, so nothing collides and no data is lost. “Has the schema” means every table the migration set leaves behind (expectedTables, replayed from the migrations’ own CREATE, DROP and RENAME statements), not one sentinel table, because a file built some other way can hold some of the tables and not the rest.

Adoption runs no migration, so it runs no data migration either. Any file the app or the tooling has migrated carries its records, so adoption meets only a file built some other way.

Each migration is one batch

A migration’s statements and its record run as one batch: all of them on one connection, in one transaction, or none of them. A migration cut off part-way therefore leaves nothing behind, and the next boot runs it whole. Adopting a file stamps its records in one batch too.

The driver provides the batch (SqlDriver.batch, packages/core/src/driver.ts). A driver over one connection, as the tooling’s, the tests’ and the web build’s are, passes batchOnOneConnection. The desktop’s plugin runs on a pool of connections, where a BEGIN and a COMMIT sent from JavaScript can land on different ones, so its driver sends the batch to the shell’s sql_batch command, which runs it on a connection of its own (apps/desktop/src-tauri/src/batch.rs).

drizzle-kit changes a column by rebuilding its table between PRAGMA foreign_keys=OFF and =ON. SQLite ignores that pragma inside a transaction, and with foreign keys on, dropping the old table would delete every row that cascades from it. So the runner takes the two pragmas out of a migration and runs its batch with foreign keys off instead: they are switched off before the transaction opens, and the batch commits only if PRAGMA foreign_key_check then finds no row pointing at one that is gone.

Sync’s triggers go first

A profile linked for sync carries capture triggers that name each table’s columns, and while they stand, a migration that drops a column those triggers name fails, and every migration after it. So whenever any migration is pending, the runner drops every _sync_ trigger first, and ensureCapture puts them back after the run, built for the schema the migrations leave. A migration’s own writes are therefore never captured, which is right: every device runs the same migrations itself.

A migration to a synced table is expand-then-contract, so devices on either side of it can still exchange changes: a newer device ignores columns an older change carries that its table no longer has, and an older device is refused until it updates (Synced Databases).

Inspecting

pnpm run db:measure prints a profile’s __drizzle_migrations rows and its tables. A dev build logs [db] connect Nms and [db] migrate Nms — applied [...] (or adopted existing schema) to the WebView console, which Driving the App (CDP) reads.

Tests

packages/core/src/migrator.test.ts runs the runner over real SQLite: a fresh file, ordering by when, a second run applying nothing, adoption and a migration after it, both refusals and the cause chain, the real baseline on a fresh file, expectedTables against a real run, a half-built schema not adopted, a migration cut off part-way leaving nothing and then applying whole, a table rebuild keeping the rows that cascade from it (beside the control that loses them with foreign keys on), a rebuild that would leave a row without its parent refused, and a migration that drops a column the sync triggers name. batch.rs tests the desktop’s batch the same ways, and that a pool opened as the plugin opens one enforces foreign keys on every connection.

Related

Repository Seam · DB Dev Commands · Profiles · Synced Databases · Driving the App (CDP)