How to Use Flyway with Spring Boot For Database Migrations

Flyway is one of the best open-source database migration tools. Flyway supports writing migrations in SQL and Java (for advanced data transformations or dealing with LOB). Here we are discussing how we can use flyway in the spring boot application and what are the best practices we could follow in such a setup.

How Flyway Works

Managing database scripts could be a major issue while developing an application with a team. Since all the developers have privilege to change, add, delete any kind of structure inside the database within the developments they done.

So what if we have a method to manage versioning with the database scripts? That’s where we can use flyway.

Using flyway we should create a versioned database migration script set, which getting evolved and centrally shared. Hence any change that we do to our system database is getting notified because everyone inside the team is using the same script set. This is just like using Git or SVN to version source codes.

So in the flyway, we have versioned migration scripts (if we are using SQL-based migrations), so when we deploy the application, the flyway automatically detects if there is any new version to the database with comparing its own history. If it has any update, it will automatically be added to the database.

Hence we don’t need to buzz to our colleague to get newer scripts that should run inside the database before deploying our next release.

Supported Databases For Flyway

There are more than 10 databases that support for flyway to do database migrations.

• Oracle (incl. Amazon RDS)
• MySQL (incl. Amazon RDS, Azure Database & Google Cloud SQL)
• Aurora MySQL
• MariaDB (incl. Amazon RDS)
• Percona XtraDB Cluster
• TestContainers
• SQL Server (incl. Amazon RDS & Azure SQL Database)
• Azure Synapse (Formerly Data Warehouse)
• PostgreSQL (incl. Amazon RDS, Azure Database, Google Cloud SQL & Heroku)
• Aurora PostgreSQL
• Redshift
• CockroachDB
• DB2
• SAP HANA
• Sybase ASE
• Informix
• HSQLDB
• H2
• Derby
• Snowflake
• SQLite
• Firebird

This tutorial is developed using Spring Boot, If you are really new to Spring Boot, Please follow our article on How to Create a Spring Boot Project.

We are going to use MySql as a database for this tutorial and we will demonstrate how we can use flyway for database migration with spring boot.

Main topics we are going to discuss here,

• How Flyway Works
• Adding Required Dependencies
• Configure Database Properties
• Defining Flyway Migration Scripts
• Starting the app with migrations
• Define Gradle Task To Generate Flyway Scripts
• Stop Running Flyway Migrations
• Conclusion

Adding Required Dependencies

If you’re using Gradle, then add the following dependencies to your gradle.properties.

implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

or else if you’re using maven use add the following dependencies into the pom.xml.

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-core</artifactId>
    </dependency>

Configure Database Properties

Here as I mentioned above I’m going to use the MySql database as the datastore. Hence I need to add the required configurations into the spring boot application in order to connect the application and the MySql database.

Add the following properties with adding the correct host, username, and password to your database connection.

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/flyway_demo_project
spring.datasource.username=root
spring.datasource.password=password

Now we are ready to run this application. But there is one thing that should be present on the codebase. That’s ‘db/migration’ folder which required to start the flyway. So just add a folder with db and then migrate inside db to the src/main/resources. Then start your application.

If you’ve configured the database properties and correctly run the application, Then there should be an automatically created table called ‘flyway_schema_history‘ inside your database. That’s the table that flyway used to keep tack on flyway migrations.

Defining Flyway Migration Scripts

There are a few standards that we need to follow while creating migration scripts for the flyway.

First, let’s see how we should name the script. When we are writing a new migration script we should follow the below pattern to name those scripts.

__.sql

Eg:- V1.0.0__create_user_table.sql

Here the prefix is V, the version is 1.0.0 and the description is create_user_table. The version must be unique. The description is purely informative for you to be able to remember what each migration does.

Keep in mind there are two underscores between version and description in flyway scripts.

Prefix

A prefix is used to define the type of migration between Versioned, Undo, and Repeatable migrations.

Versioned Migration – V

This is the most common migration which we could have with flyway. a versioned migration only runs for once and flyway keeps the track of when this versioned migration is applied.

Versioned migrations are normally used to

• Creating/altering/dropping tables/indexes/foreign keys/enums/UDTs
• Reference data updates
• User data corrections

Add the following file into the database migration naming it as ‘V1.0.0__create_user_table.sql’.

CREATE TABLE user (
    id       varchar(36) NOT NULL,
    user_name varchar(255) DEFAULT NULL,
    password varchar(255) DEFAULT NULL,
    PRIMARY KEY (ID)
);

Undo Migration – U

Undo migration is the opposite of the Versioned migration. Here we can undo any change that we have done to the database using a undo script. But normally this can be done using Versioned script as well and this type of migrations are not detecting with spring boot.

Repeatable Migrations – R

Repeatable migrations are the next most important migration type. This type of migration doesn’t have any version since this type of migration is running repeatedly when starting the application.

R Scripts are commonly used for the following scenarios.

• (Re-)creating views/procedures/functions/packages/…

• Bulk reference data reinserts.

Add the following file into the database migration naming it as R__create_user_view.sql

CREATE OR REPLACE VIEW registered_users ASSELECT id, user_nameFROM user;

Version

Version is only should be present for versioned migration and Undo migration. The main thing you need to remember is version can’t be repeated since flyway stores the versions that effected and if there is any repeated version coming with a script, application will not be started saying,

nested exception is org.flywaydb.core.api.FlywayException: Found more than one migration with version

We can get anything like below for a version,

• 1
• 001
• 5.2
• 1.2.3.4.5.6.7.8.9
• 205.68
• 20130115113556
• 2013.1.15.11.35.56
• 2013.01.15.11.35.56

Description

Basically description is used to show what is the use of the migration script. hence description could include underscores or spaces separate the words.

The file name consists of the following parts:

• Prefix: V for versioned (configurable), U for undo (configurable), and R for repeatable migrations (configurable)
• Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)
• Separator: __ (two underscores) (configurable)
• Description: Underscores or spaces separate the words
• Suffix: .sql (configurable)

Starting the app with migrations

Now can create few migration files and start the application. If things are correctly named and configured flyway should capture both V and R scripts and apply those into our database as follows.

Define Gradle Task To Generate Flyway Scripts

Using the following Gradle task you can create a new flyway script file inside your application with the default prefix as V and the version will be generated to unique value using LocalDateTime. All you have to do is rename it with any prefix and insert the script body.

import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

task flywayNewTemplate {
    description 'Creates new sql migration template.'
    doLast {
        def version = '1.0'
        def marker = LocalDateTime.now().format(DateTimeFormatter.ofPattern('yyyyMMddHHmmss'))
        String fileName = "V${version}.${marker}__RENAME_ME.sql"

        def flywayLocation = "${sourceSets.main.resources.sourceDirectories.first().path}/db/migration"
        new File("${flywayLocation}/${fileName}").createNewFile()

        println "Created a new migration file: ${fileName}"
    }
}

Then you can create new flyway script using following command.

$ ./gradlew flywayNewTemplate

Stop Running Flyway Migrations

If there any circumstances where to stop expecting flyway while starting spring boot, you can easily do that using spring boot properties.

Just add following into your application.properties

spring.flyway.enabled=false

Conclusion

In this article, we’ve discussed Database Migration Using Flyway in Spring Boot with how we can create a flyway migration file, where we should store those, best practices, and easy way to generate unique flyway scripts using Gradle.

You can find source codes for this tutorial from our Github.