Integrating Flyway With Spring Boot

Overview

This tutorial helps you configure Flyway as the database migration tool in your Spring Boot project. Spring boot has extensive support for Flyway migration so you should not have any problem adding/configuring this dependency.

Why use Flyway for Spring Boot Data Migration

It is essential to keep track of database changes, especially in large projects with many changes over a long time. Flyway (or any database migration tool) helps you keep track of changes. Think of it as git for databases.

Configure Flyway in Spring Boot App

Adding Flyway to your Spring boot application is simple. The only dependency you need is flyway-core:

<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-core</artifactId>
		</dependency>

Let's quickly create a MariaDB/MySQL instance using docker and a starter database named testdb:

docker run --rm -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=testdb -p 3306:3306 mysql:10.3

Let's open a connection to the database to verify that we are able to connect to it. I'm using DBeaver here but you can use any tool you prefer:

By default, all migration scripts are placed in this directory: Let's add a database connection in the application.properties file:

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop

If I start the app now, the database is still blank because there isn't any migration yet: Let's get started by creating a simple SQL script that creates a table to store users:

CREATE TABLE users ( 
   id INT NOT NULL, 
   name VARCHAR(50) NOT NULL
);

A Quick note about the FlywayDB naming convention

The file name starts with one letter prefix (uppercase), followed by a version number. After that, two underscores are used as a separator. Then, you need to give the migration file a name stating its action. Finally, the file ends with .SQL as its extension.

For example: V1__Create_user_table.sql

Start the app to see FlywayDB migration in action

Now let's start the app again, you should see the table is created, alongside another table that FlywayDB created for you.

Very important note for MySQL/MariaDB users

I had trouble integrating Flyway with MariaDB/MySQL with error messages like this:

Caused by: org.flywaydb.core.api.FlywayException: Unsupported Database: MariaDB 10.9
	at org.flywaydb.core.internal.database.DatabaseTypeRegister.getDatabaseTypeForConnection(DatabaseTypeRegister.java:106) ~[flyway-core-8.5.13.jar:na]
	at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:76) ~[flyway-core-8.5.13.jar:na]
	at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:147) ~[flyway-core-8.5.13.jar:na]
	at org.flywaydb.core.Flyway.migrate(Flyway.java:124) ~[flyway-core-8.5.13.jar:na]
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-2.7.4.jar:2.7.4]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.23.jar:5.3.23]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.23.jar:5.3.23]
	... 18 common frames omitted

Basically, it's either:

• Unsupported Database: MariaDB 10.x (10.3, 10.4, 10.5...)
• Unsupported Database: MySQL 5.5 (8.0)...

It's not a problem with your database version. Whether you configure a connection to MariaDB or MySQL, you treat them all as MySQL (yes, you use MySQL connector/configuration for MariaDB). In pom.xml, you must have the following dependencies:

<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-mysql</artifactId>
		</dependency>

In application.properties, make sure your connection config look like this:

spring.datasource.url=jdbc:mysql://localhost:3306/testdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Use this for both MySQL and MariaDB databases. Funny, isn't it? The integration for PostgreSQL is simpler. You just need to get the right driver dependencies and connection string.

Changing/Evolving database schema

Let's say you want to add a column to store email addresses to the table above, what do you do? When I first learn about Flyway, my temptation is to edit the file and add one more column:

CREATE TABLE users ( 
   id INT NOT NULL, 
   name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL
);

However, if I do this, there is no way Flyway knows how to track the changes. In fact, if I edit the file like this and try to start the app, I will get an error:

Migration checksum mismatch for migration version 1
-> Applied to database : -655193423
-> Resolved locally    : -1075142686
Either revert the changes to the migration, or run repair to update the schema history.

The error message is quite clear. Once the migration is run, you are not supposed to edit the file. If you want to make changes to your database's schema, create a new file and add the SQL commands there. Let's create another file to add the email column. Now, name the file V2__Add_email_column_to_users_table.sql

ALTER TABLE users ADD COLUMN email VARCHAR(50) NOT NULL;

Let's run the app now and sure enough, the email column is added to the table:

Conclusion

In this post, I've shown you how to integrate Flyway with Spring boot. Though there are some difficulties in configuring for MariaDB, FlywayDB is a great tool to keep track of your database's schema changes. As usual, the code for this post is available on GitHub