Background Introduction

Self Introduction

Hello. My name is Li Lin from the DevOps Team of the KINTO Technologies Global Development Group. Until 2017, I worked in China as an engineer, project manager, and university lecturer. In 2018, I started working in Japan. I’m a working mother of two, balancing my job while actively reskilling.

Meet our DevOps team

The Global Development Group's DevOps team started its operations this year. Our team is international, and the DevOps Team members speak Japanese, Chinese, and English as their native languages. We make sure to communicate smoothly by considering each member’s language skills. As a new team, each member has different experiences, but we always cooperate actively when facing challenges. I believe our teamwork is going well.

DevOps Team Responsibilities

Currently, there are multiple teams within the Global Development Group. The DevOps Team acts as a common team overseeing the entire Global Development Group. Our specific responsibilities are as follows:

Target audience of this article

This article is intended for experienced developers who are considering or have already implemented Flyway. When I first started using Flyway, I did some research online but found that there was very little information providing an overall picture. This article serves as a proposal for introducing Flyway. I would be honored if you find the information helpful.

Introducing Flyway

What is Flyway?

Flyway is an Open-Source database migration tool. It makes it easy to version control databases across multiple environments. The applicable scenarios for each command are as follows:

Baseline

Running the Baseline command creates the initial version for Flyway. The default version of Baseline is "1". In the Community Edition, you can create a baseline only once. It cannot be updated. If some tables already exist in the target database, you must run Baseline. Otherwise, the Migrate command will result in an error. [Scenario] Step 1) Set the version of the already applied SQL scripts to a number smaller than "1" before introducing Flyway. Step 2) Execute the Baseline command 3) Execute the Migrate command. As a result, SQL scripts with a version number of "1" or higher will be applied. [Reference] Baselines an existing database

Clean

The Clean command completely clears the target schema. Since this makes the schema empty, you must implement measures to prevent it from being used in production environments. [Scenario] If you want to revert to the initial version, you can do so by following the steps below. Step 1) Run the Clean command Step 2) Run the Migrate command [Reference] Wiping your configured schemas completely clean

Info

Flyway information is displayed. This command allows you to verify if Flyway can connect to the database. [Scenario] After execution, the following information is displayed (example):

| Category  | Version | Description | Type | Installed On | State   | 
+-----------+---------+-------------+------+--------------+---------+
| Versioned | 00.01   | initial     | SQL  |              | Pending |
| Versioned | 00.02   | initial     | SQL  |              | Pending |
+-----------+---------+-------------+------+--------------+---------+

[Reference] Prints the details and status information about all the migrations

Migrate

Applies new SQL files that have not yet been applied. This is the most commonly used command. It is used every time the database needs to be updated to a new version. [Reference] Migrates the schema to the latest version

Repair

Removes the execution history of the SQL scripts that resulted in errors. However, the execution results cannot be removed. The Repair command only removes the execution history of failed SQL scripts from the flyway_shema_history table (Flyway's version control table) in the database. The following situation is common: In such cases, carefully check which SQL scripts were applied and make sure all scripts are applied correctly.

If a single SQL file contains multiple SQL scripts and an error occurs, the scripts before the error will be applied, while those after the error will not be. 

[Scenario] [Example] When you are applying V01_07, V01_08, and V01_09, if V01_07 and V01_08 succeed but V01_09 fails, you can take the following steps. Step 1) Fix V01_09 Step 2) Execute the Repair command Step 3) Run the Migrate command again [Reference] Repairs the schema history table

Validate

This command checks whether the SQL scripts in the project have been applied to the database and also checks if the versions match. You can also use it to verify that the current database matches the version in the cloud. [Reference] Validates the applied migrations against the available ones

Background to Flyway's implementation

If you don’t use a tool like Flyway, you will need to log in to a bastion server for the database and run update scripts every time you deploy. Most of the Global Development Group's services are composed of microservices. As the number of environments grew, the traditional method of updating databases via bastion servers became increasingly burdensome and risky, leading to operational challenges.

