Option to run migrations without a transaction

[LukeMathWalker]

As far as my understanding goes, all migration scripts are currently run within a SQL transaction.
This creates issue in Postgres when trying to change a type (e.g. add a variant to an enum):

error: while executing migrations: 
error returned from database: 
ALTER TYPE ... ADD cannot run inside a transaction block

I'd say there are two ways forward:

• make it the user responsibility to wrap its migration query in a transaction, if it makes sense;
• add a way to "disable" transaction wrapping to the CLI.

What do you think?

[abonander]

@LukeMathWalker why did you close this?

[LukeMathWalker]

When pinning the version of Postgres to 12/13 I fail to reproduce the issue. I closed it until I can add enough details for you to reproduce 👍

[LukeMathWalker]

Upon closer investigation, it only happens with Postgres 11 or earlier: https://stackoverflow.com/a/53150621/5861287

[abonander]

I could see something like sqlx migrate run --no-transaction (-n for short, maybe?)

[abonander]

Or perhaps disabling transactions per-file with -- SQLX_NO_TRANSACTION at the start of the file?

[LukeMathWalker]

A per-file solution would be ideal - it might be undesirable to disable transaction guarantees for all migrations while it is actually needed only for one.

A comment could be a good solution, although moves the migration scripts away from being pure SQL.

An alternative would be to automatically add a BEGIN TRANSACTION and COMMIT to the up.sql template generated by sqlx migrate add which the user can choose to keep or remove.

[abonander]

A comment could be a good solution, although moves the migration scripts away from being pure SQL.

I don't think it's an issue since that's a valid SQL comment.

An alternative would be to automatically add a BEGIN TRANSACTION and COMMIT to the up.sql template generated by sqlx migrate add which the user can choose to keep or remove.

The problem is that would silently cause existing migrations in the wild to not run in a transaction, which would have unpredictable results. Someone might also erase those without realizing that they probably want it to be in a transaction.

I guess we could have --no-transaction be an option for sqlx migrate add that adds -- SQLX_NO_TRANSACTION to the generated file. We're already talking about having a sqlx-config.json or something like that which can configure defaults for things like this.

[LukeMathWalker]

I don't think it's an issue since that's a valid SQL comment.

It is indeed a valid SQL comment, but it actually does stuff, so it's not really a comment 😛
I think it's a viable strategy, just wanted to stress that out.

The problem is that would silently cause existing migrations in the wild to not run in a transaction, which would have unpredictable results. Someone might also erase those without realizing that they probably want it to be in a transaction.
I guess we could have --no-transaction be an option for sqlx migrate add that adds -- SQLX_NO_TRANSACTION to the generated file. We're already talking about having a sqlx-config.json or something like that which can configure defaults for things like this.

Sounds good to me.
Do you need help implementing this?