Using PostgreSQL databases

EfCore.TestSupport version 5.1.0 added methods that will create options to provide an PostgreSQL database for testing. One provides a class-level unique database name, and one provides a method-unique database name, and one that allows you to access EF Core logging.

NOTE: you need at least unit test class-level unique databases when using xUnit, because xUnit runs all the unit test classes in parallel, and you don't want multiple unit tests trying to update the same database!

The CreatePostgreSqlUniqueClassOptions() extension method

This returns an EF Core options for a PostgreSQL database using the base connection string PostgreSqlConnection from the appsettings.json file (see this docs page about this file) but the name of the database now has the type name of the object (which should be this) as a suffix. See test code below

[Fact]
public void TestPostgreSqlUniqueClassOk()
{
    //SETUP
    //ATTEMPT
    var options = this.CreatePostgreSqlUniqueClassOptions<BookContext>();
    using (var context = new BookContext(options))
    {
        //VERIFY
        var builder = new NpgsqlConnectionStringBuilder(
            context.Database.GetDbConnection().ConnectionString);
        builder.Database.ShouldEndWith(GetType().Name);
    }
}

Ability to add extra options to the DbContextOptionsBuilder<T>

The SQL Server options extension methods have an optional parameter that allows you to set extra options at the DbContextOptionsBuilder<T> level. Below is part of the unit tests showing how to add/override options.

//... previous code removed to focus on the feature
var options2 = this.CreatePostgreSqlUniqueClassOptions<BookContext>(
    builder => builder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
using (var context = new BookContext(options2))
{
    //VERIFY
    var book = context.Books.First();
    context.Entry(book).State.ShouldEqual(EntityState.Detached);
}

The CreatePostgreSqlUniqueMethodOptions() extension method

This returns a SQL Server options with the connection string from the appsettings.json file but the name of the database now has the type name of the object (which should be this) followed by the method name as a suffix. See test code below

[Fact]
public void TestPostgreSqUniqueMethodOk()
{
    //SETUP
    //ATTEMPT
    var options = this.CreatePostgreSqlUniqueMethodOptions<BookContext>();
    using (var context = new BookContext(options))
    {

        //VERIFY
        var builder = new NpgsqlConnectionStringBuilder(
            context.Database.GetDbConnection().ConnectionString);
        builder.Database
            .ShouldEndWith($"{GetType().Name}_{nameof(TestPostgreSqUniqueMethodOk)}" );
    }
}

NOTE: You shouldn't really need the CreatePostgreSqlUniqueMethodOptions<T> method, as xUnit runs the methods inside a test class serially, so CreateUniqueClassOptions<T> should be enough to avoid parallel unit tests accessing the same database.

Obtaining EF Core logging data

Its often useful to see what EF Core is doing when something isn't working properly. The `CreateUniqueMethodOptionsWithLogTo method returns the logs. Below is a simple version that captures the logs into a list, but more complex options are possible - see Tools for capturing EF Core logging for more details.

var logs = new List<string>();
var options = this.CreatePostgreSqlUniqueClassOptionsWithLogTo<BookContext>(log => logs.Add(log));
using var context = new BookContext(options);
//... rest of test left out

How to get an empty database with the correct schema

There are two ways to create an empty database with the correct schema. The obvious one is to call EnsureDeleted, followed by EnsureCreated. This takes 350 ms. on my Windows PC with the PostgreSQL server running in a Windows Subsystem for Linux, known as WSL - see this article about how I set that up.

In corresponding with Shay Rojansky via EF Core issues we came up another way that is much faster (about 80 ms. on my PC) and follows the same approach as the SQL Server EnsureClean method. Therefore I have added a PostgreSQL version of the EnsureClean method - see the code below

var options = this.CreatePostgreSqlUniqueClassOptions<BookContext>();
using var context = new BookContext(options);

context.Database.EnsureCreated(); 

The EnsureClean's setUpSchema parameter

NOTE: The EnsureClean method has a parameter boolean parameter called setUpSchema that defaults to true. If you set this to false then it doesn't call the EnsureClean method, which allows you to use another way to set the database schema, such as via a migration. Unlike the SQL Server EnsureClean method it deletes all migration history tables, so you can apply a normal migration and it will work.