Is it possible to run migrations inside a transaction in Ecto (with MariaEx, MySQL) so that, if some migrations go OK but one of them fails, all of them are rolled back?
The context is running a migration command on a Distillery release, as in this guide - I want to be able to roll back a deploy, including all its migrations, if anything goes wrong in one of them.
I tried this but it didn’t work as I expected, the successful migrations are not rolled back:
I know I could run manually every pending migration, and manually roll them back if needed, but it could be the case that some of the migrations couldn’t be rolled back, that’s why I think running all of them in a single transaction would be better.
MySQL does not support altering the table structure in a transaction, instead it will implicitly commit the transaction. See this list of statements that cause an implicit commit. That’s why I think for Ecto to accomplish something like that, it would need to emulate transactions on its own side, and decide what to do if a migration was only partly completed (how do you roll it back partly?).
As such I think with MySQL it would be very difficult to accomplish this. Not sure about PostgreSQL or other database systems.
PostgreSQL does support altering table structure in a transaction, and when using PostgreSQL with Ecto, if there’s a failure within an individual migration, that migration is rolled back by default.
Running a list of individual migrations in a single transaction would take some custom code, but it’s at least possible with PostgreSQL.