Run all pending migrations together in one transaction, if --group specified?

[kajmagnus]

What about a --group flag for the migrate run command, which groups all pending migrations together, and runs in a single transaction?

Could look like this: sqlx migrate run --group.

Background: Sometimes it's helpful to have all pending transactions succeed, or none of them. Rather than have, say, 3 out of 5 succeed, but the last-but-one failed. If all or none, the outcome becomes more predictable.

I've previously been using Flywaydb for migrations. They have a group feature, which I was using:

https://documentation.red-gate.com/fd/flyway-group-setting-277579001.html

Flyway Group Setting
Description
Whether to group all pending migrations together in the same transaction when applying them (only recommended for databases with support for DDL transactions)

(Flyway has been working great, but because of a flavor of dependency hell (related to later Flyway versions not supporting Java 11, but Java 17 having removed the Nashorn script engine), looks as if it's simpler to switch to sqlx.)

Looking at the source code, if moving the transaction to outside the for loop, could that be what's needed? (Plus the command line flag.)

sqlx/sqlx-cli/src/migration.rs

Line 52 in 5c573e1

let mut tx = migrator.begin_migration().await?;

    let migrations = load_migrations()?;

    for mig in migrations.iter() {
        let mut tx = migrator.begin_migration().await?;  <—— if --group, move to outside the loop?

        if tx.check_if_applied(&mig.name).await? {

Related: #3129 "Add version infromation in sqlx migrate run output for failed migration"

Sqlx looks great b.t.w. :- )

[sezna]

Just throwing a +1 on this -- this would be a great feature for reliability!