preface

Flyway, a management database version framework, has been introduced into the project recently to solve the problem of chaotic database script iteration. It's a shame that the company has always manually iterated the database version and configured the init SQL component of MybatisPlus.

I have always felt that there is no problem, but every time the tester deploys the project test, the developer needs to send the database SQL file to the test, which sometimes causes confusion in the script file and leads to some unnecessary trouble. Therefore, I want to use a tool that can record the iteration of SQL file version. Flyway can solve this problem.

Today, we will introduce the method of using Flyway to manage database versions in Spring Boot.

Flyway introduction

Flyway is a simple open source database version controller (the Convention is greater than the configuration). It mainly provides migrate, clean, info, validate, baseline, repair and other commands. It supports SQL (PL/SQL, T-SQL) and Java, supports command-line clients, and also provides a series of plug-in support (Maven, Gradle, SBT, ANT, etc.).

• Official website: https://flywaydb.org/

realization

Let's preset a development goal first:

1. Assuming that we need to develop a user management system, it is necessary for us to design a user table and add, delete, modify and query the user table.
2. After the function of task 1 is completed, we have a new requirement. We need to add a field to the user table to see how to change the database table structure.

Achievement of goal 1

Step 1:

Create a basic Spring Boot project, and add necessary dependencies related to Flyway, MySQL connection and data access in pom.xml (Spring Boot starter JDBC is selected here as an example)

<!-- Java edition -->
<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- flyway-core rely on 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>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

• yaml profile content

spring:
  # flyway configuration
  flyway:
    # Open flyway
    enabled: true
    # Disable database cleanup
    clean-disabled: true
    # Encoding of SQL migration
    encoding: UTF-8
    # The location of the migration script. The default is db/migration
    locations: classpath:db/migration
    # File name prefix for SQL migration.
    sql-migration-prefix: V
    # File name separator for SQL migration.
    sql-migration-separator: __
    # File name suffix for SQL migration.
    sql-migration-suffixes: .sql
    # Whether validation is invoked automatically when migration is performed.
    validate-on-migrate: true
    # Whether to perform benchmark migration automatically when the target schema is found to be non empty and there is a table without metadata during migration. The default is false
    baseline-on-migrate: true
    # The fully qualified name of the JDBC driver. By default, it is automatically detected according to the URL.
    driver-class-name:
    # DBC url of the database to be migrated. If not set, the primary configured data source is used.
    url: jdbc:mysql://localhost:3306/flyway?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
    # Login password for the database to be migrated.
    password: root
    # Login user of the database to be migrated.
    user: root

  # MySQL configuration
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/flyway
    username: root
    password: root

Step 2:

Create versioned SQL scripts according to Flyway's specifications.

• Create a db directory in the src/main/resources directory of the project, and then create a migration directory in the db directory
• Create a versioned SQL script V1 in the migration directory__ user_ version.sql

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

Step 3:

Write the corresponding entity definition according to the structure of the User table

@Data
@NoArgsConstructor
public class User {
    private Long id;
    private String name;
    private Integer age;
}

Step 4:

Write user operation interface and Implementation

package com.mobaijun;

import java.sql.SQLException;
import java.util.List;

/**
 * Software: IntelliJ IDEA 2021.2 x64
 * Author: https://www.mobaijun.com
 * Date: 2021/11/15 13:43
 * InterfaceName:UserService
 * Interface Description: user interface
 */
public interface UserService {
    
    /**
     * Add a new user
     */
    int create(String name, Integer age);

    /**
     * Query users by name
     */
    List<User> getByName(String name);

    /**
     * Delete user by name
     */
    int deleteByName(String name);

    /**
     * Get total number of users
     */
    Integer getAllUsers() throws SQLException;

    /**
     * Delete all users
     */
    int deleteAllUsers();
}

Step 5:

Implementation class writing

package com.mobaijun;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.List;

