Now that we have database migrations and API integration tests set up we can combine them together. In this post, I will go through some tips and tools that proved to be really helpful. Source code.

Double-check that you are using test DB

The first thing that you should be doing is make sure you are running tests in test environment. Usually when working we switch our connection strings to our QA or even one of our production databases, the least that we want is to corrupt these databases.

This check will vary from project to project, I usually check client or user count in DB, for tests, there shouldn't be that many, at least for our tests.

var maxAllowedEntries = 100;

using var conn = new NpgsqlConnection(settings.Value.ConnectionString);

var entries = await conn.ExecuteScalarAsync<int>("select count(*) from weather_predictions.predictions");

return entries < maxAllowedEntries;

Disable test concurrency

We have one test DB, logically we can only run one test at a time. This can be controlled with collections.

[Collection(nameof(NotThreadSafeResourceCollection))]
public class WeatherForecastControllerDatabaseTests
{

[CollectionDefinition(nameof(NotThreadSafeResourceCollection), DisableParallelization = true)]
public class NotThreadSafeResourceCollection
{
}

Clear database after each test with Respawn

It is possible to restart docker and rebuild the entire database after each test but that would take a lot of time. Respawn NuGet allows us to clear the database, either full or partial with initialization scripts.

var checkpoint = new Checkpoint
{
    TablesToIgnore = new string[] { },
    SchemasToInclude = new string[] { },
    DbAdapter = DbAdapter.Postgres
};
using var conn = new NpgsqlConnection(settings.Value.ConnectionString);
await conn.OpenAsync();

await checkpoint.Reset(conn);

Comparing objects

For object comparison, I highly recommend using Compare-Net-Objects, this NuGet will cover all the use cases that you need.

//When we ignore collection order we must specify key which will be used to match actual with expected
comparer.Config.IgnoreCollectionOrder = true;
comparer.Config.CollectionMatchingSpec.Add(typeof(WeatherForecast), new List<string> { nameof(WeatherForecast.Date) });

//Summary will be random, we don't really care about in this test. Check if it's not null.
comparer.Config.CustomPropertyComparer<WeatherForecast>(obj => obj.Summary,
    new CustomComparer<string, string>((expected, actual) => !string.IsNullOrEmpty(actual)));

Summary

In order to run tests fluently some gluing is needed. The first thing should be security, make sure you are using test DB and not production or another shared database. Since only one test can be running at any given time - make sure to configure concurrency, otherwise, you will have to debug a lot of random failing tests due to multiple tests writing data to DB. Lastly comparing objects should be easy, instead of writing tons of helpers invest some time and look into Compare-Net-Objects it's highly customizable and will cover all your cases.