Run migration (forward-only Drift schema migration)

Apply a schema change to a local Drift/SQLite database by following the exact ordered ritual below. In an offline-first app there is no server and no re-sync path, so the on-device DB is the single source of truth: a migration that drops or corrupts a column permanently destroys hand-entered records that exist nowhere else, and with no telemetry nobody ever reports it — the user just uninstalls. This is a manual, low-freedom, human-run workflow. Execute the steps in order; do not improvise, reorder, or skip.

Non-negotiable rules

Take the pre-migration snapshot FIRST — before the database is opened. Do it at the composition boundary that opens
```
AppDatabase
```
, never inside
```
onUpgrade
```
:
```
PRAGMA wal_checkpoint(TRUNCATE)
```
any live handle so the
```
-wal
```
is folded into the main file, then copy the DB file and its
-wal
/
-shm
sidecars while nothing holds the file open. Opening the database is what triggers the migration. Never copy or overwrite bytes under a live connection. The snapshot is the only "rollback" SQLite has; restore it on any failure.
Forward-only. Never write a down migration. SQLite has no true down migration and you must not pretend to. "Rollback" means restore the snapshot, nothing else.
Steps are append-only — NEVER edit a shipped step. Editing a released step corrupts data for every user who skips versions. To fix a bug in a prior step, add a new step and bump
```
schemaVersion
```
— never back-edit history.
Bump
schemaVersion
by exactly one per shipped schema change, and add the matching
```
fromNToM
```
branch. A schema changed without a version bump means no migration runs at all on the user's device — a CI gate must catch it.
Commit the versioned schema snapshot. Run
```
drift_dev make-migrations
```
(or
```
schema dump
```
), commit the exported snapshot under
```
drift_schemas/
```
, and commit
```
schema_versions.dart
```
. The delta becomes a reviewable diff and the test suite diffs live schema against it.
The open is wrapped in try/restore, entirely outside any connection. If opening the database throws, the migration failed and that connection is dead: close it, restore the snapshot file(s) taken before the open, and rethrow. All file operations happen while nothing has the DB open — never overwrite the live file from inside
```
onUpgrade
```
.
A green shape check is necessary, never sufficient.
```
migrateAndValidate
```
compares
```
CREATE
```
statements only — it never reads a row. A migration that rebuilds a table perfectly and copies zero rows passes it, green. The content test is the deliverable, not the shape test.
No migration merges without the full test suite green — every
```
from → to
```
path (including multi-version jumps), a content test per new pair,
```
PRAGMA integrity_check == ok
```
,
```
PRAGMA foreign_key_check
```
empty, and a forced mid-migration throw that restores the snapshot.
Store canonical values only in any new column (integer minor units for money keyed to the ISO-4217 exponent, SI integers for quantities, UTC epoch millis for instants, a local serial-day integer for a calendar date). Never add a float-money, display-string, or localized-numeral column.

The ordered workflow

Run commands from the package that owns the database (single-package apps: the app root; see the multi-package note at the end).

Design the change against your data-layer conventions (see
```
persistence-drift
```
). Edit the table class.
Bump
schemaVersion
by one in the database class.
Scaffold the snapshot + step stub and commit the generated snapshot:
bash
```
dart run drift_dev make-migrations
```
On pinned versions where
```
make-migrations
```
is not confirmed working, use the three explicit commands instead (see "Snapshot commands" below).
Write the append-only forward step — add one new
```
fromNToM
```
branch. Never modify an existing branch. Create only the objects this version adds; never reference a column added in a later version.
Regenerate codegen so the generated database picks up the new version (invoke the
```
run-codegen
```
skill):
bash
```
dart run build_runner build --delete-conflicting-outputs
```
Run the migration test suite (see Verification). All paths, the content test, both PRAGMAs, and the forced-throw restore must pass.
Analyze, format, and commit the table change, the bumped
```
schemaVersion
```
, the new step, the committed snapshot, and the tests together in one change.

The migration guard

The snapshot/restore lives at the composition boundary, entirely outside any open connection;

onUpgrade

only runs the steps and checks foreign keys. Keep this exact shape.

dart

// ── composition boundary (app_database_opener.dart) ─────────────────────────
// Snapshot BEFORE the open, restore AFTER a failed open — no connection is ever
// live while the bytes are copied. Opening is what triggers onUpgrade.
Future<AppDatabase> openMigratedDatabase(File dbFile) async {
  // 1. Checkpoint any prior handle, then copy dbFile + its -wal/-shm sidecars.
  final snapshot = await _snapshotDbFiles(dbFile);
  final db = AppDatabase(NativeDatabase(dbFile));
  try {
    // 2. Force the migration to run NOW, while the file fallback still stands.
    await db.customStatement('PRAGMA user_version;');
    return db;
  } catch (_) {
    // 3. The failed connection is dead — close it, THEN restore with nothing open.
    await db.close();
    await _restoreDbFiles(snapshot);
    rethrow;
  }
}

