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.