Is it considered an anti-pattern to run multiple migration scripts at once?

[boblauer]

The scenario I'm imagining is this:

• We have a web application that we are about to deploy, that relies on changes from 2 new migration scripts. Let's call them 5.sql and 6.sql.
• We kick off the deploy process, which copied our web files to a new server that is not yet live (blue/green deployments).
• Our migration scripts now run, and 5.sql succeeds, but 6.sql fails for whatever reason (lost connection, bad sql, etc).
• Our web application "rolls back", as it is considered a failed deploy since the migration script exited with a non-0 status code.

Our database is now in an inconsistent state. Our web app has not been updated, but 5.sql is now live in production.

I know that each individual file runs in a transaction, but because the entire process itself does not run in a transaction, would it be considered bad practice to run multiple migration scripts in a row?

[ThomWright]

I think the mitigation here is generally to make your migrations backwards-compatible with the previous version of your application.

Even if you only run one migration at a time, you might run into similar problems if the migration is not backwards compatible. Let's say you are deploying multiple instances of an application one at a time, the first deployment will run the migration. Meanwhile, the other instances will still be running the previous (potentially incompatible) version of the application.

I'm not sure what this library can do to help. In terms of it being an anti-pattern to run multiple migrations at once, I guess I would recommend keeping the number small (ideally 1) and doing small, frequent deployments. That said, I can see legitimate reasons for running multiple at once. For example, when setting up a new environment.

This is a great question, and I reckon the general idea of backwards compatibility deserves a mention in the docs, so I'll leave this open.