使用 Testcontainers 运行真实数据库

概述

Testcontainers 是一个开源的 .NET 库,允许你在 Docker 容器中自动管理临时数据库实例。它为集成测试提供了最接近生产环境的测试方案,因为你实际上是在与真实的数据库引擎(SQL Server、PostgreSQL、MySQL 等)进行交互。

核心优势

特性	InMemory	SQLite In-Memory	Testcontainers
真实性	❌ 低	⚠️ 中	✅ 最高(真实数据库)
SQL方言	❌ 无	⚠️ SQLite	✅ 目标数据库
性能特征	❌ 不真实	⚠️ 部分真实	✅ 完全真实
存储过程	❌ 不支持	⚠️ 有限	✅ 完全支持
索引优化	❌ 无	⚠️ 基础	✅ 完整
并发行为	❌ 不真实	⚠️ 部分真实	✅ 完全真实
迁移测试	❌ 不适合	⚠️ 有限	✅ 完美
配置复杂度	✅ 简单	✅ 简单	⚠️ 需要Docker
启动速度	⚡ 极快	🚀 快	🐢 较慢(10-30s)

快速开始

1. 安装 NuGet 包

bash

dotnet add package Testcontainers.MsSql        # SQL Server
dotnet add package Testcontainers.PostgreSql   # PostgreSQL
dotnet add package Testcontainers.MySql        # MySQL

2. 基本配置

csharp

using DotNet.Testcontainers.Builders;
using Testcontainers.MsSql;

public class MsSqlContainerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container;
    
    public MsSqlContainerFixture()
    {
        _container = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("YourStrong@Passw0rd")
            .WithPortBinding(1433, assignRandomHostPort: true)
            .Build();
    }
    
    public string ConnectionString => _container.GetConnectionString();
    
    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        
        // 可选: 等待数据库就绪
        await WaitForDatabaseReady(_container.GetConnectionString());
    }
    
    public async Task DisposeAsync()
    {
        await _container.DisposeAsync();
    }
    
    private static async Task WaitForDatabaseReady(string connectionString)
    {
        using var connection = new SqlConnection(connectionString);
        
        for (int i = 0; i < 30; i++) // 最多等待30秒
        {
            try
            {
                await connection.OpenAsync();
                return;
            }
            catch
            {
                await Task.Delay(1000);
            }
        }
        
        throw new TimeoutException("Database did not become ready in time");
    }
}

3. xUnit 测试基类

csharp

public abstract class IntegrationTestBase : IClassFixture<MsSqlContainerFixture>, IAsyncLifetime
{
    protected readonly AppDbContext Context;
    protected readonly string ConnectionString;
    private readonly MsSqlContainerFixture _fixture;
    
    protected IntegrationTestBase(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
        ConnectionString = fixture.ConnectionString;
        
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(ConnectionString)
            .Options;
        
        Context = new AppDbContext(options);
    }
    
    public async Task InitializeAsync()
    {
        // 每次测试前创建干净的数据库架构
        await Context.Database.EnsureCreatedAsync();
    }
    
    public async ValueTask DisposeAsync()
    {
        // 清理数据
        foreach (var entity in Context.ChangeTracker.Entries().Select(e => e.Entity).ToList())
        {
            Context.Remove(entity);
        }
        await Context.SaveChangesAsync();
        
        await Context.DisposeAsync();
    }
}

完整的测试示例

场景 1: 数据库迁移测试

csharp

public class MigrationTests : IClassFixture<MsSqlContainerFixture>
{
    private readonly MsSqlContainerFixture _fixture;
    
    public MigrationTests(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task Migrations_ShouldApplySuccessfully()
    {
        // Arrange
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        
        // Act: 应用所有迁移
        await context.Database.MigrateAsync();
        
        // Assert: 验证架构已创建
        var tables = await GetDatabaseTablesAsync(context);
        
        Assert.Contains(tables, t => t == "Products");
        Assert.Contains(tables, t => t == "Orders");
        Assert.Contains(tables, t => t == "Customers");
    }
    
    [Fact]
    public async Task Migration_SeedData_ShouldBeInserted()
    {
        // Arrange
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        await context.Database.MigrateAsync();
        
        // Act: 查询种子数据
        var categories = await context.Categories.ToListAsync();
        
        // Assert
        Assert.NotEmpty(categories);
        Assert.Contains(categories, c => c.Name == "Electronics");
    }
    
    private static async Task<List<string>> GetDatabaseTablesAsync(AppDbContext context)
    {
        var sql = @"SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE'";
        
        var tables = new List<string>();
        
        using var command = context.Database.GetDbConnection().CreateCommand();
        command.CommandText = sql;
        
        await context.Database.OpenConnectionAsync();
        
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            tables.Add(reader.GetString(0));
        }
        
        return tables;
    }
}

