I am trying to create a project for my integration tests, that spin up a testcontainer using docker. Im also trying to then create my sql server. It creates the sql server fine, but its not creating the db or schema.

I can get it to create the db using a connection which seems like a hack and im not sure is the right way about it, but then trying to migrate still doesnt work.

public class CustomApiFactory : WebApplicationFactory<IApiMarker>, IAsyncLifetime
{
    private const string Database = "NewDb";
    private readonly MsSqlContainer _dbContainer;

    public CustomApiFactory()
    {
        _dbContainer = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04")
            .WithPassword("Password123")
            .WithPortBinding(1433)
            .WithCleanUp(true)
            .Build();
    }

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

        var connectionString = _dbContainer.GetConnectionString();
        await CreateDatabaseIfNotExists(connectionString);
        await ApplyMigrations(connectionString);
    }

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

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Test");
        builder.ConfigureServices(services =>
        {
            services.AddTestAuthentication();
        });

        builder.ConfigureLogging(logging =>
        {
            logging.ClearProviders();
        });

        builder.ConfigureTestServices(services =>
        {
            services.RemoveAll(typeof(DbContextOptions<ApplicationContext>));
            services.AddDbContext<ApplicationContext>(opts =>
            {
                opts.UseSqlServer($"{_dbContainer.GetConnectionString()};Database={Database}");
            });
        });
    }

    private async Task CreateDatabaseIfNotExists(string connectionString)
    {
        using (var conn = new SqlConnection(connectionString))
        {
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = $"IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{Database}') " +
                              $"CREATE DATABASE [{Database}]";
            await cmd.ExecuteNonQueryAsync();
        }
    }

    private async Task WaitForSqlServerToBeReady(string connectionString)
    {
        var retryCount = 10;
        for (int i = 0; i < retryCount; i++)
        {
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    await conn.OpenAsync();
                    Console.WriteLine("SQL Server is ready.");
                    return;
                }
            }
            catch (SqlException)
            {
                Console.WriteLine("SQL Server is not ready. Retrying in 5 seconds...");
                await Task.Delay(5000);
            }
        }

        throw new Exception("SQL Server did not become ready in time.");
    }

    private async Task ApplyMigrations(string connectionString)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationContext>();
        optionsBuilder.UseSqlServer(connectionString);

        using (var context = new ApplicationContext(optionsBuilder.Options))
        {
            try
            {
                await context.Database.MigrateAsync();
                Console.WriteLine("Migrations applied successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error applying migrations: {ex.Message}");
                throw;
            }
        }
    }
}