Singleton Pattern with TestContainers and SQL Server in C#

When working with web services there is often data access which could benefit from some level of coverage in integration tests. That means re-creating our storage, whatever that may be, in our tests. A key consideration with how we create our storage technology is test independence. There are many approaches to managing test independence such as wrapping database calls in a transaction, carefully managing storage IDs, real test databases, or a combination of these. Instead, this article demonstrates an approach where each test class gets a database for itself. It also serves as a dotnet example using the singleton pattern with test containers which, at the time of writing this article, there appears to be few examples of.

Like the Java package, Testcontainers for dotnet manages the Docker resources itself to ensure they are cleaned up at the end of a test suite run, without any containers left hanging.

As an example, a singleton can be defined that will be responsible for the SQL Server instance.

public sealed class SqlServerSingleton
{
    private static readonly Lazy<Task<SqlServerSingleton>> Lazy = new(async () =>
    {
        var singleton = new SqlServerSingleton();
        await singleton.InitializeAsync();
        return singleton;
    });

    private readonly MsSqlContainer _container = new MsSqlBuilder().Build();

    private SqlServerSingleton()
    {
    }

    public static Task<SqlServerSingleton> Instance => Lazy.Value;

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

    public string GetConnectionString()
    {
        return _container.GetConnectionString();
    }
}

Using xUnit we can write a class fixture which references the SqlServerSingleton instance. In the below example the connection string to the SQL Server instance running in Docker is fetched from the singleton. A unique database name is added to the connection string. With minimal setup we have a connection string to a real SQL Server instance with unique database name.

public class IntegrationTestFixture : IAsyncLifetime
{
    public string? DatabaseConnectionString;

    public async Task InitializeAsync()
    {
        var sqlServerSingleton = await SqlServerSingleton.Instance;
        var serverConnectionString = sqlServerSingleton.GetConnectionString();
        DatabaseConnectionString = new SqlConnectionStringBuilder(serverConnectionString)
        {
            InitialCatalog = $"integration-test-{Guid.NewGuid()}"
        }.ConnectionString;
    }

    public Task DisposeAsync()
    {
        return Task.CompletedTask;
    }
}

What happens next depends upon the desired setup. The database so far does not exist. So we need to consider the database schema. Likely there is already a mechanism for managing migrations in the application the integration tests are being written for. One straight forward approach is to ensure the database is created and up to date within the class fixture.

A complete example with data migrations using DbUp can be found on GitHub here.

If a WebApplicationFactory is being used to initialize a web service for each test class, override the configuration to feed in the connection string. Alternatively, whatever data access class being covered can be initialized using the connection string.

With this setup test classes can be run independently and in parallel. The approach may not be appropriate in all situations, for example with many test classes and a limited degree of parallelization, slow migrations may drag out test suite run durations. However there are several advantages. There is only a single SQL Server instance across all tests, while xUnit can still run the tests in parallel. Ordinarily, to share test context between test classes a test collection would do the job. Test collections are run sequentially, so there can be a typical trade off against parallelization when sharing a database server between test classes. To get up and running requires very little setup, so if you're unsure give it a go one rainy Friday afternoon.

The examples in this article focuses on the SQL Server TestContainers module. The singleton test container can also be used with any test container necessary. Whether it is SQL Server, or Blob Storage, or one of the many modules available out the box from TestContainers.