Mastering Flyway: Automatic Database Migrations with Spring Boot, CLI, and Maven

When an application is upgraded, its database schema often needs to be upgraded as well; Flyway automates this process at application startup.

Flyway Overview

Flyway is a database migration tool that simplifies migrations and provides version control similar to Git. It supports command‑line usage, Maven plugin, and integration with Spring Boot.

Simple: Easy to use and learn, migrations are defined by sequential SQL scripts.

Professional: Focused solely on database migration, eliminating unrelated concerns.

Powerful: Supports many databases, third‑party tools, and CI/CD pipelines.

Related Concepts

How It Works

When using Flyway, you write migration scripts such as

V1__Initial_Setup.sql

and

V2__First_Changes.sql

. Flyway creates a

flyway_schema_history

table to record the execution status of each script, enabling version control. During migration, Flyway reads this table to determine which scripts still need to be applied.

Script Naming Convention

Proper naming of Flyway SQL scripts is required for correct execution order. The following diagram illustrates the convention.

To be recognized by Flyway, migration scripts must follow these rules:

Prefix:

V

for versioned migrations,

U

for undo scripts,

R

for repeatable migrations.

Version: Scripts are executed in ascending version order.

Separator: Use a double underscore

__

to separate version and description.

Description: Human‑readable explanation of the migration.

Suffix: Must end with

.sql

.

Relevant Commands

migrate

: Apply pending migrations to bring the schema to the latest version.

clean

: Drop all objects in the configured schemas (use with caution).

info

: Show detailed information and status of all migrations.

validate

: Verify that applied migrations are still valid.

undo

: Roll back migrations (requires undo scripts).

baseline

: Create the

flyway_schema_history

table based on an existing schema.

repair

: Repair the

flyway_schema_history

table.

Command‑Line Tool

Flyway can be used via its command‑line tool to experience database migration.

Download the Flyway community edition from https://flywaydb.org/download and unzip it.

Modify the configuration file

/conf/flyway.conf

to set your database connection.

<code>flyway.url=jdbc:mysql://localhost:3306/flyway?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=Asia/Shanghai
flyway.user=root
flyfly.password=root
</code>

Add SQL migration scripts under the

/sql

directory, e.g.,

V1.0.1__Create_ums_admin_table.sql

:

<code>CREATE TABLE `ums_admin` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `icon` varchar(500) DEFAULT NULL COMMENT '头像',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `nick_name` varchar(200) DEFAULT NULL COMMENT '昵称',
  `note` varchar(500) DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `status` int(1) DEFAULT '1' COMMENT '帐号启用状态：0->禁用；1->启用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='后台用户表';
</code>

Run

flyway migrate

. If the history table does not exist, first run

flyway baseline

to create it.

After baseline, execute

flyway migrate

and observe success messages.

Repeatable scripts (prefix

R

) are executed after all versioned scripts.

Rollback scripts (prefix

U

) can be created, e.g.,

U1.0.1__Create_ums_admin_table.sql

and

U1.0.2__Add_ums_admin.sql

:

<code># U1.0.1__Create_ums_admin_table.sql
DROP TABLE ums_admin;
</code>

<code># U1.0.2__Add_ums_admin.sql
DELETE FROM ums_admin;
</code>

The community edition does not support

undo

; therefore, backups are recommended before migrations.

Maven Plugin

Flyway also provides a Maven plugin with functionality equivalent to the command‑line tool.

Add the Flyway Maven plugin to

pom.xml

and configure the database connection.

<code><!--Flyway Maven Plugin-->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.3.2</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/flyway?serverTimezone=Asia/Shanghai</url>
        <user>root</user>
        <password>root</password>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>
</plugin>
</code>

Place migration scripts under

src/main/resources/db/migration

.

The plugin supports commands such as

flyway:info

,

flyway:migrate

, etc.

Running

mvn flyway:info

displays migration status similar to the CLI.

<code>[INFO] --- flyway-maven-plugin:7.3.2:info (default-cli) @ mall-tiny-flyway ---
[INFO] Flyway Community Edition 7.3.2 by Redgate
[INFO] Database: jdbc:mysql://localhost:3306/flyway (MySQL 5.7)
[INFO] Schema version: 1.0.2
[INFO] +------------+---------+------------------------+----------+---------------------+----------+
| Category   | Version | Description            | Type     | Installed On        | State    |
+------------+---------+------------------------+----------+---------------------+----------+
|            | 1       | << Flyway Baseline >>  | BASELINE | 2020-12-24 11:17:35 | Baseline |
| Versioned  | 1.0.1   | Create ums admin table | SQL      | 2020-12-24 11:17:42 | Success  |
| Versioned  | 1.0.2   | Add ums admin          | SQL      | 2020-12-24 11:33:40 | Success  |
| Repeatable |         | Ums admin view         | SQL      | 2020-12-24 11:33:40 | Success  |
+------------+---------+------------------------+----------+---------------------+----------+
[INFO] BUILD SUCCESS
</code>

Using Flyway with Spring Boot

Spring Boot natively supports Flyway, making integration straightforward.

Add the Flyway core dependency (no version needed):

<code><!--Flyway Dependency-->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
</code>

Configure Flyway in

application.yml

:

<code>spring:
  datasource:
    url: jdbc:mysql://localhost:3306/flyway?useUnicode=true&amp;characterEncoding=utf-8&amp;serverTimezone=Asia/Shanghai
    username: root
    password: root
  flyway:
    enabled: true
    clean-disabled: true
    locations: classpath:db/migration
    table: flyway_schema_history
    baseline-on-migrate: true
    baseline-version: 1
    encoding: UTF-8
    out-of-order: false
    schemas: flyway
    validate-on-migrate: true
</code>

Run the Spring Boot application; Flyway automatically creates the schema history table and applies pending migrations, logging the process.

<code>2020-12-24 14:38:15.659  INFO --- o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 6.4.1 by Redgate
2020-12-24 14:38:15.898  INFO --- o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/flyway (MySQL 5.7)
2020-12-24 14:38:15.972  INFO --- o.f.core.internal.command.DbValidate     : Successfully validated 3 migrations (execution time 00:00.047s)
2020-12-24 14:38:15.988  INFO --- o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table `flyway`.`flyway_schema_history` with baseline ...
2020-12-24 14:38:16.106  INFO --- o.f.core.internal.command.DbBaseline     : Successfully baselined schema with version: 1
2020-12-24 14:38:16.122  INFO --- o.f.core.internal.command.DbMigrate      : Current version of schema `flyway`: 1
2020-12-24 14:38:16.134  INFO --- o.f.core.internal.command.DbMigrate      : Migrating schema `flyway` to version 1.0.1 - Create ums admin table
2020-12-24 14:38:16.248  INFO --- o.f.core.internal.command.DbMigrate      : Migrating schema `flyway` to version 1.0.2 - Add ums admin
2020-12-24 14:38:16.281  INFO --- o.f.core.internal.command.DbMigrate      : Migrating schema `flyway` with repeatable migration Ums admin view
2020-12-24 14:38:16.314  INFO --- o.f.core.internal.command.DbMigrate      : Successfully applied 3 migrations to schema `flyway` (execution time 00:00.206s)
</code>

Conclusion

Compared with manual schema upgrades, Flyway provides a safer, automated way to keep databases in sync with application releases. Although the community edition lacks built‑in rollback, creating backups before migrations is essential.