Spring Boot Database Migrations with Flyway

YatinDecember 1st, 2020Last Updated: November 30th, 2020

0 646 11 minutes read

Welcome, in this tutorial, we will see how to implement a database migration in a spring boot application using Flyway.

1. Introduction

Before going further in this tutorial, we will look at the common terminology such as introduction to Spring Boot, Lombok, and Flyway.

1.1 Spring Boot

Spring boot is a module that provides rapid application development feature to the spring framework including auto-configuration, standalone-code, and production-ready code
It creates applications that are packaged as jar and are directly started using embedded servlet container (such as Tomcat, Jetty or, Undertow). Thus, no need to deploy the war files
It simplifies the maven configuration by providing the starter template and helps to resolve the dependency conflicts. It automatically identifies the required dependencies and imports them into the application
It helps in removing the boilerplate code, extra annotations, and XML configurations
It provides powerful batch processing and manages the rest endpoints
It provides an efficient JPA-starter library to effectively connect the application with the relational databases
It offers a Microservice architecture and cloud configuration that manages all the application related configuration properties in a centralized manner

1.2 Lombok

Lombok is nothing but a small library which reduces the amount of boilerplate Java code from the project
Automatically generates the getters and setters for the object by using the Lombok annotations
Hooks in via the Annotation processor API
Raw source code is passed to Lombok for code generation before the Java Compiler continues. Thus, produces properly compiled Java code in conjunction with the Java Compiler
Under the target/classes folder you can view the compiled class files
Can be used with Maven, Gradle IDE, etc.

1.2.1 Lombok features

Feature	Details
`val`	Local variables are declared as `final`
`var`	Mutable local variables
`@Slf4J`	Creates an SLF4J logger
`@Cleanup`	Will call `close()` on the resource in the `finally` block
`@Getter`	Creates getter methods for all properties
`@Setter`	Creates setter for all non-final properties
`@EqualsAndHashCode`	Generates implementations of `equals(Object other)` and `hashCode()` By default will use all non-static, non-transient properties Can optionally exclude specific properties
`@ToString`	Generates String of class name, and each field separated by commas Optional parameter to include field names Optional parameter to include a call to the super `toString` method
`@NoArgsConstructor`	Generates no-args constructor Will cause compiler error if there are final fields Can optionally force, which will initialize final fields with 0/false/null var – mutable local variables
`@RequiredArgsContructor`	Generates a constructor for all fields that are `final` or marked `@NonNull` The constructor will throw a `NullPointerException` if any `@NonNull` fields are null val – local variables are declared `final`
`@AllArgsConstructor`	Generates a constructor for all properties of the class Any `@NotNull` properties will have null checks
`@Data`	Generates typical boilerplate code for POJOs Combines – `@Getter`, `@Setter`, `@ToString`, `@EqualsAndHashCode`, `@RequiredArgsConstructor` No constructor is generated if constructors have been explicitly declared
`@Builder`	Implements the Builder pattern for object creation
`@Value`	The immutable variant of `@Data` All fields are made `private` and `final` by default

1.3 Flyway

Flyway is a database migration and version control tool. The migration is applied in the version number order specified in the migration file
The database migration in flyway is not dependent on @Entity annotation. You will have to manually write the migration scripts in SQL or Java. However, SQL is the preferred choice
The database migrations are read from the classpath:db/migration folder by default. However, this can be modified by setting the spring.flyway.locations property in the application.properties file
The migration scripts follow a standard nomenclature i.e. V<VERSION_NUMBER>__<DESCRIPTION>.sql
At each application run, only the pending migrations are applied. Flyway manages this via a metadata table (the name of the metadata table is set through the spring.flyway.table property in the application.properties file). This table is automatically created (if not exists) and updated with the information during each run
Migration scripts cannot be changed once applied as flyway compares the checksum of each script in every run and throws an exception on application startup if there is a mismatch
Spring boot auto enables and triggers flyway at the application startup when the flyway core library is included in the project. However, this can be turned off by setting the spring.flyway.enabled property to false in the application.properties file

Let us go ahead with the tutorial implementation but before going any further I’m assuming that you’re aware of the Spring boot basics.

2. Spring Boot Database Migrations with Flyway

2.1 Application Pre-requisite

To start with this tutorial, we are hoping that readers at present have the PostgreSQL up and running on their local environment. For easy setup, the reader can have the PostgreSQL up and running on the Docker environment. Readers can execute the below commands to get the PostgreSQL database container running on Docker in minutes.

Docker Commands

# docker command to pull the latest postgresql image and run on the given port (i.e. 5433)
# POSTGRES_PASSWORD variable represents the PostgreSQL password
 
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD=<your_password> --name postgres postgres
 
# docker command to check if the container is started successfully or not
docker ps -a

If everything goes well the container would be started successfully as shown in Fig. 1.

