如何在Github Action里集成测试SQL交互代码

民富

闲话

有段时间没更新文章了，主要是工作生活都比较忙碌，无法挤出太多时间来写作。另外，以前我的文章倾向于详尽地解释和阐述内容，比较花时间。今后我的文章会更倾向于技术分享/代码分享/思路分享，只点出关键信息，以此节省写作时间。如果读者有疑问，欢迎在评论区留言，如果我有时间会尽量回复。

正篇开始...
为什么需要集成测试SQL交互代码

因为单元测试不会覆盖SQL交互代码，通常直接mock掉。端到端测试（E2E testing) 流程太长。因此，集成测试是最佳选择。

Github Action 提供了 Service container 功能

参考文档 About service containers - GitHub Docs

You can use service containers to connect databases, web services, memory caches, and other tools to your workflow.

这个功能可以帮助在CI/CD 中集成测试数据库/web服务/缓存服务等。本质上其实就是在 workflow 中创建一个对应的docker container在后台运行，不过Github Action 的 Service container 功能可读性更好，也省了一些代码。

以SQL server 为例
    services:
      sqlserver:        
        image: mcr.microsoft.com/azure-sql-edge
        env:         
          MSSQL_SA_PASSWORD: Admin123
          ACCEPT_EULA: Y
          MSSQL_PID: Developer
        ports:         
          - 1433:1433
        options: >-
          --name sqlpoc其中 mcr.microsoft.com/azure-sql-edge 是微软官方提供的轻量级的 SQL server docker image，Azure SQL Database emulator (Preview) 就是基于这个 image 做的。

如何初始化测试数据库

主要包括创建表，stored procedure，插入测试数据等
setup.sql
-- This file contains SQL statements that will be executed after the build script.
-- This file contains SQL statements that will be executed after the build script.
--USE [master]  
--GO  

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'MySchool')
BEGIN
CREATE DATABASE [MySchool]
END
GO  

USE [MySchool]  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE procedure [dbo].[CourseExtInfo] @CourseId nvarchar(10)  
as  
select c.CourseID,c.Title,c.Credits,d.Name as DepartmentName  
from Course as c left outer join Department as d on c.DepartmentID=d.DepartmentID  
where c.CourseID=@CourseId  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
create procedure [dbo].[DepartmentInfo] @DepartmentId int,@CourseCount int output  
as  
select @CourseCount=Count(c.CourseID)  
from course as c  
where c.DepartmentID=@DepartmentId  

select d.DepartmentID,d.Name,d.Budget,d.StartDate,d.Administrator  
from Department as d  
where d.DepartmentID=@DepartmentId  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
Create PROCEDURE [dbo].[GetDepartmentsOfSpecifiedYear]   
@Year int,@BudgetSum money output  
AS  
BEGIN  
        SELECT @BudgetSum=SUM([Budget])  
  FROM [MySchool].[dbo].[Department]  
  Where YEAR([StartDate])=@Year   

SELECT [DepartmentID]  
      ,[Name]  
      ,[Budget]  
      ,[StartDate]  
      ,[Administrator]  
  FROM [MySchool].[dbo].[Department]  
  Where YEAR([StartDate])=@Year  

