Skip to content

Schema migration helpers

Database migrations are typically written incrementally, with one piece of code transforming the database schema to the next version. By chaining these migrations, you can write schema migrations even for very old app versions.

Reliably writing migrations between app versions isn't easy though. This code needs to be maintained and tested, but the growing complexity of the database schema shouldn't make migrations more complex. Let's take a look at a typical example making the incremental migrations pattern hard:

  1. In the initial database schema, we have a bunch of tables.
  2. In the migration from 1 to 2, we add a column birthDate to one of the table (Users).
  3. In version 3, we realize that we actually don't want to store users at all and delete the table.

Before version 3, the only migration could have been written as m.addColumn(users, users.birthDate). But now that the Users table doesn't exist in the source code anymore, that's no longer possible! Sure, we could remember that the migration from 1 to 2 is now pointless and just skip it if a user upgrades from 1 to 3 directly, but this adds a lot of complexity. For more complex migration scripts spanning many versions, this can quickly lead to code that's hard to understand and maintain.

If you're using the make-migrations command or other drift tools that export your schema, you can instruct drift to generate minimal code for all your schema versions. This is part of the make-migrations command, but this step can also be invoked manually.

The generated file (generated next to your database file) defines a stepByStep utility that can be passed to onUpgrade:

@DriftDatabase()
class Database extends _$Database {
  // Constructor and other methods

  MigrationStrategy get migration {
    return MigrationStrategy(
      onUpgrade: stepByStep(
        from1To2: (m, schema) async {
          await m.addColumn(schema.users, schema.users.birthdate);
        },
        from2To3: (m, schema) async {
          await m.deleteTable('users');
        },
      ),
    );
  }
}

This simplifies writing migrations a lot, since:

  1. Each fromXToY function has access to the schema it's migrating to. So, schema.users is available in the migration from version one to two, despite the users table being deleted afterward.
  2. Migrations are structured as independent functions, making them easier to maintain.
  3. You can keep testing migrations to old schema versions, giving you confidence that the migrations are correct and users upgrading from different versions won't run into issues.

Customizing step-by-step migrations

The stepByStep function generated by the drift_dev schema steps command gives you an OnUpgrade callback. But you might want to customize the upgrade behavior, for instance by adding foreign key checks afterwards.

The Migrator.runMigrationSteps helper method can be used for that, as this example shows:

onUpgrade: (m, from, to) async {
  // Run migration steps without foreign keys and re-enable them later
  // (https://drift.simonbinder.eu/docs/advanced-features/migrations/#tips)
  await customStatement('PRAGMA foreign_keys = OFF');

  await m.runMigrationSteps(
    from: from,
    to: to,
    steps: migrationSteps(
      from1To2: (m, schema) async {
        // we added the dueDate property in the change from version 1 to
        // version 2
        await m.addColumn(schema.todos, schema.todos.dueDate);
      },
      from2To3: (m, schema) async {
        // we added the priority property in the change from version 1 or 2
        // to version 3
        await m.addColumn(schema.todos, schema.todos.priority);
      },
    ),
  );

  if (kDebugMode) {
    // Fail if the migration broke foreign keys
    final wrongForeignKeys =
        await customSelect('PRAGMA foreign_key_check').get();
    assert(wrongForeignKeys.isEmpty,
        '${wrongForeignKeys.map((e) => e.data)}');
  }

  await customStatement('PRAGMA foreign_keys = ON;');
},

Here, foreign keys are disabled before running the migration and re-enabled afterwards. A check ensuring no inconsistencies occurred helps to catch issues with the migration in debug modes.

Moving to step-by-step migrations

If you've been using drift before stepByStep was added to the library, or if you've never exported a schema, you can move to step-by-step migrations by pinning the from value in Migrator.runMigrationSteps to a known starting point.

This allows you to perform all prior migration work to get the database to the "starting" point for stepByStep migrations, and then use stepByStep migrations beyond that schema version.

onUpgrade: (m, from, to) async {
  // Run migration steps without foreign keys and re-enable them later
  // (https://drift.simonbinder.eu/docs/advanced-features/migrations/#tips)
  await customStatement('PRAGMA foreign_keys = OFF');

  // Manually running migrations up to schema version 2, after which we've
  // enabled step-by-step migrations.
  if (from < 2) {
    // we added the dueDate property in the change from version 1 to
    // version 2 - before switching to step-by-step migrations.
    await m.addColumn(todos, todos.dueDate);
  }

  // At this point, we should be migrated to schema 3. For future schema
  // changes, we will "start" at schema 3.
  await m.runMigrationSteps(
    from: math.max(2, from),
    to: to,
    steps: migrationSteps(
            from2To3: (m, schema) async {
              // we added the priority property in the change from version 1 or
              // 2 to version 3
              await m.addColumn(schema.todos, schema.todos.priority);
            },
          ),
        );

        if (kDebugMode) {
          // Fail if the migration broke foreign keys
          final wrongForeignKeys =
              await customSelect('PRAGMA foreign_key_check').get();
          assert(wrongForeignKeys.isEmpty,
              '${wrongForeignKeys.map((e) => e.data)}');
        }

        await customStatement('PRAGMA foreign_keys = ON;');
      },

Here, we give a "floor" to the from value of 2, since we've performed all other migration work to get to this point. From now on, you can generate step-by-step migrations for each schema change.

If you did not do this, a user migrating from schema 1 directly to schema 3 would not properly walk migrations and apply all migration changes required.

Manual Generation

Important Note

This command is specifically for generating the step by step migration helper. If you are using the make-migrations command, this is already done for you.

Drift provides tools to export old schema versions. After exporting all your schema versions, you can use the following command to generate code aiding with the implementation of step-by-step migrations:

$ dart run drift_dev schema steps drift_schemas/ lib/database/schema_versions.dart

The first argument (drift_schemas/) is the folder storing exported schemas, the second argument is the path of the file to generate. Typically, you'd generate a file next to your database class.

The generated file contains a stepByStep method which can be used to write migrations easily:

// This file was generated by `drift_dev schema steps drift_schemas/ lib/database/schema_versions.dart`
import 'schema_versions.dart';

MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (Migrator m) async {
      await m.createAll();
    },
    onUpgrade: stepByStep(
      from1To2: (m, schema) async {
        // we added the dueDate property in the change from version 1 to
        // version 2
        await m.addColumn(schema.todos, schema.todos.dueDate);
      },
      from2To3: (m, schema) async {
        // we added the priority property in the change from version 1 or 2
        // to version 3
        await m.addColumn(schema.todos, schema.todos.priority);
      },
    ),
  );
}

stepByStep expects a callback for each schema upgrade responsible for running the partial migration. That callback receives two parameters: A migrator m (similar to the regular migrator you'd get for onUpgrade callbacks) and a schema parameter that gives you access to the schema at the version you're migrating to. For instance, in the from1To2 function, schema provides getters for the database schema at version 2. The migrator passed to the function is also set up to consider that specific version by default. A call to m.recreateAllViews() would re-create views at the expected state of schema version 2, for instance.