Spring Boot Database flyway - PostgreSQL Database — Fig.1: PostgreSQL Database on Docker

2.2 Tools Used for Spring boot application and Project Structure

We are using Eclipse Kepler SR2, JDK 8, and Maven. In case you’re confused about where you should create the corresponding files or folder, let us review the project structure of the spring boot application.

Spring Boot Database flyway - Project structure — Fig. 2: Project structure

Let us start building the application!

3. Creating a Spring Boot application

Below are the steps involved in developing the application.

3.1 Maven Dependency

Here, we specify the dependency for the Spring Boot (Web, JPA, and Actuator), H2 database, Flyway core, Lombok, and PostgreSQL. Maven will automatically resolve the other dependencies. The updated file will have the following code.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.0</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
 
    <groupId>com.springboot.dbmigrate.flyway</groupId>
    <artifactId>Springbootflyway</artifactId>
    <version>0.0.1-SNAPSHOT</version>
 
    <name>Springbootflyway</name>
    <description>Demo project for database migration using flyway in springboot</description>
 
    <properties>
        <java.version>1.8</java.version>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- spring boot actuator dependency to enable the "/actuator/flyway" endpoint -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- flyway database migration dependency -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <!-- postgresql dependency -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- lombok dependency -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <!-- to make spring boot as a fat jar so that all required jar files and 
                main file is added for running the code from docker. -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
</project>

3.2 Application Properties

Create a new properties file at the location: Springbootflyway/src/main/resources/ and add the following code to it. Here we will define the application configuration, postgresql database settings, flyway settings, and actuator configuration.

application.properties

### application configuration
server.port=9091
spring.application.name=springboot-flyway-db-migration
 
### postgresql database settings
# enter the db username
spring.datasource.username=
# enter the db password
spring.datasource.password=
# enter the db url
spring.datasource.url=jdbc:postgresql://localhost:5433/exercises
spring.datasource.driver-class-name=org.postgresql.Driver
# db-related settings
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=false
 
### flyway settings
# name of the schema history table
spring.flyway.table=flyway_schema_history
# each script runs within a single transaction. To run all pending scripts in a single transaction
# uncomment the below property. the default value of this property is false
# spring.flyway.group=true
 
### management configuration
# actuator endpoint port number
management.server.port=9095
# exposing health, info, and flyway endpoints
# url - http://localhost:9095/actuator/flyway
management.endpoints.web.exposure.include=info,health,flyway

3.3 Java Classes

Let us write all the java class(es) involved in this application. Remember as this tutorial is purely is only on the database migration so we will just create the main class (required for application startup). The other java classes in this project are created so that you can confirm that migration is successfully executed and the data is persisted in the PostgreSQL database.

3.3.1 Implementation/Main class

Add the following code to the main class to bootstrap the application from the main method. Always remember, the entry point of the spring boot application is the class containing @SpringBootApplication annotation and the static main method.

SpringbootflywayApplication.java

package com.springboot.dbmigrate.flyway;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
import lombok.extern.slf4j.Slf4j;
 
@Slf4j
@SpringBootApplication
public class SpringbootflywayApplication {
     
    public static void main(String[] args) {
        SpringApplication.run(SpringbootflywayApplication.class, args);
        log.info("Springboot flyway application is started successfully.");
    }
     
}

3.4 Database Classes

Let us write all the database migration SQL scripts (under Springbootflyway/src/main/resources/db/migration location) involved in this application. These migration scripts will be executed during the application startup. Each migration script is run within a single transaction. However, you can configure all migrations in a single script by setting the spring.flyway.group=true property in the application.properties file. The information for these migration scripts can be read from the actuator endpoint or the flyway metadata table.

3.4.1 V1.0__create_table.sql

Add the following code to the 1.0 version script where we will create the table structure under the exercises database.

V1.0__create_table.sql

CREATE TABLE drug (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    brand VARCHAR(100) NOT NULL,
    company VARCHAR(100)
);

3.4.2 V1.1__insert_table.sql

Add the following code to the 1.1 version script where we will insert the data to the table.

V1.1__insert_table.sql

insert into drug (name, brand, company) values ('Danazol', 'Danazol', 'Lannett Company, Inc.');
insert into drug (name, brand, company) values ('caffeine citra', 'caffeine citrate', 'Sagent Pharmaceuticals');
insert into drug (name, brand, company) values ('Escitalopram', 'Escitalopram', 'REMEDYREPACK INC.');
insert into drug (name, brand, company) values ('RABBIT', 'RABBIT', 'ALK-Abello, Inc.');
insert into drug (name, brand, company) values ('ZINC ACETATE A', 'Zicam Cold Remedy Ultra Rapidmelts', 'Matrixx Initiatives, Inc.');

3.4.3 V1.2__alter_table.sql

Add the following code to the 1.2 version script where we will alter the table to add a new boolean column and set its default value to false.

