Fast unit tests with databases: A primer

When it comes to unit tests, you often also have to test code with services you integrate with. In many cases you have to test your code against a database, often a relational database like MSSQL, PostgreSQL or MySQL.

Autor: Johannes Hartmann
Datum: 13. Juli 2022
Lesedauer: 6 Minuten

Database integration tests often tend to have one thing in common: they are slow.

As a reference: One of our medium sized projects has about 3400 tests, 1200 of which are database tests. Running them one by one against a database took somewhere around 90 minutes on our build servers.

This is not fun and basically was a nightly-only thing, having the developers wait that long after a small fix before being able to merge would just be a nuisance. We had worked around this by only running a few tests against the database during the day, and running the other tests using an in-memory provider, which brought the testing time down to a more manageable 20 minutes (more on that later).

Today we run the full set of database tests against a database server within 40 seconds. Yes, down from 90 minutes to 40 seconds.
A little foretaste? Here’s an example of running those tests, 1200 of which are database tests, running in real time on my developer machine:

This is so fast that during development you often tend to just run all tests, instead of just running a few selected ones – or even worse, just wait until your build server sends you a mail with failed tests. This in turn also reduces quite some “commit - push - wait - fix” round trips during development.

In this three-part series of blogs you’ll learn about our testing strategy, our testing framework and, in the last part, about how we implement everything in our projects.

The slow start

When starting with database unit tests, you often start simple:

Create, test, delete, repeat

Each test follows the steps

Create test database
Add some test data (seed)
Test
Drop test database

This is nice and simple. Creating and dropping the test database can be done in a base class, so the tests themselves stay tidy.

That approach is fine when the number of database tests is somewhere in the two-digit range, but due to the heavy setup-process this approach gets slow, fast.

The setup overhead of recreating the entire database (with tables) each time can be mitigated by setting up the test database once and clearing all data after each test run.

There are libraries like Respawn which help you with this, but this also brings quite some limitations with it. You might want to have some basic test data always available, like a user, some master data and so on. You’d either need to exclude those tables or at least some specific rows from deleting. Additionally, you are not allowed to modify those during the tests as it would affect other tests. Alternatively you could seed the database with that base seed for each test, adding additional overhead.

Transactions to the rescue! (or not…)

Next approach:
Create the database once
Seed it with test data
For each test:
- Start a database transaction
- Test
- Rollback transaction

This is great (at first). You only have a one time setup cost, each test starts with that clean, consistent set of data and all the changes of a test get rolled back and do not affect other tests. You can even run the tests in parallel without them affecting each other.

However, doing it this way can have quite some side effects. Your code might not even be testable within transactions, e.g. when it explicitly specifies to start a part with no transaction or a new transaction.

Your testing code might produce deadlocks on the database that wouldn’t occur on production.

It can generate false positives (test fails within a transaction that would not fail on production) or false negatives (test does not fail, even if it fails on production). This is also dependent on how the libraries you use handle connections.

So all in all, using transactions to maintain a consistent state between tests smells a bit dangerous. It might work out for you, but it has an increasing chance of side effects when the number of tests grows into the hundreds or thousands.

Testing strategy – core concepts

We have a few core concepts for testing, performance wise and conceptually.

The blog posts focus on C#, Entity Framework Core, XUnit, virtualization with Docker and parts of our Fusonic.Extensions, but many ideas can be applied to other languages and libraries as well.

In memory testing

A big performance bottleneck are IO limits. Even on fast NVMe SSDs copying a database and writing back updates to it takes a while (a few hundred milliseconds at least for each test).

When thinking about InMemory database tests, using a different database provider comes to mind, like EntityFrameworkCore.InMemory or SQLite InMemory. But this comes with a lot of side effects and you are not testing the real thing then, as for example Jimmy Bogard pointed out in one of his blog posts.

Our InMemory tests use a real database. We’re using an often overlooked trick: The database service for testing gets started in a docker container and the data folder of the database is mounted to an in memory tmpfs. So when the database writes its data to the disc, it actually does this in memory only.

This neat little trick brings a huge performance boost to your database tests and can be implemented with hardly any effort, leaving your testing code with maybe a connection string change. And it can be done with lots of databases, e.g. PostgreSQL, MySQL and (with a little detour) MSSQL. Technical details about that later.

Another nice side effect: No cleanup of test databases is needed. Once the docker container for the test database server shuts down, which is directly after the tests, all databases are gone.

When you can’t use virtualization, our framework allows you to switch database providers. You can run your unit tests with EntityFrameworkCore.InMemory during the day and test against a real database during the night with a simple switch.

Parallel testing

Another big performance bottleneck is serial testing. Running database tests one by one leaves a lot of resources unused. This slows down the whole process, even when running in memory.

But when running the tests in parallel, what if one test deletes the data that the other test just created for testing? How do you run parallel tests on a single database without having the tests affect each other?

The answer is simple: You don’t.

We prepare a test database that is used as a template. Each test accessing the database gets a copy from that template, thus having the same consistent state as every other test.

Support for core architectures

In our .NET projects we use some architectural decisions across many projects and we also committed to specific libraries for those. Our base classes for the tests support them during setup and when using them, so we can quickly start testing new assemblies without a big overhead.

This includes for example dependency injection support using SimpleInjector. So in the sample code you’ll see calls like GetInstance<AppDbContext>() to get an instance of the DbContext configured in the DI-Container.

Avoid side effects

As soon as you use classes that cache something, using them in the test itself and in the tested code can cause side effects. It causes false positives (test fails when it should succeed) and false negatives (test succeeds when it should fail). This is the case for the DbContext of Entity Framework Core, but also applies to basically every other class that stores data during its lifetime.

This is best explained with a simple example. Consider a method that updates the title of an entity:

public async Task ChangeTitleAsync(int id, string newTitle)
{
     var entity = await dbContext.SomeEntities.FindAsync(id);
     entity.Title = newTitle;
}

It uses the following test:

[Fact]
public async Task ChangeTitle_SetsNewTitle()
{
    // Arrange
    var dbContext = GetInstance<DbContext>();
    var entity = dbContext.Add(new SomeEntity { Title = "Old title "}).Entity;
    await dbContext.SaveChangesAsync();
 
    // Act
    var service = GetInstance<SomeService>();
    await service.ChangeTitleAsync(entity.Id, "New title");
 
    // Assert
    var updatedEntity = await dbContext.FindAsync(entity.Id);
    updatedEntity.Title.Should().Be("New title");
}

Should this test fail? Yes. ChangeTitle() does not save the entity after updating it when it should.

Does this test fail? No. dbContext.FindAsync() retrieves a cached entity. It was added to the cache when arranging the test data, was retrieved using FindAsync() in the tested service method and once more when calling assert.

So the test only checked if a cached instance of an entity got its title changed, and it could not ensure that the change was actually saved to the database.

Nowadays DbContext allows clearing its cache explicitly, but as those side effects are not exclusive to EF Core and clearing the cache can be forgotten easily, we solve the issue at its root:

Every step in the test gets its own lifetime scope from the DI container. This ensures that the service call is tested to the real environment as close as possible. This is especially true for web APIs, where every call gets its own lifetime scope.