Migrating a mobile database in flutter (SQLite)

Database migration is a headache for any developer working in an application with a non-trivial state. Whenever the state structure changes it must be depicted in the underlying database as well. Coming from a Java background I am used to having libraries to help me out with this problem, such as flyway or liquibase. So, naturally, I was curious to see how I can tackle database migration in an SQLite database using flutter.

---

Using sqflite plugin

The most common plugin to use for SQLite in flutter is sqflite. It provides callbacks to facilitate migrations, but the actual migration implementation is up to the user. If you take a look at the signature of the methodopenDatabase, you can spot an onCreate and an onUpgradeoption.

Using onCreate and onUpgrade

In order to facilitate schema migration you should provide appropriate callbacks for onCreate (schema initialisation) and onUpgrade(schema migration) options. Much like so:

Where the initialSchema is an initialization sql statement and themigrationScript is a migration sql statement. Mind you that there is a limitation in sqflite, which prevents the execution of multiple sql statements in a single db.execute()call. This means that you have to separate multiple statements into different executions. Much like so:

Binding the version

This is all straight forward up to now, but what about the version option? Well, this option is the one controlling when and if the migration will run. So, you have to put extra attention into ensuring it always has the correct number. A nice trick you can do is to bind this number with the number of migration scripts you have.

Let’s say you have the following transition from migrationScripts1to migrationScripts2.

Obviously you would like to run all 3 new migration scripts that have been added. If you bind the version to the number of migration scripts, say n, and given that the version cannot be less than 1, you would like to get from version 1 to version 1 + n.

version: migrationScripts.length + 1

But what if you want to transition from following migrationScript1 to migrationScript2?

In this situation you want to migrate from version 2 to version 4. This is where the oldVersion variable in the onUpgrade callback comes to play. So, with some index manipulation you can end up to something like the following.

Using sqlite_migration library

I have used the above pattern way too many times so I decided to create a library out of it. The sqflite_migration library was created for my own sanity and it basically abstracts away all sqflite migration details into a migration layer, which I think is straightforward to use.

You just have to use the `openDatabaseWithMigration` method and pass the migration configuration.

As you can see using sqflite_migration library simplifies things a lot, as you do not have to deal with index manipulation and script execution yourself.

---

Database migration is a very complex topic. More so when you have to do it in embedded applications such as mobile apps. sqflite_migration, was created as a very simple abstraction layer on top of sqflite functionality. Being simple though doesn’t mean it is not useful.