END  
GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Course](
    [CourseID] [nvarchar](10) NOT NULL,  
    [Year] [smallint] NOT NULL,  
    [Title] [nvarchar](100) NOT NULL,  
    [Credits] [int] NOT NULL,  
    [DepartmentID] [int] NOT NULL,  
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED   
(  
[CourseID] ASC,  
[Year] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,  
[Name] [nvarchar](50) NOT NULL,  
[Budget] [money] NOT NULL,  
[StartDate] [datetime] NOT NULL,  
[Administrator] [int] NULL,  
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED   
(  
[DepartmentID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,  
[LastName] [nvarchar](50) NOT NULL,  
[FirstName] [nvarchar](50) NOT NULL,  
[HireDate] [datetime] NULL,  
[EnrollmentDate] [datetime] NULL,  
CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED   
(  
[PersonID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[StudentGrade](
    [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,  
    [CourseID] [nvarchar](10) NOT NULL,  
    [StudentID] [int] NOT NULL,  
    [Grade] [decimal](3, 2) NOT NULL,  
     CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED   
(  
    [EnrollmentID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]  

GO  

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
create view [dbo].[EnglishCourse]  
as  
select c.CourseID,c.Title,c.Credits,c.DepartmentID  
from Course as c join Department as d on c.DepartmentID=d.DepartmentID  
where d.Name=N'English'  

GO  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)  
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)  
SET IDENTITY_INSERT [dbo].[Department] ON   

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)  
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)  
SET IDENTITY_INSERT [dbo].[Department] OFF  
SET IDENTITY_INSERT [dbo].[Person] ON   

INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (1, N'Hu', N'Nan', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (2, N'Norman', N'Laura', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (3, N'Olivotto', N'Nino', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (4, N'Anand', N'Arturo', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (5, N'Jai', N'Damien', NULL, CAST(0x0000A0BF00000000 AS DateTime))  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (6, N'Holt', N'Roger', CAST(0x000097F100000000 AS DateTime), NULL)  
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (7, N'Martin', N'Randall', CAST(0x00008B1A00000000 AS DateTime), NULL)  
SET IDENTITY_INSERT [dbo].[Person] OFF  
SET IDENTITY_INSERT [dbo].[StudentGrade] ON   

INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, N'C1045', 1, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, N'C1045', 2, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, N'C1045', 3, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, N'C1045', 4, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, N'C1045', 5, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, N'C1061', 1, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, N'C1061', 3, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, N'C1061', 4, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, N'C1061', 5, CAST(1.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, N'C2021', 1, CAST(2.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, N'C2021', 2, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, N'C2021', 4, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, N'C2021', 5, CAST(3.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, N'C2042', 1, CAST(2.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, N'C2042', 2, CAST(3.50 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, N'C2042', 3, CAST(4.00 AS Decimal(3, 2)))  
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, N'C2042', 5, CAST(3.00 AS Decimal(3, 2)))  
SET IDENTITY_INSERT [dbo].[StudentGrade] OFF  
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])  
REFERENCES [dbo].[Department] ([DepartmentID])  
GO  
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]  
GO  
ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])  
REFERENCES [dbo].[Person] ([PersonID])  
GO  
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]  
GO如何在 GitHub action 的 workflow 中将setup.sql 导入测试数据库中

• 从项目代码中把 setup.sql 拷贝到测试数据库所在的 container 里
  > docker cp "./db/sql/setup.sql" sqlpoc:/setup.sql
  
• 连接测试数据库并执行这个sql脚本
  > docker exec sqlpoc /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Admin123 -i "/setup.sql"
  

踩坑

SQL Server container 启动到服务可用有短暂的几秒钟间隔，因此马上连接会显示失败。
解决办法：需要多次尝试连接测试数据库
如bash脚本
for i in {1..50};
    do
        docker exec sqlpoc /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Admin123 -i "/setup.sql"
        if [ $? -eq 0 ]
        then
            echo "setup.sql completed"
            break
        else
            echo "not ready yet..."
            sleep 1
        fi
    done记得给对应的脚本加执行权限
git update-index --chmod=+x .\setup-db.sh
也可以尝试使用 Github Action 的 Retry Step - GitHub Marketplace 来实现

    - name: Copy set-up.sql
      run: docker cp "./db/sql/setup.sql" sqlpoc:/setup.sql
    - name: Set up DB and tables
      uses: nick-fields/retry@v2
      with:
        retry_wait_seconds: 2
        timeout_seconds: 30
        max_attempts: 10
        command: docker exec sqlpoc /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Admin123 -i "/setup.sql"
其他方案：
微软官方还在推行另一种方式来管理 DB 项目，目前还是 Preview 阶段，有兴趣的读者可以自己尝试看看
Use SDK-style projects with the SQL Database Projects extension - Azure Data Studio
也可以使用这种方案初始化我们的测试数据库。

C# SQL 交互代码

代码主要参考了官方文档的示例代码
using System;
using System.Data;
using Microsoft.Data.SqlClient;

namespace DemoSqlClient
{
    public class DemoSqlUtility
    {
        // Set the connection, command, and then execute the command with non query.  
        public static Int32 ExecuteNonQuery(string connectionString, string commandText,
            CommandType commandType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    // There're three command types: StoredProcedure, Text, TableDirect. The TableDirect   
                    // type is only for OLE DB.   
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(parameters);

                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // Set the connection, command, and then execute the command and only return one value.  
        public static Object ExecuteScalar(string connectionString, string commandText,
            CommandType commandType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    cmd.CommandType = commandType;
                    cmd.Parameters.AddRange(parameters);

                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        // Set the connection, command, and then execute the command with query and return the reader.  
        public static SqlDataReader ExecuteReader(string connectionString, string commandText,
            CommandType commandType, params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);

            using (SqlCommand cmd = new SqlCommand(commandText, conn))
            {
                cmd.CommandType = commandType;
                cmd.Parameters.AddRange(parameters);

                conn.Open();
                // When using CommandBehavior.CloseConnection, the connection will be closed when the   
                // IDataReader is closed.  
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return reader;
            }
        }
    }
}

测试代码

不仅测试了普通的 SQL 语句，还测试了 Stored Procedure
using DemoSqlClient;
using FluentAssertions;
using Microsoft.Data.SqlClient;
using System.Data;

namespace IntegrationTest
{
    public class SqlIntegrationTest
    {
#pragma warning disable MEN002 // Line is too long
        private const string ConnectionStringToTestDB = "Server=tcp:localhost;Authentication=Sql Password;Database=MySchool;User=sa;Password=Admin123;TrustServerCertificate=true";
#pragma warning restore MEN002 // Line is too long

        [Fact]
        public void ReadViaTextCommand()
        {
            // Act
            var minHitParam = new SqlParameter("@MinCredits", SqlDbType.Int, 100);
            minHitParam.Value = 3;
            SqlDataReader sqlDataReader = DemoSqlUtility.ExecuteReader(
                ConnectionStringToTestDB,
                @"SELECT TOP (1000) [CourseID],[Title]
                  FROM [MySchool].[dbo].[Course]
                  WHERE [Credits] > @MinCredits",
                CommandType.Text,
                minHitParam);

            var result = new List<string>();
            using (SqlDataReader reader = sqlDataReader)
            {
                while (reader.Read())
                {
                    result.Add(reader.GetString(0) + "," + reader.GetString(1));
                }
            }

            // Assert
            result.Should().HaveCount(3);
            result.Should().BeEquivalentTo(new[]
                {
                "C1045,Calculus",
                "C1061,Physics",
                "C2042,Literature"
            });
        }

        [Fact]
        public void ReadViaStoredProcedure()
        {
            // Act
            var courseIdParam = new SqlParameter("@CourseId", SqlDbType.NVarChar, 10);
            courseIdParam.Value = "C1045";

            SqlDataReader sqlDataReader = DemoSqlUtility.ExecuteReader(
                ConnectionStringToTestDB,
                "[dbo].[CourseExtInfo]",
                CommandType.StoredProcedure,
                courseIdParam);

            var result = new List<string>();
            using (SqlDataReader reader = sqlDataReader)
            {
                while (reader.Read())
                {
                    result.Add(reader.GetString(0) + "," + reader.GetString(1) + "," + reader.GetInt32(2) + "," + reader.GetString(3));
                }
            }

            // Assert
            result.Should().HaveCount(1);
            result.Should().BeEquivalentTo(new[] { "C1045,Calculus,4,Mathematics" });
        }

        [Fact]
        public void InsertData()
        {
            // Act
            var courseIdParam = new SqlParameter("@CourseId", SqlDbType.NVarChar, 10);
            courseIdParam.Value = "C1045";

            var affectedRows = DemoSqlUtility.ExecuteNonQuery(
                ConnectionStringToTestDB,
                @"INSERT INTO [MySchool].[dbo].[Person] ([LastName]
                          ,[FirstName]
                          ,[HireDate]
                          ,[EnrollmentDate]) VALUES('Li', 'Si', '2023-01-01 00:00:00.000', '2023-01-01 00:00:00.000')",
                CommandType.Text,
                courseIdParam);

            // Assert
            affectedRows.Should().Be(1);
        }
    }
}

最终效果

代码仓库 GitHub - freewheel70/PublicDemoSqlClient
pipeline 成功


第一次连接DB失败，重试后成功


三个测试都成功


Bonus

项目根目录下有多个脚本文件 GitHub - freewheel70/PublicDemoSqlClient
可以本地运行 setup-db-local.ps1 （Windows 用户）或者  setup-db-local.sh （Mac/Linux用户） 来创建本地的 SQL server container，然后运行测试。

<hr/>
参考文档：
About service containers - GitHub Docs
https://github.com/marketplace/actions/retry-step
SqlCommand Class (System.Data.SqlClient)
Introducing the Azure SQL Database emulator - Azure SQL Database
Use SDK-style projects with the SQL Database Projects extension - Azure Data Studio