Integration Testing with Real Databases Using Testcontainers

TL;DR: Testcontainers lets you run real SQL Server in Docker inside your tests. This guide shows how to use fixtures, FluentMigrator, and xUnit to run fully isolated integration tests.

Learn how to test ProductRepository against a real SQL Server 2022 container using Testcontainers and xUnit.

---

What Is Testcontainers?

Testcontainers is a .NET library that lets you spin up real Docker containers programmatically during tests. Instead of mocking databases or using shared dev instances, you get isolated, disposable infrastructure that runs in Docker.

Why use it?

• Tests against real databases (not mocks or in-memory databases)
• Disposable environments (fresh container per test run)
• CI/CD ready (works anywhere Docker runs)
• No manual setup (no installing SQL Server locally)

Common use cases:

• Integration testing repositories with SQL Server
• Testing Azure services with emulators (Cosmos DB, Storage)
• Testing message queues (RabbitMQ, Kafka)
• End-to-end API testing with real infrastructure

---

Why Testcontainers?

Integration tests verify that multiple components work together correctly—in this case, your repository talking to a real SQL Server database. Unlike unit tests that mock dependencies, integration tests use real infrastructure provided by Docker containers.

Before Testcontainers

❌ Manual setup required (install SQL Server locally)

❌ Shared database state (tests could interfere with each other)

❌ CI/CD challenges (hard to replicate production-like environments)

❌ Cleanup headaches (leftover test data)

With Testcontainers

✅ Spin up Docker containers programmatically

✅ Isolated, disposable environments (each test suite gets a fresh container)

✅ Automatic cleanup (containers destroyed after tests)

✅ CI/CD ready (works anywhere Docker is available)

┌─────────────────────────────────────────┐
│  Your Test:                             │
│  [ProductRepositoryTests]               │
│           ↓                             │
│  [SqlServerContainerFixture]            │
│           ↓                             │
│  [Docker Container: SQL Server 2022]    │
│           ↓                             │
│  [FluentMigrator runs migrations]       │
│           ↓                             │
│  [Test executes against real database]  │
└─────────────────────────────────────────┘

---

Setup

Install the Testcontainers NuGet packages:

dotnet add package Testcontainers.MsSql --version 4.2.0

Prerequisites:

• Docker installed and running
• .NET 8 SDK
• xUnit test project

---

The Pattern: Fixture + Collection

Testcontainers uses xUnit's fixture pattern to share expensive resources (like containers) across multiple tests:

// 1. FIXTURE: Manages container lifecycle
public class SqlServerContainerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container;
    public string ConnectionString => _container.GetConnectionString();

    public async Task InitializeAsync() => await _container.StartAsync();
    public async Task DisposeAsync() => await _container.DisposeAsync();
}

// 2. COLLECTION: Shares fixture across tests
[CollectionDefinition("SqlServer")]
public class SqlServerCollection : ICollectionFixture<SqlServerContainerFixture> { }

// 3. TESTS: Use the shared fixture
[Collection("SqlServer")]
public class ProductRepositoryTests
{
    private readonly SqlServerContainerFixture _fixture;

    public ProductRepositoryTests(SqlServerContainerFixture fixture)
    {
        _fixture = fixture;
    }
}

Key concepts:

• Fixture = Container lifecycle manager (start/stop)
• Collection = Shares fixture across test classes
• Tests = Inject fixture via constructor

---

Step 1: Create the SqlServerContainerFixture

The fixture manages the SQL Server container lifecycle and runs FluentMigrator to create your database schema:

File: Fixtures/SqlServerContainerFixture.cs

using DotNet.Testcontainers.Builders;
using FluentMigrator.Runner;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;
using ProductWebAPI.Database.Migrations;
using Testcontainers.MsSql;
using Xunit;

namespace CommonComps.UnitTests.Integration.Fixtures;

/// <summary>
/// Shared SQL Server container fixture for all integration tests.
/// Uses FluentMigrator from ProductWebAPI.Database for schema creation.
/// </summary>
public class SqlServerContainerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container;

    public string ConnectionString => _container.GetConnectionString();

    public SqlServerContainerFixture()
    {
        _container = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-latest")
            .WithPassword("YourStrong@Passw0rd!")
            .WithWaitStrategy(Wait.ForUnixContainer().UntilPortIsAvailable(1433))
            .Build();
    }

    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        RunMigrations();
    }

    public async Task DisposeAsync()
    {
        await _container.DisposeAsync();
    }

    private void RunMigrations()
    {
        // Use FluentMigrator from ProductWebAPI.Database
        var migrationAssembly = typeof(CreateInitialSchema).Assembly;

        var serviceProvider = new ServiceCollection()
            .AddFluentMigratorCore()
            .ConfigureRunner(rb => rb
                .AddSqlServer()
                .WithGlobalConnectionString(ConnectionString)
                .ScanIn(migrationAssembly).For.Migrations()
                .ScanIn(migrationAssembly).For.EmbeddedResources())
            .AddLogging(lb => lb.AddFluentMigratorConsole())
            .BuildServiceProvider(false);

        using var scope = serviceProvider.CreateScope();
        var runner = scope.ServiceProvider.GetRequiredService<IMigrationRunner>();
        runner.MigrateUp();
    }
}

