Spring Boot Database Migrations with Flyway

Rajeev SinghSpring Boot3 mins read

Spring Boot Database Migrations with Flyway

Nothing is perfect and complete when it is built the first time. The database schema of your brand new application is no exception. It is bound to change over time when you try to fit new requirements or add new features.

Flyway is a tool that lets you version control incremental changes to your database so that you can migrate it to a new version easily and confidently.

In this article, you’ll learn how to use Flyway in Spring Boot applications to manage changes to your database.

We’ll build a simple Spring Boot application with MySQL Database & Spring Data JPA, and learn how to integrate Flyway in the app.

Let’s get started!

Creating the Application

Let’s use Spring Boot CLI to generate the application. Fire up your terminal and type the following command to generate the project. 

spring init --name=flyway-demo --dependencies=web,mysql,data-jpa,validation,flyway flyway-demo

Once the project is generated, import it into your favorite IDE. The directory structure of the application would look like this -

Spring Boot Flyway Database Migration Example Directory Structure"

Configuring MySQL and Hibernate

First create a new MySQL database named flyway_demo.

Then Open src/main/application.properties file and add the following properties to it -

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/flyway_demo?useSSL=false
spring.datasource.username = root
spring.datasource.password = root

## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

## This is important
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = validate

Please change spring.datasource.username and spring.datasource.password as per your MySQL installation.

The property spring.jpa.hibernate.ddl-auto is important. It tries to validate the database schema according to the entities that you have created in the application and throws an error if the schema doesn’t match the entity specifications.

Creating a Domain Entity

Let’s create a simple Entity in our application so that we can create and test flyway migration for this entity.

First, Create a new package called domain inside com.example.flywaydemo package. Then, create the following User.java file inside com.example.flywaydemo.domain package -

package com.example.flywaydemo.domain;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Column(unique = true)
    @Size(min = 1, max = 100)
    private String username;

    @NotBlank
    @Size(max = 50)
    private String firstName;

    @Size(max = 50)
    private String lastName;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

Creating a Flyway Migration script

Flyway tries to read database migration scripts from classpath:db/migration folder by default.

All the migration scripts must follow a particular naming convention - V<VERSION_NUMBER>__<NAME>.sql. Checkout the Official Flyway documentation to learn more about naming convention.

Let’s create our very first database migration script. First, Create the db/migration folder inside src/main/resources directory - 

mkdir -p src/main/resources/db/migration

Now, create a new file named V1__init.sql inside src/main/resources/db/migration directory and add the following sql scripts -

CREATE TABLE users (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  username varchar(100) NOT NULL,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UK_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Running the Application

When you run the application, flyway will automatically check the current database version and apply any pending migrations.

Run the app by typing the following command in terminal -

mvn spring-boot:run

When running the app for the first time, you’ll see the following logs pertaining to Flyway, which says that it has migrated the schema to version 1 - init.

Flyway Migration Logs

How does Flyway manage migrations?

Flyway creates a table called flyway_schema_history when it runs the migration for the first time and stores all the meta-data required for versioning the migrations in this table.

mysql> show tables;
+-----------------------+
| Tables_in_flyway_demo |
+-----------------------+
| flyway_schema_history |
| users                 |
+-----------------------+
2 rows in set (0.00 sec)

You can check the contents of flyway_schema_history table in your mysql database -

mysql> select * from flyway_schema_history;
+----------------+---------+-------------+------+--------------+------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script       | checksum   | installed_by | installed_on        | execution_time | success |
+----------------+---------+-------------+------+--------------+------------+--------------+---------------------+----------------+---------+
|              1 | 1       | init        | SQL  | V1__init.sql | 1952043475 | root         | 2018-03-06 11:25:58 |             16 |       1 |
+----------------+---------+-------------+------+--------------+------------+--------------+---------------------+----------------+---------+
1 row in set (0.00 sec)

It stores the current migration’s version, script file name, and checksum among other details in the table.

When you run the app, Flyway first validates the already applied migration scripts by calculating their checksum and matching it with the checksum stored in the meta-data table.

So, If you change V1__init.sql after the migration is applied, Flyway will throw an error saying that the checksum doesn’t match.

Therefore, for doing any changes to the schema, you need to create another migration script file with a new version V2__<NAME>.sql and let Flyway apply that when you run the app.

Adding multiple migrations

Let’s create another migration script and see how flyway migrates the database to the new version automatically.

Create a new script V2__testdata.sql inside src/main/resources/db/migration with the following contents -

INSERT INTO users(username, first_name, last_name) VALUES('callicoder', 'Rajeev', 'Singh');
INSERT INTO users(username, first_name, last_name) VALUES('flywaytest', 'Flyway', 'Test');

If you run the app now, flyway will detect the new migration script, and migrate the database to this version.

Open mysql and check the users table. You’ll see that the above two entries are automatically created in the users table -

mysql> select * from users;
+----+------------+-----------+------------+
| id | first_name | last_name | username   |
+----+------------+-----------+------------+
|  4 | Rajeev     | Singh     | callicoder |
|  5 | Flyway     | Test      | flywaytest |
+----+------------+-----------+------------+
2 rows in set (0.01 sec)

Also, Flyway stores this new schema version in its meta-data table -

mysql> select * from flyway_schema_history;
+----------------+---------+-------------+------+------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script           | checksum    | installed_by | installed_on        | execution_time | success |
+----------------+---------+-------------+------+------------------+-------------+--------------+---------------------+----------------+---------+
|              1 | 1       | init        | SQL  | V1__init.sql     |  1952043475 | root         | 2018-03-06 11:25:58 |             16 |       1 |
|              2 | 2       | testdata    | SQL  | V2__testdata.sql | -1926058189 | root         | 2018-03-06 11:25:58 |              6 |       1 |
+----------------+---------+-------------+------+------------------+-------------+--------------+---------------------+----------------+---------+
2 rows in set (0.00 sec)

Conclusion

That’s all folks! In this article, You learned how to integrate Flyway in a Spring Boot application for versioning database changes.

You can find the source code for the sample application that we built in this tutorial in my github repository.

Thank you for reading. See you in the next blog post.