Local Data Storage

Database Migration Strategies Across All Storage Solutions

16 min Lesson 12 of 12

Database Migration Strategies Across All Storage Solutions

Every production Flutter app will eventually need to evolve its data schema — new columns, renamed keys, changed data types, or entirely new tables. Without a disciplined migration strategy, an app update can corrupt user data or crash on launch. This lesson covers how each major storage solution handles schema evolution: sqflite with raw SQL upgrades, Drift with step-by-step migration, Hive with TypeAdapter versioning, and SharedPreferences key renaming.

Core principle: A migration is a one-way, versioned transform that moves data from schema version N to version N+1. Every migration must be idempotent enough to survive an interrupted or repeated run, and must never destroy data that the user expects to see after upgrading.

sqflite: onUpgrade with ALTER TABLE

sqflite exposes three callbacks on openDatabase: onCreate, onUpgrade, and onDowngrade. When version in your call is higher than the version stored in the database file, sqflite fires onUpgrade with the old and new version numbers. A robust upgrade handler uses a while loop (or a switch with fall-through) so that an app jumping multiple versions applies every intermediate migration:

Future<Database> openAppDatabase() async {
  return openDatabase(
    'app.db',
    version: 3,
    onCreate: (db, version) async {
      // Always create at the LATEST schema
      await db.execute('''
        CREATE TABLE notes (
          id      INTEGER PRIMARY KEY AUTOINCREMENT,
          title   TEXT    NOT NULL,
          body    TEXT    NOT NULL,
          pinned  INTEGER NOT NULL DEFAULT 0,
          color   TEXT    NOT NULL DEFAULT '#FFFFFF'
        )
      ''');
    },
    onUpgrade: (db, oldVersion, newVersion) async {
      // Apply each migration step-by-step
      if (oldVersion < 2) {
        // v1 -> v2: add the pinned flag
        await db.execute(
          'ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0',
        );
      }
      if (oldVersion < 3) {
        // v2 -> v3: add a colour column
        await db.execute(
          "ALTER TABLE notes ADD COLUMN color TEXT NOT NULL DEFAULT '#FFFFFF'",
        );
      }
    },
  );
}

SQLite ALTER TABLE limits: SQLite only supports ADD COLUMN and RENAME COLUMN (the latter since SQLite 3.25). To drop a column or change a column type you must create a new table, copy data, drop the old table, and rename — the classic table-rebuild pattern.

Drift: MigrationStrategy with Step Migrations

Drift (formerly Moor) wraps sqflite and provides a typed, code-generated approach to migrations. You declare your schemaVersion on the database class and return a MigrationStrategy from the migration getter. Drift's Migrator helper understands your generated table objects so you rarely write raw SQL:

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

  @override
  int get schemaVersion => 3;

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (Migrator m) async {
      await m.createAll(); // create every table at latest schema
    },
    onUpgrade: (Migrator m, int from, int to) async {
      if (from < 2) {
        // Add the Tags table introduced in v2
        await m.createTable(tags);
      }
      if (from < 3) {
        // Add nullable archivedAt column to Notes in v3
        await m.addColumn(notes, notes.archivedAt);
      }
    },
    beforeOpen: (OpenedDatabase details) async {
      // Enforce foreign keys at every open (SQLite disables them by default)
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );
}

Drift schema tests: Run verifyDatabase() in a unit test after every migration to compare the live schema against Drift's generated expectations. This catches missed columns before users ever see them.

Hive: TypeAdapter Versioning

Hive is a key-value store that serialises objects using TypeAdapter. When you add or remove fields, you must handle the change inside the adapter's read method by providing defaults for missing fields. The field IDs assigned with @HiveField(n) are the on-disk keys — never reuse or change a field ID, only add new ones:

// Version 1 of the model — two fields
@HiveType(typeId: 0)
class UserProfile extends HiveObject {
  @HiveField(0)
  String name;

  @HiveField(1)
  String email;

  UserProfile({required this.name, required this.email});
}

// Version 2 — added bio; field(2) is safe to add,
// existing records just read null and get the default
@HiveType(typeId: 0)
class UserProfile extends HiveObject {
  @HiveField(0)
  String name;

  @HiveField(1)
  String email;

  @HiveField(2)
  String bio; // new field — default set in adapter's read()

  UserProfile({required this.name, required this.email, this.bio = ''});
}

// Regenerate the adapter with build_runner:
// flutter pub run build_runner build --delete-conflicting-outputs
// The generated adapter's read() automatically defaults missing fields to null.

Never reuse a Hive field ID. If field 2 was once age and you delete it, field 2 is retired forever. Reusing it for a new field will corrupt any record that still stores the old data type on-disk.

Migrating SharedPreferences Keys

SharedPreferences has no schema version, so key renames require a one-time migration guard stored in preferences itself. The pattern is: read the old key, write it under the new key, then remove the old key, and record a migration version flag so the migration runs only once:

Future<void> migratePreferences() async {
  final prefs = await SharedPreferences.getInstance();

  const migrationKey = 'prefs_migration_version';
  final currentMigration = prefs.getInt(migrationKey) ?? 0;

  if (currentMigration < 1) {
    // v0 -> v1: rename 'user_name' to 'display_name'
    final oldValue = prefs.getString('user_name');
    if (oldValue != null) {
      await prefs.setString('display_name', oldValue);
      await prefs.remove('user_name');
    }
    await prefs.setInt(migrationKey, 1);
  }

  if (currentMigration < 2) {
    // v1 -> v2: migrate boolean 'dark_mode' to string 'theme_mode'
    final darkMode = prefs.getBool('dark_mode');
    if (darkMode != null) {
      await prefs.setString('theme_mode', darkMode ? 'dark' : 'light');
      await prefs.remove('dark_mode');
    }
    await prefs.setInt(migrationKey, 2);
  }
}

Where to Call Migrations in Your App

All migrations must run before any data access. The safest place is inside your app's initialisation sequence, before runApp(), or inside a splash/loading screen that gates navigation:

Call openAppDatabase() (sqflite/Drift) at startup — migrations run automatically inside onUpgrade.
Call migratePreferences() explicitly once before reading any prefs key.
Register updated Hive adapters before opening any box — Hive handles field defaulting transparently on the next read.

Summary

Each storage layer has its own migration contract: sqflite uses versioned onUpgrade callbacks with conditional SQL; Drift adds a typed MigrationStrategy with generated helpers; Hive relies on stable field IDs and adapter defaulting; SharedPreferences needs a manual migration version flag. Applied consistently, these strategies ensure every user's data survives app updates without loss or corruption.

Key takeaway: Always increment your schema version, never skip migration steps, never reuse retired identifiers, and run migrations before any data access. These four rules keep user data safe across every release.

Database Migration Strategies Across All Storage Solutions

Database Migration Strategies Across All Storage Solutions

sqflite: onUpgrade with ALTER TABLE

Drift: MigrationStrategy with Step Migrations

Hive: TypeAdapter Versioning

Migrating SharedPreferences Keys

Where to Call Migrations in Your App

Summary

Tutorial Complete!