Part III: SQL Server Integration Testing with Testcontainers

tl;dr Take your containerised SQL Server setup to the next level with Testcontainers for .NET. Spin up real SQL Server instances on-demand during your test runs, execute your integration tests against them, and have them torn down automatically. No more shared test databases, no more test pollution, and no more “it works on my machine”.

In Part I and Part II of this series, we looked at setting up SQL Server using Docker Compose for local development. This works great for day-to-day development, but what about integration testing?

Integration tests that hit a real database are notoriously tricky to manage. Common problems include:

Shared state: Tests pollute each other when sharing the same database
Setup complexity: Developers need a running SQL Server before they can run tests
CI/CD headaches: Build agents need database access configured
Flaky tests: Network issues or database state cause intermittent failures

Testcontainers solves these problems elegantly. It’s a library that allows you to spin up Docker containers programmatically during your test runs. Each test (or test class) gets its own isolated database instance that is created before the tests run and destroyed afterwards.

Pre-Requisites

You’ll need:

.NET 10 SDK
Docker Desktop (or Podman) running
Your favourite IDE (Visual Studio, Rider, or VS Code)

Note: Testcontainers uses the Docker API to manage containers, so you need a container runtime available. If you’re using Podman, ensure the Docker compatibility socket is enabled.

Project Setup

Let’s create a simple project structure to demonstrate Testcontainers. We’ll have a class library with some data access code and a test project.

1
2
3
4
5
6
mkdir TestcontainersDemo && cd TestcontainersDemo
dotnet new sln
dotnet new classlib -n DataAccess
dotnet new xunit -n DataAccess.Tests
dotnet sln add DataAccess DataAccess.Tests
dotnet add DataAccess.Tests reference DataAccess

Now let’s add the required NuGet packages:

1
2
3
4
dotnet add DataAccess package Microsoft.Data.SqlClient
dotnet add DataAccess package dbup-sqlserver
dotnet add DataAccess.Tests package Testcontainers.MsSql
dotnet add DataAccess.Tests package Respawn

The key package here is Testcontainers.MsSql which provides SQL Server-specific support. Testcontainers has modules for many databases including PostgreSQL, MySQL, MongoDB, and more. We’re also using dbup-sqlserver for database migrations.

Database Migrations with DbUp

Rather than manually creating tables in code, let’s use DbUp to manage our database schema through SQL migration scripts. This is a more realistic approach that mirrors how production applications handle schema changes.

Migration Scripts

Create a Migrations folder in the DataAccess project and add these SQL scripts:

001_CreateProductsTable.sql

1
2
3
4
5
6
7
8
CREATE TABLE Products (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

CREATE INDEX IX_Products_Name ON Products (Name);

002_SeedInitialProducts.sql

1
2
3
4
INSERT INTO Products (Name, Price) VALUES
    ('Widget', 9.99),
    ('Gadget', 19.99),
    ('Gizmo', 29.99);

DbUp runs scripts in alphabetical order and tracks which scripts have been executed in a SchemaVersions table, so each migration only runs once.

The DatabaseMigrator Class

Create a DatabaseMigrator.cs file to handle running migrations:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
using System.Reflection;
using DbUp;
using DbUp.Engine;

namespace DataAccess;

public static class DatabaseMigrator
{
    public static DatabaseUpgradeResult Migrate(string connectionString)
    {
        EnsureDatabase.For.SqlDatabase(connectionString);

        var upgrader = DeployChanges.To
            .SqlDatabase(connectionString)
            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
            .WithTransaction()
            .LogToConsole()
            .Build();

        return upgrader.PerformUpgrade();
    }

    public static DatabaseUpgradeResult MigrateSilently(string connectionString)
    {
        EnsureDatabase.For.SqlDatabase(connectionString);

        var upgrader = DeployChanges.To
            .SqlDatabase(connectionString)
            .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
            .WithTransaction()
            .LogToNowhere()
            .Build();

        return upgrader.PerformUpgrade();
    }
}

Don’t forget to embed the SQL files as resources in your .csproj:

1
2
3
<ItemGroup>
  <EmbeddedResource Include="Migrations\*.sql" />
</ItemGroup>

A Simple Repository to Test

Now let’s create a repository that works with our migrated schema. In the DataAccess project, create a ProductRepository.cs file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
using Microsoft.Data.SqlClient;

namespace DataAccess;

public record Product(int Id, string Name, decimal Price);

public class ProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<int> AddProductAsync(string name, decimal price)
    {
        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        const string sql = """
            INSERT INTO Products (Name, Price)
            OUTPUT INSERTED.Id
            VALUES (@Name, @Price)
            """;

        await using var command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Price", price);

        return (int)(await command.ExecuteScalarAsync())!;
    }

    public async Task<Product?> GetProductByIdAsync(int id)
    {
        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        const string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @Id";

        await using var command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@Id", id);

        await using var reader = await command.ExecuteReaderAsync();
        if (await reader.ReadAsync())
        {
            return new Product(
                reader.GetInt32(0),
                reader.GetString(1),
                reader.GetDecimal(2));
        }

        return null;
    }

    public async Task<IReadOnlyList<Product>> GetAllProductsAsync()
    {
        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        const string sql = "SELECT Id, Name, Price FROM Products";

        await using var command = new SqlCommand(sql, connection);
        await using var reader = await command.ExecuteReaderAsync();

        var products = new List<Product>();
        while (await reader.ReadAsync())
        {
            products.Add(new Product(
                reader.GetInt32(0),
                reader.GetString(1),
                reader.GetDecimal(2)));
        }

        return products;
    }
}

