Writing Tests for Dapper with TestContainers in xUnit

Writing tests for Dapper is traditionally challenging due to two reasons:

• Firstly, Dapper is a set of extension methods on the IDbConnection interface. This makes it impossible to mock the IDbConnection interface and write unit tests for Dapper queries. The only way to test Dapper queries is to interact with a real database.

• Secondly, even if we manage to set up a real database, we need to ensure that the database has the required schema and data for the tests to run successfully.

Sadly, unlike Entity Framework Core, Dapper doesn’t provide any In-Memory database or a way to mock the IDbConnection interface. So, how do we write tests for Dapper queries? One way is to use TestContainers to create disposable containers for the test database.

In this post, we will explore how to write integration test fixtures for Dapper queries using TestContainers in an xUnit project. We’ll use FluentMigrator to perform schema migrations on the test database. And finally, we’ll also see how to set up a shared test fixture so that we can reuse the database container across multiple tests.

What is TestContainers?

TestContainers is an open-source framework that allows you to create disposable containers for integration tests to mock external dependencies such as databases, message brokers, and other services. TestContainers provides a simple and consistent API to create and manage containers for integration tests. Apart from .NET, TestContainers is also available for other programming platforms such as Java, Go and Node.js.

In this post, we will be using the TestContainers.MsSql library to create a SQL Server container for integration tests.

Premise

Let’s consider the example that we used in the previous post where we used Dapper with FluentMigrator in an ASP.NET Core application. We have a Book table with the following schema:

CREATE TABLE Book (
    Id INT PRIMARY KEY,
    Title NVARCHAR(100),
    Author NVARCHAR(100),
    Pages INT
);

We have a BookRepository class that interacts with the Book table using Dapper.

public class BookRepository(IDbConnection connection)
{
    public IEnumerable<Book> GetBooks()
    {
        return connection.Query<Book>("SELECT * FROM Books");
    }

    public Book GetBook(int id)
    {
        return connection.QueryFirstOrDefault<Book>("SELECT * FROM Books WHERE Id = @Id", new { Id = id });
    }

    public void AddBook(Book book)
    {
        connection.Execute("INSERT INTO Books (Id, Title, Author, Pages) VALUES (@Id, @Title, @Author, @Pages)", book);
    }

    public void UpdateBook(Book book)
    {
        connection.Execute("UPDATE Books SET Title = @Title, Author = @Author, Pages = @Pages WHERE Id = @Id", book);
    }

    public void DeleteBook(int id)
    {
        connection.Execute("DELETE FROM Books WHERE Id = @Id", new { Id = id });
    }
}

Now we want to write tests for the BookRepository class. So we need to setup a database container, migrate the schema, and dispose of the container after the tests are run.

Creating a Test Project

Let’s create a new xUnit test project in the same solution as the ASP.NET Core application. We can use the dotnet CLI to create a new xUnit test project.

dotnet new xunit -o LibraryApi.Tests

Add a reference to the existing ASP.NET Core project in the test project.

dotnet add reference ../LibraryApi/LibraryApi.csproj

Since we used SQL Server in the last post, we will be using the TestContainers.MsSql package to create a SQL Server container for the tests.

dotnet add package TestContainers.MsSql

For asserting the test results, let’s use the FluentAssertions package.

dotnet add package FluentAssertions

Writing the Repository Tests

Let’s write a test for the GetBooks method in the BookRepository class. In this test, we’ll insert some books into the Book table and then call the GetBooks method to get all the books. We’ll assert that the books returned by the GetBooks method are the same as the books we inserted.

To write this test, we need to construct the BookRepository instance with an IDbConnection instance. To construct the BookRepository instance, we need to:

1. Create a SQL Server container using TestContainers.
2. Migrate the schema in the database using FluentMigrator.
3. Create an SqlDbConnection instance to connect to the database.
4. Use the SqlDbConnection instance to create a BookRepository instance.

First, let’s create BookRepositoryTests class in the test project and add a TestContainer for SQL Server. For that, we can use the MsSqlBuilder class to create a SQL Server container.

private MsSqlContainer Container { get; } = new MsSqlBuilder()
    .WithImage("mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04")
    .Build();

We can start the container in the constructor of the test class.

public BookRepositoryTests()
{
    Container.Start();
}

With container started, we can get the connection string to the database using the GetConnectionString method.

public string ConnectionString => Container.GetConnectionString();

Next, we need to migrate the schema using FluentMigrator. We can create a method called MigrateDatabase to do this and call it in the constructor.

public BookRepositoryTests()
{
    Container.Start();
    MigrateDatabase();
}

