How should I handle migrations from multiple versions down?

[MiguelSOliveira]

Let's say my app is released with int get schemaVersion => 1. My users install the app and everything is fine.
Eventually I keep updating the app and the schemaVersion keeps increasing..

The way I migrate the database is like the following:

...
onUpgrade: (migrator, from, to) async {
          if (from == 7) {
            print("From 7");
          }

          if (from == 8) {
            print("From 8");
          }

          if (from == 9) {
            print("From 9");
          }

          if (from == 10) {
            print("From 10");
          }
        }
...

This obviously doesn't work for a user that hasn't opened the app since v2 for example. Since it will only run the latest migration.

But I also can't do like:

if (to === latest) {
            // migrate stuff from v2
            // migrate stuff from v3
            // migrate stuff from v4
            // ....
          }

Because this way, I risk creating duplicate entries and stuff like that...

How is this usually handled? Can anyone help me?

Thank you!

[davidmartos96]

I've shared a solution before and I've been using without issues for some time.

#789 (comment)

[MiguelSOliveira]

Oh wow, this is actually really neat... And you've never had any issues using this in production?

[davidmartos96]

Not on production. You can always screw something up on develop but the transaction gives you the safety that the schema ends up exactly as you want and if something fails in the middle, then it's rollbacked automatically.
You can also test the migrations. There is a migration test API in moor

[MiguelSOliveira]

Alright, thanks David!

[MiguelSOliveira]

One question David, if you don't mind. Why the toggle on PRAGMA foreign_keys?

You first switch it OFF, then run the migrations, then switch it back ON.

[davidmartos96]

It's good to turn it off in case there is a migration that requires table modifications not directly supported by Sqlite, so you may need multiple steps that could affect tables related by a foreign key. There is more information in the official docs: https://sqlite.org/lang_altertable.html#otheralter