Better support for incremental migrations

[ToniTornado]

Is your feature request related to a problem? Please describe. onUpgrade with some addColumn calls is working well. But when you start using createTable and alterTable along with some addColumn, you run into problems because the migrations use the latest table definition from code and sometimes a column defined there does not exist yet. Also the intended usage is not very clear once you start writing the if-conditions with from and to values (<= , < , == ?). I found people writing all possible permutations of from/to to get it work.

Describe the solution you'd like I found a good way to refactor migration code here to move code and complexity out of onUpgrade: https://github.com/simolus3/moor/issues/789

@simolus3 pointed me to this issue that describes how to get around described problems with createTable/alterTable: https://github.com/simolus3/moor/issues/1174#issuecomment-832207994

My proposal now is to develop this into a ready-made migration solution within Moor:

IncrementalMigrationStrategy(
  onCreate: (Migrator m) => m.createAll(),
  migrations: [
    // Migrate from schema version 1 to 2
    (Migrator m, DatabaseDefinition fromDb, DatabaseDefinition toDb) async {
      await m.createTable(toDb.recentSearches);
    },
    // Migrate from schema version 2 to 3
    (Migrator m, DatabaseDefinition fromDb, DatabaseDefinition toDb) async {
      await m.addColumn(fromDb.recentSearches, toDb.recentSearches.relevance);
    },
  ],
)

This is some pseudo code how I could imagine a future migration API. The implicit order of migrations in the list is very important, I'm not sure if it is enough to add that comment to each function or if you see a better way to enforce it?

It requires some improvements on the code generation that is currently only used to create the migration tests. It needs some documentation on how to use fromDb and toDb and when to run the generator to create a snapshots of the current database version.

Moor could run through the migrations list one by one and after each successful step increase the database's schema version. When a migration fails, restarting the app will cause it to run it again next time. (There may be some schema changes that do not work well with transactions of course but we could show a warning in the log when such feature is used)

I would also love to hear if @MiguelSOliveira, @davidmartos96 and @MuhammadDicky have feedback on this.

Big thanks to @simolus3 for creating such a great package.

One addition: To insert rows in the tables in any migration we also need this experimental history feature for data classes and companions. Maybe we can also access their versions through the versioned DatabaseDefinition object?

[kuhnroyal]

I usually use plain SQL migrations in order to avoid this. And I can mostly just copy the moor files. But this is only useful if you are using moor files in the first place.

I am not a fan of having the code for all previous schema versions in the app.

But one thing I have thought about is the new auto-migration feature in room. https://medium.com/androiddevelopers/room-auto-migrations-d5370b0ca6eb

We could do something similar but more basic:

AutoMigration(
  from: 1,
  to: 2,
  steps: (AutoMigrator m) {
    m.createTable("searches");
    m.addColumn("recent_searches", "relevance");
  },
)

We use plain strings here, no references to any table classes. Moor can then use the existing dumped schemas and generate plain SQL migrations from the migration steps for us. You can then verify the generated SQL code and don't need classes for an old schema. Additionally, like in Room, there would be some ManualMigration class which would require handwritten SQL code.

[ToniTornado]

Your AutoMigration approach looks very nice. But it is probably much more work to implement? I strongly agree that it is not perfect to have all schema versions in the app. We could get around that if we only generate the required SQL statements from the database versions code and store them in some top-level folder that is not included in the final app.

[davidmartos96]

I'm also using raw statements for the migrations steps. I think it's easier to reason about them.

[simolus3]

I'll need some time to think about a nice solution here. I think I can see a solution where we'd generate old schemas outside of lib/, but have some tool to generate a sound migration based on them. Room's auto migrations will likely be a lot of work, but if we have a general tool to create migration scripts then we can incrementally make it smarter.

To insert rows in the tables in any migration we also need this experimental history feature for data classes and companions

You can use moor_generator schema generate --data-classes --companions to get them as well.

We could get around that if we only generate the required SQL statements from the database versions code and store them in some top-level folder that is not included in the final app.

That's true for DDL statements, but it will get harder/impossible when you want to use inserts/selects in your migration script, right? Because then we'd still need to generate the companions used, and they need to go in lib to be usable.

[westito]

I think, we could use something like '.deprecated()' column modifier. It could be visible for migrator, but do not visible for regular Table definition, DataModels and Companions.