Using PostgreSQL in dev: Part 2 – Testing against a PostgreSQL database

This article describes how to test an application that use Entity Framework Core (shortened to EF Core) that accesses a PostgreSQL database. I describe various methods that help you to write xUnit tests that have to access a PostgreSQL database. These new PostgreSQL test methods can be found in the new version of my EfCore.TestSupport library (5.1.0) that has PostgreSQL helpers and also works with both EF Core 5 and EF Core 6.

 The full list of the “Using PostgreSQL in dev” series are:

• Using PostgreSQL in dev: Part 1 – Installing PostgreSQL on Windows
• Using PostgreSQL in dev: Part 2 – Testing against a PostgreSQL database (this article)

TL;DR; – Summary of this article

• I needed to test some code that uses PostgreSQL databases so added PostgreSQL methods to the new 5.1.0 version of my EfCore.TestSupport library, which also supports EF Core 6.
• I show what a PostgreSQL connection string looks like and how you can access a PostgreSQL database using EF Core.
• xUnit runs each test class in parallel, so each test class needs a unique PostgreSQL database. The new PostgreSQL methods handle this for you by adding the test class name on the end of the base database name.
• It’s easier to write a test if the database is empty, so I show two ways to do make the empty at the start of test:
  • The standard approach is slow (e.g., took 13 seconds per test).
  • With help from the EF Core team, I added a quicker approach (e.g., took 4.2 seconds per test).

NOTE: As well as the adding PostgreSQL test helper I also reinstated the Seed from Production feature. I removed it from version 5 because I didn’t think anyone was using it, but some people  were using this and they requested I added back.

Setting the scene – my approach to testing applications using EF Core

I write a lot of EF Core code and to make sure that code is correct I use lots of automatedtesting. Over the years I have tried different ways to write and test code (mock repository, EF6’s Effort, EF Core In-Memory), but since EF Core came out I have found that testing against a real database has a lot of pros – they are:

1. You can’t Mock EF Core’s DbContext, so you have to use an EF Core supported database in your tests.
2. If you have a good set of database test helpers, you can write tests much quicker.
3. Using an actual database also checks your EF Core code is correct too.

The definition of a unit test say that it doesn’t include a database, and the correct name is  integration testing. Use whatever term you like, but the main point is that the tests are run automatically and return pass/fail results. That means you can quickly check that a) the changes you have make to your code work correctly and b) the changes hasn’t broken any other parts of you’re your application.

As point 2 says, having a good set of database test helpers can significantly reduce the amount of test code you need to write. That’s why I created a library called EfCore.TestSupport, and in the recent update to support EF Core 6 I also added PostgreSQL helpers.

NOTE: If you have used EfCore.TestSupport before, then the PostgreSQL methods work the same as the SQL Server methods, but the method name contains “PostgreSql”.

How to access a PostgreSQL database

To access a PostgreSQL database, you need a connection string. This string containing the PostgreSQL information for the host, database name, and the rights to access that database. The string below shows these four parts that work with a PostgreSQL being run in Windows Linux subsystem.

“host=127.0.0.1;Database=YourDatabaseName;Username=postgres;Password=XXXXX”

IMPORTANT UPDATE

I had really poor performance when accessing the main postgres database and I have been trying to improve this. With the help of Shay Rojansky on the EF Core team I found the problem. The host should be 127.0.0.1 and not the localhost that the previous articles suggested. Changing this made a MASSIVE improvement, e.g. clearing the database used to take 13 seconds, now it takes 350 ms.! This article has been updated to show this.

NOTE: If you are running your PostgreSQL server in Windows Linux subsystem, then the host value in the connection string should be 127.0.0.1, the username should be postgres, and the password should be the same as the password you provided when running the Linux command: sudo passwd postgres.

You need to create a class that inherits EF Core’s DbContext class (see a super-simple example below). This contains information of the classes that you want to map to a database.

public class TestDbContext : DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options)
        : base(options) { }

    public DbSet<MyEntityClass> MyEntityClasses { get; set; }
}

NOTE: You need a constructor because you are going to provide a different connection string to your tests than you provide to your main application (see this section of the EfCore.TestSupport docs on this)

To create the DbContextOptions<TestDbContext> for your xUnit test you need install the NuGet package called Npgsql.EntityFrameworkCore.PostgreSQL and build the options to pass to your DbContext. The code below shows an example of how you would manually create the options, but the EfCore.TestSupport provides methods which will do this for you (see next section).

