Best Practices for Flyway and Hibernate with Spring Boot

Last Updated: September 12, 2022 | Published: April 7, 2019

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):

CREATE TABLE book (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  publisher VARCHAR(255) NOT NULL,
  pages INT4 NOT NULL,
  published_at TIMESTAMP NOT NULL
);

CREATE TABLE book (

id BIGSERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL,

publisher VARCHAR(255) NOT NULL,

pages INT4 NOT NULL,

published_at TIMESTAMP NOT NULL

);

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 :

Caused by: org.postgresql.util.PSQLException: ERROR: column bestreview0_.name does not exist
  Position: 165
  ... 53 common frames omitted

Caused by: org.postgresql.util.PSQLException: ERROR: column bestreview0_.name does not exist

Position: 165

... 53 common frames omitted

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:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: 
     Schema-validation: wrong column type encountered in column [avg_stars] in table [best_reviewed_books]; 
     found [numeric (Types#NUMERIC)], but expecting [varchar(255) (Types#VARCHAR)]
  ... 20 common frames omitted

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException:

Schema-validation: wrong column type encountered in column [avg_stars] in table [best_reviewed_books];

found [numeric (Types#NUMERIC)], but expecting [varchar(255) (Types#VARCHAR)]

... 20 common frames omitted

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:

DROP VIEW IF EXISTS best_reviewed_books;

CREATE VIEW best_reviewed_books AS
SELECT 
  book.id AS book_id, 
  book.name AS book_name, 
  round(avg(review.stars),2) as avg_stars,
  max(review.stars) as max_stars,
  min(review.stars) as min_stars,
  count(review.id) as total_reviews
FROM book 
JOIN review ON review.book_id = book.id 
GROUP BY book.id
ORDER BY avg_stars desc;

DROP VIEW IF EXISTS best_reviewed_books;

CREATE VIEW best_reviewed_books AS

SELECT

book.id AS book_id,

book.name AS book_name,

round(avg(review.stars),2) as avg_stars,

max(review.stars) as max_stars,

min(review.stars) as min_stars,

count(review.id) as total_reviews

FROM book

JOIN review ON review.book_id = book.id

GROUP BY book.id

ORDER BY avg_stars desc;

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:

package db.migration;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;

public class V003__UPDATE_REVIEWS extends BaseJavaMigration {

  @Override
  public Integer getChecksum() {
      // implement this if needed to detect changes
    return super.getChecksum();
  }

  public void migrate(Context context) throws Exception {

    // either create a Spring JdbcTemplate or use native JDBC
    // new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(),
    // true));

    try (Statement select = context.getConnection().createStatement()) {
      try (ResultSet rows = select.executeQuery("SELECT id, name, publisher FROM book")) {
        while (rows.next()) {
          Long id = rows.getLong(1);
          String bookName = rows.getString(2);
          String publisher = rows.getString(3);
          try (PreparedStatement preparedUpdate = context.getConnection()
              .prepareStatement("UPDATE book SET name = ?, publisher = ? WHERE id = ?")) {
            preparedUpdate.setString(1, bookName.toUpperCase());
            preparedUpdate.setString(2, publisher.toUpperCase());
            preparedUpdate.setLong(3, id);
            preparedUpdate.executeUpdate();
          }
        }
      }
    }
  }
}

package db.migration;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import org.flywaydb.core.api.migration.BaseJavaMigration;

import org.flywaydb.core.api.migration.Context;

public class V003__UPDATE_REVIEWS extends BaseJavaMigration {

@Override

public Integer getChecksum() {

// implement this if needed to detect changes

return super.getChecksum();

}

public void migrate(Context context) throws Exception {

// either create a Spring JdbcTemplate or use native JDBC

// new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(),

// true));

try (Statement select = context.getConnection().createStatement()) {

try (ResultSet rows = select.executeQuery("SELECT id, name, publisher FROM book")) {

while (rows.next()) {

Long id = rows.getLong(1);

String bookName = rows.getString(2);

String publisher = rows.getString(3);

try (PreparedStatement preparedUpdate = context.getConnection()

.prepareStatement("UPDATE book SET name = ?, publisher = ? WHERE id = ?")) {

preparedUpdate.setString(1, bookName.toUpperCase());

preparedUpdate.setString(2, publisher.toUpperCase());

preparedUpdate.setLong(3, id);

preparedUpdate.executeUpdate();

}

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:

postgres=# SELECT * FROM flyway_schema_history;
 installed_rank | version |       description        | type |        script        |  checksum  
----------------+---------+--------------------------+------+----------------------+-------------
              1 | 001     | CREATE BOOKS             | SQL  | V001__X.sql          | -1479319827 
              2 | 002     | CREATE REVIEWS           | SQL  | V002__Y.sql          | -1898131861 
              3 | 003     | UPDATE REVIEWS           | JDBC | db.migration.V003__Z |             
              4 |         | BEST REVIEWED BOOKS VIEW | SQL  | R__VIEW.sql          |  -593656637 
              5 | 004     | CREATE TAGS              | SQL  | V004__TAGS.sql       |   694725004 
(5 rows)

postgres=# SELECT * FROM flyway_schema_history;

----------------+---------+--------------------------+------+----------------------+-------------

1 | 001 | CREATE BOOKS | SQL | V001__X.sql | -1479319827

2 | 002 | CREATE REVIEWS | SQL | V002__Y.sql | -1898131861

4 | | BEST REVIEWED BOOKS VIEW | SQL | R__VIEW.sql | -593656637

5 | 004 | CREATE TAGS | SQL | V004__TAGS.sql | 694725004

(5 rows)

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:

spring:
  profiles: generatesql
  datasource:
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
  flyway:
    enabled: false
  jpa:
    properties:
      javax: 
        persistence:
          schema-generation:
            create-source: metadata
            scripts:
              action: create
              create-target: src/main/resources/ddl_jpa_creation.sql

spring:

profiles: generatesql

datasource:

url: jdbc:postgresql://localhost:5432/postgres

username: postgres

flyway:

enabled: false

jpa:

properties:

javax:

persistence:

schema-generation:

create-source: metadata

scripts:

action: create

create-target: src/main/resources/ddl_jpa_creation.sql

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:

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

Have fun using Flyway together with Hibernate and Spring Boot,

Phil

Best Practices for Flyway and Hibernate with Spring Boot

Use Flyway to apply the database schema with Spring Boot

Validate the database schema on application startup with Hibernate

Use repeatable migrations for views or functions/stored procedures

Write Flyway migrations with Java if needed

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

Testing Spring Boot Applications Masterclass Testimonials

Become a More Productive and Confident Spring Boot Developer

Quicklinks

Recently Published