How to setup dotnet Testcontainers with a SQL Server database for Integration Tests?

Question

I have an API REST .NET 7 and I want to build integrations tests using a sql server container database. I've tried to follow the example in the documentation (https://dotnet.testcontainers.org/examples/aspnet/):

const string weatherForecastStorage = "weatherForecastStorage";

var mssqlConfiguration = new MsSqlTestcontainerConfiguration();
mssqlConfiguration.Password = Guid.NewGuid().ToString("D");
mssqlConfiguration.Database = Guid.NewGuid().ToString("D");

var connectionString = $"server={weatherForecastStorage};user id=sa;password={mssqlConfiguration.Password};database={mssqlConfiguration.Database}";

_weatherForecastNetwork = new NetworkBuilder()
 .WithName(Guid.NewGuid().ToString("D"))
 .Build();

_mssqlContainer = new ContainerBuilder<MsSqlTestcontainer>()
 .WithDatabase(mssqlConfiguration)
 .WithNetwork(_weatherForecastNetwork)
 .WithNetworkAliases(weatherForecastStorage)
 .Build();

But the MsSqlTestcontainer and MsSqlTestcontainerConfiguration classes don't exist.

The nugget package version is 3.0.0 but the official documentation seems to be outdated because the constructor ContainerBuilder<> is also obsolete.

What I'm looking for is to create a container with a SQL SERVER image, then create a database/tables and use it with entity framework core (I don't know how to create them or even if this is necessary).

On the other hand, I tried something like this:

       private readonly IContainer _dbContainer = new ContainerBuilder()
               .WithImage("mcr.microsoft.com/mssql/server")
               .WithEnvironment("MSSQL_SA_PASSWORD", "MyStrongPassword")
               .WithEnvironment("ACCEPT_EULA", "Y")
               .WithEnvironment("MSSQL_DATA_DIR", "/var/opt/sqlserver/data")
               .WithEnvironment("MSSQL_LOG_DIR", "/var/opt/sqlserver/log")
               .WithEnvironment("MSSQL_BACKUP_DIR", "/var/opt/sqlserver/backup")
               .WithPortBinding(49401, 1433)
               .WithWaitStrategy(Wait.ForUnixContainer().UntilPortIsAvailable(1433).UntilCommandIsCompleted("/bin/bash", "/opt/mssql-tools/bin/sqlcmd  -S 127.0.0.1,1433 -U sa -P MyStrongPassword -d master -Q\"CREATE DATABASE Local_test"))
               //.WithBindMount(Path.GetFullPath("sql"), "/scripts/")
               //.WithCommand()
               .Build();

But this doesn't work (Error: Docker.DotNet.DockerApiException : Docker API responded with status code=Conflict, response={"message":"Container 63d8dbd9c73f924b54ecddea5febc82589ccea452a3543eebb2aa3aa8b6bc408 is not running"} ).

Copyread

We have recently changed the API around container modules, but the example should still work, since it is executed as part of the CI. You can find the new MSSQL module (which is published as its own NuGet) here: github.com/testcontainers/testcontainers-dotnet/tree/develop/… nuget.org/packages/Testcontainers.MsSql – Ser

Answer 1

But the MsSqlTestcontainer and MsSqlTestcontainerConfiguration classes don't exist.

I am certain that this question belongs to this GitHub discussion. As mentioned there, the example is no longer accurate (I will update it). The example is up-to-date again. I have shared a working example including a guide based on Microsoft's article Integration tests in ASP.NET Core. Both cover ASP.NET incl. EF.

What I'm looking for is to create a container with a SQL SERVER image, then create a database/tables and use it with entity framework core (I don't know how to create them or even if this is necessary).

Modules are independent NuGet dependencies. To run a Microsoft SQL Server container, add the NuGet dependency to your test project:

dotnet add package Testcontainers.MsSql --version 3.0.0

Modules are pre-configured and follow best practices. There is no need to override the default configuration. Creating and starting a container only requires these two lines:

var msSqlContainer = new MsSqlBuilder().Build();
await msSqlContainer.StartAsync();

Please be aware that the Microsoft SQL Server Docker image is not compatible with ARM devices, such as Macs with Apple Silicon. Instead, use the SqlEdge module. Azure SQL Edge based on ARM64 architecture is retired.

Sketchbook

I have an error in the sentence var msSqlContainer = new MsSqlBuilder().Build();: "System.ArgumentException: 'Cannot detect the Docker endpoint. Use either the environment variables or the ~/.testcontainers.properties file to customize your configuration: dotnet.testcontainers.org/custom_configuration (Parameter 'DockerEndpointAuthConfig')'" – Copyread

You need a running Docker-API compatible container runtime (System requirements) such as Docker. The error says that Testcontainers cannot auto detect your container runtime. If you are using something different than Docker, you might need to set the mentioned custom configurations according to your environment. – Sketchbook

You need Docker Desktop to be running before executing the tests. – Pyemia

Where and how do you create the db schema and seed data? I'm using Dapper instead of EF so I don't have migrations I can run. – Felike

At some point, you'll need a database migration tool such as FluentMigrator, DbUp, or you can run the SQL scripts in the Testcontainers' startup callback or after container initialization. Here's an example utilizing Flyway. – Sketchbook