Integration tests using Sql Server database in a container

Integration tests, from its definition, should be design in a way that allows testing the whole system under test (SUT). Starting from the end user experience down to the low server and database calls. When it is about integration testing the web api its often suggested in various internet resources to use some sort of in memory database during the test. Either it is a SqLite in memory database or InMemory Entity Frameowrk provider, I was never a big fun of such solutions. I mean at the end the database itself is a one of the most crucial parts of the system and can influence its functionality a lot. Memory databases are lite and quick, but hase some limitations and in case of more complex scenarios require some nasty workarounds or database context reconfiguration, which for obvious reason is not something we shoud do in integration testing.

As a fix for that problem there is a nuget package called TestContainers. TestContainers essentially brings lightway throwayaw docker containers to your integration tests. You can use it for database containerization, run selenium UI test in containerized web browser and more. And what’s also nice in case of .NET, it nicly integrates and configures all the required stuff for you.

Without any further ado let’s start with simple ASP.NET Core Web Api project.

In Visual Studio create new ASP.NET Core WebAPI project targeting .NET 7.0.

Remove all controllers and leave only the WeatherForecast class with slight modification

public class WeatherForecast
{
     public Guid Id { get; set; }
     public DateTime Date { get; set; }
     public int TemperatureC { get; set; }
     public string? Summary { get; set; }
     public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
}

Now add references to following nuget packages

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Next using Visual Studio scaffolding system, add new scaffolded item

“API with read/write endpoints using EntityFramework”

Select WeatherForecast as model class and create new classes for endpoints and database context. You should get WeatherForecastEndpoints class which essentially maps basic CRUD routes of the API using minimal APIs implementations. We will skip that for brevity here. You can download whole working solution at the end of this post.

For the database context we want to add some seed data though. So lets open the WeatherForecastContext class (or whatever name you provided during scaffold) and add OnModelCreating override

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

     public DbSet<WeatherForecast> WeatherForecast { get; set; } = default!;

     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         base.OnModelCreating(modelBuilder);
         modelBuilder.Entity<WeatherForecast>().HasData(GetSeedData());
     }

     private WeatherForecast[] GetSeedData()
     {
         var forecast = Enumerable.Range(1, 5).Select(index =>
             new WeatherForecast
             {
                 Id = Guid.NewGuid(),
                 Date = DateTime.Now.AddDays(index),
                 TemperatureC = Random.Shared.Next(-20, 55),
                 Summary = Summaries[Random.Shared.Next(Summaries.Length)]
             })
             .ToArray();
         return forecast;
     }

     static string[] Summaries = new[] { "Freezing", "Bracing", "Chilly", "Cool",
          "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching" };
 
}

Now let’s modify the web api entry point

var builder = WebApplication.CreateBuilder(args);
 
builder.Services.AddDbContext<WeatherForecastContext>(options =>
{
     var connectionString = builder.Configuration.GetConnectionString("Default");
     options.UseSqlServer(connectionString);
});
 
var app = builder.Build();
 
app.UseHttpsRedirection();
 
app.MapWeatherForecastEndpoints();
 
using (var serviceScope = app.Services.CreateScope())
{
     serviceScope!.ServiceProvider!.GetService<WeatherForecastContext>()?.Database?.Migrate();
}
 
app.Run();

As you can see, we are using Entity Framework migrations for that simple Web API. This is just to showcase that the containerized database is properly migrated and seeded. So the only last missing bit is to create initial migration. Build the project and open developer console. Then run follwoing command

dotnet ef migrations add InitialCreate

New Migrations folder with InitialCreate migration inside should be created in the project. Start the project and briefly test the api.

Now we can proceed to integration testing. Add new test project to the solution, use xUnit test framework and add following nuget packages to that:

Microsoft.AspNetCore.Mvc.Testing
Microsoft.EntityFrameworkCore
Microsoft.NET.Test.Sdk
Testcontainers

Also change the test project sdk (inside test project file) to

Microsoft.NET.Sdk.Web

What we do next is just following the Microsoft quide regarding integration tests. Let’s create two classes IntegrationTests, this will be main test class where we put all test methods, and IntegrationTestsFixture which will implement the test fixture so we will spin up only one database for all test methods runs.

public class IntegrationTests : IClassFixture<IntegrationTestsFixture>
{
     private readonly IntegrationTestsFixture Fixture;