V1.2__alter_table.sql

1	`ALTER` `TABLE` `drug` `ADD` `COLUMN` `is_generic_drug boolean` `default` `false;`

3.4.4 V1.3__insert2_table.sql

Add the following code to the 1.3 version script where we will insert some more data to the table.

V1.3__insert2_table.sql

insert into drug (name, brand, company, is_generic_drug) values ('Naproxen', 'Naproxen', 'PD-Rx Pharmaceuticals, Inc.', true);
insert into drug (name, brand, company, is_generic_drug) values ('Sodium Fluorid', 'Ludent', 'Sancilio & Company Inc', true);
insert into drug (name, brand, company, is_generic_drug) values ('Sodium Fluorid', 'PreviDent', 'Colgate Oral Pharmaceuticals, Inc.', true);
insert into drug (name, brand, company, is_generic_drug) values ('Entacapone', 'Entacapone', 'Sandoz Inc', true);
insert into drug (name, brand, company, is_generic_drug) values ('Promethazine H', 'Promethazine Hydrochloride and Dextromethorphan Hydrobromide', 'Rebel Distributors Corp', true);

4. Run the Application

To execute the application, right-click on the SpringbootflywayApplication.java class, Run As -> Java Application.

Spring Boot Database flyway - run — Fig. 3: Run the Application

5. Project Demo

When the application is executed, flyway will automatically check the current database version and apply any pending migrations. During the fresh run, all migrations present under the classpath:db/migration folder will be executed and you will see the following logs about flyway which says that it has migrated the schema.

Fresh run

2020-11-24 16:35:10.121  INFO 18148 --- [           main] o.f.c.i.database.base.DatabaseType       : Database: jdbc:postgresql://localhost:5433/exercises (PostgreSQL 12.2)
2020-11-24 16:35:10.193  INFO 18148 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 4 migrations (execution time 00:00.032s)
2020-11-24 16:35:10.210  INFO 18148 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "public"."flyway_schema_history" ...
2020-11-24 16:35:10.273  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2020-11-24 16:35:10.279  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version "1.0 - create table"
2020-11-24 16:35:10.323  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version "1.1 - insert table"
2020-11-24 16:35:10.358  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version "1.2 - alter table"
2020-11-24 16:35:10.392  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version "1.3 - insert2 table"
2020-11-24 16:35:10.434  INFO 18148 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 4 migrations to schema "public" (execution time 00:00.173s)

During the application rerun, it will first refer to the flyway metadata table to determine which migration scripts have been successfully executed and which new needs to be executed. If any new migration script is found in the classpath:db/migration folder only that will be executed else no migration will be executed and the following logs about flyway will be shown in the console.

Application rerun

2020-11-24 16:36:53.631  INFO 1852 --- [           main] o.f.c.i.database.base.DatabaseType       : Database: jdbc:postgresql://localhost:5433/exercises (PostgreSQL 12.2)
2020-11-24 16:36:53.707  INFO 1852 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 4 migrations (execution time 00:00.037s)
2020-11-24 16:36:53.729  INFO 1852 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": 1.3
2020-11-24 16:36:53.731  INFO 1852 --- [           main] o.f.core.internal.command.DbMigrate      : Schema "public" is up to date. No migration necessary.

6. Query migration and status

You can check the migration status and history in the web interface with the spring boot actuator endpoint by enabling the management.endpoints.web.exposure.include property in the application.properties file. The flyway migration status will be available at the following URL – {{protocol}}://{{baseUrl}}:{{portNumber}}/actuator/flyway. For this tutorial, you can access the URL at – http://localhost:9095/actuator/flyway.

{
    "contexts": {
        "springboot-flyway-db-migration": {
            "flywayBeans": {
                "flyway": {
                    "migrations": [
                        {
                            "type": "SQL",
                            "checksum": 976981303,
                            "version": "1.0",
                            "description": "create table",
                            "script": "V1.0__create_table.sql",
                            "state": "SUCCESS",
                            "installedBy": "postgres",
                            "installedOn": "2020-11-24T11:05:10.275Z",
                            "installedRank": 1,
                            "executionTime": 16
                        },
                        {
                            "type": "SQL",
                            "checksum": -1708858115,
                            "version": "1.1",
                            "description": "insert table",
                            "script": "V1.1__insert_table.sql",
                            "state": "SUCCESS",
                            "installedBy": "postgres",
                            "installedOn": "2020-11-24T11:05:10.319Z",
                            "installedRank": 2,
                            "executionTime": 13
                        },
                        {
                            "type": "SQL",
                            "checksum": 31710236,
                            "version": "1.2",
                            "description": "alter table",
                            "script": "V1.2__alter_table.sql",
                            "state": "SUCCESS",
                            "installedBy": "postgres",
                            "installedOn": "2020-11-24T11:05:10.355Z",
                            "installedRank": 3,
                            "executionTime": 8
                        },
                        {
                            "type": "SQL",
                            "checksum": 819672188,
                            "version": "1.3",
                            "description": "insert2 table",
                            "script": "V1.3__insert2_table.sql",
                            "state": "SUCCESS",
                            "installedBy": "postgres",
                            "installedOn": "2020-11-24T11:05:10.388Z",
                            "installedRank": 4,
                            "executionTime": 14
                        }
                    ]
                }
            },
            "parentId": null
        }
    }
}

