Mockless DB Testing

Test, Test, Test… In my years architecting, designing, coding, releasing, and yes TESTING systems prove there is not a single right way to do things, but I have opinions. I've always supported functional tests that prove the logic and output are what is expected. Part of this testing which I don't like is eternally mocking pieces of the system to make it happen. In the past you had to mock objects that would write to a database since a test harness does not always have access to a DB. I want a way to write these functional tests that can run as part of build, but also can be used to debug actual problems encountered in complex system processing.

Over the last few years, I've spent the lion's share of my time working on AWS programming using Go which in turn writes to a Postgres DB. I decided that I need to step back a few years and reengage in some C# and Microsoft technologies that will allow me to create a set of tests for data access (using Postgres) that can run in a build, but also allow for functional tests for real world problems.

In this post I will step through creating a super simple web API, a data access class library, and a test library. I'm a huge golf fan, so I wrote some silly code to build a bag of golf clubs and list the contents of the bags created. This will use the following:

• dotnet 7 (lots of CLI commands as well)

• VS Code

• EF Core

• Postgres

Let's get started.

Setup

We are going to need some projects to house the code. Below are the steps to create a solution, webapi, class lib to house the DB code and a test class. I am using VS Code for this, but you can use other tools. In the code below we are creating a solution file, 3 projects and finally adding the 3 projects to the solution file.

dotnet new sln -o Equipment
Cd Equipment
dotnet new webapi -o Equipment.API 
dotnet new mstest -o Equipment.Tests
dotnet new classlib -o Equipment.DB
dotnet sln add Equipment.API/Equipment.API.csproj
dotnet sln add Equipment.Tests/Equipment.Tests.csproj
dotnet sln add Equipment.DB/Equipment.DB.csproj

Now let's add a reference to DB class lib in webapi and test harness projects.

dotnet add Equipment.API/Equipment.API.csproj reference Equipment.DB/Equipment.DB.csproj
dotnet add Equipment.Tests/Equipment.Tests.csproj reference Equipment.DB/Equipment.DB.csproj
 
#Verify everything builds
Dotnet build

I am using VS Code for this exercise. When you open the Equipment folder using VS Code you should have something that looks like this:

DB Code:

The Equipment.DB project will house our database access code. When we are done it will consist of model classes, a db context class and a database migration class. To get started let's create the model classes and then the db context class. In the Equipment.DB folder create the model classes for the data we will need.

Bag.cs

namespace Equipment.DB;
public class Bag
{
  public Guid Id { get; set; }
  public string Description { get; set; }
  public List<Club> Clubs { get; set; }
}

Club.cs

namespace Equipment.DB;
public class Club
{
  public Guid Id { get; set; }
  public string Type { get; set; }
  public string Model { get; set; }
  public string Make { get; set; }
}

EquipmentContext.cs - Change your connection string below as needed!

This context class contains the Lists(DbSet<>) we can use to update data in the database.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
namespace Equipment.DB;
public partial class EquipmentContext : DbContext
{
  public EquipmentContext()
  {
  }
  public EquipmentContext(DbContextOptions<EquipmentContext> options)
      : base(options)
  {
  }
  public DbSet<Club> Clubs { get; set; }
  public DbSet<Bag> Bags { get; set; }
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      => optionsBuilder.UseNpgsql("Host=localhost;Database=golfing;Username=postgres;Password=xxx");
 
  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    //OnModelCreatingPartial(modelBuilder);
    modelBuilder.UseSerialColumns();
  }
  partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

In order to build the Equipment.DB project we need to add the following packages to the project. The following code can be used to add these packages so the project.

dotnet add Equipment.DB/Equipment.DB.csproj package Microsoft.EntityFrameworkCore.Design
dotnet add Equipment.DB/Equipment.DB.csproj package Microsoft.EntityFrameworkCore.Tools.DotNet
dotnet add  Equipment.DB/Equipment.DB.csproj package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add  Equipment.DB/Equipment.DB.csproj package Npgsql.EntityFrameworkCore.PostgreSQL.Design
dotnet add Equipment.DB/Equipment.DB.csproj  package Microsoft.EntityFrameworkCore.Tools

Run this command to verify that everything is building:

dotnet build

In order to allow EF core to know how to build our database and tables we need to add a migration. Each migration will describe the schema changes needed in the database to increment from one migration to the next. Lets add the first migration that will be used to create the database and tables. The "ef migrations add" command creates the new migration and the instruction that follows it will actually run the migration to create the db.

Cd Equipment.DB
dotnet ef migrations add InitialCreate
dotnet ef database update

After running the migration and database update you should be able to open pgAdmin or your other favorite Postgres tool and see the DB:

Let's go back and check that everything is building, run the following:

Cd..
Dotnet build

Testing

I setup tests for list, add, and delete for Club and Bag. Here is the code for Bag testing. The code for Club is VERY similar and you can imagine how that is. This testing is perhaps a little too verbose, but I wanted to demonstrate adding, deleting and listing data to the model. These could easily be combined into a smaller test suite that would provide the same amount of coverage. You should note that each test destroys the database using db.Database.EnsureDeleted() and then creates it using db.Database.EnsureCreated().

using Equipment.DB;
namespace Equipment.Tests;
[TestClass]
public class BagTest
{
  [TestMethod]
  public void Test_AddBag()
  {
    EquipmentContext db = new();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    db.Bags.Add(new Bag() { Description = "Test Bag 1", });
    db.SaveChanges();
  }
  [TestMethod]
  public void Test_DeleteBag()
  {
    EquipmentContext db = new();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    db.Bags.Add(new Bag() { Description = "Test Bag 1", });
    db.Bags.Add(new Bag() { Description = "Test Bag 2", });
    db.SaveChanges();
    var bag = db.Bags.First();
    var deletedId = bag.Id;
    db.Remove(bag);
    db.SaveChanges();
    bag = db.Bags.First();
    Assert.AreNotEqual(deletedId, bag.Id);
  }
  [TestMethod]
  public void Test_ListBags()
  {
    EquipmentContext db = new();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    db.Bags.Add(new Bag() { Description = "Test Bag 1", });
    db.Bags.Add(new Bag() { Description = "Test Bag 2", });
    db.SaveChanges();
    Assert.IsTrue(db.Bags.Count() > 1);
  }
}

