Schema Migrations & Versioning

This is Part 3 of the Offline-First Flutter series. In Part 2 we designed the FieldNotes schema. Now we face an inevitability everyone forgets until it bites: your schema will change after you ship. You'll add a "pinned" flag, a "color," a new sync column. And when you do, thousands of devices already have the old schema — plus something precious.

The offline-first migration stakes are higher than in a normal app. In an online app, the database is just a cache — wipe it and re-fetch. In an offline-first app, the local DB is the single source of truth (Part 1), and it may hold un-synced changes that exist nowhere else. A careless migration doesn't clear a cache — it destroys user data that was never sent to the server. Migrations here must be non-destructive by default.

Let's evolve the schema safely.

Stack: Drift on SQLite, Flutter 3.38 / Dart 3.12.

The analogy: renovating an occupied house

Analogy — renovation with the family home. A migration is renovating a house while the family still lives in it, with their belongings inside. You can add a room (a column), repaint, rewire — but you must not throw out their possessions (the un-synced rows). "Just bulldoze and rebuild" is off the table; people's stuff is in there, and some of it has no copy anywhere else.

That single constraint — preserve what's inside — is what makes offline-first migrations a discipline rather than an afterthought.

How Drift versions a schema

Two pieces drive migrations: the schemaVersion integer and a MigrationStrategy.

@DriftDatabase(tables: [Notes])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.e);

  @override
  int get schemaVersion => 2; // ← bump this on every schema change

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (Migrator m) async {
      // Fresh install: create everything at the latest schema.
      await m.createAll();
    },
    onUpgrade: (Migrator m, int from, int to) async {
      // Existing install: transform from old → new, step by step.
      if (from < 2) {
        await m.addColumn(notes, notes.isPinned); // added in v2
      }
    },
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );
}

How it works at runtime:

• On a fresh install, Drift sees no existing DB → runs onCreate (build everything at the current version).
• On an existing install, Drift compares the stored version to schemaVersion. If lower, it runs onUpgrade(m, from, to) to transform the DB in place, preserving rows.
• beforeOpen runs every launch (good for PRAGMAs or data fixups).

The golden rule: every schema change is schemaVersion++ plus an onUpgrade branch that migrates existing data forward without dropping it.

Adding a column the safe way

Say v2 adds an isPinned flag. First, the table gains the column:

class Notes extends Table {
  // ... existing columns ...
  BoolColumn get isPinned => boolean().withDefault(const Constant(false))();
}

Then schemaVersion becomes 2 and onUpgrade adds it for existing users:

if (from < 2) {
  await m.addColumn(notes, notes.isPinned);
}

Crucial: a new column added to existing rows must have a default (here false) or be nullable — old rows have no value for it. addColumn fills existing rows with the default. Adding a non-null column with no default to a populated table fails. This is the most common migration mistake.

Notice what we did not do: we didn't drop the table, didn't recreate it, didn't touch the existing notes. Every user's notes — including dirty, un-synced ones — survive untouched, now with isPinned = false.

Stepwise migrations: support every old version

Users upgrade from wherever they are. Someone who hasn't opened the app in a year might jump from v1 straight to v4. Your onUpgrade must handle every path, which is why we write it as sequential, cumulative steps:

onUpgrade: (m, from, to) async {
  if (from < 2) {
    await m.addColumn(notes, notes.isPinned);          // v1 → v2
  }
  if (from < 3) {
    await m.addColumn(notes, notes.serverUpdatedAt);   // v2 → v3
  }
  if (from < 4) {
    await m.createTable(outbox);                        // v3 → v4 (the outbox, Part 6)
  }
},

Why if (from < N) and not if (from == N-1): a device on v1 must run the v2, v3, and v4 steps in order. Using < makes each step apply to anyone below that version, so every upgrade path — v1→v4, v2→v4, v3→v4 — runs exactly the steps it needs. Test the long jumps; they're where bugs hide.

Migrations that transform data (not just structure)

Sometimes you must reshape existing rows, not just add columns. Example: v5 splits a single name field into firstName/lastName. The pattern:

