What is the operational benefit to separating database migrations and code deployments?

Question

Should Database migrations be in included in the same deployment project as the code?

If they are not included in the same deployment, the Database deployment package and the Code deployment package will get different version numbers and this will generate confusion trying to figure out which Database version corresponds to which code version.

Note that our sql migration scripts are completely independent and unrelated to the code. We are just tying the sql scripts and code to the same deployment package, so they have the same version number.

Some people have the opinion that this is a bad practice, but looking carefully at their reasons in the following articles, they don't explain it very well and though they're not able to convince me:

Decoupling-database-migrations-from-code-deployments

Database-migrations-done-right

What do you think?

Answer 1

In my experience, if the database is only connected via a single application such as a micro service api or monolithic app with one db, it does make sense to have code and db changes lock-step to avoid incomplete deployments.

If you have a database in which there is no single "owner" application, separation and manual synchronization makes sense to ensure proper order of changes.

Answer 2

If they are not included in the same deployment, the Database deployment package and the Code deployment package will get different version numbers and this will generate confusion trying to figure out which Database version corresponds to which code version.

This works fine if you have a single DB node, and a single application node.

However once you have clusters of DB nodes to migrate, and clusters of application nodes to run, you are going to have difficulty trying to keep the same version of DB code corresponding to the same version of an application node. This is especially so when you are doing rolling deployments of new application / DB migration scripts to your clusters. There will be a brief period of time whereby new application code is accessing old database schema, or old application code accessing new database schema.

The best way to deal with this is not to tie app deployment with DB migrations, but to support backward compatibility until old code / schemas can be safely dropped.

Answer 3

I had the same question after joining a team with a strict policy separating code and database migrations. The primary motivation was to avoid a situation where old code pointed to a new database schema. For example, imagine cloned servers used in load-balancing.

That image is taken from an article by another team. They summarise the benefits well. I would say that item 1 is the real reason, and items 2 and 3 are beneficial consequences.

We decided to keep those migrations backward compatible for the reasons:

1. It enables us to do zero-downtime deployment (“blue-green” deployment)
2. We can switch between branches during development without getting errors.
3. Decoupling the code version from the database version is in general a good idea

From Keeping Django database migrations backward compatible

Answer 4

Short answer is yes.

This post has the answer I was looking for:

is-it-okay-to-integrate-database-migration-tools-like-flyway-liquibase-with-the-code