数据库代码化(Database-as-Code)实战

本文涉及的产品
对象存储 OSS,20GB 3个月
对象存储 OSS,恶意文件检测 1000次 1年
对象存储 OSS,内容安全 1000次 1年
简介: 最近在做专有云输出时遇到了一个棘手的问题,客户需要将我们两年前发布的版本升级到最新版。由于跨度较长,产品代码和数据库 schema 都发生了巨大变化。产品代码部分因为采用了版本管理策略,拥有明确的升级路径,但数据库部分由于未采用代码化方案,导致升级路径缺失,整个升级过程非常艰难。

前言

最近在做专有云输出时遇到了一个棘手的问题,客户需要将我们两年前发布的版本升级到最新版。由于跨度较长,产品代码和数据库 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

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

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

Migrations based

migrations_based

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

Flyway 简介

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

Flyway 是什么

Flyway 是一款开源的数据库迁移工具,它可以方便地帮我们完成数据库的全新部署和增量升级。它有如下功能特点:

  1. 可以嵌入到应用程序里或作为单独的工具执行。
  2. 追踪已执行的迁移。
  3. 执行新的迁移。
  4. 验证数据库状态。

Flyway 原理

sls_branch

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 幂等性实现的最佳实践。

SQL Type Object Action SQL script naming convention Best Practices
DDL Table Create Table V{yyyy.mm.dd.index}__create_TB_{table_name}.sql

Example:
V2019.11.08.000__create_TB_car.sql
CREATE TABLE IF NOT EXISTS {table_name};
DDL Table Drop Table V{yyyy.mm.dd.index}__drop_TB_{table_name}.sql

Example:
V2019.11.08.000__drop_TB_car.sql
DROP TABLE IF EXISTS {table_name};
DDL Column Add Column V{yyyy.mm.dd.index}__alter_TB_{table_name}_add_column.sql

Example:
V2019.11.08.000__alter_TB_car_add_column.sql
目标 column 不存在,则进行操作。(通过存储过程封装)
DDL Column Drop Column V{yyyy.mm.dd.index}__alter_TB_{table_name}_drop_column.sql

Example:
V2019.11.08.000__alter_TB_car_drop_column.sql
目标 column 存在,则进行操作。(通过存储过程封装)
DDL Column Change Column V{yyyy.mm.dd.index}__alter_TB_{table_name}_change_column.sql

Example:
V2019.11.08.000__alter_TB_car_change_column.sql
目标 column 存在,则进行操作。(通过存储过程封装)
DDL Keys and Indexes Add V{yyyy.mm.dd.index}__alter_TB_{table_name}_add_index.sql

Example:
V2019.11.08.000__alter_TB_car_add_index.sql
目标 key 或 index 不存在,则进行操作。(通过存储过程封装)
DDL Keys and Indexes Drop V{yyyy.mm.dd.index}__alter_TB_{table_name}_drop_index.sql

Example:
V2019.11.08.000__alter_TB_car_drop_index.sql
目标 key 或 index 存在,则进行操作。(通过存储过程封装)
DML Row Insert V{yyyy.mm.dd.index}__TB_{table_name}_insert.sql

Example:
V2019.11.08.001__TB_car_insert.sql
  • 带唯一索引 Insert - 具备幂等性,重复插入会失败,推荐使用。如果希望重复时不抛异常,可以使用insert ignore intoreplaceon 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 = ?)
DML Row Update V{yyyy.mm.dd.index}__TB_{table_name}_update.sql

Example:
V2019.11.08.001__TB_car_update.sql
  • 计算式 Update - 不具备幂等性,尽量避免使用,例如UPDATE table SET number=number-1 WHERE id=1
  • 非计算式 Update - 天然具备幂等性,推荐使用,例如UPDATE table SET number=3 WHERE id=1
DML Row Delete V{yyyy.mm.dd.index}__TB_{table_name}_delete.sql

Example:
V2019.11.08.001__TB_car_delete.sql
天然具备幂等性。
DML Row Multiple tables
Multiple actions
V{yyyy.mm.dd.index}__mix.sql