These circumstances led us to consider introducing Flyway. Initially, we tried introducing a job that could execute commands in a GitHub job via Lambda on AWS. When we actually tried using it, we encountered the following issues:

• If you migrate to AWS without sufficiently verifying the SQL scripts in a local environment, the migration may fail, making recovery difficult.
• If you update the database manually without building a Flyway environment in your local environment, there is a high risk that the structure will differ from the database on AWS. With the above issues in mind, during the first PDCA cycle, we implemented the Flyway system as shown below.

Flyway implementation method by KINTO Technologies Global Development Group

To use Flyway in a Spring Boot application, we implemented the following functions:

1. Flyway is integrated directly into the application

• Usage Timing: Migrations are executed automatically when the application is started locally and when it is deployed to AWS.
• Purpose: This allows SQL migration scripts to be tested locally, and automates the migration process reducing manual effort.

2. Introducing the Flyway plugin

• Usage Timing: During local development.
• Purpose: To run Flyway commands using the plugin if automatic migration cannot be preformed locally.

3. GitHub job implementation for Flyway commands

• Usage Timing: When automatic migration cannot be performed during deployment to AWS, Flyway commands are executed using a GitHub job.
• Purpose: To enable the execution of Flyway commands without logging into AWS

Next, I will introduce the final configurations for each implementation.

Integrating Flyway into the application

By integrating Flyway into the project, you can achieve the following:

• Databases in each environment are automatically migrated after the application starts.
• Migration SQL scripts are validated in local environment before migrating to the AWS database.

The details are as follows: By running the following command, you can start a MySQL Docker image locally. Once the application starts, the latest SQL scripts will be automatically migrated.

docker-compose up -d
./gradlew bootRun

Introducing the Flyway plugin

You can also maintain the local database manually using Flyway commands. By using the plugin as shown below, you can execute these commands

Introducing GitHub jobs that can execute Flyway commands

Once deployed on AWS, the database can be automatically migrated to Aurora. However, if this does not occur, you will need to run the Flyway command manually. Flyway commands are executed via Lambda on AWS. The configuration diagram is as follows:

The flow from executing GitHub job to completing Flyway execution is as follows:

• Upload the execution file from the GitHub job to S3.
• Extract the necessary parameters from the payload (JSON).
• Use AWS CLI to extract information required for Flyway execution.
• Retrieve the zip file containing SQL scripts from the S3 bucket.
• Execute Flyway (using a Docker image on Lambda).
• Place the results in the S3 bucket.

The image below shows the process when executing the command on GitHub. We have built this system so that it can be run without logging into AWS.

This setup allows the following for each environment:

• Databases in each environment are automatically migrated after the application starts.
• Migration SQL scripts are validated in the local environment before migrating to the AWS database.
• Tools for executing Flyway commands are provided in each environment.

Using Flyway has brought the following benefits:

• Deployment time was significantly reduced (by more than half)
• Eliminating database discrepancies between environments reduced unnecessary bugs and misunderstandings during development.
• The workload required for managing database versions in each environment was minimized (as long as the version was clearly indicated by the SQL script name).
• Testing and reviewing can prevent incomplete queries from being executed.
• No need to log in to a jump server built on AWS to perform operations.

Of course, when using Flyway, there are some precautions:

• If there are many developers, decide on a consistent method of use.
• Troubleshooting and recovery from errors can be time-consuming.

Theoretically, the above mechanism also allows you to start up a database while GitHub Actions CI/CD jobs are running, but we have not yet verified this. I am also considering using Flyway to build a database for automated CI/CD testing.

While there are many benefits to using Flyway, it has also caused some issues. I believe there is room for improvement by using the PDCA cycle of usage standards. By gradually introducing Flyway depending on the environment and usage scenario, it can be used more safely and efficiently. If you're interested, we encourage you to give it a try.