An Approach to Database Testing in .NET Core 2.1

I’ve been working on a REST API written in ASP.NET Core 2.1. Recently, our team wanted to write tests that exercised our business logic and also interacted with our database. I wasn’t able to find many examples of this online, so I was motivated to write this post and share our approach.

Some Background

When developing servers that expose some type of API (e.g., REST, GraphQL, etc.), I’ve become a huge fan of writing unit tests that exercise business logic and interact with the database.

Many developers would call these “integration tests.” However, “integration tests” usually refer to tests that actually simulate making REST (or similar) calls to your API endpoints and exercise the entire system. This typically means testing your controllers, business logic, and database interaction. As an example, the Microsoft ASP.NET Core docs refer to these as integration tests.

These end-to-end style tests are great, and I often utilize them. However, I also find that I get a lot of value at a substantially lower cost by testing the business logic and database interaction in an “extended unit test.”

An Example

In this post, I’ll walk through how we set up our testing using everyone’s favorite example project (The Todo App) and describe the testing patterns we’ve developed using xUnit. The code I’ll be writing will build off of this Todo tutorial.

My directory structure looks like this:

.
|-- TodoApi/
|---- [project created by running 'dotnet new webapi -o TodoApi']
|-- TodoApi.Tests
|---- [project created by running 'dotnet new xunit']

I set up the TodoApi.Tests project by following these instructions.

In my example, I’ve added the TodoItem and TodoContext classes, but I’ve omitted the TodoController. We won’t need it here.

Let’s write a class that abstracts away the context object and allows us to communicate with the database in a more abstract way. Sometimes these classes are called “repositories,” after the repository pattern. For our example, this class will look like this:

public class TodoRepository
{
    private TodoContext _context;

    public TodoRepository(TodoContext context)
    {
        _context = context;
    }

    public void AddTodoItem(TodoItem item)
    {
        _context.TodoItems.Add(item);
        _context.SaveChanges();
    }

    public IEnumerable AllTodoItems()
    {
        return _context.TodoItems;
    }

    public void DeleteTodoItem(TodoItem item)
    {
        _context.TodoItems.Remove(item);
        _context.SaveChanges();
    }

    public void DeleteAllTodoItems()
    {
        _context.Database.ExecuteSqlCommand("delete from TodoItems");
    }

    public void DeleteCompletedTodoItems()
    {
        _context.Database.ExecuteSqlCommand("delete from TodoItems where IsComplete = 'true'");
    }
}

Most of the time, our business logic will live in other classes that utilize our repository classes to talk to the database. To keep this example simpler, I’m going to focus on writing tests against the TodoRepository. For more sophisticated projects, you can easily target testing other classes that might use the TodoRepository. Either way, the tests will interact with the database.

We can start out by using an InMemory database for our testing. We’ll eventually adapt our solution to use a SQL Server instance.

It’s common to write some testing boilerplate to perform database setup, run your test, and then tear everything down. We’ve been using the approach below:

public class WithTestDatabase
{
    public static async Task Run(Func testFunc)
    {
        var options = new DbContextOptionsBuilder()
            .UseInMemoryDatabase("IN_MEMORY_DATABASE")
            .Options;

        using (var context = new TodoContext(options)) {
            try {
                await context.Database.EnsureCreatedAsync();
                PrepareTestDatabase(context);
                await testFunc(context);
            } 
            catch (Exception e){
                throw e; 
            }
            finally {
                CleanupTestDatabase(context);
            }
        }
    }
}

This allows us to write tests that look like this:

[Fact]
public async void CanAddATodoItem()
{
    await WithTestDatabase.Run(async (TodoContext context) => {
        var todoService = new TodoRepository(context);
                
        var initialTodos = todoService.AllTodoItems().ToList();
        Assert.Equal(0, initialTodos.Count);

        todoService.AddTodoItem(new TodoItem { Name = "Item 1", IsComplete = false });
        var todos = todoService.AllTodoItems().ToList();
        Assert.Equal(1, todos.Count);
    });
}

This test asserts that when starting out with zero todo items, we can add one and see it persisted to the database.