场景 2: 复杂关系和约束测试

csharp

public class RelationshipConstraintTests : IntegrationTestBase
{
    public RelationshipConstraintTests(MsSqlContainerFixture fixture) : base(fixture) { }
    
    [Fact]
    public async Task ForeignKey_ShouldEnforceReferentialIntegrity()
    {
        // Act & Assert: 尝试创建无效的外键引用
        var order = new Order 
        { 
            CustomerId = 99999, // 不存在的客户
            OrderDate = DateTime.UtcNow,
            TotalAmount = 100m
        };
        
        Context.Orders.Add(order);
        
        // 应该抛出 DbUpdateException
        await Assert.ThrowsAsync<DbUpdateException>(() => 
            Context.SaveChangesAsync());
    }
    
    [Fact]
    public async Task UniqueConstraint_ShouldPreventDuplicates()
    {
        // Arrange: 创建唯一约束
        await Context.Database.ExecuteSqlRawAsync(
            "CREATE UNIQUE INDEX IX_Categories_Name ON Categories(Name)");
        
        var category1 = new Category { Name = "Electronics" };
        Context.Categories.Add(category1);
        await Context.SaveChangesAsync();
        
        // Act: 尝试插入重复的唯一值
        var category2 = new Category { Name = "Electronics" };
        Context.Categories.Add(category2);
        
        // Assert: 应该抛出异常
        var exception = await Record.ExceptionAsync(() => 
            Context.SaveChangesAsync());
        
        Assert.NotNull(exception);
        Assert.IsType<DbUpdateException>(exception);
    }
    
    [Fact]
    public async Task CascadeDelete_ShouldWorkCorrectly()
    {
        // Arrange
        var customer = new Customer 
        { 
            Name = "John Doe",
            Email = "john@example.com"
        };
        
        customer.Orders = new List<Order>
        {
            new Order { OrderDate = DateTime.UtcNow, TotalAmount = 100m },
            new Order { OrderDate = DateTime.UtcNow, TotalAmount = 200m }
        };
        
        Context.Customers.Add(customer);
        await Context.SaveChangesAsync();
        
        var customerId = customer.Id;
        
        // Act: 删除客户
        Context.Customers.Remove(customer);
        await Context.SaveChangesAsync();
        
        // Assert: 订单也应该被删除
        var remainingOrders = await Context.Orders
            .Where(o => o.CustomerId == customerId)
            .ToListAsync();
        
        Assert.Empty(remainingOrders);
    }
}

场景 3: 并发控制测试

csharp

public class ConcurrencyControlTests : IClassFixture<MsSqlContainerFixture>
{
    private readonly MsSqlContainerFixture _fixture;
    
    public ConcurrencyControlTests(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task OptimisticConcurrency_WithRowVersion_ShouldDetectConflicts()
    {
        // Arrange: 创建带 RowVersion 的实体
        var options1 = CreateOptions();
        var options2 = CreateOptions();
        
        await using (var context = new AppDbContext(options1))
        {
            var product = new ProductWithVersion 
            { 
                Name = "Original Product", 
                Price = 100m 
            };
            
            context.Products.Add(product);
            await context.SaveChangesAsync();
        }
        
        // Act: 两个上下文同时更新同一记录
        await using (var context1 = new AppDbContext(options1))
        await using (var context2 = new AppDbContext(options2))
        {
            var product1 = await context1.Products.FirstAsync();
            var product2 = await context2.Products.FirstAsync();
            
            // 第一个上下文更新
            product1.Price = 120m;
            await context1.SaveChangesAsync(); // 成功
            
            // 第二个上下文尝试更新(应该失败)
            product2.Price = 150m;
            
            var exception = await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
                () => context2.SaveChangesAsync());
            
            Console.WriteLine($"Concurrency conflict detected: {exception.Message}");
        }
    }
    
