Purpose of the Article: To introduce basics of data migration using Flyway

Intended Audience: All Developers

Tools and Technology: Intellij Idea, Gradle, Flyway, Spring Boot

Keywords: Flyway, database, migration, spring boot, SQL scripts versioning

Why Database Migrations?

Database migrations provide solution to many challenges we face while maintaining and deploying scripts manually. Challenges such as

1. Information about the current state of the database.
2. Manually applying all the scripts.
3. Has a specific SQL script already applied or not?
4. Complicated process of manual script versioning.
5. How to setup a new database instance?
6. Has a hotfix in production been applied in production?

Automatic database migration using Flyway provides us solutions for the above challenges. Flyway allows us to:

1. Recreate a database from scratch.
2. Scripts versioning system.
3. Make it clear about the current state of database all the time.
4. Migrate in deterministic way current version to new database version
5. Automatic scripts deployment.
6. Fail fast and auto migration on startup.

How Flyway Works

The easiest scenario is when we point Flyway to an empty database. Flyway will try to locate a table with the default name flyway_schema_history in the database and create a new one if not found.

This table is used to track the state of the database.

Once schema history table is ready, Flyway will begin scanning the filesystem or classpath of the application for migrations. Migrations can be written in SQL or Java.

Migrations are then sorted based on their version number and are applied in order and the schema history table is updated with metadata accordingly.

Now the initial state of database is in place. In order to migrate to new versions flyway will once again scan the filesystem or classpath of application for migrations.

All these migrations are checked against schema history table. If the version number is lower or equal to version already present in history table, they are ignored.

The remaining migrations are pending (available but not yet applied) and are then sorted by version number and executed in order.

The schema history table is updated accordingly.

First Database Migration

Let’s start with our first database migration using flyway and spring boot. Spring boot comes with support of Flyway, and it is very easy to start using flyway database migrations.

1. Go to https://start.spring.io/ and create a new spring boot project. Add following dependency:
2. Flyway has a naming convention for database migrations that can be configured as per our need. Add following properties in properties or application.yml file to configure naming convention.
3. Flyway provides two methods of writing migration:

• SQL-based
• Java-based

SQL-based Migration:

Create a migration script V1__init.sql in resources/db/migration folder.

Java-based Migration:

In case we need more dynamic database manipulation we can create Java-based migrations. This comes handy while modifying or generating bulk amount of data.

Naming convention is similar to SQL-based migration but overriding them requires implementing JavaMigration interface.

Create a Java class V2__InsertRecordsInFlywayDemo.java in package db.migration as follows:

Running Flyway

Flyway supports different options to run flyway migrations:

• using Command Line
• using Java API
• spring boot auto configuration
• using Gradle and Maven plugins etc.

Spring Boot Auto Configuration

By default, spring boot run Flyway database migrations automatically on application startup. When we start our spring boot application all the migrations will get applied to database.

flyway_schema_history table will get created if not already present and metadata related to migrations will be inserted into it.

As we can see all our migrations are applied to database.

We can configure the location of migrations using property spring.flyway.locations in application properties file (it take comma separated values).

Gradle Plugin

If we don’t want to run migrations automatically, we can use Flyway Gradle plugin. The plugin takes all configurations out from application and into the Gradle script:

After successful configuration, run the following commands to start Flyway migration:

gradlew flywayMigrate –info

–info sets the log level to information so that we can see the flyway logs.

Tips

Basic data migrations with Flyway are simple but can become complicated. Let’s discuss on points to get it done right.

Flyway enforces incremental database changes which means we should not modify a migration that is already applied except Repeatable ones.

Let’s modify our existing migration V1__init.sql as follows and run the migration:

Flyway migration will fail with following errors:

Fixing this is easy by simply calling repair command:

gradlew flywayRepair

Flyway with An Existing Database

We have talked about database migrations in the newly created database, but what about the existing database? How to integrate flyway with it?

To integrate flyway with existing database with flyway we need to baseline the migrations.

Baseline means we want to ignore all the migrations that are below baselineVersion. Flyway will ignore all the baselined migrations and consider only new ones above baselineVersion.

To baseline, first extract all the DDLs of the target database in a file V0__baseline.sql. Now place the file in resources/db/migration folder of the application.

We will use Gradle to baseline our migrations. Following are the configuration required in build file:

NOTE:  Flyway will baseline the migrations with version 1, once the baseline is complete it will only consider migrations with version greater than 1.

Start the application then run command gradlew flywayBaseline and once it is complete, we can see the information in flyway_schema_history table implies baseline is successful.

Once baseline is done run command to migrate all the migrations above baseline version:

gradlew flywayMigrate.

References

https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html#application-properties.data-migration

https://flywaydb.org/documentation/