使用Flyway管理数据库版本

前言

web开发中不论我们使用哪一个框架，都离不开对数据库表结构的管理。而这一类管理一直都存在一个问题：由于数据库表元数据存储于数据库中，而我们的访问逻辑都存在于Git或其他代码仓库中。Git已经帮助我们完成了代码的多版本管理，那么数据库中的表该如何做好版本控制呢？

Flyway简介

Flyway是一个简单开源数据库版本控制器（约定大于配置），主要提供migrate、clean、info、validate、baseline、repair等命令。它支持SQL（PL/SQL、T-SQL）方式和Java方式，支持命令行客户端等，还提供一系列的插件支持（Maven、Gradle、SBT、ANT等）。
官方网站：https://flywaydb.org/
Flyway通过记录数据库版本升级来进行管理。数据库中的每一个修改后状态对应数据库的一个版本。理论上Flyway引入后，不允许直接操作数据库，均需通过Flyway配置扫描路径下的SQL文件来操作。

flyway_schema_history

Flyway运行成功后，会在数据库中自动创建 flyway_schema_history（表名可通过配置修改）表，该表用于记录数据库的变更记录。
表中每一条记录对应扫描路径（一般配置在 /src/resources/db/migration）下的每一个SQL文件执行；
- 表字段 version 对应 SQL 脚本版本；
- 表字段 script 对应 SQL 脚本名；
- 表字段 success 对应 SQL 脚本执行结果，0: 失败；1: 成功。

SQL脚本命名规范

V2_1__init_request.sql 格式，具体说明如下：

V 为固定前缀分隔符，代表数据库版本化；
21 为 SQL 脚本版本，’’ 翻译为小数点，2_1 即为 2.1 版本；
__为两个下划线，代表中间分隔符；
init_request 为 SQL 脚本名，概述本脚本要进行的操作；
.sql 为固定后缀。

小试牛刀

下面我们先预设一个开发目标：
1. 假设我们需要开发一个用户管理系统，那么我们势必要设计一张用户表，并实现对用户表的增删改查操作。
2. 在任务1的功能完成之后，我们又有一个新需求，需要对用户表增加了一个字段，看看如何实现对数据库表结构的更改。

增删改查操作

第一步：创建一个基础的Spring Boot项目，并在pom.xml中加入Flyway、MySQL连接和数据访问相关的必要依赖（这里选用spring-boot-starter-jdbc作为例子）

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
</dependencies>

配置文件

spring:
  application:
    name: demo-flyway
  datasource:
    #    schema:
    #      - classpath:db/schema.sql
    #    data:
    #      - classpath:db/data.sql
    url: jdbc:h2:mem:testdbsa
    driver-class-name: org.h2.Driver
    username: sa
    password:
  # H2配置
  h2:
    console:
      enabled: true
      path: /h2
      settings:
        web-allow-others: true
        trace: true

第二步：按Flyway的规范创建版本化的SQL脚本。

在工程的src/main/resources目录下创建db目录，在db目录下再创建migration目录
在migration目录下创建版本化的SQL脚本V1__Base_version.sql

注意：如果想将SQL脚本放到其他目录，可以用spring.flyway.locations参数来配置。这里不同于1.x版本的配置项flyway.locations

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

第三步：根据User表的结构，编写对应的实体定义

@Data
@NoArgsConstructor
public class User {

    private Long id;
    private String name;
    private Integer age;

}

第四步：编写用户操作接口和实现

这里主要介绍Flyway的应用，所以采用这种比较简单的编写方式，实际项目应用中，还是推荐MyBatis的具体操作实现。

public interface UserService {

    /**
     * 新增一个用户
     *
     * @param name
     * @param age
     */
    int create(String name, Integer age);

    /**
     * 根据name查询用户
     *
     * @param name
     * @return
     */
    List<User> getByName(String name);

    /**
     * 根据name删除用户
     *
     * @param name
     */
    int deleteByName(String name);

    /**
     * 获取用户总量
     */
    int getAllUsers();

    /**
     * 删除所有用户
     */
    int deleteAllUsers();

}