    [Fact]
    public async Task PessimisticConcurrency_WithLocking_ShouldBlock()
    {
        // Arrange
        var options = CreateOptions();
        
        await using var context = new AppDbContext(options);
        await using var transaction = await context.Database.BeginTransactionAsync(
            IsolationLevel.Serializable); // 串行化隔离级别
        
        var product = await context.Products
            .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK) WHERE Id = @p0", 1)
            .FirstOrDefaultAsync();
        
        if (product != null)
        {
            product.Price = 120m;
            await context.SaveChangesAsync();
        }
        
        await transaction.CommitAsync();
    }
    
    private DbContextOptions<AppDbContext> CreateOptions()
    {
        return new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
    }
}

public class ProductWithVersion
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    
    [Timestamp]
    public byte[] RowVersion { get; set; } = Array.Empty<byte>();
}

场景 4: 存储过程和函数测试

csharp

public class StoredProcedureTests : IClassFixture<MsSqlContainerFixture>
{
    private readonly MsSqlContainerFixture _fixture;
    
    public StoredProcedureTests(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task StoredProcedure_ShouldExecuteCorrectly()
    {
        // Arrange: 创建存储过程
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        
        var createProcSql = @"
            CREATE PROCEDURE GetProductCountByCategory
                @CategoryId INT,
                @Count INT OUTPUT
            AS
            BEGIN
                SELECT @Count = COUNT(*) 
                FROM Products 
                WHERE CategoryId = @CategoryId
            END";
        
        await context.Database.ExecuteSqlRawAsync(createProcSql);
        
        // 插入测试数据
        var category = new Category { Name = "Electronics" };
        context.Categories.Add(category);
        
        context.Products.AddRange(
            new Product { Name = "Laptop", Price = 999.99m, CategoryId = category.Id },
            new Product { Name = "Phone", Price = 599.99m, CategoryId = category.Id }
        );
        
        await context.SaveChangesAsync();
        
        // Act: 调用存储过程
        var countParam = new SqlParameter("@Count", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };
        
        await context.Database.ExecuteSqlRawAsync(
            "EXEC GetProductCountByCategory @CategoryId = {0}, @Count = {1} OUTPUT",
            category.Id, countParam);
        
        var count = (int)countParam.Value;
        
        // Assert
        Assert.Equal(2, count);
    }
    
    [Fact]
    public async Task ScalarFunction_ShouldReturnCorrectResult()
    {
        // Arrange: 创建标量函数
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        
        var createFuncSql = @"
            CREATE FUNCTION CalculateDiscountedPrice(@ProductId INT, @DiscountPercent DECIMAL(5,2))
            RETURNS DECIMAL(18,2)
            AS
            BEGIN
                DECLARE @OriginalPrice DECIMAL(18,2)
                SELECT @OriginalPrice = Price FROM Products WHERE Id = @ProductId
                RETURN @OriginalPrice * (1 - @DiscountPercent / 100)
            END";
        
        await context.Database.ExecuteSqlRawAsync(createFuncSql);
        
        // 插入测试数据
        var product = new Product { Name = "Laptop", Price = 1000m };
        context.Products.Add(product);
        await context.SaveChangesAsync();
        
        // Act: 调用函数
        var discountedPrice = await context.Database.SqlQueryRaw<decimal>(
            "SELECT dbo.CalculateDiscountedPrice({0}, {1})", 
            product.Id, 10).FirstOrDefaultAsync();
        
        // Assert: 10% 折扣应该是 900
        Assert.Equal(900m, discountedPrice);
    }
}

场景 5: 性能测试

csharp

public class PerformanceTests : IClassFixture<MsSqlContainerFixture>
{
    private readonly MsSqlContainerFixture _fixture;
    
    public PerformanceTests(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task BulkInsert_ShouldCompleteInReasonableTime()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        await context.Database.EnsureCreatedAsync();
        
        // Act: 批量插入 10000 条记录
        var products = Enumerable.Range(1, 10000)
            .Select(i => new Product 
            { 
                Name = $"Product {i}", 
                Price = i * 10m,
                CategoryId = 1
            })
            .ToList();
        
        var stopwatch = Stopwatch.StartNew();
        
        context.Products.AddRange(products);
        await context.SaveChangesAsync();
        
        stopwatch.Stop();
        
        // Assert: 应该在 10 秒内完成
        Assert.True(stopwatch.ElapsedMilliseconds < 10000, 
            $"Bulk insert took {stopwatch.ElapsedMilliseconds}ms");
        
        // 验证数据已保存
        var count = await context.Products.CountAsync();
        Assert.Equal(10000, count);
    }
    
