Spring Boot + Hibernate + Flyway: don't run migrations on new database

Question

I'm using Flyway to update the DB schema. Currently, the latest version of the schema is 3 (the latest migration file is named V3__postgres.sql).

If I run the application on a database which has an older schema version, Flyway executes the update scripts as expected. However, if I run the app on a new (empty) database, flyway tries to execute the update scripts, but it doesn't find any tables (because Hibernate didn't create them yet), and the app terminates on error.

I would like Flyway to not execute the update scripts on an empty database, since when Hibernate creates the tables, they will be at the latest version anyway.

If I understand it correctly, I should be able to use parameter flyway.baseline-version for this. My theory is that if Flyway doesn't find table schema_version, it should create it and insert a record saying that the DB is at version 3. But even if I set flyway.baseline-version=3, Flyway executes the scripts anyway. I also tried to set parameter flyway.baseline-on-migrate=true and their different combinations but I couldn't get it to work.

Do I understand the baseline-version parameter correctly or am I missing something?

Note: I'm aware that since Spring Boot 2 the parameter namespace has changed to spring.flyway.*, but I'm using Spring Boot 1 so that is not the problem.

Solution 1

Solved: I created a custom FlywayMigrationStrategy bean where I manually check if Flyway has been already introduced to the database (by checking if the migration table exists). If not, I run the baseline command. Then I call the migrate command as usual.

Here is the Spring Boot configuration:

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class FlywayConfig {

    @Autowired
    private DataSource dataSource;

    @Value("${flyway.table}")
    private String flywayTableName;

    @Value("${flyway.baselineVersionAsString}")
    private String baselineVersion;

    @Bean
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {
            if (!isFlywayInitialized()) {
                flyway.setBaselineVersionAsString(baselineVersion);
                flyway.baseline();
            }
            flyway.migrate();
        };
    }

    private boolean isFlywayInitialized() {

        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metadata = connection.getMetaData();
            ResultSet result = metadata.getTables(null, null, flywayTableName, null);
            return result.next();
        } catch (SQLException e) {
            throw new RuntimeException("Failed to check if Flyway is initialized", e);
        }
    }

}

Solution 2

As comments already mentioned, flyway and hibernate should not be used together to update the schema, but this does not mean that you can't use Hibernate at all to help you maintaining your schema.

Baseline: This feature is definitely not designed to prevent migration execution on empty databases. This should be rather used when your database already exists (i.e. it has already tables and data, and you want to keep those data). For empty databases it is useless.

Example: Suppose you have an existing database, generated with 2 scripts:

V1__create_tables.sql
V2__create_constraints.sql

Now you want to manage further schema updates with flyway:

V3__First_update.sql
V4__Second_update.sql

V2 is your baseline, meaning that migration of database will not execute migrations V1 and V2, because they already exist.

If you want to combine Spring Boot + Hibernate + Flyway:

• Disable automatic schema update by hibernate - as already mentioned, this might be dangerous (hbm2ddl.auto=false)
• Use Hibernate SchemaGenerator to generate a big SQL-file, let's call it V1__initial_schema.sql, like in this old-but-still-valid article.
• If you happen to have a database in V1 with data you want to keep, you should baseline it. Otherwise just start from an empty schema. (flyway clean) and migrate (flyway migrate). WARNING: flyway clean will drop all your tables!

Now you are ready to use both Hibernate and Flyway for each upcoming schema modification. Let's suppose you have just updated your model:

• Use Hibernate SchemaGenerator to generate the same big SQL-file, let's call it generator-output.sql.
• Do a side-by-side comparison of V1__initial_schema.sql and generator-output.sql. This will help you identify the differences between the 2 files. Based on these differences, you can generate a new migration file, let's call it V2__update.sql.
• Do a flyway migrate on your database(s).