Notice the repository no longer has an InitialiseSchemaAsync method - schema management is now handled by DbUp migrations.

Writing Integration Tests with Testcontainers

Now for the interesting part. In the DataAccess.Tests project, let’s create our integration tests.

The Test Fixture

First, we’ll create a fixture that manages the SQL Server container lifecycle and runs our migrations. Create a file called SqlServerFixture.cs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
using Testcontainers.MsSql;

namespace DataAccess.Tests;

public class SqlServerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-CU12-ubuntu-22.04")
        .Build();

    public string ConnectionString => _container.GetConnectionString();

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

        // Run DbUp migrations to set up the schema
        var result = DatabaseMigrator.MigrateSilently(ConnectionString);
        if (!result.Successful)
        {
            throw new Exception($"Database migration failed: {result.Error}");
        }
    }

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

Let’s break down what’s happening here:

MsSqlBuilder() creates a builder for configuring the SQL Server container
WithImage() specifies the exact SQL Server image to use - I’m using the same image from our Docker Compose setup for consistency
Build() creates the container configuration (but doesn’t start it yet)
IAsyncLifetime is an xUnit interface that provides InitializeAsync and DisposeAsync hooks
After the container starts, we run DbUp migrations to set up the schema
GetConnectionString() returns a connection string to the running container, including the dynamically assigned port

Note: Testcontainers automatically handles the sa password and port mapping. Each container gets a random available port, so you can run tests in parallel without port conflicts. The migrations run once when the fixture initialises, so your database is ready to use immediately.

The Tests

Now let’s write some tests. Create a file called ProductRepositoryTests.cs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
namespace DataAccess.Tests;

public class ProductRepositoryTests : IClassFixture<SqlServerFixture>
{
    private readonly SqlServerFixture _fixture;

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

    [Fact]
    public async Task SeededProducts_ShouldExist()
    {
        // Arrange - migrations have already run and seeded 3 products
        var repository = new ProductRepository(_fixture.ConnectionString);

        // Act
        var products = await repository.GetAllProductsAsync();

        // Assert - verify seeded data from migration 002
        Assert.True(products.Count >= 3);
        Assert.Contains(products, p => p.Name == "Widget");
        Assert.Contains(products, p => p.Name == "Gadget");
        Assert.Contains(products, p => p.Name == "Gizmo");
    }

    [Fact]
    public async Task AddProduct_ShouldReturnNewId()
    {
        // Arrange - schema already set up by migrations
        var repository = new ProductRepository(_fixture.ConnectionString);

        // Act
        var id = await repository.AddProductAsync("New Product", 9.99m);

        // Assert
        Assert.True(id > 0);
    }

