Creating new PostgreSQL DB for every xUnit test

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
• Constructor and Dispose in xUnit
• Creating database from template

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("[e-mail]");

        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.

Comments

[vbilopav]

I did something similar but better (just for norm data access instead of ef) and I described it here: dev.to/vbilopav/net-identity-with-... (see unit tests chapter)

It is better because every single test runs under a new transaction that is rolled back when a test is finished which ensures total test isolation. Anything you do in one test is invisible in other and it is clear up (rolled back) when a test is finished. Otherwise, I'd have to run those tests in serial (one after another) which I don't like.

That is just to demonstrate my micro orm but I have the same solution for PostgreSQL database tests with transactions that use EF done for a client.

[David Kudera]

What I described in this post is creating a completely new database for each test (by cloning the template). So it creates a database -> run test -> drop database over and over for each test. Also, there is no problem in running these tests in parallel.

So because of this, I wouldn't say that neither solution is better than the other. Both are isolated per test, one with a database per test, the other with transaction per test.

But, thanks for showing the Norm. The name is quite funny to me, it reminded me NotORM from the PHP world I used some time ago.

[vbilopav]

That's interesting, new database for each test. I never thought of it. I wonder how it performs vs transaction per test. Thanks

[David Kudera]

Performance-wise new database will be slower. The first step is creating a new template database. Cloning that template database for each test is fast and runs in parallel. But you made me curious, I'll try to measure the impact.

[Bryant Davis]

is this supposed to be the already existing database? it seemed like this connection string was something where we were creating the database
Connection = $"Host=my_host;Database={TemplateDatabaseName};Username=my_user;Password=my_pw";
Message:
System.AggregateException : One or more errors occurred. (Exception while connecting) (The following constructor parameters did not have matching fixture data: DatabaseFixture databaseFixture)
---- Npgsql.NpgsqlException : Exception while connecting
-------- System.Net.Sockets.SocketException : No connection could be made because the target machine actively refused it.
--------- The following constructor parameters did not have matching fixture data: DatabaseFixture databaseFixture

Stack Trace:
---------- Inner Stack Trace #1 (Npgsql.NpgsqlException) -----
NpgsqlConnector.Connect(NpgsqlTimeout timeout)
NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)

NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
<g__OpenAsync|0>d.MoveNext()
-------- End of stack trace from previous location where exception was thrown ---
NpgsqlConnection.Open()
RelationalConnection.OpenDbConnection(Boolean errorsExpected)
RelationalConnection.OpenInternal(Boolean errorsExpected)
RelationalConnection.Open(Boolean errorsExpected)
NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
NpgsqlDatabaseCreator.Exists(Boolean async, CancellationToken cancellationToken)
NpgsqlDatabaseCreator.Exists()
RelationalDatabaseCreator.EnsureCreated()
DatabaseFacade.EnsureCreated()
DatabaseFixture.ctor() line 29
---------- Inner Stack Trace -----
NpgsqlConnector.Connect(NpgsqlTimeout timeout)
---------- Inner Stack Trace #2 (Xunit.Sdk.TestClassException) -----

[Bryant Davis]

System.AggregateException : One or more errors occurred. (22023: new collation (en_US.UTF-8) is incompatible with the collation of the template database (C.UTF-8)) (The following constructor parameters did not have matching fixture data: DatabaseFixture databaseFixture)
---- Npgsql.PostgresException : 22023: new collation (en_US.UTF-8) is incompatible with the collation of the template database (C.UTF-8)
---- The following constructor parameters did not have matching fixture data: DatabaseFixture databaseFixture

so i tried altering the SQL you were using to this:
var cmd = new NpgsqlCommand($"CREATE DATABASE {databaseName} WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1 TEMPLATE {databaseFixture.TemplateDatabaseName}", tmplConnection)

but this doesn't seem to have solved the collation problem

[kevin-presalytics]

This was super-helpful! Thank you!