Sqlite migration in flutter

Question

I am using flutter SQflite to store data in flutter and I have no problem with it so far. Now I want to update my app and the database with it. Until now I was simply replacing the old database with the new one. But now there are some tables that I want to keep there data (user data). How can I replace some tables and keep others? (there is no change in tables structure so far) Edit: I am creating and filling the database outside flutter using DB browser for SQlite (of course except for user in app data)

     Future<void> initDatabase() async {
    var databasesPath = await getDatabasesPath();
    var path = join(databasesPath, "recipes.db");

// Check if the database exists
    var exists = await databaseExists(path);

    if (!exists) {
      // Should happen only the first time you launch your application
      print("Creating new copy of database from asset");

      // Make sure the parent directory exists
      try {
        await Directory(dirname(path)).create(recursive: true);
      } catch (_) {}

      // Copy from asset
      ByteData data = await rootBundle.load(join("assets", "recipes.db"));
      List<int> bytes =
      data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);

      // Write and flush the bytes written
      await File(path).writeAsBytes(bytes, flush: true);

    } else {
      print("Opening existing database");
    }
// open the database

    db = await openDatabase(path,version: 2, readOnly: false);
  }

Solution 1

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 application onCreate will execute the two scripts of the map.

Edit: Use case with multiple tables

Map<int, String> migrationScripts = {
    1: '''CREATE TABLE users (
              id INTEGER PRIMARY KEY,
              first_name TEXT)
              ''',
    2: 'ALTER TABLE users ADD last_name TEXT',
    3: '''CREATE TABLE posts (
              id INTEGER PRIMARY KEY,
              user_id INTEGER,
              content TEXT)
              ''',
    4: 'ALTER TABLE posts ADD title TEXT',
    5: 'ALTER TABLE users ADD age INTEGER'
  };

Solution 2

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.

Comments

[nirav]

If I have multiple tables to modify then how it will work?

[chris-profico]

@nirvar I modified my post with an example or I create a new table and I modify both tables.

[magic_9527]

How to add columns in one upgrade?