Using Testcontainers.MsSql.MsSqlBuilder and create database for .NET Core/C# integration tests

Question

I'm trying to use Testcontainers to perform integration tests on my .NET Core 7 Web API which has a SQL Server data store. I can create the container, but I'm lost on where/when to create my database/schema.

I've read the MsSqlBuilder code and I don't see how I would configure the SQL Server container/database after launching.

I have the container creating/destroying successfully, but I don't know how I would use this class to configure the database name and run schema scripts or seeding scripts. The following creates container correctly to my knowledge:

public class DataLockerApiFactory: WebApplicationFactory<IApiMarker>, IAsyncLifetime
{
    private readonly MsSqlContainer msSqlContainer
        = new MsSqlBuilder().Build();

    public Task InitializeAsync() 
    {
        return msSqlContainer.StartAsync();
    }

    public new Task DisposeAsync()
    {
        return msSqlContainer.DisposeAsync().AsTask();
    }
}

I'd assume I'd run scripts in InitializeAsync. Other samples I've found on web seem to use lower level Testcontainers classes to create and configure containers and maybe that is the requirement?

Or maybe I just make an IDbConnection in InitializeAsync and run commands there to build database? If this is desired pattern, when I use a T-SQL command like CREATE DATABASE [DBNAME] and provide a FILENAME, are the files inside the container or on my local file system? And maybe I shouldn't even be creating a database with my 'normal name' and instead just use the master database and add tables, views, stored procedures, etc. to that?

Thanks in advance.

Answer 1

As mentioned in one of the other comments, you will need some form of database migration tool (Flyway, FluentMigrator, DbUp, EF) or to execute the SQL scripts after container initialization. Additionally, depending on how often your schema changes, you can create it while building the image as well. As you can see, there are many different possibilities. I have already shared the Flyway example, which I personally favor (a database migration tool, not specifically Flyway). Here is another example that creates the database after container initialization.

The example below utilizes two database connections. The master database connection is used to create the custom database. You can use this connection to further customize and configure the database instance. The actual test though relies on the custom database connections (Testcontainers version 3.6.0).

public sealed class SO : IAsyncLifetime
{
    private const string Database = "FooBarBaz";

    private readonly MsSqlContainer _msSqlContainer = new MsSqlBuilder().Build();

    private readonly DbConnectionFactory _dbConnectionFactory;

    public SO()
    {
        _dbConnectionFactory = new DbConnectionFactory(_msSqlContainer, Database);
    }

    public async Task InitializeAsync()
    {
        await _msSqlContainer.StartAsync();

        using var connection = _dbConnectionFactory.MasterDbConnection;

        // TODO: Add your database migration here.
        using var command = connection.CreateCommand();
        command.CommandText = "CREATE DATABASE " + Database;

        await connection.OpenAsync()
            .ConfigureAwait(false);

        await command.ExecuteNonQueryAsync()
            .ConfigureAwait(false);
    }

    public Task DisposeAsync()
    {
        return _msSqlContainer.DisposeAsync().AsTask();
    }

    [Fact]
    public void Question77511865()
    {
        // Given
        using var connection = _dbConnectionFactory.CustomDbConnection;

        // When
        connection.Open();

        // Then 
        Assert.Equal(ConnectionState.Open, connection.State);
    }

    private sealed class DbConnectionFactory
    {
        private readonly IDatabaseContainer _databaseContainer;

        private readonly string _database;

        public DbConnectionFactory(IDatabaseContainer databaseContainer, string database)
        {
            _databaseContainer = databaseContainer;
            _database = database;
        }

        public DbConnection MasterDbConnection
        {
            get
            {
                return new SqlConnection(_databaseContainer.GetConnectionString());
            }
        }

        public DbConnection CustomDbConnection
        {
            get
            {
                var connectionString = new SqlConnectionStringBuilder(_databaseContainer.GetConnectionString());
                connectionString.InitialCatalog = _database;
                return new SqlConnection(connectionString.ToString());
            }
        }
    }
}

Unfortunately, the Microsoft SQL Server image does not support executing database scripts during startup out of the box (unlike the PostgreSQL image). This would make initializing and seeding the database a lot easier; you could simply upload your scripts before the container starts, and you are done, as shown in this example. However, I have proposed to enhance the Testcontainers' Microsoft SQL Server module to support this feature. Personally, I have not had the time yet to implement it.

Answer 2

Assuming you are using EfCore, you need to set up Migrations, then whenever you create your test containers, it will use the Migrations you defined.

How to set up your TestContainers:

public abstract class BaseIntegrationTest : IClassFixture<IntegrationTestWebAppFactory>
    {
        private readonly IServiceScope _scope;
        protected readonly ApplicationDbContext DbContext;
    
        public BaseIntegrationTest(IntegrationTestWebAppFactory factory)
        {
            _scope = factory.Services.CreateScope();
    
            Sender = _scope.ServiceProvider.GetRequiredService<ISender>();
            DbContext = _scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    
            DbContext.Database.Migrate();
        }
    }


public class IntegrationTestWebAppFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly MsSqlContainer _msSqlContainer = new MsSqlBuilder().Build();

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureTestServices(services =>
        {
            services.RemoveAll(typeof(DbContextOptions<ApplicationDbContext>));

            services.AddDbContextFactory<ApplicationDbContext>((IServiceProvider sp, DbContextOptionsBuilder opts) =>
            {
                opts.UseSqlServer(_msSqlContainer.GetConnectionString(),
                    (options) =>
                    {
                        options.EnableRetryOnFailure();
                    });
            });
        });
    }

    public async Task InitializeAsync()
    {
        await _msSqlContainer.StartAsync();
    }

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

In Visual Studio open Package Manager Console "from View menu" Ensure that the Package Manager console default project is set to project where your Data models for EfCore are defined(ApplicationDbContext) and make project that has your database settings the startup.

run the following commands:

PM> Update-database

Build started...

Build succeeded.

Done.

PM> Add-Migration InitialCreate

Build started...

Build succeeded.

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy. To undo this action, use Remove-Migration.

PM> Update-database

Build started...

Build succeeded.

Done.

PM>

When you update your migrations, your test containers will also be updated.