private void MigrateDatabase()
{
    var serviceProvider = new ServiceCollection()
        .AddFluentMigratorCore()
        .ConfigureRunner(rb => rb
            .AddSqlServer()
            .WithGlobalConnectionString(ConnectionString)
            .ScanIn(typeof(AddBookTable).Assembly).For.Migrations())
        .BuildServiceProvider();
    var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
    runner.MigrateUp();
}

Now we can write the test for the GetBooks method. Here, we’ll insert some books into the Book table and then call the GetBooks method to get all the books.

[Fact]
public void GetBooks_ShouldReturnAllBooks()
{
    using var connection = new SqlDbConnection(ConnectionString);
    var repository = new BookRepository(connection);
    var books = new List<Book>
    {
        new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 },
        new Book { Id = 2, Title = "Book 2", Author = "Author 2", Pages = 200 },
        new Book { Id = 3, Title = "Book 3", Author = "Author 3", Pages = 300 }
    };
    InsertBooks(connection, books);

    var books = repository.GetBooks();

    books.Should().BeEmpty();
}

private void InsertBooks(SqlDbConnection connection, IEnumerable<Book> books)
{
    foreach (var book in books)
    {
        connection.Execute("INSERT INTO Books (Id, Title, Author, Pages) VALUES (@Id, @Title, @Author, @Pages)", book);
    }
}

Finally, we need to dispose of the container after the tests are run.

public class BookRepositoryTests : IDisposable
{
    ...

    public void Dispose()
    {
        Container.Dispose();
    }
}

So here is the complete test class for the BookRepository class.

public class BookRepositoryTests : IDisposable
{
    private MsSqlContainer Container { get; } = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04")
        .Build();

    public string ConnectionString => Container.GetConnectionString();

    public BookRepositoryTests()
    {
        Container.Start();
        MigrateDatabase();
    }

    private void MigrateDatabase()
    {
        var serviceProvider = new ServiceCollection()
            .AddFluentMigratorCore()
            .ConfigureRunner(rb => rb
                .AddSqlServer()
                .WithGlobalConnectionString(ConnectionString)
                .ScanIn(typeof(AddBookTable).Assembly).For.Migrations())
            .BuildServiceProvider();
        var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
        runner.MigrateUp();
    }

    [Fact]
    public void GetBooks_ShouldReturnAllBooks()
    {
        using var connection = new SqlDbConnection(ConnectionString);
        var repository = new BookRepository(connection);
        var books = new List<Book>
        {
            new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 },
            new Book { Id = 2, Title = "Book 2", Author = "Author 2", Pages = 200 },
            new Book { Id = 3, Title = "Book 3", Author = "Author 3", Pages = 300 }
        };
        InsertBooks(connection, books);

        var books = repository.GetBooks();

        books.Should().BeEmpty();
    }

    private void InsertBooks(SqlDbConnection connection, IEnumerable<Book> books)
    {
        foreach (var book in books)
        {
            connection.Execute("INSERT INTO Books (Id, Title, Author, Pages) VALUES (@Id, @Title, @Author, @Pages)", book);
        }
    }

    public void Dispose()
    {
        Container.Dispose();
    }
}

Now we can run the tests using the dotnet test command.

dotnet test

This will start the SQL Server container, migrate the schema, run the tests, and dispose of the container after the tests are run.

Sharing the Database Container across Tests

In xUnit, the test fixtures work on a per-test basis. This means that the TestContainer will be started and disposed of for each test. This can be inefficient if multiple tests can share the same database container.

To solve this problem, we can use the IClassFixture<T> interface to share the database container across multiple tests in the same class. We can create a DatabaseFixture class that implements the IAsyncLifetime interface to start and dispose of the database container. Then we’ll inject an instance of the DatabaseFixture class into any test class we want.

public class DatabaseFixture : IAsyncLifetime
{
    private MsSqlContainer Container { get; } = new MsSqlBuilder()
        .WithImage("mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04")
        .Build();

    public string ConnectionString => Container.GetConnectionString();

    public async Task InitializeAsync()
    {
        await Container.StartAsync();
        MigrateDatabase();
    }

    private void MigrateDatabase()
    {
        var serviceProvider = new ServiceCollection()
            .AddFluentMigratorCore()
            .ConfigureRunner(rb => rb
                .AddSqlServer()
                .WithGlobalConnectionString(ConnectionString)
                .ScanIn(typeof(AddBookTable).Assembly).For.Migrations())
            .BuildServiceProvider();
        var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
        runner.MigrateUp();
    }

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

Now we can use the DatabaseFixture class in the test class by implementing the IClassFixture<DatabaseFixture> interface. This allows xUnit to inject an instance of the DatabaseFixture class into the test class.

public class BookRepositoryTests(DatabaseFixture databaseFixture) : IClassFixture<DatabaseFixture>
{
    private SqlDbConnection Connection => new(databaseFixture.ConnectionString);
    private BookRepository BookRepository => new(Connection);

