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

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. The rollback-window data problem: structural compatibility vs. data-semantic compatibility
4. A bug that's easy to introduce while making the fix
5. Testing rollback compatibility automatically in CI
6. The git worktree workflow for building both versions side by side
7. 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
  },
);

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.

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');
  }
  // ...
},

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);

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. ...');
}

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 (...);

An older build that doesn't know about dark_mode_enabled never selects it, and on insert SQLite stores NULL.

However, it is critical to distinguish between two kinds of compatibility:

• Structural (schema) compatibility: Can the older application physically open the SQLite file, run its queries, and write rows without SQLite errors?
• Data-semantic compatibility: When the newer application returns, will it correctly interpret data that was created or modified while the older application was running?

Extra nullable columns (or columns with defaults) are structurally compatible with older code, provided the older application can safely ignore them. However, the newer application still needs to account for data written while those columns were unavailable.

So for the immediate rollback step, the safest schema downgrade is a structural no-op: keep the data intact and let Drift record the lower version number in SQLite's PRAGMA user_version.

⚠️ This only works when the newer schema is strictly 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);
},

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

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);
},

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
},

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

The rollback-window data problem: structural vs. semantic compatibility

Making the database structurally downgradeable prevents the crash on startup, but it exposes a deeper challenge: the semantic data gap.

Consider the complete round-trip lifecycle of a rollback:

v12 (new release)
  │
  ▼  Rollback triggered
v11 (rollback build active)
  │
  ▼  User creates, edits, or submits data
v11 writes rows (v12-only columns are stored as NULL)
  │
  ▼  Fixed v12 re-shipped
v12 (re-upgrade)

Any record created or modified while the user is on the v11 rollback build has no awareness of schema 12's columns.

Take reports.submitted_at as a concrete example.

In v11, the reports table exists, but v11 queries have no reference to submitted_at. If a user submits a report while running v11, the row is inserted or updated without touching submitted_at. In SQLite, that column remains NULL:

report_id | title          | submitted_at
----------|----------------|-------------
123       | Q3 Audit       | NULL

When the user eventually re-upgrades to v12, what does submitted_at == NULL mean?

In normal v12 code, developers often assume:

if (report.submittedAt == null) {
  // "This report has not been submitted yet"
  showDraftStatus();
  showSubmitButton();
}

That assumption is dangerous. In a post-rollback world, submitted_at = NULL does not necessarily mean "not submitted." It may mean:

This record was created or modified while the application was running an older schema during the rollback window.

Blindly treating NULL as a definitive business state can trigger serious defects: a report the customer successfully submitted under v11 suddenly reappears as a draft, or a background sync process submits the report to your server a second time.

Two ways to solve the NULL/unknown problem

Depending on your domain and data model, there are two approaches to address this:

Approach A — Backfill during the re-upgrade migration

If the existing v11 data contains enough surrounding information to reconstruct or infer the correct state, from11To12 can backfill the column when the user returns to v12:

from11To12: (m, schema) async {
  if (!await _columnExists('reports', 'submitted_at')) {
    await m.addColumn(reportsTable, reportsTable.submittedAt);
  }

  // Backfill from existing v11 state where possible.
  // The exact conditions depend entirely on your application's data model
  // (for example, checking an existing status column or an audit log).
  await customStatement('''
    UPDATE reports
    SET submitted_at = ...
    WHERE submitted_at IS NULL
      AND ...;
  ''');
},

If your schema already has an existing status column or sync timestamp from schema 11, you can use that to backfill a sensible value. The exact SQL depends on your specific data model, but the key is placing it inside the idempotent from11To12 migration step.

Approach B — Treat NULL as "unknown" in application logic

If v11 simply does not capture enough data to reconstruct the missing value, the v12 application logic should treat NULL as unknown, rather than assuming "no":

if (report.submittedAt == null) {
  // Unknown: this record may have passed through an older schema
  // during a rollback window. Reconcile with the server or inspect
  // secondary state before allowing re-submission.
  await reconcileReportStatusWithServer(report);
} else {
  showSubmittedConfirmation(report.submittedAt!);
}

Treating NULL as an ambiguous state prompts your app to check secondary signals — querying your sync server, inspecting local event logs, or running a reconciliation check — before taking a destructive or user-visible action.

Neither approach is universal; the right choice depends on your business domain. What matters is that your v12 code does not mistake a missing column value for a definitive business state.

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: ...,
    // ...
  );
},

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);

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

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

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}');

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

