· 6 min read

Offline-First Database Migrations Without Losing User Data

In an offline-first app the phone holds the only copy of unsent data. How I plan local schema migrations so an app update never loses a change.

In an offline-first app, a database migration is the riskiest code you ship. The local database is not a cache. For data that has not synced yet, it is the only copy in the world. If an app update runs a bad migration, the server cannot give that data back. I work on an IoT mobile app that keeps all user data on the device and syncs only at a few moments, so I treat every schema change as a data-safety task, not a refactor. This post covers the rules I follow.

Why offline-first database migrations are different

On a backend, you run a migration once, on a database you control, with a backup and a person watching. On phones, every install runs it alone, on data you have never seen, at a moment the user picks.

Three things make it harder:

So the goal is simple to say: after any update, from any old version, every row and every unsent change is still there.

Rule 1: one small step per version

I never write a migration that goes "from whatever is there to the current schema". I write one step per schema version and chain them. Version 2 to 5 runs steps 2→3, 3→4 and 4→5, in order.

Mapika uses Drift on top of SQLite, and Drift has good tooling for this. dart run drift_dev make-migrations saves a snapshot of each schema version and generates a stepByStep helper. Each step only sees the schema of its own version, so an old step cannot break when the current tables change later. The Drift migration docs explain the setup.

A sketch of what that looks like:

@override
MigrationStrategy get migration => MigrationStrategy(
      onCreate: (m) => m.createAll(),
      onUpgrade: stepByStep(
        from1To2: (m, schema) async {
          // New column: nullable or with a default, never "required".
          await m.addColumn(schema.outbox, schema.outbox.payloadVersion);
        },
        from2To3: (m, schema) async {
          await m.createTable(schema.syncLog);
        },
      ),
    );

The Drift docs suggest moving the stepByStep call into a static field, so a step never touches the current schema by accident. I do the same.

The same idea works without Drift. With plain SQLite, read PRAGMA user_version, run each step in a loop, and bump the version after each one.

Rule 2: only add, and remove later

A step that drops or renames a column is a step that can lose data. So I split risky changes over several releases:

  1. Add. Create the new column or table. Copy data into it. Keep the old one.
  2. Use. The app reads and writes the new place. The old column stays, unused.
  3. Remove. A later release drops the old column, once the data in it can no longer matter.

It feels slow. It is also the only version of a rename that I trust on a database I cannot see.

Two smaller habits help here. New columns are nullable or have a default, because SQLite will not add a NOT NULL column without a default to a table that already has rows. And each step runs in a transaction, so a failure leaves the old schema whole instead of half-migrated.

Rule 3: the outbox payload is a schema too

This is the one that is easy to miss. Outbox rows store their payload as JSON. That JSON was written by the old version of the app. After the update, the new sync code reads it.

If version 3 renamed a field, a payload from version 2 still has the old name. The table migration passed, the app works, and then sync sends a change the server rejects. Or worse, accepts with a missing field.

So I store a payload version on each outbox row, and the sync code upgrades old payloads before it sends them:

Map<String, Object?> upgradePayload(OutboxItem item) {
  final json = jsonDecode(item.payload) as Map<String, Object?>;
  var version = item.payloadVersion ?? 1;

  if (version < 2) {
    // Example: v2 renamed "units" to "unit".
    json['unit'] = json.remove('units') ?? 'metric';
    version = 2;
  }
  if (version < 3) {
    // Example: v3 added "source"; old rows came from the user.
    json.putIfAbsent('source', () => 'user');
    version = 3;
  }
  return json;
}

The same chain idea as the table steps: one small step per version, applied in order. The server can also accept old payload versions for a while. I prefer to fix it on the client, because the client is the one that knows how the old data was written.

Rule 4: test the paths users actually take

A migration that works on a fresh install proves nothing. Fresh installs never run onUpgrade. What I test before a release:

For the device test I keep old release builds around. Rebuilding an old version from source is possible, but the store build is the one users have.

Rule 5: plan for the step that fails anyway

Even with tests, a step can fail on one real device. Decide in advance what the app does then. My rules:

Takeaways