/// <summary>
/// Collection definition for SQL Server tests
/// </summary>
[CollectionDefinition("SqlServer")]
public class SqlServerCollection : ICollectionFixture<SqlServerContainerFixture>
{
}

What's happening here:

1. MsSqlBuilder creates a SQL Server 2022 container
2. InitializeAsync starts the container and runs FluentMigrator
3. RunMigrations scans your migration assembly and applies all migrations
4. [CollectionDefinition] allows multiple test classes to share this container

---

Step 2: Write Integration Tests

Now write tests that use the real SQL Server container. Tests are injected with the fixture via constructor:

File: Integration/ProductRepositoryTests.cs

using CommonComps.UnitTests.Integration.Fixtures;
using CommonComps.Models;
using CommonComps.Repositories;
using FluentAssertions;
using Microsoft.Extensions.Configuration;
using Xunit;

namespace CommonComps.UnitTests.Integration;

/// <summary>
/// Integration tests for ProductRepository using SQL Server container
/// </summary>
[Collection("SqlServer")]
public class ProductRepositoryTests
{
    private readonly SqlServerContainerFixture _fixture;
    private readonly IProductRepository _repository;

    public ProductRepositoryTests(SqlServerContainerFixture fixture)
    {
        _fixture = fixture;

        var configuration = new ConfigurationBuilder()
            .AddInMemoryCollection(new Dictionary<string, string?>
            {
                ["ConnectionStrings:DefaultConnection"] = _fixture.ConnectionString
            })
            .Build();

        _repository = new ProductRepository(configuration);
    }

    [Fact]
    public async Task GetByIdAsync_WithExistingId_ReturnsProduct()
    {
        // Act - Query real database
        var result = await _repository.GetByIdAsync(1);

        // Assert - Verify data from container
        result.Should().NotBeNull();
        result!.Name.Should().Be("Laptop");
        result.SKU.Should().Be("LAP-001");
        result.Price.Should().Be(1299.99m);
        result.Category.Should().NotBeNull();
        result.Category.Name.Should().Be("Electronics");
    }

    [Fact]
    public async Task GetByIdAsync_WithNonExistingId_ReturnsNull()
    {
        // Act
        var result = await _repository.GetByIdAsync(999);

        // Assert
        result.Should().BeNull();
    }

    [Fact]
    public async Task GetAllAsync_ReturnsOnlyActiveProducts()
    {
        // Act
        var result = await _repository.GetAllAsync();

        // Assert - Migration seeds 4 active products
        result.Should().NotBeEmpty();
        result.Should().HaveCountGreaterThanOrEqualTo(4);
        result.Should().OnlyContain(p => p.IsActive);
    }

    [Fact]
    public async Task CreateAsync_WithValidProduct_ReturnsNewId()
    {
        // Arrange
        var newProduct = new Product
        {
            Name = "Test Product",
            SKU = $"TEST-{Guid.NewGuid():N}".Substring(0, 20),
            Description = "Test description",
            Price = 99.99m,
            CategoryId = 1,
            IsActive = true
        };

        // Act - Insert into real database
        var newId = await _repository.CreateAsync(newProduct);

        // Assert - Verify it was inserted
        newId.Should().BeGreaterThan(0);

        var inserted = await _repository.GetByIdAsync(newId);
        inserted.Should().NotBeNull();
        inserted!.Name.Should().Be("Test Product");
    }
}

Visual demo:

Key differences from unit tests:

Unit Test	Integration Test
Mock<IProductRepository>	Real ProductRepository
Mocked GetByIdAsync returns fake data	Queries actual SQL Server container
No database interaction	Real database queries via Dapper
Fast (milliseconds)	Slower (seconds)

---

Using FluentMigrator for Schema Setup

Instead of manually creating tables with SQL scripts, leverage FluentMigrator to:

✅ Reuse your production migrations

✅ Ensure test schema matches production

✅ Automatically seed test data

Migration example (already shown in SqlServerContainerFixture):