    [Fact]
    public async Task GetProductById_ShouldReturnProduct_WhenExists()
    {
        // Arrange
        var repository = new ProductRepository(_fixture.ConnectionString);
        var id = await repository.AddProductAsync("Test Gadget", 19.99m);

        // Act
        var product = await repository.GetProductByIdAsync(id);

        // Assert
        Assert.NotNull(product);
        Assert.Equal("Test Gadget", product.Name);
        Assert.Equal(19.99m, product.Price);
    }

    [Fact]
    public async Task GetProductById_ShouldReturnNull_WhenNotExists()
    {
        // Arrange
        var repository = new ProductRepository(_fixture.ConnectionString);

        // Act
        var product = await repository.GetProductByIdAsync(99999);

        // Assert
        Assert.Null(product);
    }

    [Fact]
    public async Task GetAllProducts_ShouldReturnAllProducts()
    {
        // Arrange
        var repository = new ProductRepository(_fixture.ConnectionString);
        await repository.AddProductAsync("Product A", 10.00m);
        await repository.AddProductAsync("Product B", 20.00m);

        // Act
        var products = await repository.GetAllProductsAsync();

        // Assert
        // We use >= because tests share the database and include seeded data + other tests' data
        Assert.True(products.Count >= 5); // 3 seeded + 2 added here
    }
}

A few things to note:

IClassFixture<SqlServerFixture> tells xUnit to create a single instance of our fixture and share it across all tests in this class
The fixture is injected via the constructor
Migrations run once when the fixture initialises, so the database has the schema and seeded data ready
Each test creates its own repository instance but shares the same database container
The container starts once before any tests run, and is disposed after all tests complete

Running the Tests

Run the tests with:

1
dotnet test

The first run will take a bit longer as Docker pulls the SQL Server image (if not already cached). Subsequent runs will be much faster.

You should see output similar to:

1
2
3
4
Starting test execution, please wait...
A total of 1 test files matched the specified pattern.

Passed!  - Failed:     0, Passed:     4, Skipped:     0, Total:     4

Isolating Tests with Fresh Containers

The example above shares a container across all tests in a class. This is efficient but means tests can affect each other. If you need complete isolation, you can create a fresh container per test:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
using Testcontainers.MsSql;

namespace DataAccess.Tests;

public class IsolatedProductRepositoryTests : IAsyncLifetime
{
    private readonly MsSqlContainer _container = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-CU12-ubuntu-22.04")
        .Build();

    private ProductRepository _repository = null!;

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

        // Run migrations for this isolated container
        var result = DatabaseMigrator.MigrateSilently(_container.GetConnectionString());
        if (!result.Successful)
        {
            throw new Exception($"Database migration failed: {result.Error}");
        }

        _repository = new ProductRepository(_container.GetConnectionString());
    }

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

    [Fact]
    public async Task FreshDatabase_ContainsOnlySeededProducts()
    {
        // This test has a completely isolated database with only seeded data
        var products = await _repository.GetAllProductsAsync();

        // Should have exactly 3 seeded products from migration 002
        Assert.Equal(3, products.Count);
        Assert.Contains(products, p => p.Name == "Widget");
        Assert.Contains(products, p => p.Name == "Gadget");
        Assert.Contains(products, p => p.Name == "Gizmo");
    }
}

Note: Creating a new container per test is slower but guarantees complete isolation. See the performance section below for guidance on choosing the right approach.

Performance Considerations

One of the most common concerns with Testcontainers is performance. SQL Server containers take time to start - typically 15-30 seconds depending on your machine. If you’re not careful, this overhead can make your test suite painfully slow.

Container Startup Costs

Here’s a breakdown of the different approaches and their typical overhead:

Approach	Container Starts	Typical Overhead
Per-test (`IAsyncLifetime` on test class)	Every test	15-30s per test
Per-class (`IClassFixture`)	Once per test class	15-30s per class
Per-collection (`ICollectionFixture`)	Once for entire collection	15-30s total
Shared container + Respawn	Once per test run	15-30s startup + ~50ms per reset

The per-test approach shown above should be reserved for cases where you genuinely need complete isolation and can tolerate the overhead. For most scenarios, sharing containers is the way to go.

Sharing Across Multiple Test Classes with `ICollectionFixture`

