Using Flyway for Database Version Management and Migration in Spring Boot

Flyway is a database migration tool that helps execute versioned, undo, and repeatable migration scripts during application deployment, similar to Liquibase.

Flyway Migration Types

Flyway defines three migration types:

Versioned Migrations : Executed once per version, include version, description, and checksum; used for creating, altering, or dropping tables and inserting data.

Undo Migrations : Reverse operations of versioned migrations (available in the paid edition).

Repeatable Migrations : Can be executed multiple times, contain description and checksum but no version; typically used for views, stored procedures, and functions.

The naming convention for migration files is V<version>__<description>.sql for versioned, U<version>__<description>.sql for undo, and R__<description>.sql for repeatable migrations.

Common Flyway Commands

Migrate : Scans the filesystem or classpath for pending migrations and applies them.

Clean : Removes all objects in the configured schema (useful for dev/test, not for production).

Info : Shows detailed information and status of all migrations.

Validate : Checks that applied migrations match the checksum of the migration files.

Undo : Rolls back the last migration (paid feature).

Baseline : Creates a baseline version for existing databases.

Repair : Repairs the Flyway metadata table when it becomes corrupted.

Spring Boot Integration Example

Below is a minimal pom.xml snippet that adds MySQL driver, JPA, and Flyway dependencies:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.28</version>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-mysql</artifactId>
  <version>8.5.7</version>
</dependency>

The corresponding application.yml configuration enables Flyway and points it to the migration scripts:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db_flyway?useSSL=false&autoReconnect=true&characterEncoding=utf8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: bfXa4Pt2lUUScy8jakXf
  flyway:
    enabled: true
    encoding: UTF-8
    locations: classpath:db/migration
    validate-on-migrate: true

Example Migration Scripts

Versioned migration V1.0__Init_DB.sql creates a tb_user table:

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone_number` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Repeatable migration V1.1__Init_Data.sql inserts an initial user record:

LOCK TABLES `tb_user` WRITE;
INSERT INTO `tb_user` VALUES (1,'pdai','dfasdf','[email protected]',1212121213,'afsdfsaf','2021-09-08 17:09:15','2021-09-08 17:09:15');
UNLOCK TABLES;

Running the Application

When the Spring Boot application starts, Flyway logs show the validation and execution of the two migrations, creation of the flyway_schema_history table, and successful application of both scripts:

2022-04-13 07:56:56.122 INFO 86030 --- [main] o.f.c.i.database.base.DatabaseType : Database: jdbc:mysql://localhost:3306/test_db_flyway (MySQL 8.0)
2022-04-13 07:56:56.220 INFO 86030 --- [main] o.f.core.internal.command.DbValidate : Successfully validated 2 migrations (execution time 00:00.074s)
2022-04-13 07:56:56.245 INFO 86030 --- [main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table `test_db_flyway`.`flyway_schema_history` ...
2022-04-13 07:56:56.346 INFO 86030 --- [main] o.f.core.internal.command.DbMigrate : Successfully applied 2 migrations to schema `test_db_flyway`, now at version v1.1 (execution time 00:00.058s)

After migration, the tb_user table contains the defined structure and the inserted data.

Promotional Note

The article also contains promotional messages about a ChatGPT community, giveaways, and related links, which are not part of the technical tutorial.