private void RunMigrations()
{
    var migrationAssembly = typeof(CreateInitialSchema).Assembly;

    var serviceProvider = new ServiceCollection()
        .AddFluentMigratorCore()
        .ConfigureRunner(rb => rb
            .AddSqlServer()
            .WithGlobalConnectionString(ConnectionString)
            .ScanIn(migrationAssembly).For.Migrations())
        .BuildServiceProvider(false);

    using var scope = serviceProvider.CreateScope();
    var runner = scope.ServiceProvider.GetRequiredService<IMigrationRunner>();
    runner.MigrateUp(); // Runs all migrations
}

Benefits:

• No duplicate schema definitions
• Test data seeded via migrations
• Production schema = Test schema

---

Sharing Containers Across Tests

Use xUnit's collection fixtures to share a container across multiple test classes:

// 1. Define collection
[CollectionDefinition("SqlServer")]
public class SqlServerCollection : ICollectionFixture<SqlServerContainerFixture> { }

// 2. Multiple test classes use the same collection
[Collection("SqlServer")]
public class ProductRepositoryTests { /* ... */ }

[Collection("SqlServer")]
public class CategoryRepositoryTests { /* ... */ }

[Collection("SqlServer")]
public class OrderRepositoryTests { /* ... */ }

Performance impact:

Approach	Container Lifecycle	Speed
Per-class fixture	New container per test class	Slow (30-60s per class)
Collection fixture (Recommended)	One container shared across classes	Fast (30-60s total)

---

Common Containers Reference

Container	Image	Use Case
SQL Server	mcr.microsoft.com/mssql/server:2022-latest	Relational database testing
PostgreSQL	postgres:16-alpine	PostgreSQL database testing
MySQL	mysql:8.0	MySQL database testing
Redis	redis:7-alpine	Cache/session testing
MongoDB	mongo:7	NoSQL database testing
RabbitMQ	rabbitmq:3-management	Message queue testing

---

Unit vs Integration Tests Comparison

Aspect	Unit Tests (Part 1)	Integration Tests (Part 2)
Scope	Single class in isolation	Multiple components together
Dependencies	Mocked (Mock<IProductRepository>)	Real (SQL Server container)
Infrastructure	None	Docker containers
Speed	Very fast (milliseconds)	Slower (seconds)
Purpose	Test business logic	Test data access layer
Example	ProductService with mocked repo	ProductRepository with SQL Server
Assertions	Verify method calls (mockRepo.Verify())	Verify database state
When to use	Service/controller logic	Repository CRUD operations

---

Best Practices

✅ DO

• Share containers via collection fixtures (faster)
• Use FluentMigrator to reuse production migrations
• Seed test data in migrations or fixture initialization
• Test real queries (joins, transactions, stored procedures)
• Run in CI/CD with Docker-in-Docker or Docker socket mounting

❌ DON'T

• Don't create containers per test (too slow)
• Don't duplicate schema (use migrations instead)
• Don't test business logic here (use unit tests for that)
• Don't forget Docker (tests will fail without Docker running)

Controller End-to-End Tests

For full end-to-end testing, you can test the entire HTTP pipeline from controller to database:

File: Integration/ProductControllerIntegrationTests.cs

using System.Net;
using System.Net.Http.Json;
using CommonComps.Models;
using CommonComps.Models.Requests;
using CommonComps.Models.Responses;
using CommonComps.UnitTests.Integration.Fixtures;
using FluentAssertions;
using Microsoft.AspNetCore.Mvc.Testing;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Xunit;

namespace CommonComps.UnitTests.Integration;

/// <summary>
/// End-to-End integration tests for ProductsController
/// Tests: HTTP Request → Controller → Service → Repository → SQL Server Container
/// </summary>
[Collection("SqlServer")]
public class ProductControllerIntegrationTests : IClassFixture<WebApplicationFactory<Program>>
{
    private readonly HttpClient _client;
    private readonly WebApplicationFactory<Program> _factory;
    private readonly SqlServerContainerFixture _dbFixture;

    public ProductControllerIntegrationTests(
        WebApplicationFactory<Program> factory,
        SqlServerContainerFixture dbFixture)
    {
        _dbFixture = dbFixture;

        // Configure the test server to use the container database
        _factory = factory.WithWebHostBuilder(builder =>
        {
            builder.ConfigureAppConfiguration((context, config) =>
            {
                // Override connection string with container connection string
                config.AddInMemoryCollection(new Dictionary<string, string?>
                {
                    ["ConnectionStrings:DefaultConnection"] = _dbFixture.ConnectionString
                });
            });
        });

        _client = _factory.CreateClient();
    }

