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.

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:

Running Migrations

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

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:

Become A Flutter Expert With This Course

Finally, the openDatabase method will look something like this:

About the author