For Evolutionary Database Tools like Flyway or LiquidBase, it is better to integrate the tool or left it as a stand alone step in the CI process?

Question

Although I believe my question is language and DB agnostic, I will provide the specifics technologies in this case study because it might help understanding my question (sorry for my English).

It's a Java-Spring application to be hosted by several Tomcats with one central postgresql DB whose schema is versioned by migration scripts. A tool like FlywayDB or LiquidBase is used to manage the versioning.

The database migration tool can be used in a few ways :

1. Fully integrated in the (Spring) application : when the application is starting, the DB migration tool checks its versioning table and its local scripts directory. If new scripts are found, the tool runs them and thus update the database. It also check for hash equality, etc.
2. The database migration tool is called upon at a specific step in the continuous integration process. It updates the DB and then the applications are deployed and started.

What's the best way and why ?

I though about a few arguments :

• option 1 pro : 100% the schema and the code match.
• option 1 pro : FlywayDB documentation says it's best

source : https://flywaydb.org/documentation/api/

Flyway brings the largest benefits when integrated within an application. By integrating Flyway you can ensure that the application and its database will always be compatible, with no manual intervention required. Flyway checks the version of the database and applies new migrations automatically before the rest of the application starts. This is important, because the database must first be migrated to a state the rest of the code can work with.

• option 1 con : if something happens, the application has been updated to the last version but the database has not. This server is out of service. Unless a rollback system for the application deployment is built, with the ability to catch the db migration tool error. Does it even exist or have we to implement it ourselves ?
• option 2 pro : easy to catch a db migration issue, to roll back and cancel the deployement. The production servers keep running as before, no time out.
• option 2 con : if deployement fails on one server, old code will mismatch with new DB schema but the application still runs => data corruption might silently occur.

Maybe we could keep checking the DB schema / code version matching without applying the scripts from the application runtime and apply the DB migration scripts from the continuous integration process ?

Thank you for sharing your experience on this subject.

Edit : I checked & these tools used a lock on the database to manage multiple instances trying to update the schema.

Answer 1

There's one really huge reason not to include migrations in your application that I neglected to mention: flyway needs high-level permissions that you should not be giving to an application. This violates an important security practice.

I really don't see an upside to having the application update the DB on startup. Let's consider the two main contexts of this: you have automated deployments or you have some sort of manual deployment. In the former, you can simply make the flyway update routine part of the deployment process preceding the application startup. In the second case, the manual process means there's a good possibility that something goes wrong like deploying the wrong version of the application and then you might have much bigger issues if a schema migration was bundled into that.

Database schema changes are (almost always) much more impactful, risky, and difficult to correct than application deployments. If you set up your application deployment right, when you deploy the wrong version of an application, it's simple to correct: you deploy the correct version. This is especially the case if you use containers for your solution. Changes to a database schema are not usually that simple. They are often accompanied by data transformations which may not always be reversible. The more risky a change is, the more deliberate you should be about it. Having schema changes occur as a side effect of something that is usually less risky is asking for trouble.

if deployement fails on one server, old code will mismatch with new DB schema but the application still runs => data corruption might silently occur.

I would recommend that your application check the schema on startup and verify that it matches the version the application expects. If the schema is not aligned with the application version, the application should fail to start.

If you keep the schema migration separate, it gives you much more freedom to manage the deployment process. If you have a clustered app with rolling deployments, you may need to stop everything to make a major schema change. Consider what happens if that schema change is part of a rolling deployment i.e. the old version is still up and running in some nodes. Having the application upgrade the schema might seem to simplify things but it actually complicates things, especially in the case you accidentally start the wrong version of the application that then attempts to 'fix' the database.

Answer 2

This question boils down to how to handle failures in db and application upgrades, and which one should be done first, in a situation where there are lots of application instances and one shared DB.

If it is critical to keep the downtimes as small as possible, I would try to split the deployment into the following steps:

1. Set the applications into "read-only" mode for a short time to prevent data loss (you may have to implement such a feature).

2. Upgrade the DB (schema and data) to an intermediate version, whery only changes to the DB are applied like adding columns and tables which are "backwards compatible". So the old application version can work with them at least in read-only-mode. Even an operation like "split a column into two" with some data migration is possible here, as long as the original column is not deleted right now. If this step fails, roll back the DB.

3. Then, upgrade the applications. Any application which was updated successfully can be switched to read-write-mode again. If one application server upgrade fails, fix the issue (or take the server offline). Don't proceed until all running applications are upgraded.

4. Upgrade the DB to a final version, applying any non-backwards compatible changes to the database, like deleting tables or columns which are not required any more by the new application version.

That way, you won't need a rollback system for the applications, and you don't have the risk of silent data corruption.