Creating new PostgreSQL DB for every xUnit test

David Kudera — Wed, 20 May 2020 13:24:56 +0000

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:

Create a base (template) database at the tests startup
Create a copy of the template database at the individual test startup
Run individual test
Remove test's specific database
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:

1. Create a template database

We'll start by creating a new 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.

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.

using Xunit;

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

2. Clone template database for specific tests

For that we'll create abstract DatabaseTestCase:

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.

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.

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.

DEV Community: David Kudera

Creating new PostgreSQL DB for every xUnit test

1. Create a template database

2. Clone template database for specific tests

3. Write test

Make it better