Integration testing for dotnet core APIs: Handling database

Welcome to the 2nd post in our Integration testing series. You may check out the previous post that introduces the concept of writing integration tests using WebApplicationFactory in dotnet below:

Integration testing for dotnet core APIs: Introduction

Integration testing for dotnet core APIs: Introduction

devcodex.in

Almost every application relies on persistent storage, typically through a database. Integration testing with a real database can be challenging, especially when trying to maintain isolation and consistency across tests. In this post, we will explore how to effectively manage database dependencies in integration tests using WebApplicationFactory and containerized databases.

Pre-requisites, in case you want to follow the same setup on your system:

• Postgres DB Server: This is needed in case you want to test out the main application.
• Docker: This is needed to run test containers.

To do this we need to first introduce a database in our sample superhero API. For this demo purpose, we have used a Postgres database.

For this, we made the below changes in our superhero API:

{
  "ConnectionStrings": {
    "WebApiDatabase": "Host=localhost; Database=superhero; Username=postgres; Password=postgres"
  }
}
        

Install the package Npgsql.EntityFrameworkCore.PostgreSQL in the main API project. After that, we need to provide options in the program file to guide the application to use the Postgres database:

builder.Services.AddDbContext<SuperHeroDbContext>(opt =>
    opt.UseNpgsql(configuration.GetConnectionString("WebApiDatabase")));
        

Along with this, we also need to create a migration, if not already present, to set up the database schema.

Finally, our unit test:

[Fact(DisplayName = "Get all superheros API returns all superheroes")]
public async Task Get_All_SuperHeroes_Returns_List_Of_SuperHero()
{
    // Arrange
    factory.SharedFixture.SuperHeroDbContext.SuperHero.AddRange(new List<SuperHero>()
    {
        new SuperHero(1, "Batman","Bruce Wayne","Short distance fly,Common sense","Gotham", 40),
        new SuperHero(2, "Superman", "Clark kent", "Fly, Shoot laser beam, Super strength, ice breath","Gotham", 42),
        new SuperHero(3, "Robin", "John Blake", "Detective","Gotham", 35)
    });
    await factory.SharedFixture.SuperHeroDbContext.SaveChangesAsync();
    
    // Act
    var response = await factory.CreateClient().GetAsync("/SuperHero");

    // Assert
    response.StatusCode.Should().Be(HttpStatusCode.OK);
    var superHeroes = await response.Content.ReadFromJsonAsync<List<SuperHero>>();
    superHeroes.Should().NotBeEmpty();
    superHeroes![0].Id.Should().Be(1);
    superHeroes![0].SuperName.Should().Be("Batman");
}
        

Let's understand the above test:

• Now before any of the tests will start executing, our SharedFixture code will run and it will fire up the test container as you can see in the below image:

• In the Arrange step, I added a few records directly to the database using the SharedFixture.
• After the above step, there are some records in the system and in the Act step, we will be trying to call the GET superheroes API.
• If everything is correct, we should be able to see our tests being passed like below: