Testing Database Integrations in .NET with xUnit

The Easiest Way To Write Integration Tests in .NET

5 min readJun 3, 2023

Introduction

In this article, you will learn about the purpose of integration testing, the problem it solves, and how it compares to other types of testing.

By the end of this article, you will have learned everything you need to start writing integration tests in .NET.

In our example, we will be testing a repository that uses Entity Framework with an underlying Postgres database. However, the same principles apply to any other database and ORM, even raw queries using something like Dapper.

Bored of reading? Try my interactive tutorials over at codeinteractive.dev!

Prerequisites

This guide assumes that you’re familiar with xUnit and have written tests before.

If haven’t, you can start by reading my Beginner’s Guide To Testing in .NET.

The example also assumes you’ve used Entity Framework before, so the DbContext setup won’t be shown.

To make the tests cleaner, I will be using FluentAssertions.

The Purpose of Integration Testing

Testing is a vital part of software development.

It ensures our solutions do what we expect them to do and gives us both the confidence to deliver new features, as well as assure no issues are introduced into the existing features.

In most projects, the majority of the tests you write will be unit tests.

However, there is only so much you can test in isolation. There comes a point where you need to test how your code integrates with external components.

This is where integration tests come in.

Because they use real components, they’re slower than unit tests, but still faster than end-to-end tests. This is why we generally write fewer of them.

image by Martin Fowler: The Test Pyramid

Integration vs Unit Tests

Integration and unit tests might look very similar, but they are significantly different in the way they operate, the scope they test, and the cost.

Unit Tests:

Test isolated components
Substitute dependencies with test doubles
Run inside a single process
Very fast
The cheapest testing approach

Integration Tests:

Test integrations with external components
Use real, concrete dependencies
Usually depend on an I/O operation
Slower than unit tests
More expensive than unit tests

Example

In our application, we implemented a LibraryRepository.

The repository uses EntityFramework to access an underlying Postgres database.

We Wrote a method that gets books by the author and the year.

public class LibraryRepository
{
    private readonly LibraryContext _context;

    public LibraryRepository(LibraryContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<Book>> GetBooksByAuthorAndYearAsync(string authorName, int year)
    {
        return await _context.Books
            .Where(b => b.Year == year)
            .Where(b => b.BookAuthors.Any(ba => ba.Author.Name == authorName))
            .ToListAsync();
    }
}

How do we test this?

Test Database

As I previously mentioned, integration tests use real, concrete dependencies, which means we need a real database. This is because there is no reliable way to simulate how the database is going to behave.

The database you use for testing should be separate from the database you use for your application to make sure your test cases are isolated, as well as prevent your tests from modifying real data.

In the case of Postgres, we will use the same local instance, but create a new database for each test, which we will then delete.

Test Database Lifetime

There are multiple choices when deciding on the lifetime of the test database:

One Per Test: a database is created for each test
One Per Test Class: a database is created for each test class and is shared between all tests in the class
One Per Test Suite: a database is created once and used throughout the whole test suite

For simplicity, we will be going with the first approach.

The advantage is that there is no cleanup required, as every test starts with a clean database. The disadvantage is the overhead of creating a database for each test.

The most optimal solution is usually the last one but requires some cleanup techniques beyond the scope of this guide. I will be writing about it in the following blog posts.

Integration Test Class Setup

To set up a class that creates a database for each test in xUnit, we simply do our setup in the constructor.

For our EntityFramework/Postgres repository, the setup is simple.

We create a new options builder that’s configured to connect to our test Postgres database. This is a connection string pointing to any Postgres instance, usually local.

For simplicity, the connection string is hardcoded, but you will usually load this from a config so that you can run your tests in a CI/CD pipeline.

We then create a new LibraryContext (an Entity Framework DbContext) with the options and make sure the database is created and the migrations applied.

For the cleanup, we make sure to implement the IDisposable interface and place our cleanup code in the Dispose method. xUnit will do the rest.

public class LibraryRepositoryTests : IDisposable
{
    private readonly LibraryContext _context;
    private readonly LibraryRepository _repository;

    public LibraryRepositoryTests()
    {
        var options = new DbContextOptionsBuilder<LibraryContext>()
            .UseNpgsql("Host=localhost;Database=testdb;Username=testuser;Password=testpassword")
            .Options;
        
        _context = new LibraryContext(options);
        _context.Database.EnsureDeleted();
        _context.Database.Migrate();

        _repository = new LibraryRepository(_context);
    }

    public void Dispose()
    {
        _context.Database.EnsureDeleted();
        _context.Dispose();
    }
}

This setup code will vary depending on the type of service or database you’re testing, but the basic principles are the same: Set up in the constructor, clean up in Dispose.

Writing The First Test

Having finished setting up the test class, we’re now ready to write our first test.

We want to assure that our method GetBooksByAuthorAndYearAsync returns only the books that match by both author and year, so we add multiple books to make sure we cover the edge cases.

We then write out the test exactly the same as we did with unit tests.

[Fact]
public async Task GetBooksByAuthorAndYearAsync_ShouldReturnBooks()
{
    _context.Books.AddRange(Enumerable.Range(1, 5).Select(x => new Book
    {
        Title = $"Test Book {x}",
        Year = 2020 + x,
        BookAuthors = new List<BookAuthor>
        {
            new() { Author = new Author { Name = $"Test Author {x}" } }
        }
    }));
    _context.Books.Add(new Book
    {
        Title = "Test Book With Correct Year But Incorrect Author",
        Year = 2023,
        BookAuthors = new List<BookAuthor>
        {
            new() { Author = new Author { Name = "Test Author 5" } }
        }
    });
    _context.Books.Add(new Book
    {
        Title = "Test Book With Correct Author But Incorrect Year",
        Year = 2025,
        BookAuthors = new List<BookAuthor>
        {
            new() { Author = new Author { Name = "Test Author 3" } }
        }
    });
    await _context.SaveChangesAsync();

    var books = await _repository.GetBooksByAuthorAndYearAsync("Test Author 3", 2023);

    books.Should().HaveCount(1);
    books.Single().Title.Should().Be("Test Book 3");
}

Notice that we aren’t using any mocks here. We’re filling an actual database with the test data, retrieving data from it, and then asserting the expected behavior.

Conclusion

I have shown you how to test database integrations in .NET, but the same principles can be applied to testing integrations with any external service.

In the following blog posts, I will show you how to optimize your tests to speed up the setup/teardown process.

If you’re interested in these kinds of topics, subscribe to get notified when I post more.

Any thoughts? Leave a comment