    [Fact]
    public async Task GetById_WithExistingProduct_Returns200WithProduct()
    {
        // EXECUTE - GET request to real API
        var response = await _client.GetAsync("/api/products/1");

        // VALIDATE - HTTP response
        response.StatusCode.Should().Be(HttpStatusCode.OK);

        // VALIDATE - Product data from database
        var product = await response.Content.ReadFromJsonAsync<ProductResponse>();
        product.Should().NotBeNull();
        product!.Id.Should().Be(1);
        product.Name.Should().Be("Laptop");
        product.SKU.Should().Be("LAP-001");
        product.Price.Should().Be(1299.99m);
    }

    [Fact]
    public async Task GetById_WithNonExistentProduct_Returns404()
    {
        // EXECUTE
        var response = await _client.GetAsync("/api/products/99999");

        // VALIDATE
        response.StatusCode.Should().Be(HttpStatusCode.NotFound);
    }

    [Fact]
    public async Task Create_WithValidProduct_Returns201AndCreatesInDatabase()
    {
        // ARRANGE
        var request = new CreateProductRequest
        {
            Name = "E2E Test Product",
            SKU = $"E2E-{Guid.NewGuid():N}".Substring(0, 20),
            Description = "Created via E2E test",
            Price = 199.99m,
            CategoryId = 1
        };

        // EXECUTE - POST to real API
        var response = await _client.PostAsJsonAsync("/api/products", request);

        // VALIDATE - HTTP 201 Created
        response.StatusCode.Should().Be(HttpStatusCode.Created);

        // VALIDATE - Product was saved to database
        var created = await response.Content.ReadFromJsonAsync<ProductResponse>();
        created.Should().NotBeNull();
        created!.Id.Should().BeGreaterThan(0);
        created.Name.Should().Be("E2E Test Product");

        // VALIDATE - Can retrieve from database via GET
        var getResponse = await _client.GetAsync($"/api/products/{created.Id}");
        getResponse.StatusCode.Should().Be(HttpStatusCode.OK);
    }

    [Fact]
    public async Task Update_WithValidProduct_Returns204AndUpdatesDatabase()
    {
        // ARRANGE - Create product first
        var createRequest = new CreateProductRequest
        {
            Name = "Original Name",
            SKU = $"UPD-{Guid.NewGuid():N}".Substring(0, 20),
            Price = 99.99m,
            CategoryId = 1
        };
        var createResponse = await _client.PostAsJsonAsync("/api/products", createRequest);
        var created = await createResponse.Content.ReadFromJsonAsync<ProductResponse>();

        // EXECUTE - Update the product
        var updateRequest = new UpdateProductRequest
        {
            Name = "Updated Name",
            Description = "Updated via E2E test",
            Price = 149.99m,
            CategoryId = 1,
            IsActive = true
        };
        var updateResponse = await _client.PutAsJsonAsync($"/api/products/{created!.Id}", updateRequest);

        // VALIDATE - HTTP 204 No Content
        updateResponse.StatusCode.Should().Be(HttpStatusCode.NoContent);

        // VALIDATE - Changes persisted to database
        var getResponse = await _client.GetAsync($"/api/products/{created.Id}");
        var updated = await getResponse.Content.ReadFromJsonAsync<ProductResponse>();
        updated!.Name.Should().Be("Updated Name");
        updated.Price.Should().Be(149.99m);
    }
}

What's being tested:

HTTP Request → Controller → Service → Repository → SQL Server Container
     ↓              ↓            ↓           ↓              ↓
  GET /api/    ProductsCtrl  ProductSvc  ProductRepo    Real DB
  products/1

Benefits of E2E tests:

• Tests the entire HTTP pipeline
• Validates routing, model binding, and serialization
• Ensures controller ↔ service ↔ repository integration
• Uses real database (not mocked)

Visual demo:

---

Running the Tests

Local Development

# Ensure Docker is running
docker ps

# Run all tests
dotnet test

# Run only integration tests
dotnet test --filter "FullyQualifiedName~Integration"

# Run specific test class
dotnet test --filter "FullyQualifiedName~ProductRepositoryTests"

---

Summary

What you learned:

✅ Testcontainers spin up real databases in Docker for integration testing

✅ Fixtures share containers across multiple test classes (performance)

✅ FluentMigrator ensures test schema matches production

✅ E2E tests validate the entire HTTP → Controller → Service → Repository → Database pipeline

Testing strategy:

Layer	Test Type	Tools
Business Logic	Unit Tests	Moq, xUnit
Data Access	Integration Tests	Testcontainers, xUnit
HTTP Pipeline	E2E Tests	WebApplicationFactory, Testcontainers