Database Migration with Flyway

Database Migration with Flyway


With techniques such as continuous delivery becoming more mainstream, automated database migrations are a baseline capability for many software teams. Flyway is a tool that anyone with basic knowledge of SQL can use to set up and maintain database schemas. Database migration with Flyway is gaining high traction in the industry primarily due to its simplicity and its ability to seamlessly integrates with your application lifecycle. Using Flyway, you can:

  • Create a new database
  • Hold history of migrations
  • Have a reproducible state of the database
  • Help manage changes being applied to numerous database instances

In this post, I will explain how to use Flyway in a Spring Boot application. We will create a minimalistic Spring Boot application that uses Java Persistence API (JPA) to manage user profiles.

Database and User Creation in MySQL

As a best practice, you should provide minimal access to Spring Boot user account, which is the username and password that you use in your Spring Boot application. The SQL script to create two such service accounts and a database is this.

DROP USER IF EXISTS `userprofileadmin`@`%`;
DROP USER IF EXISTS `userprofileuser`@`%`;
CREATE DATABASE IF NOT EXISTS userprofiledbCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS `userprofileadmin`@`%` IDENTIFIED WITH mysql_native_passwordBY 'password';
CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `userprofiledb`.* TO `userprofileadmin`@`%`;
CREATE USER IF NOT EXISTS `userprofileuser`@`%` IDENTIFIED WITH mysql_native_passwordBY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `userprofiledb`.* TO `userprofileuser`@`%`;

The preceding script creates two service accounts userprofileuser and userprofileadmin. It also creates a userprofiledb database. The userprofileadmin account has the additional privilege to update the database and its tables.

Execute the preceding SQL script using a MySQL client. I am using MySQL Workbench to run the script.

MySQL Workbench

The pom.xml File

To use Flyway with MySQL, you need the flyway-mysql dependency. The pom.xml file of the application is this.


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=""
    <relativePath/><!-- lookup parent from repository -->



The Entity Class

I have a minimal UserProfile JPA entity for the purpose of this post. I am using Lombok to generate boilerplate code. If you are new to Lombok, I suggest you go through my Lombok post series.

package guru.springframework.flyway.domain;

import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Table(name = "user_profile")
public class UserProfile{
@GeneratedValue(strategy = GenerationType.AUTO)
     private Long id;
     private String name;
     private String city;

