Migration strategy using alterTable method

[maxstubbersfield]

Hi there,

I am trying to use the migration in Moor and I'm not sure of the best way to go about it. From reading through other issues and discussions I really like the idea of looping through and performing the migrations one version at a time - as is suggested here #789 (comment).

The only thing preventing me from doing this is, as I understand (please correct me if I am wrong), the alterTable method will create the new table based on the current GeneratedDatabase, which could be a few versions ahead. So it doesn't seem to fit with the 'one version at a time' upgrade paradigm.

Take the following moor file for example:

CREATE TABLE IF NOT EXISTS "posts" (
	"id"        INTEGER NOT NULL PRIMARY KEY,
	"title"     TEXT,
        "subtitle"  TEXT,
	"footer"    TEXT
);

If the columns "subtitle" and "footer" removed over two versions (from v1 to v3), this would be updated to the latest table structure with the first call to alterTable (when running on v3).

The moor files changes could be as follows:
v1

CREATE TABLE IF NOT EXISTS "posts" (
	"id"        INTEGER NOT NULL PRIMARY KEY,
	"title"     TEXT,
        "subtitle"  TEXT,
	"footer"    TEXT
);

v2

CREATE TABLE IF NOT EXISTS "posts" (
	"id"        INTEGER NOT NULL PRIMARY KEY,
	"title"     TEXT,
        "subtitle"  TEXT
);

v3

CREATE TABLE IF NOT EXISTS "posts" (
	"id"        INTEGER NOT NULL PRIMARY KEY,
	"title"     TEXT
);

The alterTable method does everything I am looking for, so I am wondering if there is a way that I can still use this to perform the migrations one at a time. One solution I thought of was copying the generated table into another file and run the alterTable method on that table (frozen for that version), although that doesn't feel like the best solution - especially if there are changes to generated code.

Does anyone have an better ways to handle this? Or is my understanding incorrect?

Thanks!

[simolus3]

One solution I thought of was copying the generated table into another file and run the alterTable method on that table (frozen for that version)

It's a bit hacky to copy around files generated by build_runner, but you can actually make moor generate a stripped-down version of your table classes which can pretty much only be used for a schema.
Basically, whenever you change your schema you'll want to run this command. There's another command described in the docs that will emit table classes for each saved schema version. I designed this for testing purposes, but someone came up with the idea of using those generated files for the migration itself. I can't find the original discussion for that unfortunately, but at the end the code looked something like this:

// these are generated by dart pub run moor_generator schema generate
import 'schema/v1.dart' as v1;
import 'schema/v2.dart' as v2;
import 'schema/v3.dart' as v3;

Future<void> migrateFrom1To2(Migrator m) async {
  await m.alterTable(TableMigration(
    affectedTable: v2.SomeTable(v2.Database()),
    /*...*/
  ));
}

That's still not perfect, but it allows you to interact with a schema at an older version which helps writing complex schema migrations.

[maxstubbersfield]

Excellent, I will give this a try. Thanks for your help!

[maxstubbersfield]

@simolus3 I think this may have been the issue that you were referring too?

I copied the code that you posted in that issue (below for reference) and that is working well.

QueryExecutor _stub = LazyDatabase(() => throw UnsupportedError('stub'));

Future<void> migrate1(Migrator migrator, MoorDb db) async {
  print('Migrating v1');
  final oldDb = v2.DatabaseAtV2(_stub);
  await migrator.alterTable(TableMigration(
    oldDb.firstTable,
    newColumns: [db.firstTable.secondCol],
    columnTransformer: {
      db.firstTable.secondCol: const Constant<String>("test")
    },
  ));
  return oldDb.close();
}

Thank you!