[cli] repeatable / always migrations

[mehcode]

Proposal:

cargo sqlx migrate add --always name

Generates:

migrations/version_name.always.sql

Behavior would be that the migration is re-applied whenever its checksum changes. The primary use-case for this would be for view definitions.

I'm open to ideas for better naming here. But I think the feature itself is important. Currently, views are a bit annoying to use in SQLx because you can't create them outside of migrations to have type checking and because migration files can't easily be changed they keep requiring you to add more migration files.

[abonander]

I would argue a few things:

• "always" migrations should be run every time sqlx::migration!("...").migrate() is run, regardless of checksum (I wouldn't even bother tracking it)
• they shouldn't have version numbers
• should be in a subfolder
• always run after versioned migrations
• if we can swing it, should be run by the macros the first time they execute during a build

[Tenshock]

Hello, I would like to suggest some ideas.

As stated by @abonander, the "run always" behaviour would run all the migrations, regardless of their potential state, This behaviour may be wanted for several scenarios.

In the case of @mehcode. We want to execute the scripts but only if they are modified. We can name this behaviour "run on change".

Some database management tools already implement such behaviours, we can inspire from their philosophy.

Run Always

• Liquibase
  Liquibase implements the run always behaviour. Its current implementation stores the checksum for a "run always" script. and throw an error if the script is modified.
• DbUp
  DbUp is highly configurable and can be configured to always run scripts and not to log them.

We have two behaviours here, the first one checks for script integrity and refuse to run if the script is modified between runs, the later one is naive and straight forward as it does not even store applied migrations.

Run on Change

• Liquibase
  Liquibase implements the [run on changebehaviour](https://docs.liquibase.com/concepts/changelogs/attributes/run-always.html). Its current implementation stores the checksum for a "run on change" script and re-apply the script if the checksum is different.
• DbUp
  DbUp coupled with DbUpX plugin can store checksums for applied migrations and re-apply the script if the checksum is different.

Both behaviours are the same, we log the applied scripts and check against their checksum to know if we need to -re-run them.

Proposed implementation

We can extend the different migrations types, through the MigrationType enum, defining currently:

• Simple
• ReversibleUp
• ReversibleDown

As sqlx infers the migration type by looking at the script 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 two more types, with a corresponding suffix to match current logic.

Run Always Type

The "run always" behaviour may be a RunAlways type inferred by a .always.sql suffix, as suggested by @mehcode.

behaviour:
Either:

• run scripts without logging them
  • Simplest implementation
  • No check against their integrity
• run scripts, log them and check against their integrity (Liquibase flavour)
  • More complex implementation
  • checks against script integrity
  • We need to handle cases when we want to udpate the script voluntarily

I prefer the first one.

Run On Change Type

The "run on change" behaviour may be a RunOnChange type inferred by a .onchange.sql suffix.

behaviour:

• Scripts are still logged
• if the checksum is different, run them and update the checksum
• if the checksum is the same, skip the script

Is there some unhandled behaviours to concider?

I do not know the whole code base and potential side effects this new feature may trigger.

Finally, I would suggest to drop the prefix logic (sequential or timestamp) for such migrations type as it is irrelevant for this logic.

---

Also, @abonander, why should it be in a subfolder?