DEV Community

Cover image for Shipping a Safe Rollback: Downgrading a Drift (SQLite) Database in Flutter Without Losing User Data
George Ikwegbu Chinedu
George Ikwegbu Chinedu

Posted on

Shipping a Safe Rollback: Downgrading a Drift (SQLite) Database in Flutter Without Losing User Data

#flutter #dart #mobile #drift

You're about to ship a release that bumps your local database schema. Being careful, you also prepare a rollback build, the version currently in production, so that if the new release has problems you can push the old one straight back out.

Then you test the rollback, and it crashes on startup.

This post covers:

  1. Why Drift refuses to "upgrade" to a lower schema version
  2. The fix (and why the obvious fix doesn't work)
  3. A bug that's easy to introduce while making the fix
  4. The git worktree workflow for building both versions side by side
  5. Building three test APKs so QA can test upgrade → rollback → re-upgrade without losing data

All names, branches and paths below are made up for the article.

The setup

  • Production (v1.4.1) uses database schema 11.
  • The new release (v1.5.0, branch feature/new-release) moves to schema 12. It adds three nullable columns:
    • settings.dark_mode_enabled
    • settings.beta_features_enabled
    • reports.submitted_at
  • The rollback build (branch release/1.4.2) is production code (schema 11) with a higher build number, so the stores treat it as an update.

The local database is Drift (version 2.x), and migrations use Drift's generated stepByStep helper:

@override
int get schemaVersion => 11;

@override
MigrationStrategy get migration => MigrationStrategy(
  onCreate: (m) async {
    await m.createAll();
    await seedDefaultSettings();
  },
  onUpgrade: stepByStep(
    from1To2: (m, schema) async { /* ... */ },
    // ...
    from10To11: (m, schema) async { /* ... */ },
  ),
  beforeOpen: (details) async {
    await customStatement('PRAGMA foreign_keys = ON');
    await validateDatabaseSchema(this); // debug-only check
  },
);
Enter fullscreen mode Exit fullscreen mode

The problem

On a device that had run the schema 12 build, installing the schema 11 rollback build crashed on startup with:

StateError: runMigrationSteps was asked to downgrade from versions 12 to 11.
Migration steps can only upgrade. This might happen if users downgrade app versions.
Enter fullscreen mode Exit fullscreen mode

First attempt: handle it in beforeOpen (didn't work)

My first idea was to detect the downgrade in beforeOpen, then drop and recreate every table:

beforeOpen: (details) async {
  if (details.versionBefore != null && details.versionBefore! > schemaVersion) {
    final m = createMigrator();
    await customStatement('PRAGMA foreign_keys = OFF');
    for (final table in allTables) {
      await m.drop(table);
    }
    await m.createAll();
    await customStatement('PRAGMA foreign_keys = ON');
  }
  // ...
},
Enter fullscreen mode Exit fullscreen mode

It made no difference. The same error kept appearing, and the code inside the if never ran.

Why: Drift calls onUpgrade before beforeOpen, including for downgrades

In Drift's source (lib/src/runtime/api/db_base.dart), the open sequence looks roughly like this:

if (details.wasCreated) {
  await _resolvedMigration.onCreate(migrator);
} else if (details.hadUpgrade) {           // versionBefore != versionNow
  await _resolvedMigration.onUpgrade(migrator, details.versionBefore!, details.versionNow);
}

await _resolvedMigration.beforeOpen?.call(details);
Enter fullscreen mode Exit fullscreen mode

Note that hadUpgrade is just versionBefore != versionNow. Drift doesn't distinguish upgrades from downgrades. Both go through onUpgrade, and MigrationStrategy's own docs say so: "Schema version upgrades and downgrades will both be run here."

stepByStep then calls VersionedSchema.runMigrationSteps, which refuses to downgrade:

if (from > to) {
  throw StateError('runMigrationSteps was asked to downgrade from versions $from to $to. ...');
}
Enter fullscreen mode Exit fullscreen mode

So the exception is thrown in onUpgrade, the database fails to open, and beforeOpen never runs.

Why dropping everything was a bad idea anyway

Even if it had worked, dropping every table and recreating it would have:

  • Lost unsynced data. Anything the user created offline and hadn't uploaded yet would be gone.
  • Logged users out, if session or settings data lives in the database.
  • Skipped the default seed data. onCreate seeds default settings, but a bare createAll() doesn't.

A rollback is meant to make things safer for users, and wiping their data on the way back defeats that.

What the schema change actually was

Before choosing a fix, I compared the two schemas. Schema 12 only added nullable columns.

That matters because Drift-generated queries always list columns explicitly:

SELECT "id", "name", ... FROM "settings";
INSERT INTO "reports" ("id", "title", ...) VALUES (...);
Enter fullscreen mode Exit fullscreen mode

An older build that doesn't know about dark_mode_enabled never selects it, and on insert SQLite stores NULL. Extra nullable columns (or columns with a default) are harmless to older code.

So the safest downgrade is to do nothing: keep the data and let Drift record the lower version.

⚠️ This only works when the newer schema is additive. If it renamed or dropped columns, added NOT NULL columns without defaults, or changed table shapes, you'd need a real downgrade migration (for example m.alterTable(TableMigration(...)) to rebuild the table).

The fix

1. Handle the downgrade in onUpgrade, before stepByStep runs

stepByStep(...) returns an OnUpgrade function, (Migrator m, int from, int to) => Future<void>. Wrap it in your own closure and check the direction first:

onUpgrade: (m, from, to) async {
  // Drift calls onUpgrade for downgrades too (from > to), e.g. when a user
  // on a newer build (schema 12) installs this rollback build (schema 11).
  // stepByStep only supports upgrades and throws on a downgrade, so skip it
  // and keep the existing data. Newer schemas only added nullable columns,
  // which this version ignores. Drift then writes user_version = `to`.
  if (from > to) return;

  // Normal upgrade path. stepByStep returns an OnUpgrade function, so it
  // must be invoked with (m, from, to) and awaited.
  await stepByStep(
    from1To2: (m, schema) async { /* ... */ },
    // ...
    from10To11: (m, schema) async { /* ... */ },
  )(m, from, to);
},
Enter fullscreen mode Exit fullscreen mode

After this runs, Drift sets PRAGMA user_version to 11. The schema 12 columns are still in the file, but nothing reads them.

2. Skip debug schema validation after a downgrade

The next run in debug mode produced a different error:

Schema does not match
reports:
 columns:
  additional:
   Contains the following unexpected entries: submitted_at
settings:
 columns:
  additional:
   Contains the following unexpected entries: dark_mode_enabled, beta_features_enabled
Enter fullscreen mode Exit fullscreen mode

This comes from VerifySelf(db).validateDatabaseSchema() (from drift_dev), which compares the real tables with the current schema. That check is usually wrapped in kDebugMode, so it doesn't affect release builds, but it will get in your way when testing locally. Skip it only when the database was downgraded:

beforeOpen: (details) async {
  await customStatement('PRAGMA foreign_keys = ON');

  // After a downgrade the tables still have the newer schema's columns, so
  // the debug-only schema validation would fail. Skip it in that case;
  // fresh installs and upgrades are still validated in debug builds.
  final isDowngrade =
      details.versionBefore != null &&
      details.versionBefore! > details.versionNow;
  if (!isDowngrade) await validateDatabaseSchema(this);
},
Enter fullscreen mode Exit fullscreen mode

3. Make the newer migration safe to run twice

When a user goes 12 → 11 → 12, from11To12 runs a second time against a database that already has those columns. A plain addColumn would fail with duplicate column name. Guard it:

Future<bool> _columnExists(String table, String column) async {
  final rows = await customSelect('PRAGMA table_info($table)').get();
  return rows.any((r) => r.data['name'] == column);
}

from11To12: (m, schema) async {
  if (!await _columnExists('settings', 'dark_mode_enabled')) {
    await m.addColumn(settingsTable, settingsTable.darkModeEnabled);
  }
  // ...same for the other columns
},
Enter fullscreen mode Exit fullscreen mode

Ideally the migration in the new release is written this way from the start. It costs very little and makes a rollback much safer.

A bug that's easy to introduce while making the fix

My first version of the wrapper looked like this:

onUpgrade: (m, from, to) async {
  if (from > to) return;

  stepByStep(          // ❌ no await, and never called
    from1To2: ...,
    // ...
  );
},
Enter fullscreen mode Exit fullscreen mode

It compiles, and the downgrade test passes because it returns before reaching that line. But stepByStep(...) only builds the migration function. It's never called, so every normal upgrade does nothing. Drift still writes the new user_version, leaving users on older schemas with missing columns.

The correct form is:

await stepByStep(/* ... */)(m, from, to);
Enter fullscreen mode Exit fullscreen mode

Lesson: after touching onUpgrade, always run your migration tests for the upgrade path, not just the scenario you were fixing:

flutter test test/drift/migration_test.dart
Enter fullscreen mode Exit fullscreen mode

Testing locally without the release keystore

Release signing lived in CI, so flutter run --release wasn't possible locally. Two options:

  • Profile mode. It isn't kDebugMode, so it behaves like release for this test: 
  flutter run --profile --flavor production
Enter fullscreen mode Exit fullscreen mode

If Android complains about signing, temporarily point the build type at the debug signing config. Don't commit that change.

  • Debug mode with the validation guard from step 2.

Checking that the downgrade happened

Log the versions in beforeOpen. Use print, because kDebugMode-guarded logging doesn't appear in profile mode:

print('[DB] versionBefore=${details.versionBefore} versionNow=${details.versionNow}');
Enter fullscreen mode Exit fullscreen mode

You should see:

  • first launch of the rollback: versionBefore=12 versionNow=11
  • next launch: versionBefore=11 versionNow=11

Or check the file directly on Android:

adb exec-out run-as com.example.myapp cat app_flutter/app.db > app.db
sqlite3 app.db "PRAGMA user_version;"            # expect 11
sqlite3 app.db "PRAGMA table_info(settings);"    # schema 12 columns still there, unused
Enter fullscreen mode Exit fullscreen mode

(getApplicationDocumentsDirectory() maps to app_flutter/ on Android. If the app is still running, also pull app.db-wal.)

The git workflow: two versions side by side with git worktree

To test the round trip you need builds from two branches. Your working branch probably has uncommitted local files (environment config, FVM settings and so on), so switching back and forth is painful. git worktree checks another branch out into a separate folder that uses the same repository.

# from the repo root
git worktree list
git worktree add ../myapp-v12 feature/new-release
cd ../myapp-v12

# confirm it's really the newer schema
grep -n "schemaVersion =>" lib/data/database/database.dart   # expect 12
Enter fullscreen mode Exit fullscreen mode

Untracked local files aren't in git, so copy them across:

cp ../myapp/.fvmrc .
cp ../myapp/lib/core/env.dart lib/core/      # make sure both builds use the same backend environment
fvm install
fvm flutter pub get
Enter fullscreen mode Exit fullscreen mode

One rule: a branch can only be checked out in one worktree at a time. If you get fatal: '<branch>' is already checked out, git worktree list shows where it is.

When you're done:

git worktree remove ../myapp-v12
Enter fullscreen mode Exit fullscreen mode

Building the three QA builds

QA needs to test three transitions on one device without uninstalling, because uninstalling deletes the database and makes the test pointless.

APK Branch Schema Build number Purpose
A feature/new-release 12 150 The new release
B release/1.4.2 11 151 The rollback
C feature/new-release 12 152 Re-upgrade after the rollback

There are two Android rules to work around:

  1. Same signing key. Android won't install an update signed with a different key (INSTALL_FAILED_UPDATE_INCOMPATIBLE). Build all three on the same machine, so they're all signed with ~/.android/debug.keystore.
  2. versionCode must not go down when the user taps an APK to install it. That's why C exists: the same code as A, with a higher build number, so QA can install it over B. Without C, QA needs adb install -r -d A.apk.

Commands 

OUT=~/Desktop/QA-ROLLBACK
FLAVOR=production
APK=build/app/outputs/flutter-apk/app-$FLAVOR-debug.apk
mkdir -p "$OUT"

# B: the rollback (main folder, release/1.4.2)
cd ~/dev/myapp
fvm flutter clean && fvm flutter pub get
fvm flutter build apk --debug --flavor $FLAVOR
cp "$APK" "$OUT/B_rollback_v1.4.2-151_db11_debug.apk"

# A: the new release (worktree, feature/new-release)
cd ~/dev/myapp-v12
fvm flutter pub get
fvm flutter build apk --debug --flavor $FLAVOR
cp "$APK" "$OUT/A_new-release_v1.5.0-150_db12_debug.apk"

# C: re-upgrade (same worktree, higher build number)
fvm flutter build apk --debug --flavor $FLAVOR --build-number 152
cp "$APK" "$OUT/C_reupgrade_v1.5.0-152_db12_debug.apk"
Enter fullscreen mode Exit fullscreen mode

Putting the order, version and schema in the file names makes it hard for QA to install them in the wrong order.

Debug APKs include every CPU architecture and can be 150–250 MB. If your ticket system rejects them, build for physical phones only:

fvm flutter build apk --debug --flavor $FLAVOR --target-platform android-arm64
Enter fullscreen mode Exit fullscreen mode

The QA test plan

Install order: A → B → C. Never uninstall in between. If a store build is already on the device, uninstall it once before A, because it's signed with a different key.

  1. Rollback (A → B). Install A, log in, go offline, create unsynced data, and change a setting. Then install B over it.
    • Expected: the app opens, the user is still logged in, and all data is still there.
  2. Use the app after the rollback. On B, create new records in the tables that still have the newer columns, and change a setting. Then go back online.
    • Expected: saving works and everything syncs.
  3. Re-upgrade (B → C). Install C over B.
    • Expected: no migration error (the guarded from11To12 skips the existing columns), the data is intact, and the new features work.
  4. Regression: production → B. Install the current store build, then B over it.
    • Expected: an 11 → 11 no-op with the data intact.
  5. Fresh install of B.
    • Expected: onCreate runs and the default settings are seeded.

Shipping it

Commit only the database change. Generated plugin registrants, rebuilt web workers and local config files can stay out of the commit:

git add lib/data/database/database.dart
git status --short     # only this file should be staged
git commit -m "Allow safe DB downgrade from schema 12 to 11 for rollback build"
git push origin release/1.4.2
Enter fullscreen mode Exit fullscreen mode

Watch for branches whose names differ only in case (release/1.4.2 vs Release/1.4.2). Check what your branch tracks before you push:

git rev-parse --abbrev-ref @{u}
Enter fullscreen mode Exit fullscreen mode

When CI builds the store version of the rollback, its build number must be higher than the new release's, or the stores won't offer it as an update.

Takeaways

  • Drift sends downgrades through onUpgrade. beforeOpen runs afterwards, so it's too late to handle a downgrade there.
  • stepByStep throws on a downgrade. Wrap it and check from > to first.
  • If the newer schema is additive, the safest downgrade is a no-op. Keep the data. Only drop or rebuild tables when you really have to.
  • Make the newer migrations idempotent (_columnExists checks) so 12 → 11 → 12 works.
  • Skip debug-only schema validation after a downgrade.
  • stepByStep(...) returns a function. Call it and await it, and run your upgrade tests after changing onUpgrade.
  • Prepare the rollback before you need it. Test the downgrade path before release day, not during an incident.
  • git worktree + same-key debug builds + build-number control lets QA test the whole upgrade → rollback → re-upgrade cycle on one device.

If your next release adds anything other than nullable columns, plan the downgrade path before you ship it.

Top comments (1)

Collapse
 
ahmedznouda profile image
Ahmed ZNOUDA •

Good catch on onUpgrade running for downgrades, and on making from11To12 safe to run twice. That's the part most people skip.

One gap I'd check in the 12 → 11 → 12 round trip: anything written while the user is on the rollback build leaves the new columns NULL. So a report submitted during that window comes back to v12 with submitted_at = NULL. If v12 reads NULL as "not submitted yet", that report shows up as a draft again, or gets submitted twice. A small backfill in from11To12 (or v12 treating NULL as "unknown", not "no") closes it.

I'd also move the "additive only" check from QA into CI: drift_dev's schema snapshots plus a SchemaVerifier test that starts at version 12 and opens it with the v11 database class, so a NOT NULL column sneaking into a later schema fails a test instead of a rollback.