Supercharge Your dotnet Integration Tests: Using Testcontainers, xUnit, and AssemblyFixture for Real SQL Server Testing

Unlock Reliable SQL Server Integration Testing in .NET with Testcontainers, xUnit, and AssemblyFixture

Testing database functionality in .NET applications can feel like a juggling act. Between keeping test data isolated, resetting environments, and avoiding conflicts, it’s easy to see why developers are constantly looking for better ways to streamline the process. What if I told you that by using Testcontainers, xUnit, and AssemblyFixture, you could create a repeatable, reliable, and clean testing setup for SQL Server that runs automatically within your test suite?

In this post, I’ll walk you through how these tools work together to create a disposable, containerized SQL Server instance for testing, along with a flexible testing framework. We’ll discuss the setup process, write a few integration tests, and go over best practices to help make this part of your workflow.

Why Use Containerized Databases for Testing?

Testing against a live database can get messy for a few reasons. Here’s a quick breakdown of some common issues:

1. Data Contamination: Every test leaves some trace, and when it’s time to run them again, that leftover data can cause issues.
2. Inconsistent Results: If someone else modifies the database, your tests can start failing unpredictably.
3. Environment Discrepancies: Local databases might not match production environments.

By using containers, you can avoid these headaches. Containerized databases allow you to set up fresh environments for each test, ensuring consistency and isolation without manual intervention.

What Are Testcontainers and Testcontainers.MsSql?

Testcontainers is a powerful .NET library that helps you create containerized environments on-demand, right from your test code. Built on Docker, Testcontainers allows you to spin up disposable environments for testing, then tear them down automatically once your tests complete.

The Testcontainers.MsSql module is specifically for Microsoft SQL Server, meaning you can spin up a SQL Server container with just a few lines of code and test your database operations in an environment that mimics production — without actually touching your live database.

Key Benefits
Using Testcontainers.MsSql comes with several advantages:

• Clean Start: Each test runs in a fresh environment, so no leftover data.
• Cross-Platform: Runs on any OS that supports Docker.
• Easy Teardown: The container is automatically destroyed when tests are done, leaving no traces.

Setting Up xUnit AssemblyFixture for Shared Resources

If you’ve worked with xUnit, you probably already know that it’s a popular, open-source testing framework for .NET. One of the things that makes xUnit versatile is its support for fixtures, which allow you to share setup and teardown logic across tests.

AssemblyFixture is an xUnit feature that lets you share a single setup across all test classes within a test assembly, which is incredibly useful when you’re working with resources like a SQL Server container that are slow or resource-intensive to initialize.

Step-by-Step Setup with Testcontainers, xUnit, and AssemblyFixture

Let’s jump into how to set up an integration testing suite with these tools in your .NET project.

Step 1: Install Required NuGet Packages
First, add the necessary NuGet packages. In your test project directory, run:

dotnet add package Testcontainers.MsSql
dotnet add package xunit
dotnet add package xunit.runner.visualstudio
dotnet add package Microsoft.NET.Test.Sdk

Step 2: Set Up the AssemblyFixture for SQL Server Container
Now, let’s create a fixture class to manage the SQL Server container’s lifecycle. The fixture class will:

• Start a SQL Server container before any tests run.
• Expose the container’s connection string for use in test classes

Here’s a sample implementation:

using System;
using System.Threading.Tasks;
using Testcontainers.MsSql;
using Xunit;

public class MsSqlContainerFixture : IAsyncLifetime
{
    public MsSqlContainer MsSqlContainer { get; private set; }

    public string ConnectionString => MsSqlContainer.GetConnectionString();

    public async Task InitializeAsync()
    {
        MsSqlContainer = new MsSqlBuilder()
            .WithPassword("your_password") // Replace with a strong password
            .Build();

        await MsSqlContainer.StartAsync();
    }

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

In this code, MsSqlContainerFixture is a class that sets up a containerized SQL Server instance and exposes its connection string. The fixture ensures the container starts up before tests and stops after they finish, which helps us avoid repeated database setup and teardown.

Step 3: Define the Collection Fixture in xUnit
Next, we need to configure xUnit to use the fixture. To do this, create a MsSqlCollection class and define it as a CollectionFixture:

[CollectionDefinition("MsSql Server Collection")]
public class MsSqlCollection : ICollectionFixture<MsSqlContainerFixture> { }

This collection fixture will be shared across all test classes in the assembly, meaning we can reuse the SQL Server container for multiple test classes.

Step 4: Write Integration Tests Using the Containerized SQL Server
With the container running and the connection string available, you’re ready to write tests that interact with the containerized database. Here’s a sample test class to demonstrate:

using Xunit;

[Collection("MsSql Server Collection")]
public class MyDatabaseTests
{
    private readonly MsSqlContainerFixture _fixture;

    public MyDatabaseTests(MsSqlContainerFixture fixture)
    {
        _fixture = fixture;
    }

    [Fact]
    public async Task TestDatabaseInsert()
    {
        // Use the connection string from the fixture
        var connectionString = _fixture.ConnectionString;

        // Example: Insert a row, query it, and assert the results
    }

    [Fact]
    public async Task TestDatabaseQuery()
    {
        var connectionString = _fixture.ConnectionString;

        // Perform a query operation and verify the results
    }
}

Each test can access the MsSqlContainerFixture and use its connection string to interact with the database. This setup ensures each test is isolated from the others, while avoiding the need to repeatedly start and stop the SQL Server container.

Step 5: Running the Tests
Run your tests as usual, either through Visual Studio Test Explorer or by using the dotnet test command. The containerized SQL Server instance will be available for the duration of the test run and will be automatically cleaned up afterward.

Best Practices for Containerized Integration Testing

Here are a few tips to keep your tests fast, consistent, and reliable:

1. Keep Tests Isolated: Ensure each test starts with a clean slate by either resetting tables or using transactions that roll back after each test.
2. Avoid Hardcoded Secrets: Store sensitive data like passwords in environment variables or a secure store, not in your code.
3. Parallel Testing: For parallel tests, each test should ideally have its own container instance to avoid conflicts.
4. Optimize Container Lifecycle: Instead of spinning up a new container for every test, use xUnit’s AssemblyFixture to share a single container across tests, as demonstrated here.

Conclusion

Combining Testcontainers.MsSql and xUnit AssemblyFixture makes it easy to set up robust integration tests for .NET applications, without worrying about database pollution or environment discrepancies. This setup is a great way to ensure your tests are consistent and isolated, mirroring real-world conditions as closely as possible.

Once you’ve set this up, you’ll wonder how you ever managed without it. Start using these tools today to simplify your .NET integration testing and build better, more resilient applications.