Flutter: Running Migrations On SQLite

In this post we will learn about adding migrations on an existing SQLite database in Flutter.

Introduction

As we know, data models that represent the database tables can often change during the development of an app. Maybe you need to add new column or update or delete one. When you make changes on the data model, it gets out of sync with the database.

Although you can drop the database and re-create it with new data models, this can cause loss of existing data on the database. Specially if the database has already reached Production, we can not simply drop and re-create the database.

A better option is to create migrations on older schema that will incrementally update the database schema to keep it in sync with the application’s data model and preserve the existing data as well.

Setup Database Migrations

For this post we will use a popular SQLite plugin called sqflite that is used to perform database operations in Flutter.

We start by creating a DBMigrator class that will hold all of our database migrations.

class DbMigrator {
   static final Map<int, String> migrations = {};
}

This class contains a static map of migrations that holds key value pairs of integers and strings. The integer number represents the incremental version number and string will contain the actual database migration script.

Once we add migrations to this list, it can look something like this:

class DbMigrator {
  static final Map<int, String> migrations = {

    1: "SQL script to initialize database tables...",
    2: "SQL script to add new column...",
    3: "SQL script to update existing column and update data as well...."
  };
}

Running Migrations

We will make use of the openDatabase method of the plugin to run our migrations.

Future<Database> openDatabase(String path,
    {int version,
    OnDatabaseConfigureFn onConfigure,
    OnDatabaseCreateFn onCreate,
    OnDatabaseVersionChangeFn onUpgrade,
    OnDatabaseVersionChangeFn onDowngrade,
    OnDatabaseOpenFn onOpen,
    bool readOnly = false,
    bool singleInstance = true}) {
  final OpenDatabaseOptions options = OpenDatabaseOptions(
      version: version,
      onConfigure: onConfigure,
      onCreate: onCreate,
      onUpgrade: onUpgrade,
      onDowngrade: onDowngrade,
      onOpen: onOpen,
      readOnly: readOnly,
      singleInstance: singleInstance);
  return databaseFactory.openDatabase(path, options: options);
}

There are 2 main arguments that we need to note here:

• version
  • Version specifies the schema version of the database being opened. This is used to decide whether to call [onCreate], [onUpgrade] and [onDownGrade].
• onUpgrade
  • This gets called if [onCreate] is not specified or if the database already exists and the version is higher than the last database version.

Migration Algorithm

So our logic to run the migrations on SQLite can be set up as:

1. Check to see if database exists.
2. If doesn’t exist, run all migrations.
3. If exists, check if current database version is same as that of the last migration version number.
4. If last migration version is greater, run new migrations.
5. Update database version with latest migration number.

We can request the current database version in the following way:

Future<int> getCurrentDbVersion(Database db) async {
    var res = await db.rawQuery('PRAGMA user_version;', null);
    var version = res[0]["user_version"].toString();
    return int.parse(version);
  }

Finally, the openDatabase method will look something like this:

initDB() async {
    //get database path
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);

    //get max migration version number
    var maxMigratedDbVersion = DbMigrator.migrations.keys.reduce(max);    

    return await openDatabase(
      path,
      version: maxMigratedDbVersion,
      onOpen: (db) {},
      onCreate: (Database db, int _) async {
        //run all migrations if we are creating the database for the first time
        DbMigrator.migrations.keys.toList()
        ..sort()//make sure to sort
        ..forEach((k) async {
          var script = DbMigrator.migrations[k];
          await db.execute(script);
        });
      },
      onUpgrade: (Database db, int _, int __) async {
        //get the version of current database
        var curdDbVersion = await getCurrentDbVersion(db);

        //get only those migration scripts whose number is greater than current database version number
        var upgradeScripts = new Map.fromIterable(
          DbMigrator.migrations.keys.where((k) => k > curdDbVersion), 
          key: (k) => k, value: (k) => DbMigrator.migrations[k]
        );

        if(upgradeScripts.length == 0) return;

        upgradeScripts.keys.toList()
        ..sort()//make sure to sort
        ..forEach((k) async {
          var script = upgradeScripts[k];
          await db.execute(script);
        });

        //once all scripts are run, we need to make sure database version is updated
        _upgradeDbVersion(db, maxMigratedDbVersion);
      }
    );
  }

_upgradeDbVersion(Database db, int version) async {
    await db.rawQuery("pragma user_version = $version;");
  }