Drift migrations without losing data

A Drift migration runs once on every device, the first time a user opens an app version that has the new schema. There is no DBA reviewing it. There is no rollback. If it crashes, the user gets a corrupt local database, and your support inbox fills up. The migration that "works on my machine" can take down a thousand devices.

This essay is about the patterns that have survived for us, and the pre-flight check that has saved us from shipping bad migrations five times.

What a Drift migration actually is#

You bump the schemaVersion in your database class. Drift compares that to the version persisted on the device. If they differ, the MigrationStrategy.onUpgrade callback runs, taking (Migrator m, int from, int to) and walking through whatever DDL you wrote.

@override
MigrationStrategy get migration => MigrationStrategy(
  onCreate: (m) => m.createAll(),
  onUpgrade: (m, from, to) async {
    if (from < 2) {
      await m.addColumn(invoices, invoices.dueDate);
    }
    if (from < 3) {
      await m.createTable(payments);
      await _backfillPaymentsFromInvoices(m);
    }
  },
);

Notice the if (from < N) chain, not if (from == N - 1). A device that has been offline for six versions still needs to traverse every step. The chain is the migration order.

The four patterns that have failed us#

Every "the migration crashed" incident we've had falls into one of these:

1. Adding NOT NULL with no default#

await m.addColumn(invoices, invoices.status); // status is NOT NULL

Existing rows have no value for status. The DDL fails, the device's schema_version stays at the old number, and the app can never finish migrating. Every restart re-runs the same failing migration.

The fix: nullable, then backfill, then add the constraint in a later migration.

// V5: add nullable
await m.addColumn(invoices, invoices.statusOptional);
// V5: backfill
await m.customStatement(
  "UPDATE invoices SET status_optional = 'draft' WHERE status_optional IS NULL"
);
// V6: rename + tighten in a later release after we know V5 ran everywhere

2. Renaming a table#

Drift's m.renameTable(...) is a wrapper around ALTER TABLE RENAME. If your code still references the old table name anywhere, the migration succeeds but the next query fails. Rename in two steps:

1. Add new table, double-write from app, backfill.
2. Drop old table in a later release.

3. Adding a UNIQUE constraint to data that has duplicates#

await m.customStatement(
  'CREATE UNIQUE INDEX idx_unique_email ON contacts(email)'
);

If two contacts on the user's device have the same email (and they do, because users), the index creation fails. You only find out when the migration crashes on someone else's device.

The fix: dedupe first, then add the unique index. The dedupe itself needs care because you must not delete data the user might need:

// Mark duplicates rather than deleting
await m.customStatement('''
  UPDATE contacts SET _dup_of = (
    SELECT MIN(id) FROM contacts c2 WHERE c2.email = contacts.email
  )
  WHERE id NOT IN (
    SELECT MIN(id) FROM contacts GROUP BY email
  )
''');

4. Migrations that take more than ~3 seconds#

The user is staring at a frozen splash. They quit the app. They relaunch. Migration starts over. They quit again. The app is "broken."

Every migration must be fast on the slowest device in the fleet. For us that is a four-year-old budget Android with a slow eMMC. Test there, not on the M-series Mac.

The pre-flight check#

Before every release we run a CI step that:

1. Checks out a snapshot of the previous version's schema.
2. Generates a synthetic dataset that exercises every column, including realistic edge cases (long strings, unicode, NULLs, pre-existing duplicates).
3. Boots the new version against that database and runs the migration to completion.
4. Asserts row counts haven't dropped.
5. Asserts the post-migration schema matches the expected one.
6. Times the whole thing and fails if it took longer than 3 seconds.

// drift_migration_test.dart
test('v8 → v9 migration on synthetic dataset', () async {
  // Build a v8 database
  final tempPath = await _createV8Database();

  // Open as v9; this triggers the migration
  final stopwatch = Stopwatch()..start();
  final db = AppDatabase(NativeDatabase(File(tempPath)));
  await db.userPrefs.currentTenant(); // force schema use
  stopwatch.stop();

  expect(stopwatch.elapsed.inSeconds, lessThan(3));
  expect(await db.contacts.select().get(), hasLength(_v8ContactCount));
  expect(await db.payments.select().get(), isNotEmpty);
});

This single test has caught five real bugs we would otherwise have shipped: a NOT NULL without backfill, a rename that broke a query, a deduplication that lost notes, an ALTER that took 14 seconds on the synthetic dataset, and a missing index that caused the migration to drop into a full scan.

What we do when a migration does crash in the field#

Three layers of defence:

1. Detect. The app catches the migration exception and shows a "Database error" splash with a recovery action.
2. Recover. A "Restore from cloud" button re-pulls from the server into a fresh local database. Works because every domain row is server-replicated.
3. Diagnose. Telemetry includes the migration step that failed and the schema version. We push a hotfix that targets that specific step.

The recovery path means a failed migration is "1-2 minutes of re-syncing" instead of "lost data and uninstall."

The boring conclusion#

Migrations on user devices are unforgiving. The way to make them boring is:

• Always nullable + backfill + constrain over multiple releases.
• Never rename in one shot.
• Dedupe before unique constraints.
• Test on real, slow devices.
• Run the pre-flight check on every PR.

The next part of this series is on state restoration: bringing the app back to where the user left it without paying for the snapshot every time. Series page tracks the rest.