How To Use Flyway for Database Migration in Spring Boot

In this article, we're going to look at how we can use Flyway to manage our SQL database schema in a Spring Boot application.

Flyway is a database migration tool that offers capabilities for migration history and rollback and allows us to separate the database schema-related layer from the database entity layer of an application.

Application Setup

The Spring Boot application we will be using can be generated using this Spring Initializr link. It contains all the necessary dependencies.

After downloading the application and resolving the dependencies, we will create a new Postgres database called spring-boot-flyway and configure the application to connect to it.

Listing 2.1 application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/spring-boot-flyway
spring.datasource.username=demo
spring.datasource.password=demo

By default, Flyway will search the db/migration/directory in the classpath for migration files containing SQL statements to manage database tables and records.

What's in store for Containers in 2023

DZone's 2023 Containers Trend Report will explore the current state of containers, key trends and advancements in global containerization strategies, and constructive content for modernizing your software architecture.

For older versions of the library, we may need to create an empty text file called .keep in resources/db/migration/ to ensure the directory is compiled and available during application start-up to avoid errors.

Having done that, we can now start the application and it should run successfully.

Basic Usage

The way Flyway works is that we create a migration file in the resources/db/migration directory and Spring Boot will automatically execute the migration script because we've added the Flyway dependencies to the classpath in section 2.

Listing 3.1 V1__Users.sql:

CREATE TABLE IF NOT EXISTS users
(
    id    SERIAL,
    email VARCHAR(200) NOT NULL,
    name  VARCHAR(200) NOT NULL,
    PRIMARY KEY (id)
);

Let's take a minute to examine the snippet in Listing 3.1. The file name, V1__Users.sql, follows a certain convention:

• The "V" indicates that this is a versioned migration.
• The "1" that follows the V is the actual version number. It can also be "V1_1", which will translate to version 1.1.
• This is followed by the separator "__" (two underscores). This separates the version information from the name of the migration file, that is, Users, in this case.
• The last part ".sql" is the extension; thus, the file contains a plain SQL statement.

At this point, restarting the application will create the users table in the database. Furthermore, we can see there's another table that we did not explicitly create - flyway_schema_history.

The flyway_schema_history is used by Flyway itself to track the migration that has been applied. If the table is missing, Flyway will assume we're initializing the database for the first time and run all the migrations in order of their version number.

When the flyway_schema_history table is present, Flyway will only apply newer migration files that have not been applied before. This means that in order for us to add new tables, we only need to create newer migration files with an updated version number and restart the application.

In addition to using SQL, we can also write our migration script in Java. In the Java migration style, our migration files are Java classes and must extend the abstract BaseJavaMigration class and implement the migrate method.

IDEs typically don't expect Java classes to be in the resources directory and so we're going to create a new package called db/migration in src/main/java. It is very important to know that this new package db/migration should be in the src/main/java directory.

Let's create a new Java migration to add a new table:

Listing 3.2 V2__Posts.java:

public class V2__Posts extends BaseJavaMigration {
    @Override
    public void migrate(Context context) throws Exception {
        var sql = """
                CREATE TABLE posts (
                     id SERIAL,
                     author_id INT NOT NULL,
                     post TEXT NOT NULL,
                     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                     PRIMARY KEY (id)
                );
                """;
        try(var statement = context.getConnection().createStatement()) {
            statement.execute(sql);
        }
    }
}

The advantage of using Java migration over SQL files is that we can add custom logic, conditions, and validations that will not be possible with plain SQL. For example, we can check if another table exists or get certain values from the environment, etc.

As you're probably guessing now, yes, it is possible to mix both SQL and Java-style migration in the same codebase, as long as we ensure the Flyway location is the same in both cases.

Flyway Configuration and Customization

Hitherto, we've been working with the default Flyway behavior. We can further tweak Flyway to suit our needs. For example, we can change the default location for migration files, configure the database schema (a.k.a., tablespace), change the SQL migration prefix from "V" to whatever we want and so much more.