// ── app_database.dart ───────────────────────────────────────────────────────
@override
int get schemaVersion => 3; // bump by exactly ONE per shipped schema change

@override
MigrationStrategy get migration => MigrationStrategy(
      onCreate: (m) => m.createAll(),
      onUpgrade: (m, from, to) async {
        // FKs OFF here — BEFORE the migration's own transaction opens, because
        // the pragma is ignored once a transaction is running.
        await customStatement('PRAGMA foreign_keys = OFF;');
        // Forward-only, append-only stepwise migration in one transaction.
        await transaction(() => _steps(m, from, to));
        // Prove the body left no dangling references — in production, not tests.
        final orphans = await customSelect('PRAGMA foreign_key_check').get();
        if (orphans.isNotEmpty) {
          throw StateError('foreign_key_check found ${orphans.length} orphans');
        }
      },
      // Pragmas are per-connection, NOT persisted — re-assert on every open.
      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON;');
      },
    );

final _steps = stepByStep(
  from1To2: (m, schema) async { /* shipped — DO NOT EDIT */ },
  from2To3: (m, schema) async {
    // Create EXACTLY the objects this version adds.
    await m.addColumn(schema.notes, schema.notes.archivedAtUtc);
  },
);

Snapshot commands (explicit form)

When not using

make-migrations

, re-run all three on every

schemaVersion

bump and commit the results:

bash

# 1. Export the schema for the CURRENT schemaVersion.
dart run drift_dev schema dump lib/data/app_database.dart drift_schemas/

# 2. Era-correct classes. --data-classes --companions is LOAD-BEARING: it emits
#    DatabaseAtV1 / DatabaseAtV2 with era-correct data classes and companions,
#    the only mechanism that lets a test write rows in the v1 world and read
#    them back in the v2 world. Without it only shape tests are possible.
dart run drift_dev schema generate --data-classes --companions \
  drift_schemas/ test/drift/generated/

# 3. Step-by-step helpers — each callback gets a schema frozen at its version.
dart run drift_dev schema steps drift_schemas/ lib/data/schema_versions.dart

Verification (blocking — must pass before commit)

The suite must prove all of the following.

Every from→to path, including the skips. A user who ignored updates upgrades

v1 → v3

directly — a code path nobody ran. A nested loop covers every pair forever, so a new bump cannot forget a skip path:

dart

group('schema shape', () {
  for (var from = 1; from < kLatestSchemaVersion; from++) {
    for (var to = from + 1; to <= kLatestSchemaVersion; to++) {
      test('v$from -> v$to', () async {
        final connection = await verifier.startAt(from);
        final db = AppDatabase(connection);
        await verifier.migrateAndValidate(db, to); // shape only — necessary, not sufficient
        await db.close();
      });
    }
  }
});

The content test — the one that matters. Write rows at v(n) with era-correct classes, run the real migration, read back at v(n+1), assert the rows are still there, still correct, and still in place. Use a hostile fixture — apostrophes, non-ASCII, em dashes, emoji, quotes, backslashes, whitespace-only — exactly where a

columnTransformer

or hand-rolled

INSERT ... SELECT

mangles a value.

'test1'

survives everything and proves nothing. Put a

reason:

on every

expect

— the failure message is the whole debugging session when no crash report is coming.

dart

test('v2 -> v3 preserves every note, byte for byte', () async {
  final schema = await verifier.schemaAt(2);

  // Write real data with v2-era classes. newConnection() over the SAME bytes is
  // what lets different-era database objects see one database.
  final old = v2.DatabaseAtV2(schema.newConnection());
  await old.into(old.notes).insert(
        v2.NotesCompanion.insert(title: 'Blank-ish', body: const Value(' ')),
      );
  await old.close();

  // Run the REAL migration, then prove integrity — FKs are off during migration
  // and SQLite reports a dangling reference to nobody.
  final db = AppDatabase(schema.newConnection());
  await verifier.migrateAndValidate(db, 3);
  expect(await db.customSelect('PRAGMA integrity_check').get(),
      [predicate((QueryRow r) => r.data.values.first == 'ok')]);
  expect(await db.customSelect('PRAGMA foreign_key_check').get(), isEmpty,
      reason: 'migration left dangling references — a lost record');
  await db.close();

  // Read back with v3-era classes and assert CONTENT, not just shape.
  final now = v3.DatabaseAtV3(schema.newConnection());
  final rows = await now.select(now.notes).get();
  expect(rows, hasLength(1), reason: 'a row disappeared during migration');
  expect(rows.single.body, ' ', reason: 'whitespace-only body must survive intact');
  await now.close();
});

The forced mid-migration throw restores the snapshot. Inject a step that throws partway, open the database through the boundary opener so the migration runs, assert the open rethrows and that the pre-open snapshot was restored — prior schema and rows left byte-identical. The restore path is the last line of defense and the one that is never exercised in production until it is.

