Managing database schemas during the lifecycle of

Question

I am developing a Spring/Hibernate/MySql application. The application is not yet in production and I currently use the Hibernate's hbm2ddl feature which is very convenient for managing changes on the domain. I also intend to use Flyway for database migrations.

At some point in the future, the application will be put in production for the first time which leads to my first set of questions:

• What is the best practice to use for schema creation (first time the app is released into production)? Specifically, should I let Hibernate's hbm2ddl create the schema on the production database or let Flyway create the first schema using a SQL script? If the second option (i.e. Flyway) is preferable, then should I generate a SQL script from a hbm2ddl-created database?

Let's then assume I have my application's first version running in production and I intend to resume development on the second version of the application using Hibernate's hbm2ddl.

• How I am going to manage changes to the domain and especially compute the difference between version one and version two of the database schema for migrating the database during the release into production of version two?

Answer 1

The best trade-off is to use hbm2ddl for integration testing only and Flyway for run time, be it QA testing or the production environment.

You can use the hbmddl as the base of your first script for Flyway too, but then every time you make a change to JPA model you need to manually create a new update script, which is not that difficult anyway. This will also enable using DB specific features too.

Because the integration testing and run-time use different strategies it's mandatory to write a system integration test that compares the schemas created by both hbmddl and Flyway. Again this is not difficult either, just need yo make sure you compare against the actual production DB (not the in-memory integration testing one).