How to migrate multiple migrations files in a single transaction?

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

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?

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

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

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?

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.

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

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.