@Service
public class UserServiceImpl implements UserService {

    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 int getAllUsers() {
        return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class);
    }

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

}

第五步：编写测试用例

@Slf4j
@SpringBootTest
public class Chapter311ApplicationTests {

    @Autowired
    private UserService userSerivce;

    @Test
    public void test() throws Exception {
        userSerivce.deleteAllUsers();

        // 插入5个用户
        userSerivce.create("Tom", 10);
        userSerivce.create("Mike", 11);
        userSerivce.create("Didispace", 30);
        userSerivce.create("Oscar", 21);
        userSerivce.create("Linda", 17);

        // 查询名为Oscar的用户，判断年龄是否匹配
        List<User> userList = userSerivce.getByName("Oscar");
        Assertions.assertEquals(21, userList.get(0).getAge().intValue());

        // 查数据库，应该有5个用户
        Assertions.assertEquals(5, userSerivce.getAllUsers());

        // 删除两个用户
        userSerivce.deleteByName("Tom");
        userSerivce.deleteByName("Mike");

        // 查数据库，应该有5个用户
        Assertions.assertEquals(3, userSerivce.getAllUsers());
    }

}

测试结果

运行测试结果ok的话，连接数据库后应该多出了这两张表：
user表就是我们维护在SQL脚本中要创建的表
flyway_schema_history表是flyway的管理表，用来记录在这个数据库上跑过的脚本，以及每个脚本的检查依据。
这样每次应用启动的时候，就可以知道哪个脚本需要运行，或者哪个脚本发生了变动，运行基础可能不对，造成数据结构的混乱而阻止运行。

表结构变动

在开始使用Flyway之后，对于数据库表接口的变更就要关闭这几个途径：
1. 直接通过工具登录数据去修改表结构
2. 已经发布的sql脚本不允许修改
正确的表结构调整途径：在flyway脚本配置路径下编写新的脚本，启动程序来执行变更。
脚本文件名的基本规则是：版本号__描述.sql
这样可以获得几个很大的好处：
1. 脚本受Git版本管理控制，可以方便的找到过去的历史
2. 脚本在程序启动的时候先加载，再提供接口服务，一起完成部署步骤
3. 所有表结构的历史变迁，在管理目录中根据版本号就能很好的追溯

增加字段

假设我们现在想对User表增加一个字段：address，用来存储用户的通讯地址，那么我们就需要这样操作实现。

第一步：创建脚本文件V1_1__alter_table_user.sql，并写入增加address列的语句
1
ALTER TABLE `user` ADD COLUMN `address` VARCHAR(20) DEFAULT NULL;

再次执行单元测试，在控制台中可以看到如下日志：

2021-06-18 10:43:54.619 |-INFO  [restartedMain] org.flywaydb.core.internal.database.DatabaseFactory [49] -| Database: jdbc:h2:mem:testdbsa (H2 1.4)
2021-06-18 10:43:54.640 |-INFO  [restartedMain] org.flywaydb.core.internal.command.DbValidate [49] -| Successfully validated 2 migrations (execution time 00:00.014s)
2021-06-18 10:43:54.649 |-INFO  [restartedMain] org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory [49] -| Creating Schema History table "PUBLIC"."flyway_schema_history" ...
2021-06-18 10:43:54.678 |-INFO  [restartedMain] org.flywaydb.core.internal.command.DbMigrate [49] -| Current version of schema "PUBLIC": << Empty Schema >>
2021-06-18 10:43:54.690 |-INFO  [restartedMain] org.flywaydb.core.internal.command.DbMigrate [49] -| Migrating schema "PUBLIC" to version 1 - Base version
2021-06-18 10:43:54.732 |-INFO  [restartedMain] org.flywaydb.core.internal.command.DbMigrate [49] -| Migrating schema "PUBLIC" to version 1.1 - alter table user
2021-06-18 10:43:54.785 |-INFO  [restartedMain] org.flywaydb.core.internal.command.DbMigrate [49] -| Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.111s)

再次访问数据库，可以看到多出了address字段