Effortless integration test isolation with .NET, XUnit and database transactions
Let's work out a solution for achieving this sort of flow in .NET WebAPI integration tests:
- Tests are run against an actual database
- Meaning we test the full database functionality in addition to the application logic
- Database is initialized once per test run
- Compute is not wasted on setting up e.g. a database per test
- Tests are isolated and can be run in parallel
- A test can initialize its own data, and resetting the state should be painless
For those looking to implement this, I have a complete demo set up here.
Note: this example uses concepts from XUnit and EF Core, but the same thing could easily be done with other test frameworks, ORMs or of course, even languages.
Initialize the database only once
In order to minimize the compute and time used, it's crucial that the database is created and initialized once per test run. XUnit 3 provides the Assembly fixture, but a static constructor works as well.
// from https://codeberg.org/mossy9/samples/src/branch/main/DotnetSamples/DatabaseIntegrationTests/Tests/Infra/DatabaseAssemblyFixture.cs
public class DatabaseAssemblyFixture : IDisposable
{
private const string ServerConnection = "Host=localhost;Port=5432;Database=postgres;Username=dbuser;Password=dbpassword";
private const string TestDbName = "test_db";
public const string DbConnection = $"Host=localhost;Port=5432;Database={TestDbName};Username=dbuser;Password=dbpassword";
public DatabaseAssemblyFixture()
{
ExecuteNonQuery($"DROP DATABASE IF EXISTS \"{TestDbName}\"", ServerConnection);
ExecuteNonQuery($"CREATE DATABASE \"{TestDbName}\"", ServerConnection);
//here you could process scripts from files, which is especially good for running e.g. flyway migrations in order
const string createScript = """
CREATE TABLE "Products" (
"Id" SERIAL PRIMARY KEY,
"Name" TEXT NOT NULL,
"Price" DECIMAL NOT NULL,
"Description" TEXT NOT NULL,
"CreatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
""";
ExecuteNonQuery(createScript, DbConnection);
}
private static void ExecuteNonQuery(string query, string connectionString)
{
using var connection = new NpgsqlConnection(connectionString);
connection.Open();
using var cmd = new NpgsqlCommand(query, connection);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
connection.Close();
}
}
Registering the assembly fixture can be done like this:
// https://codeberg.org/mossy9/samples/src/branch/main/DotnetSamples/DatabaseIntegrationTests/Tests/Infra/AssemblyInfo.cs
[assembly: AssemblyFixture(typeof(DatabaseAssemblyFixture))]
With that, each test in the namespace has a database set up the way we want. You can either use .EnsureCreated() or .Migrate() if running Code First, or initialize the db from SQL scripts if doing DB First. I tend to prefer DB first these days, so that's what this sample uses.
If you have seed data to be shared for all tests, you should seed it here - again so we only have to do it once.
Sharing a database context between the test and the app
By default, a DbContext is injected as a scoped service, meaning that there is a unique context for each e.g. HTTP request to an API. This is as it should be, but it poses a problem for tests in this approach. The API should have access to the data the test has seeded but we'd rather not actually commit those changes to disk, since then we'd have to manually work the rollback. What we can do instead is to register the DbContext as a singleton service, meaning a single instance is shared.
NOTE: it's a Bad Idea to do this in application code, but for testing it has the benefit of not having to pollute our application code with testing concerns. I think there might be a way to share a transaction between the test and the app by e.g. a middleware, but haven't had the time (or need) to try it out. Let me know if you have figured it out!
We could have the web app setup in the assembly fixture, so it too would be initialized only once, but since a DbContext only allows a single transaction at a time, we would lose out on parallelization. Initializing it per test class is a good middle ground of performance and capability. Let's utilize a Class fixture:
//https://codeberg.org/mossy9/samples/src/branch/main/DotnetSamples/DatabaseIntegrationTests/Tests/Infra/AppFixture.cs
public class AppFixture: IDisposable
{
public WebApplicationFactory<Program> Factory { get; }
public AppFixture()
{
Factory = new WebApplicationFactory<Program>().WithWebHostBuilder(builder =>
{
builder.ConfigureServices(services =>
{
services.Remove(services.Single(s => s.ServiceType == typeof(SampleDbContext)));
var optionsBuilder = new DbContextOptionsBuilder<SampleDbContext>();
optionsBuilder.UseNpgsql(DatabaseAssemblyFixture.DbConnection);
services.AddSingleton(optionsBuilder.Options);
services.AddSingleton<SampleDbContext>();
});
});
}
public void Dispose()
{
Factory.Dispose();
}
}
NOTE: Since we're having a single instance of a DbContext, it also shares the change tracking state! This means that if you _db.Products.Add(new Product{...}) in the test and then in the app e.g. _db.Products.Find(), the entity you added in the test is already tracked by EF, and the behavior might be different than when running the actual application. You could set up extensions for the tests, so the change tracker is always cleared before the API is called, use AsNoTracking whenever you wish to go straight to the database etc. This is an unfortunate foot gun for which I haven't really figured out a good catch-all solution yet. Let me know if you do!
Test isolation with transactions
For the final piece, each test should be run inside a transaction and rolled back after test completion, regardless of test state. Let's use a base class with a constructor and disposal for this:
//https://codeberg.org/mossy9/samples/src/branch/main/DotnetSamples/DatabaseIntegrationTests/Tests/Infra/IntegrationTestBase.cs
public abstract class IntegrationTestBase : IDisposable, IClassFixture<AppFixture>
{
private readonly IDbContextTransaction _transaction;
protected readonly SampleDbContext DbContext;
protected IntegrationTestBase(DatabaseAssemblyFixture _, AppFixture appFixture)
{
DbContext = appFixture.Factory.Services.GetRequiredService<SampleDbContext>();
_transaction = DbContext.Database.BeginTransaction();
}
public void Dispose()
{
_transaction.Rollback();
DbContext.ChangeTracker.Clear();
}
}
So whenever a single test is started:
- a database transaction is created
- all DbContext actions in the test and app are called inside that transaction
- after test completion the transaction is rolled back and the change tracker cleared
Note: this base class won't work if the functionality you're testing uses transactions directly. If those cases are not too frequent, I'd recommend handling the teardown manually. Otherwise I've linked a few alternative approaches at the end of this post.
A sample test
Once the infra is set up, a single test is simple and focused on the test at hand, rather than fiddling with the database state:
//https://codeberg.org/mossy9/samples/src/branch/main/DotnetSamples/DatabaseIntegrationTests/Tests/ApiIntegrationTests.cs
public class ApiIntegrationTests(DatabaseAssemblyFixture dbFixture, AppFixture appFixture) : IntegrationTestBase(dbFixture, appFixture)
{
private readonly HttpClient _client = appFixture.CreateClient();
[Fact]
public async Task CreateProduct_AssignsNewId_AndReturnsProduct()
{
var newProduct = new Product
{
Name = "New API Product",
Price = 123.45m,
Description = "Created in API test"
};
var createResponse = await _client.PostAsJsonAsync("/api/products", newProduct, cancellationToken: TestContext.Current.CancellationToken);
createResponse.EnsureSuccessStatusCode();
var createdProduct = await createResponse.Content.ReadFromJsonAsync<Product>(cancellationToken: TestContext.Current.CancellationToken);
Assert.NotNull(createdProduct);
var dbProduct = await DbContext.Products.FindAsync(new object?[] { createdProduct.Id }, TestContext.Current.CancellationToken);
Assert.NotNull(dbProduct);
}
Conclusion
As mentioned, a project showcasing this approach with an actual API can be found here.
Having this kind of infra has made it really easy to run tests with a realistic database in our dev environments and pipelines. I've used in around a dozen projects, and having worked out the kinks, the biggest caveat thus far has been the change tracker.
If you're targeting MSSQL, use Windows agents In Azure pipelines so localdb is built in, which cuts down on db setup duration quite nicely. Of course, Windows pipelines are otherwise slower than their Ubuntu counterparts, so there's the tradeoff.
Not all kinds of tests need to access a real database, and for those e.g. InMemory can be the better choice. I would, however, recommend to target the actual database by default, since running tests against anything else might allow bugs to slip through.
To mention some alternatives to this approach, there's the Respawn nuget package from the excellent Jimmy Bogard and for PostgreSQL I've heard good things for setting up a database per test using Template Databases, which is a bit wasteful but has strong isolation and the db setup is apparently pretty fast.
Thoughts, comments? Send me an email!