    [Fact]
    public async Task ComplexQuery_WithIndex_ShouldBeFast()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(_fixture.ConnectionString)
            .Options;
        
        await using var context = new AppDbContext(options);
        
        // 创建索引
        await context.Database.ExecuteSqlRawAsync(
            "CREATE INDEX IX_Products_Price ON Products(Price)");
        
        // 插入测试数据
        SeedTestData(context);
        
        // Act: 执行复杂查询
        var stopwatch = Stopwatch.StartNew();
        
        var result = await context.Products
            .Where(p => p.Price > 500)
            .OrderByDescending(p => p.Price)
            .Take(100)
            .ToListAsync();
        
        stopwatch.Stop();
        
        // Assert
        Assert.Equal(100, result.Count);
        Assert.True(stopwatch.ElapsedMilliseconds < 1000, 
            $"Query took {stopwatch.ElapsedMilliseconds}ms");
        
        // 查看执行计划
        var sql = context.Products
            .Where(p => p.Price > 500)
            .ToQueryString();
        
        Console.WriteLine($"Generated SQL:\n{sql}");
    }
    
    private static void SeedTestData(AppDbContext context)
    {
        var products = Enumerable.Range(1, 1000)
            .Select(i => new Product 
            { 
                Name = $"Product {i}", 
                Price = i * 1m,
                CategoryId = 1
            })
            .ToList();
        
        context.Products.AddRange(products);
        context.SaveChanges();
    }
}

PostgreSQL 示例

csharp

using Testcontainers.PostgreSql;

public class PostgreSqlContainerFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _container;
    
    public PostgreSqlContainerFixture()
    {
        _container = new PostgreSqlBuilder()
            .WithImage("postgres:16-alpine")
            .WithUsername("testuser")
            .WithPassword("testpass")
            .WithDatabase("testdb")
            .WithPortBinding(5432, assignRandomHostPort: true)
            .Build();
    }
    
    public string ConnectionString => _container.GetConnectionString();
    
    public async Task InitializeAsync()
    {
        await _container.StartAsync();
    }
    
    public async Task DisposeAsync()
    {
        await _container.DisposeAsync();
    }
}

// 使用示例
public class PostgreSqlTests : IClassFixture<PostgreSqlContainerFixture>
{
    private readonly PostgreSqlContainerFixture _fixture;
    
    public PostgreSqlTests(PostgreSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task JsonQuery_ShouldWorkWithPostgreSql()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql(_fixture.ConnectionString) // Npgsql for PostgreSQL
            .Options;
        
        await using var context = new AppDbContext(options);
        await context.Database.EnsureCreatedAsync();
        
        // PostgreSQL 特有的 JSON 查询
        var result = await context.Products
            .Where(p => EF.Functions.JsonContains(
                p.Metadata, 
                "{\"Category\": \"Electronics\"}"))
            .ToListAsync();
        
        Assert.NotNull(result);
    }
}

Docker Compose 集成

docker-compose.test.yml

yaml

version: '3.8'

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      SA_PASSWORD: "YourStrong@Passw0rd"
      ACCEPT_EULA: "Y"
    ports:
      - "1433"
    healthcheck:
      test: /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd" -Q "SELECT 1"
      interval: 5s
      timeout: 3s
      retries: 10
  
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
      POSTGRES_DB: testdb
    ports:
      - "5432"
    healthcheck:
      test: pg_isready -U testuser
      interval: 3s
      timeout: 2s
      retries: 5

在测试中使用 Docker Compose

csharp

public class DockerComposeFixture : IAsyncLifetime
{
    public async Task InitializeAsync()
    {
        // 启动 Docker Compose
        await ProcessHelper.RunAsync("docker-compose", 
            "-f docker-compose.test.yml up -d");
        
        // 等待服务就绪
        await WaitForServices();
    }
    
    public async Task DisposeAsync()
    {
        // 停止 Docker Compose
        await ProcessHelper.RunAsync("docker-compose", 
            "-f docker-compose.test.yml down");
    }
    
    private static async Task WaitForServices()
    {
        // 等待 SQL Server
        await WaitForSqlServer();
        // 等待 PostgreSQL
        await WaitForPostgreSql();
    }
}

