納西瑟斯是希臘神話裡出了名的美男子，不管是山林裡的寧芙，還是其他愛慕他的年輕人，全都被他拒絕了，後來其中一位被他傷害的人受不了，抬頭向神明祈求：

就讓他也愛上一個永遠得不到的人吧！

復仇女神涅墨西斯聽見了這個祈求，決定讓納西瑟斯也嘗嘗這種滋味。

有一天，納西瑟斯打獵打到又熱又累，走進森林深處時，正好看到一座清澈的水池，這座水池從來沒有牧人、牲畜或飛鳥靠近，就連樹枝都沒有掉進去過，平靜得像一面鏡子，他本來只是想喝口水，沒想到低頭一看，水裡竟然出現一位俊美的少年。

他笑，水裡的人也跟著笑，他靠近，對方也一起靠近，他伸手想碰對方，手指卻只碰到冰冷的池水，眼前那張臉也跟著碎成一圈圈漣漪，水面只要重新平靜下來，那個人就會再次出現在眼前，而且每個表情、每個動作都跟納西瑟斯一模一樣，他捨不得離開，只能一直守在池邊，看著一個近在眼前、卻怎麼樣都碰不到的人。

後來他的眼淚掉進水裡，再次把那張臉打散，直到這個時候，納西瑟斯才終於明白，水裡根本沒有另一個人，他愛上的一直都是自己的倒影。

但就算知道真相，他還是沒有辦法離開，最後不吃不喝，在水池旁一點一點失去力氣，等其他人來找他時，納西瑟斯已經消失了，原地只剩下一朵花。

如果我們一直覺得只做單元測試就能測到所有東西的話，那就太自以為事大錯特錯了，有些型態的 Legacy Code 不是單元測試就可以解決的，我們需要上升到更高的層次，也就是「整合測試」。

緊急事件

今日範例 是一套提供給不同健身房使用的雲端會員平台，某天下午出現了一個非常緊急的 Issue：

會員搜尋結果疑似出現其他租戶資料。

現在 A 健身房的櫃台搜尋「陳小」，結果裡面竟然混進 B 健身房會員「陳小美」的資料……

在系統裡，這不是「搜尋結果多了一筆」而已，Tenant 是客戶之間的安全邊界，只要有一筆資料越過這條線，就得先當成資料外洩事件處理，今天畫面上露出的是姓名，下一個欄位可能就是電話、緊急聯絡人或消費紀錄，而且既然同一組查詢條件能穿透，受影響的也可能不只眼前這兩個租戶。

我們必須意識到一件非常嚴重的事情，租戶隔離已經失效。

TenantId 明明就在 WHERE 裡

這個搜尋功能可以用「會員姓名」或「緊急聯絡人姓名」做模糊查詢，所以本來就需要一個 OR，兩條路徑只要其中一條符合，就應該回傳會員資料。讓我們看看現在程式碼長怎樣：

public async Task<IReadOnlyList<MemberRow>> SearchAsync(
    int tenantId,
    string? keyword)
{
    var sql = """
        SELECT m.MemberId, m.TenantId, m.Name
        FROM dbo.Members m
        WHERE m.IsActive = 1
          AND m.TenantId = @TenantId
        """;

    if (!string.IsNullOrWhiteSpace(keyword))
    {
        sql += """
          AND m.MemberId IN (
              SELECT ec.MemberId
              FROM dbo.EmergencyContacts ec
              WHERE ec.IsActive = 1
                AND ec.Name LIKE @Keyword
          )
          OR m.Name LIKE @Keyword
        ORDER BY m.MemberId;
        """;
    }

    await using var conn = new SqlConnection(_connectionString);
    var rows = await conn.QueryAsync<MemberRow>(sql, new
    {
        TenantId = tenantId,
        Keyword = $"%{keyword}%"
    });

    return rows.ToList();
}

參數化有做，@TenantId 也確實有寫進 WHERE 了，快速掃過去甚至會覺得租戶條件好端端地站在那裡，到底怎麼漏的？

我們試著把字串真的接起來就會看得比較清楚：

WHERE m.IsActive = 1
  AND m.TenantId = 101
  AND m.MemberId IN (...)
   OR m.Name LIKE N'%陳小%'

在 Transact-SQL 裡，AND 的優先順序高於 OR，所以 SQL 語法其實可以看成是：

WHERE (
        m.IsActive = 1
    AND m.TenantId = 101
    AND m.MemberId IN (...)
)
OR m.Name LIKE N'%陳小%'

這等於是會員姓名只要符合，前面的 IsActive 與 TenantId 就都不用判斷，修復之前，我們需要一支能重現外洩的回歸測試，但要測什麼？直接 Assert SQL 字串裡有沒有括號太脆弱了，改個換行或換成 Query Builder 這個測試就會壞，而且字串長得像正確答案，也不代表 SQL Server 執行後真的有隔離租戶。

所以我們真正要驗證的是可觀察結果，DAL 把 query 送進真正的 SQL Server 後，租戶 A 能不能看見租戶 B 的資料，這已經跨過單元測試的範圍了，所以我們有請今天的主角 Testcontainers。

Testcontainers 到底是什麼？

如果還不熟悉 Image、Container 與 Port，或電腦尚未完成安裝，可以先閱讀我另外整理的 Docker 基本與安裝，裡面包含 Windows、macOS 的安裝流程與基本驗證，這裡就不重新講一次了 > <

在容器普及以前，資料庫整合測試通常得共用一顆測試 DB，或要求每位開發者自己安裝 SQL Server，前者很容易被別人的測試資料污染，兩組測試同時清除 table 還會互相影響到，後者則常卡在版本、登入設定與本機環境差異，到了 CI 階段又很有可能會被退回，所以光是把可重複、可隔離的資料庫維護好，成本就非常之高。

容器技術普及之後，以 .NET 測試來說，一次整合測試的生命週期會是：

1. Testcontainers 請 Container Runtime 建立指定版本的 SQL Server。
2. 等 SQL Server 能接受連線後，取得這次 Container 的連線字串。
3. 把 schema 套進空資料庫。
4. 每支測試放入自己情境需要的 Given 資料。
5. 呼叫 production code，Assert 真正查詢結果。
6. 測試結束後銷毀 Container。

乾淨簡潔，所以現在要做到程式碼的整合測試已經比以前來的簡單了，我們今天就照著生命週期的順序，來一一建立這些所需要的 Infra。

Container 是新的，Schema 不能是假的

Container 每次都能重新建立，但 schema 不應該在每次 Container 初始化時用一大段 CREATE TABLE 字串重寫，因為這樣就會多養一份副本，本來就有獨立的 db 專案做版本控制，整合測試專案只負責把 DB 專案裡的 schema 帶進測試輸出目錄就好。

以下兩個檔案，就是這次案例會用到的 table schema，Members 與 EmergencyContacts 都把 TenantId 放進複合主鍵或外鍵，代表 MemberId 只需要在各自租戶內唯一：

CREATE TABLE dbo.Members
(
    TenantId INT NOT NULL,
    MemberId INT NOT NULL,
    IsActive BIT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_Members PRIMARY KEY (TenantId, MemberId)
);

CREATE TABLE dbo.EmergencyContacts
(
    TenantId INT NOT NULL,
    EmergencyContactId INT NOT NULL,
    MemberId INT NOT NULL,
    IsActive BIT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_EmergencyContacts
        PRIMARY KEY (TenantId, EmergencyContactId),
    CONSTRAINT FK_EmergencyContacts_Members
        FOREIGN KEY (TenantId, MemberId)
        REFERENCES dbo.Members (TenantId, MemberId)
);

在 test.csproj 用 Link 的方式，build 時把 DB project 裡的 SQL 複製到輸出目錄：

<ItemGroup>
  <Content Include="..\db\Tables\**\*.sql"
           Link="SqlSchema\Tables\%(Filename)%(Extension)"
           CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>

如此一來，Members.sql 與 EmergencyContacts.sql 仍然只需要在 DB 專案統一做版本控制，這樣 Production 修改欄位後，測試建置後取得的也會是同一份 schema。

Schema Initializer

因為 Testcontainers 建立的 container 啟動後還沒有任何 table，所以測試開始前，我們仍然要把 DB project 裡的資料表套進容器：

public sealed class SqlSchemaInitializer
{
    private static readonly string[] TableScripts =
        ["Members.sql", "EmergencyContacts.sql"];

    public async Task ApplyAsync(string connectionString, string schemaRoot)
    {
        await using var conn = new SqlConnection(connectionString);
        await conn.OpenAsync();

        foreach (var script in TableScripts)
        {
            var path = Path.Combine(schemaRoot, "Tables", script);
            var sql = await File.ReadAllTextAsync(path);
            await conn.ExecuteAsync(sql);
        }
    }
}

在TableScripts中可以看到建立順序，Members.sql 先建立，EmergencyContacts.sql 才能接上 foreign key。

Fixture：連接環境

Initializer 知道怎麼套 schema，卻不負責 SQL Server 從哪裡來，Fixture 則負責把 Container 的啟動、schema 初始化與清理串成 xUnit 能理解的生命週期。

現在讓我們先在 test 專案中安裝 Testcontainers 套件：

dotnet add package Testcontainers.MsSql

程式碼如下：

public sealed class GymCloudDbFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _db = new MsSqlBuilder(
        "mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04")
        .WithDatabase("GymCloudTest")
        .Build();

    public string ConnectionString => _db.GetConnectionString();

    public async Task InitializeAsync()
    {
        await _db.StartAsync();

        var schemaRoot = Path.Combine(
            AppContext.BaseDirectory,
            "SqlSchema");

        await new SqlSchemaInitializer().ApplyAsync(
            ConnectionString,
            schemaRoot);
    }

    public Task DisposeAsync() => _db.DisposeAsync().AsTask();
}

為了示範，這裡先用固定的 SQL Server Image tag，而不是用會隨時間漂移的 latest。

MsSqlBuilder 描述要建立的 Image 與資料庫名稱，StartAsync() 會建立 Container，並等待模組的 readiness check 通過，等 SQL Server 真的能連線後，我們才從 GetConnectionString() 取得動態連線資訊，再把剛才的 SqlSchemaInitializer 接起來，DisposeAsync() 則在測試結束後回收 Container。

Seeder：提供資料

Container 與 schema 解決了「測試要在哪裡跑」，接下來我們需要資料，Seeder 提供參數化的 InsertAsync，測試只要傳入租戶、會員編號與名稱，不需要知道 SQL 怎麼寫；真正的 Given 數值仍然留在各自測試旁邊，看到失敗時，不用再跳到另一個檔案找這支測試到底塞了什麼。

using Dapper;
using Microsoft.Data.SqlClient;

namespace test;

public class MemberSearchSeeder
{
    private readonly string _connectionString;

    public MemberSearchSeeder(string connectionString)
    {
        _connectionString = connectionString;
    }
    public Task ResetAsync()
        => ExecuteAsync( """
            DELETE FROM dbo.EmergencyContacts;
            DELETE FROM dbo.Members;
            """);

    public Task InsertMemberAsync(
        int tenantId,
        int memberId,
        string name,
        bool isActive = true)
        => ExecuteAsync(
            """
            INSERT INTO dbo.Members
                (TenantId, MemberId, IsActive, Name)
            VALUES (@TenantId, @MemberId, @IsActive, @Name);
            """,
            new { tenantId, memberId, isActive, name });

    public Task InsertEmergencyAsync(
        int tenantId,
        int emergencyContactId,
        int memberId,
        string name,
        bool isActive = true)
        => ExecuteAsync(
            """
            INSERT INTO dbo.EmergencyContacts
                (TenantId, EmergencyContactId, MemberId, IsActive, Name)
            VALUES (
                @TenantId,
                @EmergencyContactId,
                @MemberId,
                @IsActive,
                @Name);
            """,
            new
            {
                tenantId,
                emergencyContactId,
                memberId,
                isActive,
                name
            });

    private async Task ExecuteAsync(
        string sql,
        object? parameters = null)
    {
        await using var conn = new SqlConnection(_connectionString);
        await conn.ExecuteAsync(sql, parameters);
    }
}

當然只是叫做 Seeder，不代表不能直接呼叫 production code，這樣可以省很多時間，有時候還能夠順便測到意想不到的東西喔 XD

測試，啟動！

接下來我們就可以來寫測試，先還原 Bug 現場：

public sealed class MemberSearchTests
    : IClassFixture<GymCloudDbFixture>
{
    private readonly string _connectionString;
    private readonly MemberSearchSeeder _seeder;

    public MemberSearchTests(GymCloudDbFixture fixture)
    {
        _connectionString = fixture.ConnectionString;
        _seeder = new MemberSearchSeeder(fixture.ConnectionString);
    }

    [Fact]
    public async Task 會員模糊搜尋_目前會回傳所有目前租戶資料()
    {
        await _seeder.ResetAsync();
        await _seeder.InsertMemberAsync(
	        tenantId: 101, memberId: 1001, name: "林小美");
        await _seeder.InsertMemberAsync(
	        tenantId: 202, memberId: 2001, name: "陳小美");
        await _seeder.InsertEmergencyAsync(
	        tenantId: 101, emergencyContactId: 5001, memberId: 1001, name: "陳小明");
        var sut = new MemberDal(_connectionString);

        var rows = await sut.SearchAsync(
            tenantId: 101,
            keyword: "陳小");

        Assert.Equal(2, rows.Count);
}

IClassFixture<GymCloudDbFixture> 會告訴 xUnit，這個測試 class 裡的案例共用同一個 Fixture instance，xUnit 先完成 InitializeAsync()，再把 Fixture 從測試 class 的建構子傳進來，因此如果有多支測試就不必各自啟動一顆 SQL Server，昂貴的環境共用，會互相污染的資料則由每支測試先 Reset，再 Insert 自己的 Given 就好了。

從上面的測試來看，租戶 101 的林小美是透過緊急聯絡人「陳小明」命中，租戶 202 則有一位會員本人叫「陳小美」，搜尋「陳小」時，按照目前的行為應該會找到兩筆，測試應該會綠燈才對。

執行測試後就會發現 Docker Desktop 中出現了一個 testcontainers，並且它會在執行結束後自行銷毀：

好的，那麼我們現在將測試改成描述正確的行為：

[Fact]
public async Task 會員模糊搜尋_應回傳目前租戶資料()
{
    await _seeder.ResetAsync();
    await _seeder.InsertMemberAsync(
	    tenantId: 101, memberId: 1001, name: "林小美");
    await _seeder.InsertMemberAsync(
	    tenantId: 202, memberId: 2001, name: "陳小美");
    await _seeder.InsertEmergencyAsync(
	    tenantId: 101, emergencyContactId: 5001, memberId: 1001, name: "陳小明");
    var sut = new MemberDal(_connectionString);

    var rows = await sut.SearchAsync(
        tenantId: 101,
        keyword: "陳小");

    Assert.Single(rows);
    var memberInfo = rows[0];
    Assert.Equal(101, memberInfo.TenantId);
    Assert.Equal(1001, memberInfo.MemberId);
    Assert.Equal("林小美", memberInfo.Name);
}

修正前執行，Assert.Single 真的拿到兩筆，租戶 101 的林小美，以及遛進來的租戶 202 陳小美，這個紅燈要先確定不是 Container 沒啟動，也不是 schema 少了 table，而是我們要抓的跨租戶查詢被真正的資料庫重現了。

修復

接下來我們就來修正它，讓他綠燈。

修法不是把 OR 拿掉，會員姓名與緊急聯絡人本來就都要搜尋，真正要做的是把兩條搜尋路徑圈成同一組，同時讓子查詢也明確帶著租戶條件，所以語法當然很簡單只要在後面的And 加上括號就好了....

WHERE m.IsActive = 1
  AND m.TenantId = @TenantId
  AND (
        m.MemberId IN (
            SELECT ec.MemberId
            FROM dbo.EmergencyContacts ec
            WHERE ec.IsActive = 1
              AND ec.TenantId = @TenantId
              AND ec.Name LIKE @Keyword
        )
        OR m.Name LIKE @Keyword
  )

修正後再跑一次，結果就綠燈了，暫時解決客戶的問題了...還沒完，後續還要繼續重構呢！

總結

折騰了好久，今天終於介紹到整合測試了，雖然整合測試涵蓋的範圍非常廣，但由此可知，其實概念是差不多的，雖說都是測試，卻也有分使用時機。

我們感受到了 Testcontainers 的強大，而且它不只是只有 Sql 喔，還有像 kafka、Redis...諸如此類 Infra 的 Container，過去還要一個一個架設測試環境，有了它們之後我只能說...真的很香。

而雖然測試容器已經相對以前來說速度變快，環境也相對乾淨了，但是成本也還是一樣挺高的，所以「省著用」就是了 XD

另外今天看的範例 Code，不得不說真的是很多 Legacy Code 的主要型態，真的是不誇張，幾乎整個專案都是 Sql 語法四處亂飛，遙想當年光是要先把 query string 乖乖解耦合到 Repository，再慢慢收到 SP...簡直是要人命，當然過程中多虧有機會接觸到 Testcontainers 這個技術！

感恩鯨魚，讚嘆鯨魚！

明天我們繼續看：當資料庫帶來更多麻煩，我們要怎麼辦？

Reference

• Operator Precedence (Transact-SQL), Microsoft Learn
• Testcontainers for .NET
• Testcontainers for .NET：Microsoft SQL Server 模組
• Testcontainers for .NET：Best Practices
• What are SQL database projects?, Microsoft Learn
• Common MSBuild project items, Microsoft Learn
• Sharing Context between Tests, xUnit.net