Nesting migrations in a transaction?

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:

MyApp.Repo.transaction(fn ->
  Ecto.Migrator.run(MyApp.Repo, migrations_path(:my_app), :up, all: true) 
end)

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.

3 Likes

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.

1 Like