Home » Enterprise Java » spring » Boot » Spring Boot Database Migrations with Flyway

About Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

Spring Boot Database Migrations with Flyway

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

FeatureDetails
valLocal variables are declared as final
varMutable local variables
@Slf4JCreates an SLF4J logger
@CleanupWill call close() on the resource in the finally block
@GetterCreates getter methods for all properties
@SetterCreates 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

1
2
3
4
5
6
7
# 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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
<?xml version="1.0" encoding="UTF-8"?>
    <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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
### 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
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
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

1
2
3
4
5
6
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

1
2
3
4
5
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

1
2
3
4
5
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

1
2
3
4
5
6
7
8
9
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

1
2
3
4
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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
{
    "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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
[
    {
        "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

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments