{"id":"9f08abac-2c34-4973-805e-5a36f50b37d4","shortId":"JTkygY","kind":"skill","title":"dotnet-testing-advanced-testcontainers-database","tagline":"使用 Testcontainers 進行容器化資料庫測試的專門技能。當需要測試真實資料庫行為、使用 SQL Server/PostgreSQL/MySQL 容器、測試 EF Core/Dapper 時使用。涵蓋容器啟動、資料庫遷移、測試隔離、容器共享等。\nMake sure to use this skill whenever the user mentions Testcontainers database, SQL Server container, PostgreSQL container, EF Core integration test, Da","description":"# Testcontainers 資料庫整合測試指南\n\n## EF Core InMemory 的限制\n\n在選擇測試策略前，必須了解 EF Core InMemory 資料庫的重大限制：\n\n### 1. 交易行為與資料庫鎖定\n\n- **不支援資料庫交易 (Transactions)**：`SaveChanges()` 後資料立即儲存，無法進行 Rollback\n- **無資料庫鎖定機制**：無法模擬並發 (Concurrency) 情境下的行為\n\n### 2. LINQ 查詢差異\n\n- **查詢翻譯差異**：某些 LINQ 查詢（複雜 GroupBy、JOIN、自訂函數）在 InMemory 中可執行，但轉換成 SQL 時可能失敗\n- **Case Sensitivity**：InMemory 預設不區分大小寫，但真實資料庫依賴校對規則 (Collation)\n- **效能模擬不足**：無法模擬真實資料庫的效能瓶頸或索引問題\n\n### 3. 資料庫特定功能\n\nInMemory 模式無法測試：預存程序、Triggers、Views、外鍵約束、檢查約束、資料類型精確度、Concurrency Tokens 等。\n\n**結論**：當需要驗證複雜交易邏輯、並發處理、資料庫特定行為時，應使用 Testcontainers 進行整合測試。\n\n## Testcontainers 核心概念\n\nTestcontainers 是一個測試函式庫，提供輕量好用的 API 來啟動 Docker 容器，專門用於整合測試。\n\n### 核心優勢\n\n1. **真實環境測試**：使用真實資料庫，測試實際 SQL 語法與資料庫限制\n2. **環境一致性**：確保測試環境與正式環境使用相同服務版本\n3. **清潔測試環境**：每個測試有獨立乾淨的環境，容器自動清理\n4. **簡化開發環境**：開發者只需 Docker，不需安裝各種服務\n\n## 必要套件\n\n```xml\n<ItemGroup>\n  <!-- 測試框架 -->\n  <PackageReference Include=\"Microsoft.NET.Test.Sdk\" Version=\"18.3.0\" />\n  <PackageReference Include=\"xunit\" Version=\"2.9.3\" />\n  <PackageReference Include=\"xunit.runner.visualstudio\" Version=\"3.1.5\" />\n  <PackageReference Include=\"AwesomeAssertions\" Version=\"9.4.0\" />\n\n  <!-- Testcontainers 核心套件 -->\n  <PackageReference Include=\"Testcontainers\" Version=\"4.11.0\" />\n\n  <!-- 資料庫容器 -->\n  <PackageReference Include=\"Testcontainers.PostgreSql\" Version=\"4.11.0\" />\n  <PackageReference Include=\"Testcontainers.MsSql\" Version=\"4.11.0\" />\n\n  <!-- Entity Framework Core -->\n  <PackageReference Include=\"Microsoft.EntityFrameworkCore\" Version=\"9.0.0\" />\n  <PackageReference Include=\"Microsoft.EntityFrameworkCore.SqlServer\" Version=\"9.0.0\" />\n  <PackageReference Include=\"Npgsql.EntityFrameworkCore.PostgreSQL\" Version=\"9.0.0\" />\n\n  <!-- Dapper (可選) -->\n  <PackageReference Include=\"Dapper\" Version=\"2.1.72\" />\n  <PackageReference Include=\"Microsoft.Data.SqlClient\" Version=\"7.0.0\" />\n</ItemGroup>\n```\n\n> **重要**：使用 `Microsoft.Data.SqlClient` 而非舊版 `System.Data.SqlClient`，提供更好的效能與安全性。\n\n## 環境需求\n\n- Windows 10 版本 2004+，啟用 WSL 2，8GB RAM（建議 16GB+），64GB 磁碟空間\n- Docker Desktop 建議設定：Memory 6GB、CPUs 4 cores、Swap 2GB、Disk 64GB\n\n## 基本容器操作模式\n\n使用 `IAsyncLifetime` 管理容器生命週期，在 `InitializeAsync` 中啟動容器並建立 DbContext，在 `DisposeAsync` 中釋放資源。支援 PostgreSQL（`PostgreSqlBuilder`）和 SQL Server（`MsSqlBuilder`）。\n\n> 完整程式碼範例請參考 [references/container-basics.md](references/container-basics.md)\n\n## Collection Fixture 模式：容器共享\n\n在大型專案中，每個測試類別都建立新容器會遇到嚴重的效能瓶頸。Collection Fixture 讓所有測試類別共享同一個容器，**測試執行時間可減少約 67%**。\n\n包含 SQL 腳本外部化策略（將 SQL 檔案從 C# 程式碼分離）與 Wait Strategy 最佳實務。\n\n> 完整 Collection Fixture 實作、SQL 腳本外部化與 Wait Strategy 範例請參考 [references/collection-fixture-and-scripts.md](references/collection-fixture-and-scripts.md)\n\n## EF Core 進階功能測試\n\n涵蓋 Include/ThenInclude 多層關聯查詢、AsSplitQuery 避免笛卡兒積、N+1 查詢問題驗證、AsNoTracking 唯讀查詢最佳化等完整測試範例。\n\n> 完整程式碼範例請參考 [references/orm-advanced-testing.md](references/orm-advanced-testing.md#ef-core-進階功能測試)\n\n## Dapper 進階功能測試\n\n涵蓋基本 CRUD 測試類別設置、QueryMultiple 一對多關聯處理、DynamicParameters 動態查詢建構等完整測試範例。\n\n> 完整程式碼範例請參考 [references/orm-advanced-testing.md](references/orm-advanced-testing.md#dapper-進階功能測試)\n\n## Repository Pattern 設計原則\n\n### 介面分離原則 (ISP) 的應用\n\n- `IProductRepository`：基礎 CRUD 操作介面（GetAll、GetById、Add、Update、Delete）\n- `IProductByEFCoreRepository`：EF Core 特有進階功能（SplitQuery、BatchUpdate、NoTracking）\n- `IProductByDapperRepository`：Dapper 特有進階功能（多表查詢、動態參數、預存程序）\n\n### 設計優勢\n\n1. **單一職責原則 (SRP)**：每個介面專注於特定職責\n2. **介面隔離原則 (ISP)**：使用者只需依賴所需的介面\n3. **依賴反轉原則 (DIP)**：高層模組依賴抽象而非具體實作\n4. **測試隔離性**：可針對特定功能進行精準測試\n\n## 常見問題處理\n\n### Docker 容器啟動失敗\n\n```bash\n# 檢查連接埠是否被佔用\nnetstat -an | findstr :5432\n\n# 清理未使用的映像檔\ndocker system prune -a\n```\n\n### ContainerNotRunningException（4.8.0+ 新增）\n\nTestcontainers 4.8.0 起，預設等待策略改為等待容器進入 **Running** 狀態。若容器未能正常啟動，會拋出此例外。常見原因：映像檔名稱錯誤、Docker 資源不足、連接埠衝突。\n\n### 記憶體不足問題\n\n- 調整 Docker Desktop 記憶體配置\n- 限制同時執行的容器數量\n- 使用 Collection Fixture 共享容器\n\n### 測試資料隔離\n\n在 `Dispose` 中按照外鍵約束順序清理資料（OrderItems → Orders → Products → Categories）。\n\n## 輸出格式\n\n- 產生使用 Testcontainers 的 xUnit 整合測試類別（.cs 檔案）\n- 包含 IAsyncLifetime 容器生命週期管理程式碼\n- 包含 Collection Fixture 共享容器設定\n- 產生外部化 SQL 腳本檔案（.sql）與對應的 .csproj 設定\n- 包含 Repository Pattern 介面與實作範例\n\n## 參考資源\n\n### 原始文章\n\n本技能內容提煉自「老派軟體工程師的測試修練 - 30 天挑戰」系列文章：\n\n- **Day 20 - Testcontainers 初探：使用 Docker 架設測試環境**\n  - 鐵人賽文章：https://ithelp.ithome.com.tw/articles/10376401\n  - 範例程式碼：https://github.com/kevintsengtw/30Days_in_Testing_Samples/tree/main/day20\n\n- **Day 21 - Testcontainers 整合測試：MSSQL + EF Core 以及 Dapper 基礎應用**\n  - 鐵人賽文章：https://ithelp.ithome.com.tw/articles/10376524\n  - 範例程式碼：https://github.com/kevintsengtw/30Days_in_Testing_Samples/tree/main/day21\n\n### 官方文件\n\n- [Testcontainers 官方網站](https://testcontainers.com/)\n- [Testcontainers for .NET](https://dotnet.testcontainers.org/)\n- [Testcontainers for .NET / Modules](https://dotnet.testcontainers.org/modules/)\n\n### 相關技能\n\n- `dotnet-testing-advanced-testcontainers-nosql` - NoSQL 容器測試（MongoDB、Redis）\n- `dotnet-testing-advanced-webapi-integration-testing` - 完整 WebAPI 整合測試\n- `dotnet-testing-advanced-aspnet-integration-testing` - ASP.NET Core 基礎整合測試\n- `dotnet-testing-advanced-xunit-upgrade-guide` - xUnit v3 升級指南（含 Testcontainers.XunitV3 整合說明）\n\n> **xUnit v3 使用者注意**：搭配 xUnit v3 時，可使用 `Testcontainers.XunitV3`（4.9.0）套件自動管理容器生命週期，取代手動實作 `IAsyncLifetime`。","tags":["dotnet","testing","advanced","testcontainers","database","agent","skills","kevintsengtw","agent-skills","ai-assisted-development","copilot-skills","csharp"],"capabilities":["skill","source-kevintsengtw","skill-dotnet-testing-advanced-testcontainers-database","topic-agent-skills","topic-ai-assisted-development","topic-copilot-skills","topic-csharp","topic-dotnet","topic-dotnet-testing","topic-github-copilot","topic-integration-testing","topic-testing","topic-unit-testing","topic-xunit"],"categories":["dotnet-testing-agent-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/kevintsengtw/dotnet-testing-agent-skills/dotnet-testing-advanced-testcontainers-database","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add kevintsengtw/dotnet-testing-agent-skills","source_repo":"https://github.com/kevintsengtw/dotnet-testing-agent-skills","install_from":"skills.sh"}},"qualityScore":"0.461","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 23 github stars · SKILL.md body (5,175 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-04-24T13:02:25.288Z","embedding":null,"createdAt":"2026-04-18T23:04:16.961Z","updatedAt":"2026-04-24T13:02:25.288Z","lastSeenAt":"2026-04-24T13:02:25.288Z","tsv":"'+1':241 '/)':430,436 '/articles/10376401':402 '/articles/10376524':420 '/kevintsengtw/30days_in_testing_samples/tree/main/day20':406 '/kevintsengtw/30days_in_testing_samples/tree/main/day21':424 '/modules/)':443 '1':57,125,296 '10':153 '16gb':162 '2':69,131,158,300 '20':393 '2004':155 '21':408 '2gb':174 '3':94,134,304 '30':389 '4':138,171,308 '4.8.0':326,329 '4.9.0':497 '5432':319 '64gb':163,176 '67':208 '6gb':169 '8gb':159 'add':279 'advanc':4,448,458,468,478 'api':119 'asnotrack':243 'asp.net':472 'aspnet':469 'assplitqueri':238 'bash':314 'batchupd':287 'c':215 'case':86 'categori':358 'collat':91 'collect':198,204,222,348,371 'concurr':67,104 'contain':37,39 'containernotrunningexcept':325 'core':41,48,54,172,233,250,284,413,473 'core/dapper':17 'cpus':170 'crud':255,275 'cs':365 'csproj':379 'da':44 'dapper':252,265,290,415 'dapper-進階功能測試':264 'databas':6,34 'day':392,407 'dbcontext':184 'delet':281 'desktop':166,344 'dip':306 'disk':175 'dispos':353 'disposeasync':186 'docker':121,141,165,312,321,338,343,397 'dotnet':2,446,456,466,476 'dotnet-testing-advanced-aspnet-integration-test':465 'dotnet-testing-advanced-testcontainers-databas':1 'dotnet-testing-advanced-testcontainers-nosql':445 'dotnet-testing-advanced-webapi-integration-test':455 'dotnet-testing-advanced-xunit-upgrade-guid':475 'dotnet.testcontainers.org':435,442 'dotnet.testcontainers.org/)':434 'dotnet.testcontainers.org/modules/)':441 'dynamicparamet':259 'ef':16,40,47,53,232,249,283,412 'ef-core-進階功能測試':248 'findstr':318 'fixtur':199,205,223,349,372 'getal':277 'getbyid':278 'github.com':405,423 'github.com/kevintsengtw/30days_in_testing_samples/tree/main/day20':404 'github.com/kevintsengtw/30days_in_testing_samples/tree/main/day21':422 'groupbi':77 'guid':481 'iasynclifetim':179,368,500 'include/theninclude':236 'initializeasync':182 'inmemori':49,55,81,88,96 'integr':42,460,470 'iproductbydapperrepositori':289 'iproductbyefcorerepositori':282 'iproductrepositori':273 'isp':271,302 'ithelp.ithome.com.tw':401,419 'ithelp.ithome.com.tw/articles/10376401':400 'ithelp.ithome.com.tw/articles/10376524':418 'join':78 'linq':70,74 'make':23 'memori':168 'mention':32 'microsoft.data.sqlclient':147 'modul':440 'mongodb':453 'mssql':411 'mssqlbuilder':194 'n':240 'net':433,439 'netstat':316 'nosql':450,451 'notrack':288 'order':356 'orderitem':355 'pattern':268,383 'postgresql':38,189 'postgresqlbuild':190 'product':357 'prune':323 'querymultipl':257 'ram':160 'redi':454 'references/collection-fixture-and-scripts.md':230,231 'references/container-basics.md':196,197 'references/orm-advanced-testing.md':246,247,262,263 'repositori':267,382 'rollback':64 'run':332 'savechang':61 'sensit':87 'server':36,193 'server/postgresql/mysql':13 'skill':28 'skill-dotnet-testing-advanced-testcontainers-database' 'source-kevintsengtw' 'splitqueri':286 'sql':12,35,84,129,192,210,213,225,375,377 'srp':298 'strategi':219,228 'sure':24 'swap':173 'system':322 'system.data.sqlclient':149 'test':3,43,447,457,461,467,471,477 'testcontain':5,8,33,45,112,114,116,328,361,394,409,426,431,437,449 'testcontainers.com':429 'testcontainers.com/)':428 'testcontainers.xunitv3':486,496 'token':105 'topic-agent-skills' 'topic-ai-assisted-development' 'topic-copilot-skills' 'topic-csharp' 'topic-dotnet' 'topic-dotnet-testing' 'topic-github-copilot' 'topic-integration-testing' 'topic-testing' 'topic-unit-testing' 'topic-xunit' 'transact':60 'trigger':99 'updat':280 'upgrad':480 'use':26 'user':31 'v3':483,489,493 'view':100 'wait':218,227 'webapi':459,463 'whenev':29 'window':152 'wsl':157 'xml':144 'xunit':363,479,482,488,492 '一對多關聯處理':258 '不支援資料庫交易':59 '不需安裝各種服務':142 '並發處理':109 '中可執行':82 '中啟動容器並建立':183 '中按照外鍵約束順序清理資料':354 '中釋放資源':187 '交易行為與資料庫鎖定':58 '介面分離原則':270 '介面與實作範例':384 '介面隔離原則':301 '以及':414 '但真實資料庫依賴校對規則':90 '但轉換成':83 '使用':7,11,146,178,347,396 '使用真實資料庫':127 '使用者只需依賴所需的介面':303 '使用者注意':490 '來啟動':120 '依賴反轉原則':305 '共享容器':350 '共享容器設定':373 '初探':395 '動態參數':293 '動態查詢建構等完整測試範例':260 '包含':209,367,370,381 '升級指南':484 '原始文章':386 '參考資源':385 '取代手動實作':499 '可使用':495 '可針對特定功能進行精準測試':310 '含':485 '和':191 '唯讀查詢最佳化等完整測試範例':244 '啟用':156 '單一職責原則':297 '在':80,181,185,352 '在大型專案中':202 '在選擇測試策略前':51 '基本容器操作模式':177 '基礎':274 '基礎應用':416 '基礎整合測試':474 '外鍵約束':101 '多層關聯查詢':237 '多表查詢':292 '天挑戰':390 '套件自動管理容器生命週期':498 '完整':221,462 '完整程式碼範例請參考':195,245,261 '官方文件':425 '官方網站':427 '容器':14,122 '容器共享':201 '容器共享等':22 '容器啟動失敗':313 '容器測試':452 '容器生命週期管理程式碼':369 '容器自動清理':137 '實作':224 '將':212 '專門用於整合測試':123 '常見原因':336 '常見問題處理':311 '建議':161 '建議設定':167 '後資料立即儲存':62 '必要套件':143 '必須了解':52 '情境下的行為':68 '應使用':111 '提供更好的效能與安全性':150 '提供輕量好用的':118 '搭配':491 '操作介面':276 '支援':188 '效能模擬不足':92 '整合測試':410,464 '整合測試類別':364 '整合說明':487 '新增':327 '映像檔名稱錯誤':337 '是一個測試函式庫':117 '時':494 '時使用':18 '時可能失敗':85 '最佳實務':220 '會拋出此例外':335 '本技能內容提煉自':387 '架設測試環境':398 '某些':73 '查詢':75 '查詢問題驗證':242 '查詢差異':71 '查詢翻譯差異':72 '核心優勢':124 '核心概念':115 '模式':200 '模式無法測試':97 '檔案':366 '檔案從':214 '檢查約束':102 '檢查連接埠是否被佔用':315 '每個介面專注於特定職責':299 '每個測試有獨立乾淨的環境':136 '每個測試類別都建立新容器會遇到嚴重的效能瓶頸':203 '涵蓋':235 '涵蓋基本':254 '涵蓋容器啟動':19 '清潔測試環境':135 '清理未使用的映像檔':320 '測試':15 '測試執行時間可減少約':207 '測試實際':128 '測試資料隔離':351 '測試隔離':21 '測試隔離性':309 '測試類別設置':256 '無法模擬並發':66 '無法模擬真實資料庫的效能瓶頸或索引問題':93 '無法進行':63 '無資料庫鎖定機制':65 '版本':154 '特有進階功能':285,291 '狀態':333 '環境一致性':132 '環境需求':151 '產生使用':360 '產生外部化':374 '當需要測試真實資料庫行為':10 '當需要驗證複雜交易邏輯':108 '的':362 '的應用':272 '的限制':50 '相關技能':444 '真實環境測試':126 '確保測試環境與正式環境使用相同服務版本':133 '磁碟空間':164 '程式碼分離':216 '等':106 '管理容器生命週期':180 '範例程式碼':403,421 '範例請參考':229 '簡化開發環境':139 '系列文章':391 '結論':107 '老派軟體工程師的測試修練':388 '而非舊版':148 '腳本外部化策略':211 '腳本外部化與':226 '腳本檔案':376 '自訂函數':79 '與':217 '與對應的':378 '若容器未能正常啟動':334 '複雜':76 '記憶體不足問題':341 '記憶體配置':345 '設定':380 '設計優勢':295 '設計原則':269 '語法與資料庫限制':130 '調整':342 '讓所有測試類別共享同一個容器':206 '資料庫整合測試指南':46 '資料庫特定功能':95 '資料庫特定行為時':110 '資料庫的重大限制':56 '資料庫遷移':20 '資料類型精確度':103 '資源不足':339 '起':330 '輸出格式':359 '連接埠衝突':340 '進行容器化資料庫測試的專門技能':9 '進行整合測試':113 '進階功能測試':234,251,253,266 '避免笛卡兒積':239 '重要':145 '鐵人賽文章':399,417 '開發者只需':140 '限制同時執行的容器數量':346 '預存程序':98,294 '預設不區分大小寫':89 '預設等待策略改為等待容器進入':331 '高層模組依賴抽象而非具體實作':307","prices":[{"id":"6796c90a-d865-4d86-9c3b-5862f2558437","listingId":"9f08abac-2c34-4973-805e-5a36f50b37d4","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"kevintsengtw","category":"dotnet-testing-agent-skills","install_from":"skills.sh"},"createdAt":"2026-04-18T23:04:16.961Z"}],"sources":[{"listingId":"9f08abac-2c34-4973-805e-5a36f50b37d4","source":"github","sourceId":"kevintsengtw/dotnet-testing-agent-skills/dotnet-testing-advanced-testcontainers-database","sourceUrl":"https://github.com/kevintsengtw/dotnet-testing-agent-skills/tree/main/skills/dotnet-testing-advanced-testcontainers-database","isPrimary":false,"firstSeenAt":"2026-04-18T23:04:16.961Z","lastSeenAt":"2026-04-24T13:02:25.288Z"}],"details":{"listingId":"9f08abac-2c34-4973-805e-5a36f50b37d4","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"kevintsengtw","slug":"dotnet-testing-advanced-testcontainers-database","github":{"repo":"kevintsengtw/dotnet-testing-agent-skills","stars":23,"topics":["agent-skills","ai-assisted-development","copilot-skills","csharp","dotnet","dotnet-testing","github-copilot","integration-testing","testing","unit-testing","xunit"],"license":"mit","html_url":"https://github.com/kevintsengtw/dotnet-testing-agent-skills","pushed_at":"2026-03-31T07:28:56Z","description":"AI Agent Skills for .NET Testing - Based on 30-Day Testing Challenge (iThome Ironman 2025 Winner)","skill_md_sha":"f493f31062ffdd9c150334ead34b24f21550125e","skill_md_path":"skills/dotnet-testing-advanced-testcontainers-database/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/kevintsengtw/dotnet-testing-agent-skills/tree/main/skills/dotnet-testing-advanced-testcontainers-database"},"layout":"multi","source":"github","category":"dotnet-testing-agent-skills","frontmatter":{"name":"dotnet-testing-advanced-testcontainers-database","description":"使用 Testcontainers 進行容器化資料庫測試的專門技能。當需要測試真實資料庫行為、使用 SQL Server/PostgreSQL/MySQL 容器、測試 EF Core/Dapper 時使用。涵蓋容器啟動、資料庫遷移、測試隔離、容器共享等。\nMake sure to use this skill whenever the user mentions Testcontainers database, SQL Server container, PostgreSQL container, EF Core integration test, Dapper testing, or Collection Fixture, even if they don't explicitly ask for container-based database testing.\nKeywords: testcontainers, 容器測試, container testing, database testing, 資料庫測試, MsSqlContainer, PostgreSqlContainer, MySqlContainer, EF Core testing, Dapper testing, Testcontainers.MsSql, Testcontainers.PostgreSql, GetConnectionString, IAsyncLifetime, CollectionFixture"},"skills_sh_url":"https://skills.sh/kevintsengtw/dotnet-testing-agent-skills/dotnet-testing-advanced-testcontainers-database"},"updatedAt":"2026-04-24T13:02:25.288Z"}}