Best Practices for Flyway and Hibernate with Spring Boot

Manually applying your database schema is cumbersome and error-prone. Fortunately, there are technologies for version-controlling your database scripts to automate this process across all environments. In the Java ecosystem, Flyway is one of the most popular and a perfect fit alongside Hibernate and Spring Boot.  Flyway is described as the following:

“Version control for your database.
Robust schema evolution across all your environments.
With ease, pleasure and plain SQL.”

With Spring Boot you get a nice integration with Hibernate out-of-the-box. In the past, I've used Flyway alongside Hibernate in nearly every project and want to share my best practices for this setup in this blog post. You should have basic knowledge about Flyway (or start here to learn about it) to get the most of this blog post.

Use Flyway to apply the database schema with Spring Boot

The first and most important practice is not to use spring.jpa.hibernate.ddl-auto=create or update or create-drop (worst) in production. With these properties, you could update and migrate your database schema with Hibernate directly. This might be a valid option for pet projects, but not for enterprise applications in production as you can't control the automatic migration process in detail. You also won't get information about the current database schema version of an environment (e.g. staging, test, dev …).

For your database scripts, you should rely only on Flywayand write versioned migration scripts within your codebase (src/main/resources/db/migration is the default folder):

With the Flyway dependency on the classpath, Spring Boot will initialize everything for you. Once the first connection is established to the database, Flyway will create a flyway_schema_history table to track the already applied database scripts with their checksum. The checksum is essential, as the database scripts are read-only once they are applied to the database so for every new change, you have to introduce a new script and can't update an older script. This workflow might seem costly if you start with Flyway but you get full control of your database schema and reliability.

Validate the database schema on application startup with Hibernate

Using Flyway for your schema model comes with a small downside as you now have to maintain your JPA model and their corresponding DDL scripts in parallel and keep them in sync.

If your database model gets out-of-sync with your Java model you'll run into exceptions during runtime when Hibernate tries to extract the JDBC ResultSet like the following :

To avoid such errors, you can ask Hibernate to validate the database schema against its own model. This is achieved with the property spring.jpa.hibernate.ddl-auto=validate. With this setup, your Spring Boot application will first apply missing Flyway scripts to the database and then check if the JPA and database model match during startup.

If there is e.g. a missing column or wrong data type, your application won't start:

You'll fail fast and early during development and won't get exceptions due to schema differences during runtime. With the help of the schema validation log, you'll also be able to spot and fix the error fast.

Use repeatable migrations for views or functions/stored procedures

The normal Flyway version scripts e.g. V001__CREATE_USER.sql are applied once to the database and are locked due to their checksum. While this is useful for your tables, there might be use cases where you don't want to create a new version for a simple change like renaming a column in a view or updating the logic within a function or stored procedure.

For these scenarios, Flyway offers the opportunity to create so-called Repeatable migrations (more information in the docs) ,which get executed whenever the checksum of the file changes. To create such migration scripts you need the prefix R like R__CREATE_PERSON_VIEW.sql:

Using this repeatable migration, you can make changes to the script file whenever you want, and it gets applied to the database after all migration scripts (with V as prefix) are executed.

 Write Flyway migrations with Java if needed

If you need custom logic for your next database migration, which may result in a complex SQL script, you can also use Java and JDBC to manage migrations.

You just have to follow the Flyway naming convention, place a class in the db.migration package and extend the BaseJavaMigration class:

With the Context object, you have access to the underlying JDBC connectionand execute any SQL you need for your migration.

What you should be aware of when using Java-based migrations is that your checksum is null by default but can be implemented by overriding the getChecksum() method. The output of the flyway_schema_history table looks like the following with a Java migration included:

A common scenario for using a Java migration might be a recalculation a column or changes to BLOB or CLOB attributes.

 (For lazy developers) Let Hibernate create an initial version of the schema

Typing the SQL scripts for a bigger (already existing) JPA model might be laborious. Fortunately, JPA offers a feature for the schema-generation (for lazy developers). With JPA, you can output the DDL scripts to a file and modify/adjust them if needed. For this, I often create a specific Spring profile and connect to a local database:

You can then use the scripts for your Flyway migrations and add optimizations (e.g indexes). Furthermore, don't forget to add missing parts (e.g., views, functions …) manually.

A running Spring Boot application with Flyway and Hibernate is available on GitHub and contains every discussed best practice in this blog post.

Find further persistence-related tips & tricks here:

• Dynamic SQL Querying & Pagination with Querydsl and Spring Data JPA
• Lazy Loading of JPA attributes with Hibernate
• Avoid repeating attributes in JPA entities

… and make sure to read Vlad Mihalcea's excellent JPA book.

Have fun using Flyway together with Hibernate and Spring Boot,

Phil