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 CreatePostgreSqlUniqueDatabaseOptions() 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.CreatePostgreSqlUniqueDatabaseOptions<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.CreatePostgreSqlUniqueDatabaseOptions<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 works but takes a long time - 12 seconds on my Windows PC.

In corresponding with Shay Rojansky via EF Core issues that there was a method within the npgsql.efcore.pg repo that is uses within EF Core's internal testing code called EnsureClean. With permission from the EF Core team I have copied this method into this library - see the code below

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

context.Database.EnsureCreated(); 

This takes 4 seconds, which is significantly faster than EnsureDeleted + 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.

????????????????????????????? More to come ????????????????????????????????