Faster .NET Database Integration Tests with Respawn and xUnit

The nuances of data access are myriad, so when writing tests around complex data scenarios, I recommend just working with the actual database. Over time, you’ll find yourself with more valuable tests but, often, relatively slower tests compared to their in-memory alternatives. Like all things, there are trade-offs, but you can still strategize in making your tests faster.

In this post, I’ll show you how to take advantage of xUnit class fixtures and the OSS library Respawn to manage the state of your database across tests. This will help speed up your tests when faster steps replace a few expensive ones.

What is Respawn?

Respawn is a utility library designed to help developers reset databases to an “initial state”. With some configuration, Respawn can intelligently reset a database for testing use cases.

Other strategies might employ complete database tear-downs, complex transaction management, or expensive Docker containerization strategies. When mixed with your database management strategy around database migrations, look-up tables, and other stateful database elements, the overhead can compound over time and hurt the developer feedback loop many crave from an excellent test suite.

Respawn allows you to choose which tables and schemas to ignore in the reset process.

var respawner = await Respawner.CreateAsync(connection, new RespawnerOptions
{
    TablesToIgnore = new Table[]
    {
        "sysdiagrams",
        "tblUser",
        "tblObjectType",
        new Table("MyOtherSchema", "MyOtherTable")
    },
    SchemasToExclude = new []
    {
        "RoundhousE"
    }
});

It also supports multiple database providers, including Microsoft SQL Server, Postgres, MySQL, Oracle, and Informix.

Using xUnit fixtures (which we’ll see later), you only need to call a single method to reset the database.

await respawner.ResetAsync("MyConnectionStringName");

After every reset, your database will return to its initial state and be ready for another round of test assertions.

xUnit ReSpawn Fixture

I’ve created a sample Respawn and xUnit project with all the code in this blog post so you can try it out in your development environment.

For folks following along in the post, I’m using a GlobalUsing.cs file for namespaces and some C# 12 (.NET 8) features. Here are the required namespaces.

global using Dapper;
global using Dapper.Contrib.Extensions;
global using Microsoft.Data.SqlClient;
global using Respawn;
global using Xunit;
global using Xunit.Abstractions;

When working with Respawn, you’ll need a fixture. In the world of xUnit, fixtures are shared resources across test classes. In the case of this sample, we’ll create a DatabaseFixture, which will manage the database using a Respawner instance.

namespace Spawn;

// ReSharper disable once ClassNeverInstantiated.Global
public class DatabaseFixture : IAsyncLifetime
{
    private const string DatabaseName = "test";
    
    private readonly Dictionary<string, string> connectionStrings = new()
    {
        { DatabaseName, $"Data Source=localhost,11433;Database={DatabaseName};User Id=sa;Password=Pass123!;Encrypt=FALSE;" },
        { "master", "Data Source=localhost,11433;Database=master;User Id=sa;Password=Pass123!;Encrypt=FALSE;" }
    };

    public async Task<SqlConnection> GetOpenConnectionAsync(string databaseName = DatabaseName)
    {
        var sqlConnection = new SqlConnection(connectionStrings[databaseName]);
        await sqlConnection.OpenAsync();
        return sqlConnection;
    }

    private Respawner respawn = null!;

    public async Task InitializeAsync()
    {
        await MigrateAsync();

        respawn = await Respawner.CreateAsync(connectionStrings[DatabaseName],
            new RespawnerOptions
            {
                DbAdapter = DbAdapter.SqlServer
            });
    }

