• 如何解决SQL文件管理的难题?
  • 发布于 2个月前
  • 600 热度
    0 评论
高效的管理软件工程中的SQL文件,不仅影响软件研发团队的效能,还影响系统的稳定性。在过往的经历中,SQL文件在团队中通常没有得到很好的管理。以下是我的经验。

SQL文件管理是如何影响软件研发团队的效能和系统稳定性的
想象一下一名后端开发,当涉及到数据库方面的业务开发时,他的工作流程如下:
1. 在本地启动开发环境:打开IDE、启动本地的DB;
2. 写业务代码,将业务代码转成SQL文件;
3. 手工在本地DB中执行SQL文件;
4. 本地启动程序,然后调用服务,验证SQL文件的逻辑和业务逻辑是否工作正常;
5. 如果不正常,重复2,3,4。这个过程是开发人员最低效的地方;
6. 如果不正常,则另执行一条修复的SQL。至此他已经执行2条SQL,接下来还可能增加,直到他完成业务开发。

最后,开发人员会回溯他到底执行了哪些SQL语句,并整理出一个最终的SQL文件给到运维(开发还经常整理错误)。并告诉运维,这个SQL需要在测试环境上执行。

当运维在测试环境执行该SQL时,发现报错了,然后他们反馈给开发。开发第一反应通常是:我在本地执行好好的啊。然后开发与运维共同“联调”。好在花了一天时间,他们调通了,原来是开发漏了一条SQL语句。同时他们也发现开发本地环境的DB版本不同,且SQL schema与测试环境不一致。这是另一个导致他们花这么多时间调bug的原因。

但是,接下来的事情也不是一帆风顺。他们在生产遇到了同样的问题。还因此产生P0事故。不同的人看待以上工作流程会有不同的看法。有人觉得是开发人员的能力的问题,有人觉得是运维的能力的问题,也有人觉得是SRE的能力的问题,还有人觉得是项目管理流程的问题等等。

这时,通常会有人提出:我们需要一个SQL管理平台,以提高研发效率和降低对稳定的影响。但是,我们真的需要另搭建一个SQL管理平台吗?SQL管理平台为什么能解决以上问题,又解决了什么问题?这是我们在“搭建SQL管理平台”前必须考虑清楚的。

开发人员解决问题的方式
在过往的经历中,经常有开发人员要求能在本地连接到测试环境DB的权限。即开发人员希望能在本地直接云端的测试环境DB,方便开发。作为运维团队的Leader,面对开发团队大量的“投诉”的压力,你会如何处理?

我是拒绝的。理由是
1. 开发团队会直接把测试环境DB污染。将来DB的schema会失控;
2. 测试环境不再是一个可信的环境。

这时,开发人员和测试人员又提出再搭建一套开发环境。我又拒绝了。这是另一个话题,关注我,我将来可能会说到。

SQL文件的管理维度
对于SQL文件的管理,需要从三个维度来考虑:
维度一:SQL文件的来源
SQL文件的来源可能有以下几种:
1. SQL文件来自哪里(From where)
2. SQL文件放在哪里(Where)
3. SQL文件该由谁及如何执行(Who and How )
4. 来自程序员、DBA等工程师的手写;
5. 来自自动化DDL生成工具。

SQL文件的产生,应该尽量避免手写。比如从实体类到SQL的映射。同时,如果使用DDL工具,这个工具应该有自动生成变更的能力,而不只是有生成create语句的能力。要实现自动DDL的功能,需要编程语言及相关的库来支持,比如Java的JPA+Hibernate。而手写的SQL,在现实情况下是无法避免的。

维度二:SQL文件的存放地点
SQL文件的存放地点可能有以下几种:
1. 程序员或者DBA的本地开发环境,直到他离职;
2. 放在一些SQL执行平台上;
3. 放在公司内部的文档平台上;
4. 放在一个独立的Git仓库里;
5. 与程序代码放在同一个单仓库。

不论存放在哪里,都必须支持SQL文件的版本化。也就是数据库中的schema应该是有版本的。版本化使不同环境之间的数据变更的执行有依据。

维度三:SQL文件由谁执行,以及执行方式
通常SQL的执行方式决定了SQL文件的执行人。可能有以下几种方式:
1. 人工登录到DB执行;
2. 由于SQL执行工具自动执行。行业普遍希望有一个DB平台来实现这一目标。

虽然是三个维度,在实践过程,通常是同时考虑的。接下来,我们来展示一种高效的SQL管理方式。

一种高效的管理方式
以下,我们以Java工程为例,展示一种高效的管理方式。它不仅代表的是一种管理方式,也代表的是一种高效的开发习惯。
我们使用以下技术栈:
1. 构建工具:Bazel;
2. 版本控制:Git;
3. JPA实现:Ebean。Ebean是一款比Hibernate设计更优秀的JPA实现;
4. 自动生成DDL的工具:Ebean ddl generator;
5. 自动化DB集成测试库:Testcontainers。用于自动化测试DDL文件。