var connectionString =  
       “host=127.0.0.1;Database=YourDatabaseName;Username=postgres;Password=XXXXX”;
var optionsBuilder = new DbContextOptionsBuilder<TContext>();
optionsBuilder.UseNpgsql(connectionString);
using var context = new TestDbContext(optionsBuilder.Options);
//… your test goes here

But there are issues when using xUnit for your testing, which makes creating the options a bit more complex, which I cover in the next section.

Using xUnit with databases

The xUnit library is the main test library used by EF Core and ASP.NET Core and recommended for .NET testing. I really like xUnit’s feature that runs each test class in parallel because running all the tests finishes much faster. The downside of xUnit running in parallel is that each test class needs its own database. That’s because having multiple tests accessing the same database in parallel is very likely to break your tests.

For this reason, EfCore.TestSupport database methods return a unique database name for each xUnit test class. It does this taking the base PostgreSqlConnection from the appsettings.json file (see code below) and adds the name of the test class to the end of the base database name. This gives you a database name unique to each test class.

{
  "ConnectionStrings": {
    "PostgreSqlConnection": "host=127.0.0.1;Database=TestSupport-Test;Username=postgres;Password=your-password",
   // The SQL Server connection string has been removed
  }
}

NOTE: EfCore-TestSupport makes sure the database in the PostgreSqlConnection connection string in the appsettings.json file ends with the word “Test”. This is useful to differentiate PostgreSQL databases that are used for testing, and therefore can be deleted without losing any application data.

The test shown below comes from the TestPostgreSqlHelpers test class tests in the EfCore.TestSupport repo. The CreatePostgreSqlUniqueDatabaseOptions extension method creates the PostgreSQL options using the this input to get the test class name to add to the database name. The options are then used to create a new DbContext.

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

    // rest of the test left out
}

NOTE: The code about uses the CreatePostgreSqlUniqueDatabaseOptions extension method, but there are other versions to provide logging and method-level database uniqueness.  See the EfCore.TestSupport PostgreSQL documentation for more these methods.

Making sure a test database is empty (quickly!)

It’s possible to write tests that run correctly even if there is data already in the database, but that makes it harder to write a valid test. The best approach is to make sure the database is empty before each runs, and the simplest way is to delete the database and create a new database, as shown below.

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

    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    //ATTEMPT
    context.SeedDatabaseFourBooks();

    //VERIFY
    context.Books.Count().ShouldEqual(4);
}

This is easy do and works all situations but before I found a fix it took about 13 seconds, but now its a very respectable 350 ms. Because of the initial slow 13 seconds I wasted a lot of time trying to find ways to the way improve this, but that’s how it goes in development 🙁

But it did create a version that might be of use to you called EnsureClean. Shay Rojansky, who is the PostgreSQL expert and evangelist, came back with some ideas and a PostgreSQL version of the SQL Server EnsureClean that I added in version 5.1.0 of the library.

The EnsureClean deletes all the tables, types, extensions etc. and then adds tables etc. using a version of the EnsuredCreated method. You can see this

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

    context.Database.EnsureClean(); 

    //ATTEMPT
    context.SeedDatabaseFourBooks();

    //VERIFY
    context.Books.Count().ShouldEqual(4);
}

EnsureClean takes about 80 ms. which is 4 times quicker than the EnsureDeleted plus EnsureCreated approach, but whether its worth using is up to you.

Conclusion

I have found automated tests is one of the best ways for delivering a good, correct code. But at the same time, I don’t want to be spend a lot of time writing tests. So, in 2017 I built a library called EfCore.TestSupport that provide methods that handles the setting up and managing test database. And as of November 2021, there has been nearly 400,000 downloads of the EfCore.TestSupport library.

Up until now the EfCore.TestSupport library supported SQL Server and Cosmos DD databases (and SQLite in-memory databases). But now that I use PostgreSQL databases, I added support for PostgreSQL databases too. My use of these methods showed me that it was worth adding some extra methods to speed up the emptying of a PostgreSQL database at the start of a test.

I was working on updating the EfCore.TestSupport library to support EF Core 6, so it was a good time to add these new PostgreSQL features too (see the EfCore.TestSupport ReleaseNotes file for the full details on the changes). I hope these new features will help you in your testing EF Core applications that use PostgreSQL databases.

Happy coding.