    private async Task MigrateAsync()
    {
        // only doing this for the sample,
        // you'll likely already have the database created somewhere
        {
            try
            {
                await using var connection = await GetOpenConnectionAsync("master");
                await connection.ExecuteAsync(
                    // lang=SQL
                    $"""
                     IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE [name] = N'{DatabaseName}')
                        CREATE DATABASE {DatabaseName};
                     """
                );
            }
            catch (SqlException e)
            {
                throw new Exception("Create and run the container found in the docker-compose.yml", e);
            }
        }

        {
            await using var connection = await GetOpenConnectionAsync();
            await connection.ExecuteAsync(
                // lang=SQL
                """
                IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[People]') AND type in (N'U'))
                  BEGIN
                      CREATE TABLE People
                      (
                          ID INT PRIMARY KEY IDENTITY,
                          FirstName NVARCHAR(50),
                          LastName NVARCHAR(50),
                          Age INT,
                          Email NVARCHAR(255)
                      );
                  END;
                """);
        }
            
    }

    public Task ResetAsync()
        => respawn.ResetAsync(connectionStrings[DatabaseName]);

    public async Task DisposeAsync()
    {
        await using var connection = await GetOpenConnectionAsync("master");
        await connection.ExecuteAsync(
            // lang=SQL
            $"""
            IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = N'{DatabaseName}')
            BEGIN
                ALTER DATABASE {DatabaseName}
                SET SINGLE_USER -- Disallow multi-user access.
                WITH ROLLBACK IMMEDIATE -- Rollback any transaction in progress.
                DROP DATABASE {DatabaseName};
            END;
            """
        );
    }
}

We must utilize the IAsyncLifetime interface in xUnit to call Respawn’s asynchronous methods. I’ve also added a helper method of GetOpenConnectionAsync to make it easier to write tests using the fixture.

Note that the migration strategy in the fixture does not use any particular approach commonly found in the .NET ecosystem. You’ll likely use EF Core migrations, Roundhouse, DbUp, or another migration strategy. Adjust the code for your particular use case. I’ve also taken the steps to create the database if it does not currently exist. Creating the database might be optional based on your development environment.

Now let’s use our fixture in a new test class.

namespace Spawn;

public class RespawnTests(DatabaseFixture database, ITestOutputHelper output)
    : IClassFixture<DatabaseFixture>, IAsyncLifetime
{
    [Fact]
    public async Task Can_Insert_Person_Into_People()
    {
        await using var connection = await database.GetOpenConnectionAsync();
        await connection.InsertAsync<Person>(new()
        {
            FirstName = "Khalid",
            LastName = "Abuhakmeh",
            Age = 40,
            Email = "khalid@example.com"
        });

        var person = await connection.QueryFirstAsync<Person>("select top 1 * from people");
        var total = await connection.ExecuteScalarAsync("select count(ID) from People");

        output.WriteLine($"{person.FirstName} says hi!");

        Assert.NotNull(person);
        Assert.Equal(expected: 1, actual: total);
    }

    [Fact]
    public async Task People_Table_Is_Always_Empty()
    {
        await using var connection = await database.GetOpenConnectionAsync();
        var person = await connection.QueryFirstOrDefaultAsync<Person>("select top 1 * from people");
        Assert.Null(person);
    }

    public Task InitializeAsync()
        => Task.CompletedTask;

    public Task DisposeAsync()
        => database.ResetAsync();
}

We use the IAsyncLifetime interface again to ensure that our database is reset after each test is run. In the DisposeAsync method, we invoke the fixture’s ResetAsync method, which resets our database to its initial state. That’s it! Easy peasy.

On my development machine, the total time of the tests is about 250ms, with the bulk of the time spent creating and tearing down my database for the sample. In your use case, you can cut the cost of database creation and teardown by creating the database outside the scope of the class fixture.

Conclusion

With Respawn and xUnit class fixtures, you can significantly improve your integration test performance and get a tighter feedback loop. You also get the added value of knowing your code is testing against the “real” thing. Integration tests can help catch behavioral changes in the underlying database technology and find issues with queries, and database features you’d otherwise miss when working with stubs.

I hope you found this post helpful, and please give the sample a try. I’ve set it up so you can run through this sample in seconds, and it should give you a great jumping-off point.

Thanks for reading and sharing my posts with friends and colleagues. Cheers.