Example:
V2019.11.08.001__mix.sql
如果针对下列情况有事务性要求,可以将 SQL 放到一个 mix 脚本里。
  • 单表多类 DML
  • 多表单类 DML
  • 多表多类 DML

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

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

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

总结

采用数据库代码化方案后,数据库的维护升级工作变得轻松简单。

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

参考资料

扩展阅读

相关文章
|
21小时前
|
SQL Oracle 关系型数据库
Python连接数据库进行数据查询的操作代码
mysql数据库(mariadb) 连接数据库 首先,你需要使用MySQLdb.connect()函数建立与MySQL数据库的连接。你需要提供数据库服务器的地址(host),用户名(user),密码(passwd),以及你想要操作的数据库名称(db)。 创建Cursor对象 一旦建立了数据库连接,你可以使用连接对象的cursor()方法来创建一个cursor对象。这个方法返回一个cursor实例,你可以使用这个实例来执行SQL查询和命令。
|
28天前
|
安全 关系型数据库 API
Nuxt3 实战 (七):配置 Supabase 数据库
这篇文章介绍了如何为Nuxt项目集成Supabase数据库。文章首先阐述了选择Supabase作为Nuxt项目的数据库的理由,包括其良好的网络评价、与Nuxt的良好集成以及对用户认证和身份鉴权的支持。接着,文章详细介绍了Supabase的特点,如使用PostgreSQL作为数据库、提供完整的认证系统、支持实时数据同步和提供对象存储服务等。然后,文章指导读者如何在Nuxt项目中安装和配置Supabase,包括设置重定向策略和获取数据库访问密钥。最后,文章强调了在开发环境和生产环境中使用不同密钥的重要性。
Nuxt3 实战 (七):配置 Supabase 数据库
|
9天前
|
JavaScript 关系型数据库 MySQL
Python实战:从猎聘网获取职位信息并存入数据库
Python实战:从猎聘网获取职位信息并存入数据库
|
16天前
|
SQL 存储 关系型数据库
MySQL数据库案例实战教程:数据类型、语法与高级查询详解
MySQL数据库案例实战教程:数据类型、语法与高级查询详解
32 3
|
14天前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-条件处理程序(通过SQLSTATE指定具体的状态码,通过SQLSTATE的代码简写方式 NOT FOUND)
MySQL数据库——存储过程-条件处理程序(通过SQLSTATE指定具体的状态码,通过SQLSTATE的代码简写方式 NOT FOUND)
11 0
MySQL数据库——存储过程-条件处理程序(通过SQLSTATE指定具体的状态码,通过SQLSTATE的代码简写方式 NOT FOUND)
|
17天前
|
API 数据库 数据库管理
Flask Web开发基础:数据库与ORM实战
该文介绍了如何使用 Flask、SQLAlchemy 和 SQLite 实现数据库操作。首先,通过创建虚拟环境和安装 flask-sqlalchemy(版本2.5.1)及 sqlalchemy(版本1.4.47)来设置环境。接着,配置数据库URI,定义User和Movie模型类表示数据库表,并通过db.create_all()创建表。文章还展示了如何插入、查询、更新和删除记录,强调了db.session.commit()在保存更改中的关键作用。查询涉及filter、order_by等方法,提供了一系列示例。
300 1
|
18天前
|
数据库 SQL 存储
在编制代码时优化数据库操作性能
【6月更文挑战第3天】本文介绍了代码中可使用的SQL语句优化策略,了解并应用本文介绍的这些技巧可显著提升数据库性能。
24 2
|
1月前
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】(代码演示&可cv代码)
|
28天前
|
Cloud Native 关系型数据库 分布式数据库
【PolarDB开源】PolarDB数据迁移实战:平滑过渡至云原生数据库
【5月更文挑战第24天】本文介绍了如何平滑迁移数据至阿里云的云原生数据库PolarDB,包括迁移准备、策略选择、步骤、验证及示例代码。通过需求分析、环境准备和数据评估,选择全量、增量或在线迁移策略。使用数据导出、导入及同步工具(如DTS)完成迁移,并在完成后验证数据一致性、性能和安全。正确执行可确保业务连续性和数据完整性。
125 1
|
29天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
170 2