[Enhancement]: Single database per test.

[PureKrome]

Problem

First linked/referenced in #1165
Shoutout to @0xced

Context: MS-SQL containers + xUnit / testing

One of the frustrating things with running DB tests and test containers is that all the DB tests are synchronous. This is via the Collection which we run all the tests under.

This raises some issues

• slowness: would be nicer to have a multiple DB tests running at the same time
• data isolation: each test should have it's own data so it doesn't conflict/fight with other tests.
• data changed: test 1 might do something to the db data, while test 2 then expects the data to be in a fresh state, but it's modified.

a quick fix to all of this is a single container per test method but that is resource expensive 😢
Resetting the Db back after each test still means we're stuck with synchronous tests.

Solution

Would be really lovely would be to have a mix of both!

• 🐳 single container
• 💾 single db per test method

(I believe this is what 🐦‍⬛ RavenDb does?)

In the context of MS-SQL this could be achieved via the connection string Initial Catalog key/value.

So maybe this means

• we don't use the [Collection()] attribute (which forces all the tests to run in synchronous mode)
• when the test run first starts (is this called the test app?), we create the single container
• when each test method runs, then we can set the initial catalogue to be unique.

For example - here's two classes to try and set this up. I'm not sure how close this is to #1165 PR code:

// Simple fixture which is ran once at start when the test run first runs..

public class SqlServerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer _msSqlContainer;

    public SqlServerFixture() =>
        _msSqlContainer = new MsSqlBuilder()
            .WithImage("mcr.microsoft.com/mssql/server:2022-CU13-ubuntu-22.04")
            //.WithReuse(true)
            .Build();

    public string ConnectionString => _msSqlContainer.GetConnectionString();

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

    public async Task DisposeAsync()
    {
        if (_msSqlContainer != null)
        {
            await _msSqlContainer.StopAsync();
        }
    }
}

// Sample 'DB' base class for tests

public abstract class BaseSqlServerTest(
    SqlServerFixture _sqlServerFixture,
    ITestOutputHelper _testOutputHelper) : IClassFixture<SqlServerFixture>, IAsyncLifetime
{
    protected string ConnectionString { get; private set; }

    public async Task InitializeAsync()
    {
        // Generate a unique database name using the test class name and the test name
        const int maxDatabaseNameLength = 100;// MSSql has a problem with long names.
        var guid = Guid.NewGuid().ToString().Replace("-", "");
        var testName = _testOutputHelper.TestDisplayName();
        var uniqueDbName = $"{testName}_{guid}";
        if (uniqueDbName.Length > maxDatabaseNameLength)
        {
            var truncatedText = uniqueDbName.Substring(0, maxDatabaseNameLength - Guid.NewGuid().ToString().Length);
            uniqueDbName = $"{truncatedText}_{guid}";
        }

        // Update the connection string to use the unique database name.
        // ⭐⭐ This is the magic 🪄🪄🪄 
        ConnectionString = new SqlConnectionStringBuilder(_sqlServerFixture.ConnectionString)
        {
            InitialCatalog = uniqueDbName
        }.ToString();

        _testOutputHelper.WriteLine($"** SQL Server is running. Connection String: {ConnectionString}");
    }

    public Task DisposeAsync() => Task.CompletedTask;
}

So now we can just inherit this into our own xUnit test class:

public class DoSomethingAsyncTests(SqlServerFixture _sqlServerFixture, ITestOutputHelper _testOutputHelper)
    : BaseSqlServerTest(_sqlServerFixture, _testOutputHelper)
{
}

So what I left out of this solution is creating your own DbConnection or EF DbContext. Do that .. then you can Seed your data and you're good to go. You can even add that functionality to the above BaseSqlServerTest class if you want that to happen for each test ran or just set some protected properties which can be accessible in all your concrete classes which inherit from this.

Benefit

• Single Container: Container creation is expensive
• Faster DB tests: a single db and single isolated data per test. tests can run parallel now! (if xUnit allows that)

Potential downside: each test will seed the data which could be more records than needed. That's up to the developer.
Potential downside: each tests has to create all the tables and other schema objects.

I'm assuming that the sum of the positive (minus the negs) will still be faster that the current 'Collection' solution.

Alternatives

Would you like to help contributing this enhancement?

Yes

[summerson1985]

Hi,

In a scenario when you have hundreds of tests and each test needs to run db migration consisting of tens (if not hundreds) of migration files the tests become slow anyway. You also do want to test a scenario as close to real life as possible meaning multiple records created, modified, deleted, etc. in parallel (hello deadlocks). 1 db per test does not appear to be such an improvement then.

We achieve great test isolation using unique user ids - guid - then each user creates its own entity, let's say shopping cart, puts items in the cart, updates, etc. The cart is then selected for assertion by a userId. Hundreds of tests are getting executed in parallel thanks to Xunit.Extensions.Ordering

[PureKrome]

@summerson1985 Great points but also different scenario's. Yep, it's true that it can be a very important test to see how systems work with load. I can't stress hard enough how much mental anquish i've suffered over the years when seeing 'deadlock' errors/situations 😭 💀

But those are a specific test condition which would generally be targeting applications with lots of multicurrent requests. I would have thought that a common starting/entry level test scenario would be to just making sure all the DB queries work. For 1 person. With -some- existing data.

I was hoping this would be considered with their .NET library, especially considering they are doing a heap of awesome work on #1165