spring boot integrated flyway quick start demo

3 min readMar 6, 2024

一、flyway introduction

We all know that Git/SVN is a version control tool in the code world, so Flyway is a version control tool in the database world, which can record changes in the database. Many companies may maintain and synchronize database scripts manually, but they often encounter situations where they are overlooked due to negligence. Here is a simple example:

We added a new field to a table in the development environment, but forgot to submit the SQL script when submitting the test, which caused bugs and interrupted the test, thus affecting the efficiency of development and testing.

With Flyway, we can uniformly manage all SQL script changes according to version agreements, automatically synchronize the database in all environments, without manual control, and no longer have to worry about various environmental problems caused by database out-of-synchronization.。

二、mysql environment setup

version: '3'
services:
  mysql:
    image: registry.cn-hangzhou.aliyuncs.com/zhengqing/mysql:5.7  
    container_name: mysql_3306                                    
    restart: unless-stopped                                       
    volumes:                                                      
      - "./mysql/my.cnf:/etc/mysql/my.cnf"
      - "./mysql/init-file.sql:/etc/mysql/init-file.sql"
      - "./mysql/data:/var/lib/mysql"
#      - "./mysql/conf.d:/etc/mysql/conf.d"
      - "./mysql/log/mysql/error.log:/var/log/mysql/error.log"
      - "./mysql/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d" 
    environment:                        
      TZ: Asia/Shanghai
      LANG: en_US.UTF-8
      MYSQL_ROOT_PASSWORD: root         
      MYSQL_DATABASE: demo              
    ports:                              
      - "3306:3306"

启动

docker-compose -f docker-compose.yml -p mysql5.7 up -d

三、Code project

pom.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
<artifactId>flyway</artifactId>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- flyway-core dependency V:7.7.3 -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
</project>

application.yaml

server:
  port: 8088
spring:
  flyway:
    enabled: true
    clean-disabled: true
    encoding: UTF-8
    locations: classpath:db/migration
    sql-migration-prefix: V
    sql-migration-separator: __
    sql-migration-suffixes: .sql
    validate-on-migrate: true
    baseline-on-migrate: true
    driver-class-name:
    url: jdbc:mysql://localhost:3306/zq?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
    password: root
    user: root
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/zq
    username: root
    password: root

Code repository

https://github.com/Harries/springboot-demo

四、test

sql file， in resource directory，create db/migration,

Test 1: Create table

V1__user_version.sql

DROP TABLE IF EXISTS user ;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary',
  `name` varchar(20) NOT NULL COMMENT 'name',
  `age` int(5) DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Start the application spring boot application and view the results

2024-03-05 10:27:54.267 INFO 20728 --- [ main] o.f.c.internal.license.VersionPrinter : Flyway Community Edition 6.0.8 by Redgate
2024-03-05 10:27:54.862 INFO 20728 --- [ main] o.f.c.internal.database.DatabaseFactory : Database: jdbc:mysql://localhost:3306/zq (MySQL 5.7)
2024-03-05 10:27:54.901 INFO 20728 --- [ main] o.f.core.internal.command.DbValidate : Successfully validated 1 migration (execution time 00:00.015s)
2024-03-05 10:27:55.051 INFO 20728 --- [ main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table `zq`.`flyway_schema_history` ...
2024-03-05 10:27:55.151 INFO 20728 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema `zq`: << Empty Schema >>
2024-03-05 10:27:55.159 INFO 20728 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema `zq` to version 1 - user version
2024-03-05 10:27:55.166 WARN 20728 --- [ main] o.f.c.i.s.DefaultSqlScriptExecutor : DB: Unknown table 'zq.user' (SQL State: 42S02 - Error Code: 1051)
2024-03-05 10:27:55.217 INFO 20728 --- [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema `zq` (execution time 00:00.075s)
2024-03-05 10:27:55.290 INFO 20728 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8088 (http) with context path ''
2024-03-05 10:27:55.293 INFO 20728 --- [ main] com.et.flyway.DemoApplication : Started DemoApplication in 2.896 seconds (JVM running for 3.283)

Test 2: Modify the table mechanism

V1_1__alter_table_user.sql

ALTER TABLE `user` ADD COLUMN `address` VARCHAR(20) DEFAULT NULL;

When you start the spring boot application, the scripts will not all be executed . It will be executed from the last version number. As shown below

2024-03-05 10:31:22.738 INFO 29244 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2024-03-05 10:31:22.931 INFO 29244 --- [ main] o.f.c.internal.license.VersionPrinter : Flyway Community Edition 6.0.8 by Redgate
2024-03-05 10:31:23.479 INFO 29244 --- [ main] o.f.c.internal.database.DatabaseFactory : Database: jdbc:mysql://localhost:3306/zq (MySQL 5.7)
2024-03-05 10:31:23.522 INFO 29244 --- [ main] o.f.core.internal.command.DbValidate : Successfully validated 2 migrations (execution time 00:00.022s)
2024-03-05 10:31:23.537 INFO 29244 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema `zq`: 1
2024-03-05 10:31:23.547 INFO 29244 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema `zq` to version 1.1 - alter table user
2024-03-05 10:31:23.617 INFO 29244 --- [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema `zq` (execution time 00:00.089s)
2024-03-05 10:31:23.684 INFO 29244 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8088 (http) with context path ''
2024-03-05 10:31:23.687 INFO 29244 --- [ main] com.et.flyway.DemoApplication : Started DemoApplication in 2.702 seconds (JVM running for 3.027)