Never mark a migration done on a green analyzer alone. It is safe only once the content test, both PRAGMAs, the all-paths loop, and the snapshot-restore test are green.

Migration mechanics that fail silently

```
PRAGMA foreign_keys
```
is ignored once a transaction is open — toggle FKs OFF at the top of
```
onUpgrade
```
, before the
```
transaction()
```
that wraps the steps, and re-enable them unconditionally in
beforeOpen
(the pragma is per-connection, not persisted in the file).
Run
```
PRAGMA foreign_key_check
```
after the migration body, not only in tests.
```
flutter test
```
runs in a plain Dart VM where
```
sqlite3_flutter_libs
```
does nothing; macOS falls back to the system
```
libsqlite3
```
(real version-skew risk) and Linux needs
```
libsqlite3-dev
```
installed before the run.

Anti-patterns

Editing a shipped step — rewrites history for existing installs; a version-skipping user runs the edited step against old data and loses it.
Writing a down migration — SQLite has none; the only rollback is the snapshot restore.
Changing the schema without bumping
schemaVersion
— no migration runs on the user's device; the schema and code silently diverge.
Marking done on
migrateAndValidate
alone — it is a shape comparison that reads zero rows; a migration that copies nothing passes it green.
INSERT ... SELECT
/
columnTransformer
verified only with
'test1'
fixtures — the mangling lives in apostrophes, emoji, and backslashes.
select
/
update
of a not-yet-added column in an older step — write queries only against the schema frozen at that step's version.
Assuming a pragma persists in the file —
```
foreign_keys
```
,
```
synchronous
```
, and any cipher key are per-connection; re-assert on every open.
Leaving
eraseDatabaseOnSchemaChange
reachable in a release build — it is a DEBUG-only convenience; shipped, it wipes real user data on every bump.
Running the migration test as one big test instead of the every-pair loop — the skip paths are the ones that get forgotten on the next bump.

Definition of done

```
schemaVersion
```
bumped by exactly one; a matching append-only
```
fromNToM
```
step added; no edit to any already-shipped step; no down migration.
```
make-migrations
```
(or the three explicit
```
schema
```
commands) re-run; the committed snapshot and
```
schema_versions.dart
```
are in the same change.
The pre-migration snapshot is taken before the database is opened; the open is wrapped in try/restore-and-rethrow, with every file operation outside any live connection.
Codegen regenerated with
```
--delete-conflicting-outputs
```
.
The shape loop covers every pair up to
```
kLatestSchemaVersion
```
.
A new content test for the new pair uses a hostile fixture,
```
reason:
```
on every
```
expect
```
, and per-key (not per-list-index) assertions.
```
PRAGMA integrity_check == ok
```
and
```
PRAGMA foreign_key_check
```
empty after the migration; FKs disabled before the migration transaction, re-enabled in
```
beforeOpen
```
.
A forced mid-migration throw restores the snapshot, leaving prior schema and rows intact.
```
eraseDatabaseOnSchemaChange
```
is DEBUG-only and unreachable in release.
Analyzer and formatter clean; table change, version bump, step, snapshot, and tests committed together.

When multi-package (workspace)

If Drift is confined to a data package inside a Dart pub workspace, run every command from that package (or the workspace root for

make-migrations

), keep

drift_schemas/

and

schema_versions.dart

inside that package, and run the migration suite there. In a single-package app all of this lives at the app root — nothing about the ritual requires a workspace.

Related skills

See
```
persistence-drift
```
for the data-layer conventions the schema change must obey (STRICT tables, schema-level CHECK/FK, WAL/pragmas, DAO transactions).
See
```
run-codegen
```
for the deterministic
```
build_runner
```
pass invoked in step 5.
See
```
error-handling-typed-results
```
for the never-lose-data guarantees (transactions, autosave drafts, soft-delete/Undo) this workflow complements.
See
```
testing-strategy
```
for the real-in-memory-DB test discipline the content and shape tests follow.
See
```
ci-pipeline-and-gates
```
for the schema-freshness gate that fails a build when the schema changed without a committed snapshot bump.

References

Drift — Migrations: https://drift.simonbinder.eu/Migrations/
Drift — Migration test API (
```
SchemaVerifier
```
,
```
make-migrations
```
): https://drift.simonbinder.eu/Migrations/tests/
Drift —
```
stepByStep
```
: https://drift.simonbinder.eu/Migrations/step_by_step/
SQLite —
```
PRAGMA integrity_check
```
/
```
foreign_key_check
```
: https://sqlite.org/pragma.html
SQLite — Write-Ahead Logging: https://sqlite.org/wal.html

run-migration

NPX Install

Tags

SKILL.md Content

Run migration (forward-only Drift schema migration)

Non-negotiable rules

The ordered workflow

The migration guard

Snapshot commands (explicit form)

Verification (blocking — must pass before commit)

Migration mechanics that fail silently

Anti-patterns

Definition of done

When multi-package (workspace)

Related skills

References