In the configuration below, we configure the path where the migration files are located and disable cleaning the database (i.e., dropping all tables) to prevent accidental use in a production environment.

Listing 4.1 application.properties:

spring.flyway.locations=classpath:migrations
spring.flyway.clean-disabled=true

There are other configurable properties under the spring.flyway key that we can use to fine-tune the behavior of the library. Also, we can consult the Flyway documentation page for reference.

Flyway Callbacks

Flyway provides us with the ability to configure callbacks that can be invoked at different stages of the migration process. The callback mechanism is a handy way of performing certain actions at different phases of the migration life cycle. 

Let's say we have some default data that we want to seed when our application is starting up. We can simply create a callback that supports the AFTER_MIGRATE event.

Listing 5.1 FlywayDatabaseSeeder.java:

public class FlywayDatabaseSeeder implements Callback {

    @Override
    public boolean supports(Event event, Context context) {
        return event.name().equals(Event.AFTER_MIGRATE.name());
    }
    
    @Override
    public void handle(Event event, Context context) {
        try(var statement = context.getConnection().createStatement()) {

            var ADMIN_EMAIL = "[email protected]";
            
            var checkQuery = "SELECT id FROM users WHERE email = %s"
                    .formatted(ADMIN_EMAIL);
            
            statement.execute(checkQuery);
            ResultSet resultSet = statement.getResultSet();
            resultSet.last();

            //return if the seeder has already been executed
            if(resultSet.getRow() >= 0) return;

            var sql = """
                    INSERT INTO users (email, name) VALUES
                    ('%s', 'Super Admin')
                    """.formatted(ADMIN_EMAIL);
            
            statement.execute(sql);
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        return true;
    }

    @Override
    public String getCallbackName() {
        return FlywayDatabaseSeeder.class.getName();
    }
}

In the listing above, in the supports method, we declared that this callback should only be executed for the AFTER_MIGRATE event and in the handle method, we outlined the logic for inserting the default super admin user if it does not exist already.

Before this can work, we need to register the callback class with Flyway in SpringBoot. We do this by creating a FlywayMigrationStrategy bean.

Listing 5.2 SpringBootFlywayApplication.java:

@Bean
public FlywayMigrationStrategy flywayMigrationStrategy() {
	return (flywayOld) -> {

		/*
		 Update the existing autoconfigured Flyway
		 bean to include our callback class
		*/
		Flyway flyway = Flyway.configure()
				.configuration(flywayOld.getConfiguration())
				.callbacks(new FlywayDatabaseSeeder())
				.load();

		flyway.migrate();

	};
}

There are other events in the org.flywaydb.core.api.callback.Eventenum that we can configure our Callback classes to support. For example, you can have a callback that'll support the AFTER_MIGRATE_ERROR event and send a Slack notification to alert the engineers.

Tips and Tricks

While developing in your local environment, you can delete the entry for a migration from the flyway_schema_history table.

Next time you start your application, the migration for which you deleted its history will be executed again. This way, you can correct errors or update your schema while still developing on your local machine without dropping the entire database.

Additionally, in SpringBoot, you can control when Flyway should execute the migration script on application start-up. For example, let's say we don't want the migration to be automatically executed on our local environment. We can do the following:

Listing 6.1 SpringBootFlywayApplication.java:

@Bean
public FlywayMigrationStrategy flywayMigrationStrategy(@Value("${spring.profiles.active}") String activeProfile) {
	return (flywayOld) -> {

		/*
		 Update the existing autoconfigured Flyway
		 bean to include our callback class
		*/
		Flyway flyway = Flyway.configure()
				.configuration(flywayOld.getConfiguration())
				.callbacks(new FlywayDatabaseSeeder())
				.load();

		if(!"local".equalsIgnoreCase(activeProfile)) {
			flyway.migrate();
		}

	};
}

Conclusion

One of the advantages of using a database migration tool is that it makes the database schema a part of the application codebase. This makes it easier to track changes to the database over time as there's a central reference point in the application.

The complete source code is available on GitHub, Happy coding.