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.