Keeping aligned with this pattern, we can write all kinds of tests, such as:

[Fact]
public async void CanDeleteFinishedTodos()
{
    await WithTestDatabase.Run(async (TodoContext context) => {
        var todoService = new TodoRepository(context);

        var initialTodos = todoService.AllTodoItems().ToList();
        Assert.Equal(0, initialTodos.Count);

        todoService.AddTodoItem(new TodoItem { Name = "Item 1", IsComplete = false });
        todoService.AddTodoItem(new TodoItem { Name = "Item 2", IsComplete = true });
        todoService.AddTodoItem(new TodoItem { Name = "Item 3", IsComplete = true });
        todoService.AddTodoItem(new TodoItem { Name = "Item 4", IsComplete = false });

        var todos = todoService.AllTodoItems().ToList();
        Assert.Equal(4, todos.Count);

        todoService.DeleteCompletedTodoItems();
                
        todos = todoService.AllTodoItems().ToList();
        Assert.Equal(2, todos.Count);
    });
}

InMemory Database vs. SQL Server

Up to this point, we’ve used an InMemory database when running our tests, configured by the line:

.UseInMemoryDatabase("IN_MEMORY_DATABASE")

InMemory databases can be great, especially for testing. They reduce the amount of infrastructure setup required, and they can often be spun up in parallel. However, the .NET InMemory database options can be quite limiting when writing your tests.

The docs do a good job of detailing the limitations. The .NET InMemory database is not relational. Because of that, you won’t be able to test any constraints in your database or referential integrity characteristics. For example, you might add a constraint that prevents a column from being null, but if you use an InMemory database, you can’t test that it actually works correctly.

Further, because the InMemory option isn’t relational, you won’t be able to test transactionality. This is probably the largest shortcoming. Often, applications have a few complicated workflows that must be completed in a transactional manner. It’s critical to be able to test these workflows to ensure the desired transactionality is working correctly.

Luckily, you aren’t stuck using the .NET InMemory database. On our project, tests run against a local SQL Server database. While this does come with a few disadvantages–namely, you can’t run your tests in parallel if they all require access to a singular test database–this tradeoff is well worth it. It allows us to run our tests in a much more production-like environment.

We can adjust our test configuration to use a local SQL Server database via the following:

Instead of the .UseInMemoryDatabase("IN_MEMORY_DATABASE") line, we can use .UseSqlServer(GetTestConnectionString()).

Our GetTestConnectionString() method looks like this:

public static string GetTestConnectionString()
{
    const string databaseName = "";
    string databaseUsername = "";
    string databasePassword = "";
            
    return $"Server=localhost;" +
           $"database={databaseName};" +
           $"uid={databaseUsername};" +
           $"pwd={databasePassword};" +
           $"pooling=true;";
}

The values for DB_NAME, USERNAME, and PASSWORD, can be hard-coded, but that isn’t very flexible. It’s better to read them in from the environment. Alternatively, if you are using the .NET secrets.json file approach, you could create a secrets file for your test project and have your test code read them in from there.

Setup and Teardown

You may have noticed the PrepareTestDatabase and CleanupTestDatabase methods earlier in my post. The actions required in each of these methods will depend on the type of database you are using. In our project, we currently have the ability to switch between the InMemory and SQL Server approaches. We may eventually phase out the InMemory approach, but until we do, it’s handy to have the option to run in that mode. Because of this flexibility, our setup and teardown methods check to see what type of database is being used and adjust based on that.

Our PrepareTesetDatabase method currently seeds some data into our database. You could also imagine running migrations in that method. Our CleanupTestDatabase method deletes everything, and looks like this:

public static void CleanupTestDatabase(TodoContext context)
{
    if (context.Database.IsInMemory())
    {
        context.Database.EnsureDeleted();
    }
    else
    {
        if (context.Database.IsSqlServer())
        {
            context.Database.ExecuteSqlCommand("DELETE FROM TodoItems");
        }
    }
}

Conclusion

Being able to write tests that exercise your business logic and interact with your database can give you incredible confidence that your code is working correctly. If anyone has thoughts about our approach or other approaches that they like to use I would love to hear about them.