Persisting data in a database is one of the most common tasks that applications have to perform, and it’s so crucial that without it the rest of the app is often useless. It’s also an area that’s notoriously hard to test. In this blog post, you can read about several ways of doing it and some problems they come with. Finally, I’m going to share with you how I use Testcontainers to test the integration with a database and why I think it’s the best way to do it.

EF Core in-memory database provider

If your app is using Entity Framework Core, you might be tempted to just use the in-memory database provider for testing. It’s got some things going for it - it’s really easy to use and it’s very fast, probably the fastest of the methods I’m going to mention in this article. There are some issues with it though, especially if you’re using a SQL database in production. The in-memory provider is not a relational database and it will allow you to save data that would violate referential integrity constraints in a relational database. Furthermore, it doesn’t support several other functions you might need, including transactions and raw SQL queries - this means that if you use the unit of work pattern or query the database through Dapper it’s not going to work.

SQLite

SQLite is a database engine that can either run in-memory or store state in a file. It’s using SQL and supports all the features that were missing in the previous provider. However, the implementations of SQL language differ between databases, sometimes even in simple use cases - for example, SQLite is case sensitive, while Microsoft SQL Server isn’t. The syntax for getting the first 10 elements, often used for pagination, is also different - in SQL Server we’d write SELECT * from TABLE LIMIT 10, while the same SQLite query would be SELECT TOP(10) * from TABLE. If at this point you’re thinking why should I care, the EFCore’s provider will handle that for me, then please remember that the implementations of providers, and by extension the result of the operations you run on them, are going to be different, too. While testing against SQLite is better than not testing at all, it’s not going to guarantee the same valid behavior on production, using your actual database engine.

Local database server

The easiest way of running the tests against the same database engine as the one you use on production is to have an instance dedicated for testing running on your machine. The first tradeoff I personally have to make in projects that go this route is whether to allow the database server to autostart and sip the precious and scarce resources of the company-provided laptop, or disable it and inevitably forget to turn it on before running tests. Another issue with this approach is that you either have to run tests sequentially and clean up after each one, or the tests are going to depend on each other and won’t be deterministic. Also, it’s one more thing to maintain for every developer on the team. Finally, this approach won’t work if you’d like to run the tests in a CI/CD pipeline unless you also have a…

Remote database server

A remote database server used for testing purposes solves some issues with local database servers, like running tests in CI/CD or consuming your machine’s resources. However, it comes with the cost of even more maintenance, and instead of running your own tests sequentially, you now have to juggle running the CI/CD pipelines and tests done by team members. You could add more servers and have one for CI/CD and one per team member, but that comes with a more literal kind of cost - neither the server resources nor cloud databases are free.

Dockerfile or docker compose

Another route you could take is building a dedicated dockerfile or docker compose file that would contain both the system under test and its dependencies like the SQL database. This allows you to pick a database engine that reliably replicates the one you use in production, and secondly, as long as your tests are not dependent on each other or always run in the same sequence, the tests should be deterministic (technically speaking you still can connect to the database and mess things up this way, but let’s assume we’re not maliciously trying to mess with the tests). This should work well in the CI/CD pipeline, but for the development environment, well, it’s a bit clunky. While running tests through the console might be preferable for some, I got used to the graphical user’s interface provided by the IDE - especially if you want to run only some of the tests instead of the entire test suite, automate running them during development, quickly preview the results or debug failures.

Testcontainers

Testcontainers is a library allowing you to spin up Docker containers from code. It allows specifying any image and parameters such as exposed ports, port bindings, environment variables, entry point or command. You can also run your own Dockerfile. In this case we’re after a database container, and for that and several other popular usecases Testcontainers provides some helpful defaults and convenience, for example it can create a connection string for us. Being able to run the containers from code gives us great flexibility, especially since Testcontainers takes care of assigning ports for us - we can for example have run a database container per test for crucial tests, and then have a few databases shared between test collections so that they can run in parallel.

Let’s look at how we can leverage Testcontainers to test the integration with a database. I have created a ToDo class and a repository that allows saving and retrieving ToDos. Let’s take a look at a test checking whether the repository saves the ToDos. First, we have to create a test container - in this case, we’re using MsSqlTestcontainer, one of the helpful defaults provided by Testcontainers.

private MsSqlTestcontainer _sqlContainer;

public ToDoRepositoryTests()
{
    _sqlContainer = new TestcontainersBuilder<MsSqlTestcontainer>()
        .WithDatabase(new MsSqlTestcontainerConfiguration
        {
            Password = "Your_password123"
        })
        .Build();
}

Right now we’ve only specified what the container should look like, but it’s not running yet. That’s done through an asynchronous StartAsync() method. We’re going to use xUnit’s IAsyncLifetime interface to call this method before running the tests, and while we’re at it, we’re also going to stop the container in the DisposeAsync() method of the same interface. After we start the container, we also have to migrate and seed the database - it’s an implementation detail, so I’m going to omit it in the code snippet.

public class ToDoRepositoryTests : IAsyncLifetime
{
  // omited field and constructor

  public async Task InitializeAsync()
  {
      await _sqlContainer.StartAsync();

      Migrate();
  }

  public async Task DisposeAsync()
  {
      await _sqlContainer.StopAsync();
  }
}

The last thing to do is write the test. It’s fairly straightforward, but please bring your attention to the line instantiating the repository where we’re using the convenient ConnectionString property of the container.

[Fact]
public void Add_ShouldSaveToDoToDatabase()
{
    var repository = new ToDoRepository(_sqlContainer.ConnectionString);
    var toDoToAdd = new ToDo("ToDoContent");

    repository.Add(toDoToAdd);
    var addedToDo = repository.Get(toDoToAdd.Id);

    Assert.NotNull(addedToDo);
}

What we’ve achieved is a reliable and deterministic test that’s using the same database as we’d use in production. The downside is that running this single test takes a little bit more than a minute on my machine, but the Testcontainers library is already working on an experimental feature that would allow keeping the same container for future test runs, and if we write our tests in such a way that we can reuse the same container we should only have to wait for it to start once.

If you run this code sample, you might be surprised by the Ryuk container that will start before the database container. That’s a resource reaper used by the library to clean up the resources taken by the containers during the test run.

You can download the project here. If you decide to run the test yourself please make sure you have Docker installed and running.