Testcontainers — Real Databases in Integration Tests

In-memory database (EF Core InMemory, SQLite):
  → Fast
  → Does not support SQL Server-specific features (schemas, computed columns, row-level security)
  → Does not run migrations — schema differences cause silent failures
  → Does not enforce FOREIGN KEY constraints (SQLite without pragma)
  → Tests pass, production fails — the worst kind of false confidence

Testcontainers:
  → Spins up a real SQL Server (or PostgreSQL, Redis, etc.) in Docker
  → Runs your actual migrations against it
  → All SQL Server features work: schemas, triggers, computed columns, constraints
  → Tests take 15-30 seconds (container startup) instead of milliseconds
  → Worth it for integration tests — not for unit tests

Rule: unit tests use no I/O; integration tests use Testcontainers.

// NuGet: Testcontainers.MsSql, Microsoft.EntityFrameworkCore.SqlServer

// xUnit collection fixture — one container shared across the test class
public sealed class SqlServerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
        .WithPassword("YourStrong!Passw0rd")
        .Build();

    public string ConnectionString { get; private set; } = string.Empty;

    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        ConnectionString = _container.GetConnectionString();
    }

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

// Share fixture across tests in the same collection
[CollectionDefinition("SqlServer")]
public sealed class SqlServerCollection : ICollectionFixture<SqlServerFixture> { }

[Collection("SqlServer")]
public sealed class PrescriptionRepositoryTests
{
    private readonly string _connectionString;

    public PrescriptionRepositoryTests(SqlServerFixture fixture)
    {
        _connectionString = fixture.ConnectionString;
    }

    // Each test gets a fresh DbContext with a clean database state
    private PrescriptionsDbContext CreateContext()
    {
        var options = new DbContextOptionsBuilder<PrescriptionsDbContext>()
            .UseSqlServer(_connectionString)
            .Options;

        var context = new PrescriptionsDbContext(options);

        // Run migrations once (idempotent)
        context.Database.Migrate();

        return context;
    }

    [Fact]
    public async Task AddAsync_Prescription_CanBeRetrievedById()
    {
        await using var context = CreateContext();
        var repository = new PrescriptionRepository(context);

        var prescription = Prescription.CreateDraft(
            PatientId.Of(Guid.NewGuid()),
            MedicationName.Of("Warfarin"),
            DosageValue.Of(5m, "mg"));

        await repository.AddAsync(prescription, CancellationToken.None);
        await context.SaveChangesAsync();

        // Detach to force a real DB read
        context.ChangeTracker.Clear();

        var loaded = await repository.GetByIdAsync(prescription.Id, CancellationToken.None);
        loaded.Should().NotBeNull();
        loaded!.MedicationName.Value.Should().Be("Warfarin");
    }
}

[Fact]
public async Task GetActiveByWard_ReturnsOnlyActiveWarfarin()
{
    await using var context = CreateContext();

    // Seed directly via context to set up known state
    var wardId  = Guid.NewGuid();
    var patient = new { Id = Guid.NewGuid() };

    context.Database.ExecuteSqlRaw("""
        INSERT INTO prescriptions.prescriptions
            (id, patient_id, medication_name, status, ward_id, created_at)
        VALUES
            (NEWID(), @patientId, 'Warfarin',  'Approved', @wardId, GETUTCDATE()),
            (NEWID(), @patientId, 'Heparin',   'Approved', @wardId, GETUTCDATE()),
            (NEWID(), @patientId, 'Warfarin',  'Expired',  @wardId, GETUTCDATE())
        """,
        new SqlParameter("@patientId", patient.Id),
        new SqlParameter("@wardId",    wardId));

    var repository = new PrescriptionRepository(context);
    var results    = await repository.GetActiveByWardAsync(wardId, CancellationToken.None);

    // Only the Approved Warfarin and Heparin should return — not the Expired Warfarin
    results.Should().HaveCount(2);
    results.All(p => p.Status == PrescriptionStatus.Approved).Should().BeTrue();
}

// PostgreSQL (for Marten / event sourcing)
private readonly PostgreSqlContainer _postgres = new PostgreSqlBuilder()
    .WithImage("postgres:16-alpine")
    .WithDatabase("clinical_test")
    .WithUsername("test_user")
    .WithPassword("test_password")
    .Build();

// Redis (for IDistributedCache tests)
private readonly RedisContainer _redis = new RedisBuilder()
    .WithImage("redis:7-alpine")
    .Build();

// Using both in the same fixture:
public async Task InitializeAsync()
{
    await Task.WhenAll(_postgres.StartAsync(), _redis.StartAsync());
    PostgresConnectionString = _postgres.GetConnectionString();
    RedisConnectionString    = _redis.GetConnectionString();
}

public async Task DisposeAsync()
{
    await Task.WhenAll(_postgres.DisposeAsync().AsTask(), _redis.DisposeAsync().AsTask());
}

// Strategy 1: Wrap each test in a transaction and roll back
// Fast — no data persists between tests
public sealed class TransactionalTestBase : IAsyncDisposable
{
    protected readonly PrescriptionsDbContext Db;
    private readonly IDbContextTransaction _tx;

    public TransactionalTestBase(SqlServerFixture fixture)
    {
        Db  = CreateContext(fixture.ConnectionString);
        _tx = Db.Database.BeginTransaction();
    }

    public async ValueTask DisposeAsync()
    {
        await _tx.RollbackAsync();
        await Db.DisposeAsync();
    }
}

// Strategy 2: Recreate the database for each test class
// Slower but completely clean — no transaction leaks
public async Task InitializeAsync()
{
    await _container.StartAsync();
    var db = CreateContext();
    await db.Database.EnsureDeletedAsync();
    await db.Database.MigrateAsync();
}

// Strategy 3: Use a unique schema per test run
// Allows parallel test runs against the same container
var schemaName = $"test_{Guid.NewGuid():N}";
options.UseSqlServer(connection,
    sql => sql.MigrationsHistoryTable("__EFMigrationsHistory", schemaName));