     public IntegrationTests(IntegrationTestsFixture fixture)
     {
         Fixture = fixture;
     }

     [Fact]
     public async Task WeatherForecastEndpoints_Getall_ShouldReturnValues()
     {
         var response = await Fixture.ApiClient!.GetAsync("/api/WeatherForecast");
         response.StatusCode.Should().Be(HttpStatusCode.OK);
         var weatherForecasts = await response.Content.ReadFromJsonAsync<WeatherForecast[]>();
         weatherForecasts.Should().NotBeEmpty();
     }

     [Fact]
     public async Task WeatherForecastEndpoints_OnPost_ShouldStoreEntity()
     {
         var newEntity = new WeatherForecast
         {
             Date = DateTime.Now,
             Id = Guid.NewGuid(),
             Summary = "Windy",
             TemperatureC = 10
         };
         var response = await Fixture.ApiClient!
                             .PostAsJsonAsync("/api/WeatherForecast", newEntity);
         response.StatusCode.Should().Be(HttpStatusCode.Created);
     }
}

Here we call couple of api endpoints, one GET and one POST and check the status codes of the responses. Btw. I’m using FluentAssertions package here which brings awesome test helpers.

Then we can implement the fixture class.

public class IntegrationTestsFixture : IAsyncLifetime
{
     public HttpClient? ApiClient;
     public readonly MsSqlTestcontainer DbContainer;
     private readonly WebApplicationFactory<Program> appFactory;

     public IntegrationTestsFixture()
     {
       ...
     }

     public async Task InitializeAsync()
     {
         await DbContainer.StartAsync();
         ApiClient = appFactory.CreateClient();
     }

     public async Task DisposeAsync()
     {
         await DbContainer.StopAsync();
     }
}

First of all the fixture class implements the IAsyncLifetime interface from xUnit making it easier to control the database container creation and disposal. The InitializeAsync is called before any test starts and DisposeAsync is called after all tests are done. So here we’ve put the container start and stop methods. Also note that here we create the web api client right after the db container is started.

Now we can proceed with the fixture class constructor. First we need to tell the TestContainers to use Ms Sql Server implementation. There are special classes ready to use, that handles everything for us. These are 

MsSqlTestcontainerConfiguration and
MsSqlTestcontainer

However there are few settings we need to configure

var databaseServerContainerConfig = new MsSqlTestcontainerConfiguration();
databaseServerContainerConfig.Database = "WeatherForecast";
databaseServerContainerConfig.Password = Convert.ToBase64String(RandomNumberGenerator.GetBytes(12));
databaseServerContainerConfig.Environments.Add("MSSQL_PID", "Express");
 
DbContainer = new TestcontainersBuilder<MsSqlTestcontainer>()
     .WithDatabase(databaseServerContainerConfig)
     .Build();

MsSqlTestcontainerConfiguration internally downloads and spins up a mssql docker image

mcr.microsoft.com/mssql/server:2017-CU28-ubuntu-16.04

and so according to this we have to configure these settings and use Sql Server authentication. Not a big deal, all settings are self explanatory, I think. Note, that for every run we generate new random sa password. Then the rest of code is essentially same as we woudn’t use TestContainers at all. Except we have the Sql Server connection string already provided by the DbContainer.

appFactory = new WebApplicationFactory<Program>()
.WithWebHostBuilder(builder =>
{
   builder.ConfigureServices((IServiceCollection services) =>
   {
      services.RemoveAll<DbContextOptions<WeatherForecastContext>>();
      services.RemoveAll<DbConnection>();
      services.AddDbContext<WeatherForecastContext>(options =>
      {
         options.UseSqlServer(DbContainer.ConnectionString + "Trust Server Certificate=True;");
      });
  });
});

So what is going on here? We configure the test server and essentially need to override the database context configuration. So first we remove all service registrations that are originally added in the web api Program. Then we configure database context with connection string that is provided by the database container. One quick fix here though. As Sql Server running in a docker container does not have the valid certificate we need to add the setting to Trust Server Certificate.

That’s it!. We can now start the Docker locally, if it’s not already running, fire our tests and see how the TestContainers spin up database conteiner and deletes them after the tests are done. Note that first run can take some time as the docker image needs to be pulled from the hub.

What’s nice about TestContainers is the out of the box support for Azure DevOps pipeline. So simply push you project to the repo, setup a standard pipeline with test run task and you’r done.

Thanks!