How to migrate multiple migrations files in a single transaction?

Questions / Help
,
1

I’ve been wondering for some time now if there is a way to instruct ecto to run all, not yet applied, migrations inside a single transaction.

For example, let’s say I have two migrations:

20260406204201_remove_some_stuff.exs

20260406206213_add_some_stuff.exs

Now, let’s say that I push this to prod, ecto will create a transaction to run 20260406204201_remove_some_stuff.exs, commit it, and then create a new transaction for 20260406206213_add_some_stuff.exs.

Now, let’s imagine that for some reason 20260406206213_add_some_stuff.exs migration fails.

Now, I have a system that I can’t rollback to the last working version because 20260406204201_remove_some_stuff.exs removed some fields, so I can’t actually rollback it, and I can’t install the new version because I can’t finish the full migration.

Meaning that I will need to first create a new release with 20260406206213_add_some_stuff.exsfixed and I will have downtime during that period.

So, I was wondering, is there some way to make Ecto run ALL migrations inside the same migration?

If I could do that, then the example above would work just fine, since 20260406204201_remove_some_stuff.exs and 20260406206213_add_some_stuff.exs will run inside the same transaction, so even if 20260406206213_add_some_stuff.exs fails, 20260406204201_remove_some_stuff.exs will not be committed, so it would be safe to rollback to the older, working version.

2

Migration is an operation which converts database from one valid state to another valid state (in both directions). If some migration leads to invalid state, it means that the migration is incorrect. You can work with incorrect migrations, but it is much harder thing to work with than the correct migrations

Calling multiple migrations inside one transaction is possible, you’d just have to merge the code of all of them into a single migration. Check out this guide for more info about it: GitHub - fly-apps/safe-ecto-migrations: Guide to Safe Ecto Migrations · GitHub

2 Likes
3

Nice, this is a timely question as I’m planning to tackle migration soon in my library and I didn’t know this was a thing, feels like this should be one transaction with multiple savepoints.

Any reason why you would’t want this behavior?

4

Performance. DDL transactions lock tables for the duration of the transaction.

1 Like
5

Ah, yes, I was aware of ecto.dump, but unless I’m misunderstanding something, It will not fix the issue I’m having.

AFAIK, ecto.dump will always dump your current DB state, it can’t create a SQL file for a subset of migrations (In my example 20260406206213 and 20260406204201), so that’s only useful if you are applying the dump to a new, empty DB or to just be able to remove migrations files for already applied migrations.

mix ecto.migrate do have the options –log-migrations-sql and –log-migrator-sql which will output the SQL code, but you can’t get that without actually applying the migration to the DB, meaning that it doesn’t solve the issue too.

Too bad that we don’t have a dry-run or some way to extract the sql commands from the migrations to run it manually

6

If I were you, I’d probably make sure (and test) that either all migrations can be rolled back, you can adjust old migrations too if needed, OR replace all old migrations with one, idempotent, new migration.

Too bad that we don’t have a dry-run or some way to extract the sql commands from the migrations to run it manually

You could rollback to the latest migration version on prod with your test environment, and then re-run all migrations with log migrations sql. You’ll get raw sql.

Can you elaborate more on the problem you’re solving?

7

Tbh I don’t think even with the request implemented it would help much. If you’re relying on transactional rollback I’d strongly suggest this to be enforced by having all code in a single migration and not be relying on deployment order. Nothing will make sure 20260406204201_remove_some_stuff.exs won’t be rolled out individually besides manual “making sure”, which is not a great state to be in.

If you really need distinct migrations you’re probably better off making individual migrations in a way they do not break the prev. version of your application, so you do not need to rollback anything to stay with a working application.

3 Likes
8

The link I’ve shared is not only about ecto.dump and if you want to merge two migrations into a single one, the most part of the advise still applies

9

Two options:

  1. Accept multiple migrations and extra work to make sure each is non-breaking and is deployed together with code that also makes use of the new schema / new data;
  2. Make one big migration.

#1 could be viewed as safer but it’s absolutely introducing code churn – you’ll write interim code to work with the interim state of the schema and the data… and you’ll be throwing it away next week.

#2 requires more planning and more testing.

I’d usually vote for #2 but I’ve done #1 more times than it due to various business requirements and limitations.

1 Like