Sqlite migration in flutter

An example of migration.

In the code below, the "openDatabase" method goes in this order:

• Try to recover the database via the link provided in parameter

• If the database does not exist, the method will execute the code provided in the onCreate parameter

• If the database exists, the method will check the version of the database and compare it with the version number provided as a parameter.

• If the version of the database does not correspond to the version supplied as a parameter, the method will then execute the code of the onUpgrade parameters.

Based on the medium article, but without using the “sqlite_migration“ package

For the example, I am initializing a users table which contains an id and first_name column.

// I use a map for more readability, the key represents the version of the db  Map<int, String> migrationScripts = {    1: '''CREATE TABLE users (              id INTEGER PRIMARY KEY,              first_name TEXT)              '''  };  Future initDatabase() async {    // count the number of scripts to define the version of the database    int nbrMigrationScripts = migrationScripts.length;    var db = await openDatabase(      join(await getDatabasesPath(), "database.db"),      version: nbrMigrationScripts,      // if the database does not exist, onCreate executes all the sql requests of the "migrationScripts" map      onCreate: (Database db, int version) async {        for (int i = 1; i <= nbrMigrationScripts; i++) {          await db.execute(migrationScripts[i]);        }      },      /// if the database exists but the version of the database is different       /// from the version defined in parameter, onUpgrade will execute all sql requests greater than the old version      onUpgrade: (db, oldVersion, newVersion) async {        for (int i = oldVersion + 1; i <= newVersion; i++) {          await db.execute(migrationScripts[i]);        }      },    );    return db;  }

Now, if I want to add a last_name column, I just have to add the sql query in the “migrationScripts” map.

Map<int, String> migrationScripts = {    1: '''CREATE TABLE users (              id INTEGER PRIMARY KEY,              first_name TEXT)              ''',    2: 'ALTER TABLE users ADD last_name TEXT'  };

• If the user already had a version 1 database,onUpgrade will run the second script of the map

• If the user has just installed the applicationonCreate will execute the two scripts of the map.

Check out this medium post it runs through how to set up migrations, the author even created a package to sanitise the whole process. It's a little old but should still hold up well. I used it when setting up my app, which is 3 months old.

This plugin should help you organise SQL queries that create/alter/drop any table you may want, into migrations that can be run on DB initialisation. I'd recommend moving from creating the db in your DB Browser to using SQL queries as this post does, to keep everything in one place.