if (from < 5) {
  await m.addColumn(people, people.firstName);
  await m.addColumn(people, people.lastName);
  // Backfill: copy/transform existing data into the new columns.
  await customStatement('''
    UPDATE people
    SET first_name = substr(name, 1, instr(name, ' ') - 1),
        last_name  = substr(name, instr(name, ' ') + 1)
    WHERE name LIKE '% %';
  ''');
  // (Optionally drop the old column in a LATER version, once safe.)
}

Be conservative with destructive steps. Dropping a column or table loses data permanently — and in offline-first that data might be the only copy of an un-synced change. Prefer to add new, backfill, and keep the old for at least one release; remove only once you're certain nothing un-synced depends on it. When in doubt, don't drop.

Don't forget the sync metadata in migrations

An offline-first-specific trap: when a migration changes data, decide what it means for sync.

• If a migration modifies a row's content (like the name-split), should that count as a change to push? Usually no — it's a local representation change, not a user edit, so don't set isDirty = true (you'd cause spurious syncs and possible conflicts).
• If a migration adds a syncable field the server also gained, you may need to mark rows for a one-time re-sync. Decide deliberately.

Migrations and the sync engine are entangled: a migration that flips isDirty on every row will, on next launch, try to push your entire database to the server. Always reason about what a migration does to isDirty/version (Part 2).

Testing migrations (don't ship them blind)

A broken migration corrupts real users' irreplaceable data — so migrations are the one thing you must test. Drift supports exporting each schema version and verifying upgrades between them:

# Export the current schema as a versioned snapshot:
dart run drift_dev schema dump lib/db/app_database.dart drift_schemas/

# Generate migration-test helpers from the snapshots:
dart run drift_dev schema generate drift_schemas/ test/generated_migrations/

// A migration test (full setup in Part 11):
test('upgrades v1 → v2 and keeps notes', () async {
  final connection = await verifier.startAt(1);   // open at schema v1
  final db = AppDatabase(connection);
  // seed a v1 note, then:
  await verifier.migrateAndValidate(db, 2);        // run migration, assert schema v2
  // assert the seeded note still exists and isPinned defaulted to false
});

Always test the "long jump" (oldest supported version → latest) and that existing rows survive with sensible defaults. We build a full migration test in Part 11. A migration you didn't test is a data-loss incident waiting for a release.

Practice Challenges

Challenge 1 — Bump and branch. You add a non-null color column (default '#FFFFFF') in v3. Write the schemaVersion value and the onUpgrade branch.

if (from < 3) {
  await m.addColumn(notes, notes.color); // column declared with .withDefault('#FFFFFF')
}

Challenge 2 — Spot the data-loss bug. A dev "migrates" by await m.drop(notes); await m.createTable(notes);. Why is this catastrophic in an offline-first app?

Challenge 3 — The long jump. Why must onUpgrade use if (from < N) rather than if (from == N - 1)?

Challenge 4 — The accidental full sync. A migration sets isDirty = true on every row to "be safe." What happens on next launch, and what's the fix?

Challenge 5 — New non-null column. Why does adding a non-null column without a default to a populated table fail?

Questions to test yourself

Q1 (basic). What two things must you change to make a schema migration in Drift?

Q2 (basic). Why are migrations higher-stakes in offline-first than in an online app?

Q3 (intermediate). Why must a new column on existing rows have a default or be nullable?

Q4 (intermediate). Why write onUpgrade as cumulative if (from < N) steps?

Q5 (advanced). How can a migration accidentally trigger a full re-sync, and how do you avoid it?

Q6 (advanced). Why prefer "add new + backfill + keep old" over dropping a column, and when can you finally drop it?

Wrapping up

• Your schema will change after shipping; migrations are mandatory, and in offline-first they must be non-destructive because the local DB may hold the only copy of un-synced data.
• Drift drives migrations with schemaVersion + a MigrationStrategy (onCreate for fresh installs, onUpgrade for existing ones, beforeOpen each launch).
• Add columns with defaults/nullability; write onUpgrade as cumulative if (from < N) steps to support long jumps.
• Transform data by add → backfill → keep old; drop only later, deliberately. Watch what migrations do to isDirty to avoid accidental full syncs.
• Test migrations (especially the oldest→newest jump) with Drift's schema tooling — untested migrations risk irreversible data loss.

In Part 4 we connect the database to the screen: the repository pattern and reactive UI — how the UI reads only the local DB through Riverpod streams, so the network is fully off the critical path.