    [Fact]
    public void GetBooks_ShouldReturnAllBooks()
    {
        // Arrange
        var books = new List<Book>
        {
            new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 },
            new Book { Id = 2, Title = "Book 2", Author = "Author 2", Pages = 200 },
            new Book { Id = 3, Title = "Book 3", Author = "Author 3", Pages = 300 }
        };
        InsertBooks(books);

        // Act
        var result = BookRepository.GetBooks();

        // Assert
        result.Should().BeEquivalentTo(books);
    }

    private void InsertBooks(IEnumerable<Book> books)
    {
        foreach (var book in books)
        {
            Connection.Execute("INSERT INTO Books (Id, Title, Author, Pages) VALUES (@Id, @Title, @Author, @Pages)", book);
        }
    }
}

Now the TestContainer is reused across multiple tests in the test class. With this approach, we can write additional tests for the BookRepository class that use the same database container.

But there’s a caveat right now - Since we are sharing the database container across multiple tests, the database needs to be cleaned up after each test. For this, we can simply implement the IDisposable interface in the BookRepositoryTests class and delete the data from the Book table.

public class BookRepositoryTests(DatabaseFixture databaseFixture) : IClassFixture<DatabaseFixture>, IDisposable
{
    ...

    public void Dispose()
    {
        using var connection = new SqlDbConnection(databaseFixture.ConnectionString);
        connection.Execute("DELETE FROM Books");
    }
}

Here’s the complete test class for the BookRepository class with all the tests sharing the same database container.

public class BookRepositoryTests(DatabaseFixture databaseFixture) : IClassFixture<DatabaseFixture>, IDisposable
{
    private SqlDbConnection Connection => new(databaseFixture.ConnectionString);

    private BookRepository BookRepository => new(Connection);

    [Fact]
    public void GetBooks_ShouldReturnAllBooks()
    {
        var books = new List<Book>
        {
            new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 },
            new Book { Id = 2, Title = "Book 2", Author = "Author 2", Pages = 200 },
            new Book { Id = 3, Title = "Book 3", Author = "Author 3", Pages = 300 }
        };
        InsertBooks(books);

        var result = BookRepository.GetBooks();

        result.Should().BeEquivalentTo(books);
    }

    [Fact]
    public void GetBook_ShouldReturnBookById()
    {
        var book = new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 };
        InsertBooks(new[] { book });

        var result = BookRepository.GetBook(1);

        result.Should().BeEquivalentTo(book);
    }

    [Fact]
    public void AddBook_ShouldAddBook()
    {
        var book = new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 };

        BookRepository.AddBook(book);

        var result = BookRepository.GetBook(1);
        result.Should().BeEquivalentTo(book);
    }

    [Fact]
    public void UpdateBook_ShouldUpdateBook()
    {
        var book = new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 };
        InsertBooks(new[] { book });

        var updatedBook = new Book { Id = 1, Title = "Book 1 Updated", Author = "Author 1 Updated", Pages = 200 };
        BookRepository.UpdateBook(updatedBook);

        var result = BookRepository.GetBook(1);
        result.Should().BeEquivalentTo(updatedBook);
    }

    [Fact]
    public void DeleteBook_ShouldDeleteBook()
    {
        var book = new Book { Id = 1, Title = "Book 1", Author = "Author 1", Pages = 100 };
        InsertBooks(new[] { book });

        BookRepository.DeleteBook(1);

        var result = BookRepository.GetBook(1);
        result.Should().BeNull();
    }

    private void InsertBooks(IEnumerable<Book> books)
    {
        foreach (var book in books)
        {
            Connection.Execute("INSERT INTO Books (Id, Title, Author, Pages) VALUES (@Id, @Title, @Author, @Pages)", book);
        }
    }

    public void Dispose()
    {
        using var connection = new SqlDbConnection(databaseFixture.ConnectionString);
        connection.Execute("DELETE FROM Books");
    }
}

Wrapping Up

In this post, we explored how to write integration test fixtures for Dapper queries using TestContainers. We also saw how to share the database container across multiple tests in the same class by creating a shared test fixture DatabaseFixture. This approach allows us to write tests for Dapper queries that interact with a real database and ensure that the database has the required schema and data for the tests to run successfully.

Tip: To go one step further, you can also use ICollectionFixture<T> to share the database container across multiple test classes.