infomix数据库版本sql_数据库代码化（Database-as-Code）实战

前言

最近在做专有云输出时遇到了一个棘手的问题，客户需要将我们两年前发布的版本升级到最新版。由于跨度较长，产品代码和数据库 schema 都发生了巨大变化。产品代码部分因为采用了版本管理策略，拥有明确的升级路径，但数据库部分由于未采用代码化方案，导致升级路径缺失，整个升级过程非常艰难。为了让以后的版本升级能顺利进行，需要制定出一套统一的数据库代码化方案。

What we needed to do was to change our mindset of how we treated our database. We had to stop treating it like some special artifact or some unique scenario, and we started looking at it through the same perspective that we were treating our web code.

State based VS Migrations based

State based 和 Migrations based 是来实现数据库代码化的两种常用方法，下面分别进行介绍。

State based

在 state based 模式下，我们仅需要维护数据库的目标状态。每个表、存储过程、视图、触发器都将保存为单独的 SQL 文件，这些文件将是数据库对象状态的真实表示。而升级数据库所需的脚本会由工具自动生成，从而大大减轻维护成本。

但这种模式并不能很好地处理数据迁移场景，例如，将 user 表的 name 列拆分成 first name 和 last name 两个字段。这是因为数据表里的数据往往是上下文相关的，这意味着工具无法对数据进行可靠的假设以生成升级脚本。

Migrations based

在 migrations based 模式下，我们需要自行维护数据库从一个版本到另一个版本的变更脚本。相比 state based，该模式增加了维护的成本和复杂性，但它能让我们更直接地控制迁移过程，从而能够处理诸如数据迁移这样上下文相关的场景。并且由于变更通过命令式的方式描述，我们可以更早地对其进行评审。实现了 migration based 的代表性工具有 Liquibase、Flyway 等。

Flyway 简介

本章将介绍 migrations based 模式下的代表性工具之一 Flyway，文中使用的版本为6.0.8。

Flyway 是什么

Flyway 是一款开源的数据库迁移工具，它可以方便地帮我们完成数据库的全新部署和增量升级。它有如下功能特点：
1. 可以嵌入到应用程序里或作为单独的工具执行。
2. 追踪已执行的迁移。
3. 执行新的迁移。
4. 验证数据库状态。

Flyway 原理

Flyway 的工作原理如下：
1. 初始时，在数据库中创建一个名为flyway_schema_history的元数据表，该表用于记录 migration 的执行情况。
2. 扫描用户指定目录下的 migration 脚本，并将它们按版本号由低到高排序。
3. 将 migration 脚本依次应用到数据库上。同时，元数据表也会相应更新。
4. 下次增量升级数据库时，flyway 会根据元数据表中记录的执行情况，找出本次新增的迁移脚本并依次执行。

数据库代码化方案

由于我们不仅需要处理 schema 的变更，还面临一些数据迁移的场景，所以最终选择了 migrations based 模式，并使用 Flyway 帮助我们实现数据库代码化。

迁移脚本组织结构

为了兼顾公有云和专有云，同时考虑老版专有云的升级场景，我们设计了如下目录结构用于管理 SQL 迁移脚本。

|--{db1}
     |--flyway.conf
     |--base_sql
          |--V0.000__a.sql
          |--V0.001__b.sql
          |...
          `--V0.025__z.sql
     |--upgrade_legacy_private_cloud_sql
          |--V2.000__create_TB_t1.sql
          |--V2.001__alter_TB_a.sql
          `--V2.002__TB_b_update.sql
     |--upgrade_sql
          |--V2019.11.11.000__alter_TB_b_add_column.sql
          |--V2019.11.11.001__TB_c_insert.properties
          |--V2019.11.11.001__TB_c_insert.sql
          `--V2019.11.13.000__mix.sql
 |--{db2}
      |--flyway.conf
      ...
 |--common
      `--procedure.sql

下面对其进行说明：
1.每个数据库对应一个独立的目录，包含了该数据库的迁移脚本和配置信息。
2.数据库目录下的文件 flyway.conf 包含了该数据库的连接、认证、baseline 等信息。
3.子目录 base_sql 用于存放存量 schema，版本号格式为0.xxx。该目录中的内容确定后将不允许变更。
4.子目录 upgrade_legacy_private_cloud_sql 用于存放专有云老版本到新版本的迁移脚本，版本号格式为2.xxx。
5.子目录 upgrade_sql 统一存放公有云和专有云的后续迁移脚本。由于公有云并未编制版本，且专有云不同版本的编号方式也存在差异，这里选用日期作为迁移脚本的版本前缀，格式为yyyy.mm.dd.index。
6.不同环境的 DML 可能存在差异。针对这种场景，需要在迁移脚本中将差异部分用占位符表示，占位符的实际值由同名 properties 文件渲染得到，例如V{yyyy.mm.dd.index}__xxx.properties。同名 properties 文件存放在基线里，不同环境可设置不同的值，运行时会将对应环境里的文件拷贝至 upgrade_sql 目录。
7.文件common/procedure.sql包含了变更 index、column、key 的存储过程，这些存储过程实现了变更的幂等性。

迁移脚本粒度

一般情况下，建议一个脚本只包含针对一个对象的一类操作。例如，Vxxx_TB_car_add_column.sql代表为数据表car增加列，Vyyy_TB_car_insert.sql代表向数据表car中插入数据。这种模式符合单一职责的设计思想，可以大大减少合并冲突的数量和复杂性。