     public UserProfile(String name, String city) {
  = name;

The Repository Interface

I am using the repository programming model of Spring Data JPA. The repository interface is this.

package guru.springframework.flyway.repositories;

import guru.springframework.flyway.domain.UserProfile;
import org.springframework.stereotype.Repository;

public interface UserProfileRepositoryextends CrudRepository<UserProfile,Long> {

The Bootstrap Class

I have created a bootstrap class to insert seed data to the database when the application class. The bootstrap class is this.

package guru.springframework.flyway.bootstrap;

import guru.springframework.flyway.domain.UserProfile;
import guru.springframework.flyway.repositories.UserProfileRepository;
import org.apache.catalina.User;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Configuration;

public class UserProfileLoaderimplements CommandLineRunner{
private final UserProfileRepositoryuserProfileRepository;

public UserProfileLoader(UserProfileRepositoryuserProfileRepository) {
this.userProfileRepository= userProfileRepository;

public void run(String... args) throws Exception {

     UserProfileuserProfileEJ= new UserProfile("Eric Johnson", "Oklahoma City");

     UserProfileuserProfileAS= new UserProfile("Andy Smith", "New York City");

     userProfileRepository.findAll().forEach(user -> {
          System.out.println("Name: " + user.getName());
          System.out.println("City: " + user.getCity());

The Properties File

The properties file will hold the information required to the userprofiledb database in the MySQL instance. In this file, we will also provide the service account credentials.

V<Version No>_ _<Name>.sql

Note the double underscore post Version No;. You can read more about Flyway naming patterns here.


The spring.flyway.user and spring.flyway.password properties instruct Spring Boot to create a separate database connection with the specified credentials to perform migrations.

Migration Script

By default, Spring Boot looks for Flyway migration scripts in resources/db/migration directory. Migration scripts have naming standard and the syntax is:

Note the double underscore postV<Version No;. You can read more about Flyway naming patterns here.

V<Version No>_ _<Name>.sql

migration script standard

The migration script is this.


drop table if exists user_profile;
drop table if exists hibernate_sequence;

create table user_profile (
                      id bigintnot null,
                      name varchar(255),
                      city varchar(255),
primary key (id)
) engine=InnoDB;

create table hibernate_sequence (
) engine=InnoDB;

insert into hibernate_sequencevalues ( 1 );

The preceding Flyway script creates a user_profile table.

Running the Application

Build, package, and run your application.

The application creates the tables in the userprofiledb database and populates the user_profile table with seed data.

user profile table

In the preceding figure, note the flyway_schema_history table. Flyway exclusively uses this table to track the version of each database, recording in it every versioned migration file applied to build that version.

The structure and content of the flyway_schema_history table is something like this.

flyway schema history table

To accommodate evolving database schemas, Flyway:

  • Scans the application classpath (current working directory) and locations for available migration files.
  • Compares migration files against the history table. If a version number in the filename is lower or equal to a version marked as current, it is ignored
  • Marks any remaining migration files as pending migrations. These are sorted based on the version number
  • Executes every migration file with a version number in version order, between the current version and the version specified as a target. If no target version is specified, it executes them all.
  • Updates the metadata table accordingly, as each migration is applied

Note: The flyway_schema_history table is exclusively for use by Flyway. Never edit or update it directly.

Migration Script to Alter Table

Assume that we need to alter the user_profile table to add a gender type. To do so, we will update the entity class with a gender field, like this.
package guru.springframework.flyway.domain;

import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Table(name = "user_profile")
public class UserProfile{
@GeneratedValue(strategy = GenerationType.AUTO)
     private Long id;
     private String name;
     private String city;
     private char gender;

     public UserProfile(String name, String city, char gender) { = name; city;

We will also need to update the UserProfileLoader class to initialize UserProfile objects additionally with genders, like this.

UserProfileuserProfileEJ= new UserProfile("Eric Johnson", "Oklahoma City", 'M');

UserProfileuserProfileAS= new UserProfile("Andy Smith", "New York City",'M');

Next is to create a migration script to alter the user_profile table. We will name the migration script as V2_ _add_gender_to_user_profile.sql

V2_ _add_gender_to_user_profile.sql

alter table user_profileADD gender CHAR;

user profile alter table

On accessing the lyway_schema_history table, you can see the versioning information of the migration script.

Flyway with Spring Boot Java Configuration

Spring Boot provides the FlywayMigrationStrategy functional interface as the strategy to initialize Flyway migration.

You can create custom implementations of this interface as a Spring Bean to override the default migration behavior.

For example, before migration, you might want to clean up the database.

Note: This use case is purely for development purpose and not to be used in production.

The following code shows a Spring configuration class, JavaConfigDbClean that creates a FlywayMigrationStrategy bean to perform a database clean-up before migration.
package guru.springframework.flyway.config;

import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

public class JavaConfigDbClean{

public FlywayMigrationStrategyclean(){
    return flyway -> {

On running the application now, you can check the console log to ensure that Flyway has cleaned the database before running the two migration scripts.

Console Output


An alternative to Flyway that is widely used in the industry is Liquibase. Spring Boot supports both Flyway and Liquibase. However, both tools have additional capabilities available from the command line or build tool plugins. You can use one of them in your system independent of Spring Boot. I have used both and found Liquibase is a larger and more robust product as compared to Flyway. The latter supports SQL and Java only. But Flyway seems to have more popularity. Irrespective, both tools are mature and widely used. In upcoming posts, I plan to cover both in details.

If you want to go in-depth on how to use both the tools, I recommend my Udemy course Hibernate and Spring Data JPA: Beginner to Guru

About SFG Contributor

Staff writer account for Spring Framework Guru

    You May Also Like

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.