/**
 * Software: IntelliJ IDEA 2021.2 x64
 * Author: https://www.mobaijun.com
 * Date: 2021/11/15 13:47
 * ClassName:UserServiceImpl
 * Class description: user implementation class
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    UserServiceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int create(String name, Integer age) {
        return jdbcTemplate.update("insert into user(name, age) values(?, ?)", name, age);
        ;
    }

    @Override
    public List<User> getByName(String name) {
        List<User> users = jdbcTemplate.query("select * from user where name = ?", (resultSet, i) -> {
            User user = new User();
            user.setId(resultSet.getLong("ID"));
            user.setName(resultSet.getString("NAME"));
            user.setAge(resultSet.getInt("AGE"));
            return user;
        }, name);
        return users;
    }

    @Override
    public int deleteByName(String name) {
        return jdbcTemplate.update("delete from user where name = ?", name);
    }

    @Override
    public Integer getAllUsers() throws SQLException {
        return jdbcTemplate.queryForObject("select count(1) from user", Integer.class);
    }

    @Override
    public int deleteAllUsers() {
        return jdbcTemplate.update("delete from user");
    }
}

Step 6:

Write test cases

package com.mobaijun;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class SpringBootFlywayApplicationTests {
    
    @Autowired
    private UserService userService;

    @Test
    void contextLoads() {
        userService.deleteAllUsers();
        // Add 5 users
        userService.create("Ink white", 18);
        userService.create("Li Bai", 100);
        userService.create("Zhang San", 100);
        userService.create("Wang Wu", 100);
        userService.create("Li Si", 100);

        // Query whether the ink and white ages match
        List<User> userList = userService.getByName("Ink white");
        Assertions.assertEquals(18, userList.get(0).getAge().intValue());

        // Query all users
        Assertions.assertEquals(5, userService.getAllUsers());
        // Delete two users
        userService.deleteByName("Wang Wu");
        userService.deleteByName("Li Si");
    }
}

Step 7:

Run the unit tests written above to verify the effect. Not surprisingly, if the unit test runs ok Connect to the database. There should be two more tables:

• The user table is the table we maintain to be created in the SQL script
• flyway_ schema_ The history table is the management table of flyway, which is used to record the scripts running on the database and the inspection basis of each script. In this way, each time the application starts, you can know which script needs to be run, or which script has changed. The running foundation may be wrong, causing confusion in the data structure and preventing the operation.

Achievement of goal 2

With the above foundation, let's talk about how to change the table structure in the future.

First of all, after you start using Flyway, you should close these channels for the change of database table interface:

1. Modify the table structure by logging in the data directly through the tool
2. The published sql script cannot be modified

The correct way to adjust the table structure: write a new script under the flyway script configuration path and start the program to execute the change. In this way, several great benefits can be obtained:

1. Scripts are controlled by Git version management and can easily find past history
2. The script is loaded when the program starts, and then provides interface services to complete the deployment steps together
3. The historical changes of all table structures can be well traced in the management directory according to the version number

The following is a specific operation according to an actual demand. Suppose we want to add a field to the user table: address, which is used to store the user's communication address, then we need to do so.

Step 1: create script file V1_1__alter_table_user.sql and write the statement to add the address column

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

The basic rule for script file names is: version number__ Description. sql. Of course, if you have more detailed requirements, you can do more detailed file name planning. The specific details can be obtained by referring to the official documents in the references at the end of the document.

Step 2: execute the unit test again. You can see the following log in the console:

2021-11-15 14:03:58.193  INFO 16840 --- [           main] c.m.SpringBootFlywayApplicationTests     : Starting SpringBootFlywayApplicationTests using Java 1.8.0_281 on mobai with PID 16840 (started by mobai in D:\IdeaProject\spring-boot-wikis\spring-boot-flyway)
2021-11-15 14:03:58.201  INFO 16840 --- [           main] c.m.SpringBootFlywayApplicationTests     : No active profile set, falling back to default profiles: default
2021-11-15 14:03:59.270  INFO 16840 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 7.7.3 by Redgate
2021-11-15 14:04:00.337  INFO 16840 --- [           main] o.f.c.i.database.base.DatabaseType       : Database: jdbc:mysql://localhost:3306/flyway (MySQL 8.0)
2021-11-15 14:04:00.382  INFO 16840 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.021s)
2021-11-15 14:04:00.393  INFO 16840 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `flyway`: 1
2021-11-15 14:04:00.403  INFO 16840 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `flyway` to version "1.1 - alter table user"
2021-11-15 14:04:00.427  INFO 16840 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2021-11-15 14:04:00.445  INFO 16840 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `flyway`, now at version v1.1 (execution time 00:00.058s)
2021-11-15 14:04:01.241  INFO 16840 --- [           main] c.m.SpringBootFlywayApplicationTests     : Started SpringBootFlywayApplicationTests in 3.435 seconds (JVM running for 4.441)
2021-11-15 14:04:01.409  INFO 16840 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-11-15 14:04:01.448  INFO 16840 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.

• Check the contents of the database again:

The Address column already exists in the user table

The Flyway management table already has the loading record of the new script

Source address: Portal Welcome to share your database version management!

reference resources

Documentation - Flyway by Redgate • Database Migrations Made Easy.

Spring Boot 2.x basic tutorial: using Flyway to manage database version | program