How To Use Flyway for Database Migration in Spring Boot
In this article, take a look at how to use Flyway to manage a SQL database schema in a Spring Boot application.
Join the DZone community and get the full member experience.
Join For FreeIn 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.
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 = "superadmin@example.com";
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.Event enum 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.
Published at DZone with permission of Seun Matt. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments