Compatible versioning and migrations

[ryanheise]

On Android, sqlite3 databases use the user_version pragma to store the version, with 0 (the default) indicating the database is new and the schema needs to be created. Schema upgrade and downgrade functions are contained in the app binary (containing static SQL statements), and there is no migrations table. To downgrade, the required SQL statements are in the app binary just like the upgrade SQL statements. So the only artifact stored in the database file itself that reflects versioning is the user_version pragma which is a standard feature of sqlite3.

sqflite also adopts the same practice.

I think this approach therefore has some desirable properties in terms of compatibility.

Do you have any thoughts/opinions on using this approach too, for compatibility? I realise that's complicated by the fact that you are already doing things a little differently from this and so any changes to this might introduce backwards compatibility issues for your own users, unless you can do this in a backwards compatible way.

[mugikhan]

Hey @ryanheise, What’s driving your need to consider this approach? Are you aiming to address a specific compatibility issue, simplify your existing implementation, or align with a widely adopted standard?

Additionally, are there any particular challenges or pain points in your current approach that you’re hoping this could resolve?

[ryanheise]

Hi @mugikhan , I have an Android app which has an existing SQLite database. When reimplementing it in Flutter, I used sqflite which allowed the migrations to work in a compatible way with the original app. I am now reimplementing the DB code using sqlite_async and I didn't like the idea of it ignoring the current version that's embedded in the database and basically losing its place in which version it's migrating from and to.

Fortunately I've since realised that the sqlite_async migrations API is all in a separate class which I don't need to use. So what I've done is written my own migration implementation that is compatible with Android's approach. With this, it picks up the correct installed version and migrations happen in the same way that they did in the past.

So actually there is no problem after all because it is technically possible to use/implement alternative migration implementations and bypass the one built into this package. I suppose it might be helpful to have some mention of this fact in the docs, and maybe it is even conceivable for the package to provide an alternative migration implementation. Here's mine:

extension SqliteReadContextExtension on SqliteReadContext {
  Future<int> getVersion() async {
    return (await get('PRAGMA user_version')).columnAt(0) ?? 0;
  }
}

extension SqliteWriteContextExtension on SqliteWriteContext {
  Future<void> setVersion(int version) async {
    await execute('PRAGMA user_version = $version');
  }
}

Future<void> initDb(
  SqliteDatabase db, {
  Future<void> Function(SqliteWriteContext db)? onConfigure,
  Future<void> Function(SqliteWriteContext db)? onCreate,
  Future<void> Function(SqliteWriteContext db, int oldVersion, int newVersion)?
      onDowngrade,
  Future<void> Function(SqliteWriteContext db, int oldVersion, int newVersion)?
      onUpgrade,
  required int newVersion,
}) async {
  await onConfigure?.call(db);
  final version = await db.getVersion();
  if (version != newVersion) {
    await db.writeTransaction((tx) async {
      if (version == 0) {
        await onCreate?.call(tx);
      } else if (version > newVersion) {
        if (onDowngrade == null) {
          throw Exception('onDowngrade $version->$newVersion unsupported');
        }
        await onDowngrade.call(tx, version, newVersion);
      } else {
        await onUpgrade?.call(tx, version, newVersion);
      }
      await tx.setVersion(newVersion);
    });
  }
}

And for reference, here's the source code from the Android SDK which implements the same logic in its original form:

https://github.com/iosphere/sqlite-android/blob/master/library/src/main/java/org/sqlite/database/sqlite/SQLiteOpenHelper.java