Support versioned fixtures for ensuring that migrations preserve data correctly

[abonander]

Motivated by #3177. cc @Tortoaster

Instead of allowing multiple migrations sources, we could just support versioned fixtures, which would do exactly what you want: migrate to a specific version, run the appropriate fixture, then run the following migrations.

We don't need to separately track what fixtures have been run, because we'll know what fixtures to run based on the current migration version. We should probably track and check if migrations have previously been run with fixtures, for consistency. That can just be a bool column on the migrations table.

We shouldn't need to track or hash individual fixtures for a couple reasons:

• Fixtures should always be executable in transactions, and thus should be all-or-nothing.
  • If it can't execute in a transaction for whatever reason, it's DDL and it belongs in a migration.
• Fixtures should be idempotent.
• If we've migrated past the version for a given fixture, it doesn't make sense to run it anyway as we can't assume it'll be compatible with the new schema.

At most, I could maybe see storing a cumulative hash for all fixtures at a given version, to detect changes. Fixing a mismatch would almost always involve resetting the database if it's all just development data anyway.

However, if fixtures are idempotent, we could just automatically re-run the fixtures for the latest version if we detect a change. This would make development and testing easier. Or if we don't track hashes, we can always just re-run the fixture for the latest version when a migration is called-for.

When a user requests to migrate with fixtures (sqlx db setup --with-fixtures, sqlx migrate run --with-fixtures, .with_fixtures(true) on Migrator), the algorithm might look like this:

1. Scan fixtures/versioned/ for fixtures files with the same naming and versioning scheme as migrations.

   • For multiple fixtures at the same version... maybe a folder?
   • Or allow multiple fixtures with the same version and execute them in lexicographical order.

2. Compare with the set of migrations.

   • For every fixture with version X, error if there does not also exist a migration with version X.

3. For every fixture with version X, run migrations up to and including version X.

   • Error if previous migrations have associated fixtures that were not run.

4. Run all fixtures associated with migration version X in a single transaction.

   • Bail if any fixtures fail to apply.

5. Run any following migrations that don't have associated fixtures up to the next one that does.

6. Repeat steps 3-5 until all migrations have been run.

Ideally, you would also keep a set of fixtures showing what the data should look like at the latest version, in fixtures/.

We could even have a command in sqlx-cli to check that the data in the latest fixtures matches the data in the versioned fixtures, or update it if it doesn't.

[Tortoaster]

Sounds good! It requires a bit more work to implement, but is admittedly a much more rigid solution.

I'm not sure when I'll have the time to work on a PR, but I'll look at it soon™!