Additionally I setup a list of the equipment like this:

using Equipment.DB;
namespace Equipment.Tests;
[TestClass]
public class EquipmentTest
{
  [TestMethod]
  public void Test_SetupEquipment()
  {
    EquipmentContext db = new();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    List<Club> clubs = new();
    clubs.Add(new Club() { Type = "3iron", Model = "Xzz", Make = "Golf Manufacturer" });
    clubs.Add(new Club() { Type = "7iron", Model = "Duffer", Make = "Home make" });
    db.Bags.Add(new Bag() { Description = "Bag 1", Clubs = clubs });
    clubs = new();
    clubs.Add(new Club() { Type = "5iron", Model = "AAA", Make = "Ping" });
    clubs.Add(new Club() { Type = "6iron", Model = "Birdier", Make = "Srixon" });
    db.Bags.Add(new Bag() { Description = "Bag 2", Clubs = clubs });
 
    db.SaveChanges();
    var bags = db.Bags;
    Assert.IsTrue(bags.Count() == 2);
    Assert.IsTrue(bags.Where(b => b.Description == "Bag 2").First().Clubs.Count() == 2);
  }
}

Check that everything builds…

dotnet build

I should note at this point I had an error about a version conflict on Microsoft.EntityFrameworkCore in the webapi and db projects. I had to edit the csproj files to make the versions match.

Run all tests to verify they work. A nice thing to note for testing locally and creating more complex system tests, you can run a test individually and then open pgAdmin to look at the resulting data in the tables. I used the test explorer in VS Code, and here are the results:

WEBAPI Code:

In order to add a controller that will be able to use the database that was just created we need to setup the code in Program.cs to add the DB context and drop a connection string in the appsettings. I advised against anything as simple as what is shown below in appsettings. There are different ways to get your connection data and tons of articles on good ways to do that.

Add code needed to Program.cs:

builder.Services.AddDbContext<EquipmentContext>(
  x => x.UseNpgsql(builder.Configuration.GetConnectionString("golfing"))
);

Appsettings:

  "ConnectionStrings": {
    "golfing": "Server=localhost; Database=golfing; User Id=postgres; Password=xxx;Port=5432"
  },

Check your build(run cmd: dotnet build)

Now we need to add a controller to the webapi project(EquipmentController.cs) in the Controllers folder. Below is a ridiculously simple one that uses the injected EquipmentContext in the get method:

[ApiController]
[Route("[controller]")]
public class EquipmentController : ControllerBase
{
  private EquipmentContext _db;
  public EquipmentController(EquipmentContext db)
  {
    _db = db;
  }
  public IEnumerable<Bag> Get()
  {
    return _db.Bags.Include(c => c.Clubs).ToList();
  }
}

Check your build(run cmd: dotnet build)

Run webapi in debug

Check the response to Equipment Get(Alter your port appropriately):

https://localhost:7157/Equipment

Result:

[{"id":"2468b354-cc54-4a94-9950-d60e77ef097a","description":"Bag 2","clubs":[{"id":"239433e8-52cd-4cef-983b-017d4b68e2d5","type":"5iron","model":"AAA","make":"Ping"},{"id":"b07e6446-56f9-49c8-a9e5-17b05fe3dca4","type":"6iron","model":"Birdier","make":"Srixon"}]},{"id":"5908a4ee-eb1d-40bb-a884-10ae8f01e4e2","description":"Bag 1","clubs":[{"id":"1aff20e4-88d5-4a3a-ad4b-132bc117fc4c","type":"3iron","model":"Xzz","make":"Golf Manufacturer"},{"id":"f49298f2-f759-4d15-9e3c-f6ac0b8819c3","type":"7iron","model":"Duffer","make":"Home make"}]}]

API unit Tests

Now that we have a functional rest API we can add more methods and test around the GET/PUT/DELETE/POST endpoints that create and destroy the database like the tests above. This allows for more realistic system or e2e tests for your application.

Summary

The simple code above could be expanded to contain many more relationships involving golf, we could add actual golfers which own a given bag and/or clubs, golf balls, courses played, scores, and others. The more complex the any code base gets the more need we have to be able to setup and test any scenario, and this mock-less database approach helps.

In this post I've:

• used many of the dotnet cli commands

• learned a few new EF Core CLI commands

• picked up a few tidbits from the latest C# version

• Setup a unit test that will hit a local Postgres DB from unit tests

After following along with this everyone can have a mockless DB testing framework that hits a database without any mocked code. This approach allows tests that can run as part of a build to test database read/write functionality and logic in our code. This enables us to setup complex real-world scenarios that will write and read from the DB that might not otherwise be possible (or perhaps easy) with mocking.

It would be an interesting exercise to setup a build process in Azure that would have a Postgres DB as part of the container where these tests could run in the build process. That in fact might be a good next iteration for this code…

Now that I've coded a bag setup, I just need some tips to make me play better, miracle advice is welcome!

If you have any questions on this or other topics, please feel free to contact me: stan@myndcorepartners.com

About the author:

I am a solution architect with 20+ years of experience designing, coding, testing, and implementing large scale financial services applications. Over my career I've concentrated on Microsoft core development tools, but over the last few years have also enjoyed working with AWS, Go, and Postgres.