以下是具体的工作步骤:
步骤一:开发人员根据业务需求,写业务实体类:
@Table(name = "cp_schedules")  
@Entity  
public class ScheduleRule implements Reassignable {
    @Id  
    @GeneratedValue(strategy = GenerationType.AUTO)  
    private long id;
    
    @Column(name = "layers")  
    @DbJson  
    private ScheduleLayers layers = new ScheduleLayers();
    ...
}
步骤二,执行ddl命令生成DDL文件。命令如下:
bazel run -- //tools/rules_ebean/src/main/java/internal:ebean_ddl -p=$PWD/repository-impl/src/main/sql -g="codes.showme.domain" 
注:这是执行一个Bazel的自定义的rule的命令。它的作用是执行Ebean ddl generator,生成DDL文件到$PWD/repository-impl/src/main/sql目录。类似Maven的一个生成DDL的插件。

该自定义rule的逻辑如下:
1. 在本地启动一个临时数据库实例;
2. 将之前的DDL文件传递给Ebean;
3. Ebean根据配置,生成DDL文件;
4. Ebean执行这些DDL文件,对sql文件进行冒烟测试;
5. 关闭并删除该临时数据库实例。

注意,数据库版本应该与生产环境一致。

代码大概如下:
try (PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:12.8")  
        .withDatabaseName(TESTS_DB)  
        .withUsername("postgres")  
        .withPassword("postgres")  
  
) {
    DatabaseConfig config = new DatabaseConfig();
    dbMigration.setMigrationPath(MIGRATION_PATH);
    dbMigration.setPathToResources(sqlBasePath);  
    // 堆代码 duidaima.com
    // 生成一个完整的ddl文件,方便测试
    String generateInitMigration = dbMigration.generateInitMigration();  
    // 生成版本化的ddl文件
    String reuslt = dbMigration.generateMigration();
    ...
    // 执行sql,等于执行冒烟测试
    MigrationRunner runner = new MigrationRunner(migrationConfig);  
    runner.run(datasource);
}
最终生成的文件和目录下如:

步骤三:写集成测试代码。单元测试并不能测试到SQL文件与真实数据库的集成。所以,需要写集成测试。大概逻辑如下:
//通过testcontainers启动一个postgresql的数据库实例
@ClassRule  
public static PostgreSQLContainer postgreSQLContainer = new PostgreSQLContainer("postgres:12.8")  
        .withDatabaseName(TESTS_DB)  
        .withUsername("sa")  
        .withPassword("sa");
// 配置ebean
MigrationConfig migrationConfig = new MigrationConfig();  
        migrationConfig.setDbSchema("xxx");  
        migrationConfig.setMetaTable("xxx");  
        migrationConfig.setMigrationPath("classpath:main/sql");  
//执行sql,初始化数据库schema
MigrationRunner runner = new MigrationRunner(migrationConfig);  
runner.run(datasource);

// 保存实体数据到数据库
ScheduleRule scheduleRule = new ScheduleRule();  
scheduleRule.setName("sre");
scheduleRule.save();

// 验证结果
Assert.assertEquals(1l, scheduleRule.getId());
除了针对DB操作写集成测试,开发也可以针对Web服务进行集成测试。可以看出,以上步骤完全是在开发人员本地开发环境完成。不需要发布到测试环境验证,不需要运维人员协助。将来,运维或者DBA在准备部署生产环境时,只需要对比schema的版本的不同,就了解需要执行哪些SQL文件了。

以上SQL文件管理方式为什么高效:
1. SQL文件与业务源代码放在一起,减少不同组织、人员之间的信息传递成本;
2. SQL版本化自动化,避免人工总结最终SQL的出错概率和成本;
3. SQL文件自动化测试,减少SQL文件低级错误的概率;
4. 避免不同环境的DB的版本不一致,减少因不一致导致的故障的可能性;
5. 自动化DDL生成,减小人工转换出错的概率和转换成本;
6. 节约了本地开发环境的搭建时间。

但是,以上管理方式目前还没有覆盖到SQL文件在不同环境的执行。本文不再讨论,因为它涉及更多的是各个公司的现实情况。

异类:只针对生产环境的SQL文件
现实中,存在一些临时的,针对生产环境的SQL文件。比如修改某个业务在表中的值。以实现某个业务目标。

这些SQL文件如何管理?
首先,我们应该意识到,对于生产环境的任何变更,都应该有记录。这些记录都应该能在将来都能被发现。
如果我们有SQL管理平台,那么,除了SQL管理平台有记录,发布平台应该也有这次变更的记录。

如果我们没有SQL管理平台,至少也要将这些SQL放到Git仓库中,并在发布平台上手工将变更记录与该Git仓库的commit关联起来。


(全文完)
用户评论