(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

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

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

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.

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"

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

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.

A successful database migration only proves that the database engine could open the file without throwing a SQLite error. It does not prove that data written during the rollback window still has the correct business meaning. QA must test both structural and data-semantic transitions:

1. Install v12 (Build A). Log in, go offline, create unsynced data (e.g. drafts or reports), and change settings (dark_mode_enabled).
2. Rollback to v11 (A → B). Install B directly over A without uninstalling.
   • Expected: The app opens cleanly, the user is still logged in, and all existing data remains accessible.
3. Use the app during the rollback window. On B, create and modify records in the tables that contain v12 columns (for example, submit a new report on B where submitted_at is not referenced by code). Change a setting. Then go back online.
   • Expected: Saving works and records sync. SQLite writes NULL for the unused v12 columns.
4. Re-upgrade to v12 (B → C). Install C directly over B without uninstalling.
   • Expected:
     • No migration error occurs (the guarded from11To12 skips existing columns instead of failing with "duplicate column name").
     • Existing data created under A remains intact.
     • Critically, verify the records created or modified under B: Verify that v12-only fields on those records are interpreted correctly. Verify that NULL values are not blindly treated as a definitive business state (e.g. submitted reports should not be reverted to drafts or re-submitted).
     • Verify that records are not duplicated, corrupted, or shown in an inconsistent state.
5. Regression: production → B. Install the current store build (schema 11), then B over it.
   • Expected: An 11 → 11 no-op with all data intact.
6. Fresh install of B.
   • Expected: onCreate runs and default settings are seeded.

Put rollback compatibility in CI

Manual QA on physical devices is indispensable, but the "additive-only" constraint should not rely solely on a manual QA checklist. A developer working on a future schema could easily introduce a breaking change:

ALTER TABLE reports ADD COLUMN reviewer_notes TEXT NOT NULL; -- ❌ Breaks downgrade!

or rename an existing column, silently making future rollbacks impossible.

Catch this before release by automating rollback compatibility in CI using drift_dev's schema snapshots and SchemaVerifier.

Drift allows dumping schema snapshots to JSON files via drift_dev make-migrations. In your test suite, you can write an automated integration test that simulates the complete round-trip:

v11 database
    ↓
open/use with v12
    ↓
v12 database
    ↓
open/use with v11 (rollback)
    ↓
upgrade again to v12

Using SchemaVerifier, you can verify that the database class for v11 can cleanly open a database initialized with schema 12, and that re-opening it with v12 succeeds without errors:

// test/drift/rollback_test.dart
import 'package:drift_dev/api/migrations.dart';
import 'package:test/test.dart';

void main() {
  test('schema 12 database can safely be opened by schema 11 and re-upgraded', () async {
    final verifier = SchemaVerifier(GeneratedHelper());

    // 1. Start with schema 11, upgrade to 12
    final connection = await verifier.startAt(11);
    final dbV12 = MyDatabase(connection);
    await dbV12.customStatement('PRAGMA user_version = 12');
    await dbV12.close();

    // 2. Open the v12 database using the v11 rollback database class
    // This exercises the `if (from > to) return;` guard in onUpgrade.
    final rollbackConnection = await verifier.newConnection();
    final dbV11Rollback = MyRollbackDatabase(rollbackConnection);
    expect(() => dbV11Rollback.select(dbV11Rollback.reports).get(), returnsNormally);
    await dbV11Rollback.close();

    // 3. Re-upgrade back to v12
    // This exercises the idempotent from11To12 migration step.
    final reUpgradeConnection = await verifier.newConnection();
    final dbV12Reopened = MyDatabase(reUpgradeConnection);
    expect(() => dbV12Reopened.select(dbV12Reopened.reports).get(), returnsNormally);
    await dbV12Reopened.close();
  });
}

Run this in your pipeline:

flutter test test/drift/rollback_test.dart

This automated check validates the schema contract: if an incompatible schema change (such as a NOT NULL column without a default) is introduced, the test fails immediately in CI before the release is ever cut.

Remember: CI validates the migration contract; device testing validates the real release build. CI verifies that SQLite will accept the schema; the three-APK device test verifies that package management, background tasks, and application-level business state behave properly in production.

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

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}

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.
• Schema compatibility is not data-semantic compatibility. An older build opening a newer SQLite database without error does not mean the newer app will understand data written during the rollback window.
• Data written during the rollback window must be planned for. New nullable columns will contain NULL. Either backfill them in your re-upgrade migration or treat NULL as an explicit "unknown" state in application logic.
• If the newer schema is additive, the safest downgrade is a no-op. Keep the data. Only drop or rebuild tables when an incompatible schema change makes it unavoidable.
• Make newer migrations idempotent (_columnExists checks) so the 12 → 11 → 12 cycle does not crash with "duplicate column name".
• Skip debug-only schema validation after a downgrade so local development and QA aren't blocked by the extra columns.
• stepByStep(...) returns a function. Call it and await it, and always run your upgrade tests after editing onUpgrade.
• Enforce rollback compatibility in CI. Use Drift schema snapshots and SchemaVerifier to catch non-additive or breaking schema changes before release.
• git worktree + same-key debug builds + build-number control lets QA test the entire upgrade → rollback → re-upgrade cycle on one device without uninstalling.
• Rollback safety is a multi-layered discipline. It requires coordinating schema structure, migration idempotency, semantic business logic, and automated CI tests before release day.

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