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

score 32 · Answer 1 · edited Aug 28 '22 at 02:45

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

@nirvar I modified my post with an example or I create a new table and I modify both tables. — chris-profico, Nov 10 '21 at 23:46

Giles Correia Morton · Answer 2 · 2020-05-12T11:10:20.950

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.

score 0 · Answer 3 · answered Feb 26 '23 at 11:36

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

Sqlite migration in flutter

3 Answers3

Linked