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);
  }

Answer 1

Based on @chris-profico's answer I came up with a solutation which works exactly the same as of the database versioning perspective but allows to have multiple statements in one sql asset file, thought i'd share this:

Define SQL, important to close all satements with ;

create table foo
(
id text primary key
);
create table bar
(
id text primary key
);

Now when openDatabase is called:

    final Map<int, String> sqlScripts = await SqlAssetHelper.getSQLScripts();
    final scriptAmount = sqlScripts.length;
    final String appDocDirectory = await getDatabasesPath();
    final String databasePath = '$appDocDirectory/flink-app.db';
    _database = await openDatabase(
      databasePath,
      version: scriptAmount,
      onCreate: (db, version) async {
        _log.info('Initializing table structure...');
        for (int i = 1; i <= scriptAmount; i++) {
          List<String> scripts = sqlScripts[i]!.split(';');
          scripts.removeWhere((element) => element == '\r\n');
          for (var element in scripts) {
            await db.execute(element);
          }
        }
      },
      onUpgrade: (db, oldVersion, newVersion) async {
        _log.info('Initial table structure recognized. Applying migrations...');
        for (int i = oldVersion + 1; i <= newVersion; i++) {
          final List<String> scripts = sqlScripts[i]!.split(';');
          scripts.removeWhere((element) => element == '\r\n');
          for (var element in scripts) {
            await db.execute(element);
          }
        }
      },
    );

Also the SqlAssetHelper given your files are located in assets/sql/foo.sql:

import 'dart:convert';

import 'package:flutter/services.dart';

class SqlAssetHelper {

  static Future<Map<int, String>> getSQLScripts() async {
    final String manifest = await rootBundle.loadString('AssetManifest.json');
    final Map<String, dynamic> manifestMap = jsonDecode(manifest);

    final List<String> sqlScriptPaths = manifestMap.keys
        .where((String key) => key.contains('sql/'))
        .where((String key) => key.contains('.sql'))
        .toList();

    final Map<int, String> sqlScripts = {};
    int version = 1;
    for (String path in sqlScriptPaths) {
      final String sqlFileContent = await rootBundle.loadString(path);
      sqlScripts[version] = sqlFileContent;
      version++;
    }

    return sqlScripts;
  }
}

Answer 2

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'
  };