Spring Boot – Flyway Database

Flyway is a migration tool that automates the management of the database migration. It is an open-source database migration tool that automates the process of managing the evolving database schemas. It allows the developers to effectively manage and migrate the database schema as it evolves through different phases like design, testing, production etc.

This tool gives us more control over the execution of our SQL scripts, making them more effective, reliable, and consistent in execution. It ensures consistency by preventing a script from being executed repeatedly & ordered execution through the versioning system.

Flyway tool can be integrated directly with Spring Boot using the following Maven Dependency:-

Basic Terminologies

1. Migration

In the database context, migration refers to the process of making structured changes in the database schema such as performing a CRUD operations that changes the state of the database. Database Migration is an essential technique when dealing with industry-level projects, as our application evolves, we may encounter various anomalies or redundancies so to counter these we migrate our database which can easily be done by utilizing tools like Flyway.

2. Migration Script

Database Migration instructions are typically written in a separate file which are referred to as the migration scripts. These scripts contain the instruction to perform migration on the database in a specific query language (eg: SQL). By default, spring scans the following location for the migration script :

resources/db/migration

3. Migration Lifecycle

Migration lifecycle refers to the process of performing migration on the database. This goes as follows:

1. Write a Migration Script containing instructions on how to migrate the database
2. Save the script using the version number so that Flyway can identity the order and execute it accordingly.
3. The script is automatically executed by flyway to maintain consistency, ensuring the execution is done only once.

4. Version Control

The version control system of the flyway is very essential feature that’s defined to maintain the order of execution. Whenever the user creates a migration script, they’ll save it with their version number in following format :

V<version_number>__migrationScriptName.sql
Ex : V1__create_user_table.sql

V1 is the version number, indicating this script should be executed first in order. Ensure that after the version number comes two underscores (__) and the name is delimited by single underscores.

In this article, we’ll look at how we can apply the flyway database migration scripts in our Spring Boot Application.

Step-By-Step Implementation

Step-1 : Create a Configuration file with following dependencies:

• Web Starter
• JDBC API
• MySQL Driver (Or the driver related to the database you’re using)
• Flyway Migration

Starter File

Here’s the Maven dependency for Flyway Migration :-

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

POM File

complete settings file of spring boot application

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>3.1.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.GeeksForGeeks</groupId>
    <artifactId>FlywayExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>FlywayExample</name>
    <description>Spring Boot - Flyway Project</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-mysql</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <image>
                        <builder>paketobuildpacks/builder-jammy-base:latest</builder>
                    </image>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Step-2: Create database – flywayDemoDB on your localhost.

CREATE DATABASE flywayDemoDB;

You may optionally need to execute the following command in your database to access it using root as the username and password (if your’re getting console error) :

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

Step-3: Create application.properties file

Now open the downloaded starter file in your favorite IDE and enter the following info in the application.properties file.

Properties files contains credentials regarding external databases, files or anything that’s not a part of our application. In this example we’re connecting with the database using root as the username and password.

application.properties

spring.application.name= FlywayApp
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.url = jdbc:mysql://localhost:3306/flywayDemoDB?autoreconnect=true
spring.flyway.url = jdbc:mysql://localhost:3306/flywayDemoDB
spring.flyway.user = root
spring.flyway.password = root

Step-4: Create a Migration Script to create a new table in the flywayDemoDB database.

In this step, we will create a migration script containing the instructions on how to make migrations in our database.This script will be executed by spring automatically as soon as we run our Spring Boot Application.

Migration Script

V1__create_users_table.sql

CREATE TABLE users(
    user_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Spring will look for the migration scripts in the resources/db/migration directory. So, your final directory structure should look something like this :-

Directory Structure

Step-5: Run your spring boot application and then again check the database

Now you can execute your Spring Boot Application to make changes in the database

Java

//Spring Boot application file

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

// Project's driver class
@SpringBootApplication
public class FlywayExampleApplication {

      //Main method
    public static void main(String[] args) {
        SpringApplication.run(FlywayExampleApplication.class, args);
    }

}

After executing the above code, we can again query our flywayDemoDB for new tables.

Step-6: Check for the output in our database.

Flyway History Table

Flyway maintains a history schema, to keep track of all the changes made in the database. It contains all the information related to the migration scripts, when they’re executed, their description, version, time etc.

Conclusion

Flyway is very essential migration tool that allows us to make complex changes in our database in a consistent manner, maintaining the integrity across all the instances of database. It can easily be integrated in our Spring Boot Application using a single dependency.

• Flyway allows us to remodel our application using migration scripts in a reliable way.
• Migration Scripts are the core feature of the flyway that allows us to manage the SQL queries externally, all the while maintaining a track record of all the changes made in the database.
• Version Control is a feature that allows us to maintain the order of execution of our migration scripts.
• Flyway compares the current state of the database based on the applied migration and update the instances of database accordingly.