Is your feature request related to a problem? Please describe.

My problem is I want to to run scripts against my database that are reapplied whenever they are modified. Our use case is for stored procedures versioning. We also want to delete an on change migration without SQLx complaining about it.

Currently, migrations are only additive. This implies that to version stored procedures, we need to add a script each time a stored procedure is updated, leading to a lot of files generated, making the developer experience awful to track which implementation a specific stored procedure has.

Is is far more practical to have only one file per stored procedure (the versioning being supported by an VCS) than looking for the last migration related to a specific stored procedure. We already use SQLx to manage our database versioning, we want to use this great tool for all use cases related to the database versioning.

Describe the solution you'd like

I already forked the project with a working solution but with trade-offs. Let's explain the implementation

How to identify on-change scripts?

SQLx already supports migration types. Currently there is 3 types, Simple, ReversibleUp and ReversibleDown. The scripts have their types inferred by the filename suffix. Simple is inferred by a .sql suffix, ReversibleUp is inferred by a .up.sql suffix, ReversibleDown is inferred by a .down.sql suffix). We can add a new migration type OnChange, with a .onchange.sql.

This implies updating the sqlx migrate add command. We can add a --on-change option (like the -r option to create a reversible migration script).

How to handle the version concept?

Internally, SQLx adds a mandatory version attribute to each script. This version is inferred by the file name prefix. The version must be a parsable i64.

The internal documentation specifies that the prefix must be a positive integer but in facts, creating a migration file -1_my_script.sql works perfectly (only tested in against postgres), and the version is correctly saved in _sqlx_migrations as -1).

For on change migrations, the version concept is irrelevant, we want to create on change scripts without prefix. But we need to comply to current SQLx implementation to avoid breaking changes.

The first question is what version do we assign to on change migrations?
I chose to reserve a range of one billion from i64 max value (9_223_372_035_854_775_807 to 9_223_372_036_854_775_807, inclusive). But after reading that only positive integers are valid, The solution to save on change migrations with negative version may also be adopted.

Nevertheless, both implementations are breaking. The last one million range may break with current users migrations (even if it seems unusual, but probable). The negative versioning, even if marked as invalid in the source code, currently works. Maybe enforcing this rule may be a first step, but it is still breaking.

The second question is what to infer when creating a Migrator?
In my solution, the migrator does not infer the version from the file name as there is no file prefix. In fact, the version is totally irrelevant for further processing (as described below). The migrator will assign an arbitrary number (either starting from i64 max value or -1, depending of the solution for version we choose) and decrementing for each on change script found.

The third question is how to validate applied migrations before running them?
We use the reserved range here, if the migration version to assert is within the range, the migration is de facto valid, as it it either not applied, applied with the same checksum or applied with another checksum and each case is valid.

The fourth question is how to apply such migrations?
We also use the reserved range to get applied migrations of OnChange type here. We proceed as usual for non OnChange migrations. Then, for remaining migrations that are of OnChange type:

• If the migration checksum is found in OnChange applied migrations, it is already applied and skipped
• if the migration checksum is not found, we check from the description attribute
  • if found, that says that the migration has been updated and need to be reapplied
  • if no, it is a new migration and it needs to be applied.

To know which version to attach to new migrations to apply, we sort OnChange applied migrations and take the minimum minus 1 or the i64 max value if not OnChange have been applied before.

To sum up, all the logic relies on the chosen version range reserved for on change migrations. I think is it the main pain point on the current implementation. Is the reserved range a good approach and is the negative range more specifically a good choice?

Describe alternatives you've considered

Liquibase, DbUp and FlyWay have been considered.

• Liquibase has the feature built-in but needs a Pro license to use psql native executor.
• DbUp does not natively supports on-change re-reruns. It needs the DbUpX to support the use case. Unfortunately, the CLI options (DbOps and DbUp CLI) do not integrate the plugin.
• FlyWay does not supports the use-case.

Additional context

I already started a discussion about this topic in the issue [cli] repeatable / always migrations #871, I suggested some ideas. These ideas have matured since, has I forked and implemented a working solution, described above.