执行流程

基于上述 SQL 迁移脚本的管理模式，公有云和专有云不同场景的执行流程如下表所示：

环境场景执行流程公有云全新部署1. 执行 base_sql 中的 migration 脚本。
2. 执行 upgrade_sql 中的 migration 脚本。公有云新版升级1. 获取当前版本 x。
2. 依次执行 upgrade_sql 中版本号大于 x 的 migration 脚本。公有云已有数据库升级1. 手动将已有数据库和 base_sql 对齐。
2. 将 baseline 设为 2000。
3. 依次执行 upgrade_sql 中版本号大于 2000 的 migration 脚本。专有云全新部署1. 执行 base_sql 中的 migration 脚本。
2. 执行 upgrade_sql 中的 migration 脚本。专有云新版升级1. 获取当前版本 x。
2. 依次执行 upgrade_sql 中版本号大于 x 的 migration 脚本。专有云已有数据库升级1. 整理专有云老版到新版的 migration 脚本，并放于 upgrade_legacy_private_cloud_sql 目录下。
2. 将 baseline 设为 1。
3. 依次执行 upgrade_legacy_private_cloud_sql 和 upgrade_sql 中版本号大于 1 的 migration 脚本。

幂等性实践

理想情况下，每个迁移脚本只会在每个数据库上运行一次。但如果某次迁移执行失败，很可能需要重新执行成功迁移的步骤才能使数据库恢复到理想状态。这时以等幂方式编写迁移脚本将非常有帮助。这里我们总结了不同 DDL 和 DML 幂等性实现的最佳实践。

Create Table

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__create_TB_{table_name}.sql
• Best Practices - CREATE TABLE IF NOT EXISTS {table_name};

Drop Table

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__drop_TB_{table_name}.sql
• Best Practices - DROP TABLE IF EXISTS {table_name};

Add Column

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__alter_TB_{table_name}_add_column.sql
• Best Practices - 目标 column 不存在，则进行操作。（通过存储过程封装）

Drop Column

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__alter_TB_{table_name}_drop_column.sql
• Best Practices - 目标 column 存在，则进行操作。（通过存储过程封装）

Change Column

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__alter_TB_{table_name}_change_column.sql
• Best Practices - 目标 column 存在，则进行操作。（通过存储过程封装）

Add

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__alter_TB_{table_name}_add_index.sql
• Best Practices - 目标 key 或 index 不存在，则进行操作。（通过存储过程封装）

Drop

• SQL Type - DDL
• SQL script naming convention - V{yyyy.mm.dd.index}__alter_TB_{table_name}_drop_index.sql
• Best Practices - 目标 key 或 index 存在，则进行操作。（通过存储过程封装）

Insert

• SQL Type - DML
• SQL script naming convention - V{yyyy.mm.dd.index}__TB_{table_name}_insert.sql
• Best Practices - 带唯一索引 Insert 具备幂等性，重复插入会失败，推荐使用。如果希望重复时不抛异常，可以使用insert ignore into、replace、on duplicate key update等命令。不带唯一索引 Insert 不具备幂等性。在数据量不大的情况下，可考虑改写成如下 SQL 语句INSERT INTO table(field1, field2, fieldn) SELECT 'field1', 'field2', 'fieldn' FROM DUAL WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)。

Update

• SQL Type - DML
• SQL script naming convention - V{yyyy.mm.dd.index}__TB_{table_name}_update.sql
• Best Practices - 计算式 Update 不具备幂等性，尽量避免使用，例如UPDATE table SET number=number-1 WHERE id=1。非计算式 Update 天然具备幂等性，推荐使用，例如UPDATE table SET number=3 WHERE id=1。

Delete

• SQL Type - DML
• SQL script naming convention - V{yyyy.mm.dd.index}__TB_{table_name}_delete.sql
• Best Practices - 天然具备幂等性。

以修改表字段为例，这里将修改过程封装成了如下的存储过程。

DELIMITER $$
CREATE PROCEDURE `SAFE_CHANGE_COLUMN`(IN i_table_name VARCHAR(128),IN i_col_name VARCHAR(128), IN i_col_def VARCHAR(256))
BEGIN
    SET @tableName = i_table_name;
    SET @colName = i_col_name;
    SET @colDef = i_col_def;
    SET @colExists = 0;
    SELECT 1 INTO @colExists FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @colName LIMIT 1;
    IF @colExists THEN
        SET @query = CONCAT('ALTER TABLE ',@tableName,' CHANGE COLUMN ', @colName,' ',@colDef);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

CICD 流水线

这里我们将数据库迁移脚本和应用代码存放在相同的代码仓库里，并共享同一个 CICD 流程。这种模式最符合 DevOps 所倡导的协作、试验、快速反馈、持续改进等思想，能实现产品更快、更频繁、更稳定的交付。

总结

采用数据库代码化方案后，数据库的维护升级工作变得轻松简单。
1. 不论多长跨度的升级都能从容应对，因为任意版本之间都有了明确的升级路径。
2. 数据库 schema 变更、数据迁移行为变得可审计。
3. 数据库迁移、应用代码共享 CICD 流水线，提升产品交付发布速度。

参考资料

• Database as Code: a Novel Concept
• Why and How Database Changes Should Be Included in the Deployment Pipeline
• State vs migration-driven database delivery