Apart from this, you can also query the flyway metadata table (named flyway_schema_history) to fetch the migration script details. The flyway metadata table will be automatically created to manage migration status and history.

Spring Boot Database flyway - metadata table — Fig. 4: Flyway metadata table

For fun, you can also browse the following URL – http://localhost:9091/api/getall to fetch the information about the data stored in the table (named drug) with the help of SQL migration scripts.

[
    {
        "id": 1,
        "name": "Danazol",
        "brand": "Danazol",
        "company": "Lannett Company, Inc.",
        "genericDrug": false
    },
    {
        "id": 2,
        "name": "caffeine citra",
        "brand": "caffeine citrate",
        "company": "Sagent Pharmaceuticals",
        "genericDrug": false
    },
    {
        "id": 3,
        "name": "Escitalopram",
        "brand": "Escitalopram",
        "company": "REMEDYREPACK INC.",
        "genericDrug": false
    },
    {
        "id": 4,
        "name": "RABBIT",
        "brand": "RABBIT",
        "company": "ALK-Abello, Inc.",
        "genericDrug": false
    },
    {
        "id": 5,
        "name": "ZINC ACETATE A",
        "brand": "Zicam Cold Remedy Ultra Rapidmelts",
        "company": "Matrixx Initiatives, Inc.",
        "genericDrug": false
    },
    {
        "id": 6,
        "name": "Naproxen",
        "brand": "Naproxen",
        "company": "PD-Rx Pharmaceuticals, Inc.",
        "genericDrug": true
    },
    {
        "id": 7,
        "name": "Sodium Fluorid",
        "brand": "Ludent",
        "company": "Sancilio & Company Inc",
        "genericDrug": true
    },
    {
        "id": 8,
        "name": "Sodium Fluorid",
        "brand": "PreviDent",
        "company": "Colgate Oral Pharmaceuticals, Inc.",
        "genericDrug": true
    },
    {
        "id": 9,
        "name": "Entacapone",
        "brand": "Entacapone",
        "company": "Sandoz Inc",
        "genericDrug": true
    },
    {
        "id": 10,
        "name": "Promethazine H",
        "brand": "Promethazine Hydrochloride and Dextromethorphan Hydrobromide",
        "company": "Rebel Distributors Corp",
        "genericDrug": true
    }
]

That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!

7. Summary

In this section, you learned,

Spring boot, Lombok, and Flyway introduction
Steps to implement the database migrations with Flyway in a spring boot application

You can download the sample application as an Eclipse project in the Downloads section.

8. Download the Project

This was an example of database migrations with flyway in a spring boot application.

Download
You can download the full source code of this example here: Spring Boot Database Migrations with Flyway

Spring Boot Database Migrations with Flyway

1. Introduction

1.1 Spring Boot

1.2 Lombok

1.2.1 Lombok features

1.3 Flyway

2. Spring Boot Database Migrations with Flyway

2.1 Application Pre-requisite

2.2 Tools Used for Spring boot application and Project Structure

3. Creating a Spring Boot application

3.1 Maven Dependency

3.2 Application Properties

3.3 Java Classes

3.3.1 Implementation/Main class

3.4 Database Classes

3.4.1 V1.0__create_table.sql

3.4.2 V1.1__insert_table.sql

3.4.3 V1.2__alter_table.sql

3.4.4 V1.3__insert2_table.sql

4. Run the Application

5. Project Demo

6. Query migration and status

7. Summary

8. Download the Project

Thank you!

Yatin

Thank you!

1. Introduction

1.1 Spring Boot

1.2 Lombok

1.2.1 Lombok features

1.3 Flyway

2. Spring Boot Database Migrations with Flyway

2.1 Application Pre-requisite

2.2 Tools Used for Spring boot application and Project Structure

3. Creating a Spring Boot application

3.1 Maven Dependency

3.2 Application Properties

3.3 Java Classes

3.3.1 Implementation/Main class

3.4 Database Classes

3.4.1 V1.0__create_table.sql

3.4.2 V1.1__insert_table.sql

3.4.3 V1.2__alter_table.sql

3.4.4 V1.3__insert2_table.sql

4. Run the Application

5. Project Demo

6. Query migration and status

7. Summary

8. Download the Project

Thank you!

Related Articles

Thank you!