If you have multiple test classes that need database access, you can share a single container across all of them using xUnit’s ICollectionFixture:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
using Testcontainers.MsSql;

namespace DataAccess.Tests;

// Define the collection fixture
public class DatabaseCollection : ICollectionFixture<SqlServerFixture>
{
    // This class has no code - it's just a marker
    // The collection name "Database" links test classes to this fixture
}

// Define the collection
[CollectionDefinition("Database")]
public class SqlServerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _container = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-CU12-ubuntu-22.04")
        .Build();

    public string ConnectionString => _container.GetConnectionString();

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

Now any test class can join the collection and share the same container:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[Collection("Database")]
public class ProductRepositoryTests
{
    private readonly SqlServerFixture _fixture;

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

    // Tests here share the container with other classes in the "Database" collection
}

[Collection("Database")]
public class OrderRepositoryTests
{
    private readonly SqlServerFixture _fixture;

    public OrderRepositoryTests(SqlServerFixture fixture)
    {
        _fixture = fixture;
    }

    // Same container, different test class
}

With this approach, the container starts once when the first test in the collection runs, and is disposed after the last test completes. This dramatically reduces overhead when you have many test classes.

Fast Database Resets with Respawn

Sharing a container is efficient, but it introduces the risk of tests polluting each other. One test might insert data that causes another test to fail. You could delete data manually, but that’s tedious and error-prone.

Respawn solves this elegantly. It’s a library by Jimmy Bogard that intelligently resets your database to a clean state by deleting all data while respecting foreign key constraints. A reset typically takes around 50ms - much faster than restarting a container.

Here’s how to use Respawn with Testcontainers and DbUp:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
using Respawn;
using Testcontainers.MsSql;

namespace DataAccess.Tests;

[Collection("Database")]
public class ProductRepositoryTests : IAsyncLifetime
{
    private readonly SqlServerFixture _fixture;
    private Respawner _respawner = null!;

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

    public async Task InitializeAsync()
    {
        // Create the respawner - this analyses the database schema
        // Important: Exclude SchemaVersions table so DbUp's migration history is preserved
        _respawner = await Respawner.CreateAsync(_fixture.ConnectionString, new RespawnerOptions
        {
            DbAdapter = DbAdapter.SqlServer,
            SchemasToInclude = ["dbo"],
            TablesToIgnore = ["SchemaVersions"]  // DbUp's migration tracking table
        });

        // Reset the database before each test (clears all data except migration history)
        await _respawner.ResetAsync(_fixture.ConnectionString);
    }

    public Task DisposeAsync() => Task.CompletedTask;

    [Fact]
    public async Task AddProduct_ShouldReturnNewId()
    {
        var repository = new ProductRepository(_fixture.ConnectionString);

        var id = await repository.AddProductAsync("Widget", 9.99m);

        Assert.True(id > 0);
    }

    [Fact]
    public async Task GetAllProducts_ReturnsOnlyProductsFromThisTest()
    {
        var repository = new ProductRepository(_fixture.ConnectionString);
        await repository.AddProductAsync("Product A", 10.00m);
        await repository.AddProductAsync("Product B", 20.00m);

        var products = await repository.GetAllProductsAsync();

        // Now we can assert exact count because database was reset before this test
        // Note: Seeded data from migrations is also cleared by Respawn
        Assert.Equal(2, products.Count);
    }
}

The key differences from our earlier examples:

The test class implements IAsyncLifetime in addition to using ICollectionFixture
InitializeAsync runs before each test, resetting the database to a clean state
We exclude SchemaVersions from Respawn so DbUp doesn’t try to re-run migrations
Respawn clears all data including seeded data, giving you a truly empty database
We can now make exact assertions (e.g., Assert.Equal(2, ...) instead of Assert.True(>= 2))
The container is still shared, so we get the best of both worlds: fast startup and test isolation

Note: The first call to Respawner.CreateAsync analyses the database schema, which takes a moment. If your schema doesn’t change between tests, you can cache the Respawner instance in the fixture for even better performance.

Choosing the Right Approach

Here’s a simple decision tree:

Do you need complete isolation with zero risk of test pollution?
- Yes → Use per-test containers (accept the performance cost)
- No → Continue to step 2
Do you have multiple test classes that need database access?
- Yes → Use ICollectionFixture to share a container
- No → Use IClassFixture to share within the class
Are your tests sensitive to existing data in the database?
- Yes → Add Respawn to reset between tests
- No → Let tests accumulate data (use >= assertions where needed)

For most projects, I recommend ICollectionFixture + Respawn. This gives you a single container startup (15-30s), fast resets between tests (~50ms each), and complete test isolation. It’s the sweet spot between performance and reliability.

Customising the Container

Testcontainers provides many configuration options. Here are some useful ones:

1
2
3
4
5
6
7
8
private readonly MsSqlContainer _container = new MsSqlBuilder()
    .WithImage("mcr.microsoft.com/mssql/server:2022-CU12-ubuntu-22.04")
    .WithPassword("YourStr0ngP@ssword!")  // Custom password
    .WithEnvironment("MSSQL_PID", "Developer")  // SQL Server edition
    .WithEnvironment("TZ", "Etc/UTC")  // Timezone
    .WithWaitStrategy(Wait.ForUnixContainer()
        .UntilInternalTcpPortIsAvailable(1433))  // Custom wait strategy
    .Build();

You can also execute SQL scripts after the container starts:

1
2
3
4
5
6
7
8
public async Task InitializeAsync()
{
    await _container.StartAsync();

    // Execute initialisation script
    var script = await File.ReadAllTextAsync("init.sql");
    await _container.ExecScriptAsync(script);
}

This is similar to what we did in Part II with Docker Compose, but now it’s happening programmatically during your test setup.

Running in CI/CD

One of the biggest advantages of Testcontainers is that your tests will run the same way in CI/CD as they do locally. As long as your build agent has Docker available, the tests will work.

For GitHub Actions, ensure Docker is available (it is by default on ubuntu-latest):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
name: Integration Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup .NET
        uses: actions/setup-dotnet@v4
        with:
          dotnet-version: '10.0.x'

      - name: Run tests
        run: dotnet test --verbosity normal

That’s it. No database setup, no connection string configuration, no Docker Compose files to manage. The tests spin up their own SQL Server instances.

Best Practices

Here are some additional tips I’ve found useful when working with Testcontainers:

Pin your image versions: Always specify an exact image tag (e.g., 2022-CU12-ubuntu-22.04) rather than latest. This ensures consistent behaviour across environments and over time.
Optimise for performance: See the Performance Considerations section above for detailed guidance on sharing containers with ICollectionFixture and using Respawn for fast resets.
Don’t forget ARM Macs: If your team includes developers on Apple Silicon Macs, the same Rosetta emulation that works for Docker Compose (mentioned in Part I) applies here too.
Keep test data minimal: Only insert the data you need for each test. This keeps tests fast and makes them easier to understand.
Use WithReuse() during development: Testcontainers supports container reuse across test runs with .WithReuse(true). This skips container startup entirely if a matching container already exists, which is great for rapid iteration during development (but disable it in CI).

Conclusion

Testcontainers transforms integration testing from a painful chore into a smooth experience. Your tests become truly self-contained - they carry their infrastructure with them. No more “works on my machine” issues, no more shared test databases causing flaky tests, and no more complex CI/CD database setup.

Combined with the Docker Compose setup from the earlier posts in this series, you now have a complete solution: Docker Compose for local development, Testcontainers for automated testing. Both use the same SQL Server image, ensuring consistency across your workflow.

More on GitHub

I have captured the code from this post in a repo on GitHub.

Thanks for reading. Are you using Testcontainers? I’d love to hear about your experiences or alternative approaches in the comments below.

Pre-Requisites#

Project Setup#

Database Migrations with DbUp#

Migration Scripts#

The DatabaseMigrator Class#

A Simple Repository to Test#

Writing Integration Tests with Testcontainers#

The Test Fixture#

The Tests#

Running the Tests#

Isolating Tests with Fresh Containers#

Performance Considerations#

Container Startup Costs#

Sharing Across Multiple Test Classes with ICollectionFixture#

Fast Database Resets with Respawn#

Choosing the Right Approach#

Customising the Container#

Running in CI/CD#

Best Practices#

Conclusion#

More on GitHub#