最佳实践

1. 并行测试优化

csharp

// 为每个测试类使用独立的容器
[CollectionDefinition("SqlServer", DisableParallelization = false)]
public class SqlServerCollection : ICollectionFixture<MsSqlContainerFixture>
{
}

[Collection("SqlServer")]
public class ProductTests
{
    // 可以与其他测试类并行执行
}

[Collection("SqlServer")]
public class OrderTests
{
    // 可以与其他测试类并行执行
}

2. 容器复用

csharp

// 在多个测试运行间复用容器(开发时)
public class ReusableContainerFixture : IAsyncLifetime
{
    private static MsSqlContainer? _sharedContainer;
    
    public async Task InitializeAsync()
    {
        if (_sharedContainer == null)
        {
            _sharedContainer = new MsSqlBuilder()
                .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
                .WithPassword("YourStrong@Passw0rd")
                .Build();
            
            await _sharedContainer.StartAsync();
        }
    }
    
    public async Task DisposeAsync()
    {
        // 不销毁容器,以便下次复用
        // await _sharedContainer?.DisposeAsync();
    }
}

3. 健康检查

csharp

private static async Task<bool> IsDatabaseReady(string connectionString)
{
    try
    {
        using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        
        using var command = connection.CreateCommand();
        command.CommandText = "SELECT 1";
        await command.ExecuteScalarAsync();
        
        return true;
    }
    catch
    {
        return false;
    }
}

CI/CD 集成

GitHub Actions

yaml

name: Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    
    services:
      docker:
        image: docker:dind
        options: --privileged
    
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup .NET
        uses: actions/setup-dotnet@v3
        with:
          dotnet-version: '8.0.x'
      
      - name: Run tests
        run: dotnet test --configuration Release
        env:
          DOCKER_HOST: unix:///var/run/docker.sock

常见问题

问题 1: Docker 未运行

错误: Docker is not running

解决方案: 确保 Docker Desktop 正在运行

问题 2: 端口冲突

错误: Port already in use

解决方案: 使用 assignRandomHostPort: true

csharp

.WithPortBinding(1433, assignRandomHostPort: true)

问题 3: 容器启动慢

解决方案: 增加超时时间,使用健康检查

总结

Testcontainers 提供最真实的集成测试环境:

✅ 100% 真实: 使用真实数据库引擎
✅ 多数据库支持: SQL Server, PostgreSQL, MySQL 等
✅ 完整功能: 存储过程、触发器、索引等
✅ 适合迁移测试: 测试数据库迁移脚本
✅ CI/CD 友好: 易于自动化

虽然速度较慢,但对于关键业务的集成测试,Testcontainers 是最佳选择!

使用 Testcontainers 运行真实数据库 ​

概述 ​

核心优势 ​

快速开始 ​

1. 安装 NuGet 包 ​

2. 基本配置 ​

3. xUnit 测试基类 ​

完整的测试示例 ​

场景 1: 数据库迁移测试 ​

场景 2: 复杂关系和约束测试 ​

场景 3: 并发控制测试 ​

场景 4: 存储过程和函数测试 ​

场景 5: 性能测试 ​

PostgreSQL 示例 ​

Docker Compose 集成 ​

docker-compose.test.yml ​

在测试中使用 Docker Compose ​

最佳实践 ​

1. 并行测试优化 ​

2. 容器复用 ​

3. 健康检查 ​

CI/CD 集成 ​

GitHub Actions ​

常见问题 ​

问题 1: Docker 未运行 ​

问题 2: 端口冲突 ​

问题 3: 容器启动慢 ​

总结 ​

使用 Testcontainers 运行真实数据库

概述

核心优势

快速开始

1. 安装 NuGet 包

2. 基本配置

3. xUnit 测试基类

完整的测试示例

场景 1: 数据库迁移测试

场景 2: 复杂关系和约束测试

场景 3: 并发控制测试

场景 4: 存储过程和函数测试

场景 5: 性能测试

PostgreSQL 示例

Docker Compose 集成

docker-compose.test.yml

在测试中使用 Docker Compose

最佳实践

1. 并行测试优化

2. 容器复用

3. 健康检查

CI/CD 集成

GitHub Actions

常见问题

问题 1: Docker 未运行

问题 2: 端口冲突

问题 3: 容器启动慢

总结