Creating new PostgreSQL DB for every xUnit test
Tags: postgres, dotnet, xunit, testing
Published: 2020-05-20 13:24:56

Ideally, you would want to run all tests on the database same as on production, in separate/isolated databases, and quickly on top of it. Luckily, this is easily achievable with PostgreSQL and xUnit and it is one of the reasons I like using the PostgreSQL database.

This is what we'll do:

1. Create a base (template) database at the tests startup
2. Create a copy of the template database at the individual test startup
3. Run individual test
4. Remove test's specific database
5. Remove template database

This approach is much faster than creating a new database for every test because copying it from a template is way quicker with PostgreSQL. That means that only the first step is slow.

Using eg. `InMemory` or `Sqlite` database providers can be faster, but you'll lose the feature set of PostgreSQL.

So for this to work, we'll be using the following things:

* [Collection Fixtures in xUnit](https://xunit.net/docs/shared-context#collection-fixture)
* [Constructor and Dispose in xUnit](https://xunit.net/docs/shared-context#constructor)
* [Creating database from template](https://www.postgresql.org/docs/9.3/manage-ag-templatedbs.html)

# 1. Create a template database

We'll start by creating a new [collection fixture](https://xunit.net/docs/shared-context#collection-fixture). Collection fixtures serve the purpose of sharing some common data between multiple tests and can act as a global startup/teardown. This is where the template database will be created.

```c#
using System;
using Microsoft.EntityFrameworkCore;

public class DatabaseFixture : IDisposable
{
    private readonly DbContext _context;

    // Constructor is called only once before all tests
    public DatabaseFixture()
    {
        // Create random database name suffix as a safety measure
        var id = Guid.NewGuid().ToString().Replace("-", "");

        // Create a template database name and store it for later use
        TemplateDatabaseName = $"my_db_tmpl_{id}";

        // Create connection string and store it for later use
        Connection = $"Host=my_host;Database={TemplateDatabaseName};Username=my_user;Password=my_pw";

        // Configure DbContext
        var optionsBuilder = new DbContextOptionsBuilder();
        optionsBuilder.UseNpgsql(Connection);

        // Create instance of you application's DbContext
        _context = new MyApplicationDbContext(optionsBuilder.Options);

        // Create database schema
        _context.Database.EnsureCreated();

        // todo: Insert common data here

        // Close template database connection, we will not need it for now
        _context.Database.CloseConnection();
    }

    // We'll use this later
    public string TemplateDatabaseName { get; }

    // We'll use this later
    public string Connection { get; }

    // Dispose is called only once after all tests
    public void Dispose()
    {
        // Remove template database
        _context.Database.EnsureDeleted();
    }
}
```

If you wish to insert some common data available in all tests, you can do that after the `EnsureCreated()` call.

You may have also noticed that the database name contains a random string (`Guid`). This is just a safety measure, also we don't care about database names since they are ephemeral.

Let's also create a fixture collection definition. This is just a "helper" class used by xUnit.

```c#
using Xunit;

[CollectionDefinition("Database")]
public class DatabaseCollectionFixture : ICollectionFixture<DatabaseFixture>
{
}
```

# 2. Clone template database for specific tests

For that we'll create abstract `DatabaseTestCase`:

```c#
using System;
using Microsoft.EntityFrameworkCore;
using Npgsql;

public abstract class DatabaseTestCase : IDisposable
{
    // Constructor is called before every test
    protected DatabaseTestCase(DatabaseFixture databaseFixture)
    {
        // Create random database suffix as a safety measure
        var id = Guid.NewGuid().ToString().Replace("-", "");

        // And create test database name
        var databaseName = $"my_db_test_{id}";

        // Open connection to database and create clone of template database
        using (var tmplConnection = new NpgsqlConnection(databaseFixture.Connection))
        {
            tmplConnection.Open();

            using (var cmd = new NpgsqlCommand($"CREATE DATABASE {databaseName} WITH TEMPLATE {databaseFixture.TemplateDatabaseName}", tmplConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }

        // Create connection string
        var connection = $"Host=my_host;Database={databaseName};Username=my_user;Password=my_pw";

        // Configure DbContext
        var optionsBuilder = new DbContextOptionsBuilder();
        optionsBuilder.UseNpgsql(connection);

        // Create an instance of your application's DbContext
        DbContext = new MyApplicationDbContext(optionsBuilder.Options);
    }

    // Store application's DbContext for use in tests
    public MyApplicationDbContext DbContext { get; }

    // Dispose is called after every test
    public void Dispose()
    {
        DbContext.Database.EnsureDeleted();
    }
}
```

# 3. Write test

Everything should be ready for tests, so let's write one.

```c#
using System.Threading;
using System.Threading.Tasks;
using Xunit;

[Collection("Database")]
public class FindUserByIdHandlerTest : DatabaseTestCase
{
    private readonly FindUserByIdHandler _handler;

    public FindUserByIdHandlerTest(DatabaseFixture databaseFixture)
        : base(databaseFixture)
    {
        _handler = new FindUserByIdHandler(DbContext);
    }

    [Fact]
    public async Task Handle()
    {
        var user = new User("john@doe.com");

        await DbContext.AddAsync(user);
        await DbContext.SaveChangesAsync();

        var command = new FindUserByIdCommand(user.Id);
        var found = await _handler.Handle(command, CancellationToken.None);

        Assert.NotNull(found);
        Assert.Equal(user.Id, found.Id);
    }
}
```

You can see that we're extending from the `DatabaseTestCase` class we created before and also marked the test with `[Collection("Database")]` attribute. Both of these things are necessary and the string in `Collection` attribute must be the same as the one defined in `[CollectionDefinition()]` attribute on `DatabaseCollectionFixture` class.

If you're curious about what we're testing here, it's a simple CQRS handler. I'm using [jbogard/MediatR](https://github.com/jbogard/MediatR).

# Make it better

* Replace hard coded connection strings. I suggest loading variables from environment variables.
* Run automatically in CI.
* And of course write your own tests.