Welcome

When using unit test frameworks like xUnit the lifetime of the data you set up, for the most part, exists only in memory for the duration of that one unit test. This means that each test is run in isolation and does not affect the other tests. Or, at least, that's how they should be written.

When dealing with databases in integration tests, however, this can be tricky as the database keeps its state between test runs. This means that data which is added or manipulated remains in that state for the next test run. This can make it harder to ensure a consistent scenario each time, especially when tests are run in parallel and you cannot guarantee execution order.

Rather than trying to work around this by making sure I reference unique IDs, which can increase difficulty writing tests as the suite grows, I took the approach of using one database per test. Surprisingly, it's still pretty quick.

In this example I'll be making use of Reqnroll, which took the place of SpecFlow. I will also use a MySQL server, running in Docker.

The gist of the approach is that there is a parent class, TransientDatabaseDriver, which manages the creation and use of each temporary database and then tears it down when the test is done.

To start with, we need our TransientDatabaseDriver class:

[Binding]
public class TransientDatabaseDriver : IDisposable, IAsyncDisposable
{
	protected virtual string TestDbName => "test_db";
	protected virtual string[] SchemaFiles => [];
	public readonly string DatabaseName;

	private readonly IReqnrollOutputHelper _outputHelper;
	private readonly MySqlConnection _databaseConnection;
	private static int _counter;

	protected TransientDatabaseDriver(IReqnrollOutputHelper outputHelper)
	{
		_outputHelper = outputHelper;
		var host = Environment.GetEnvironmentVariable("TEST_DB_HOST") ?? "localhost";
		DatabaseName = TestDbName + _counter++;
		_databaseConnection = new MySqlConnection($"Server={host};Port=3306;Uid=root;Pwd=root;");
		OpenConnection();
		ExecuteNonQuery($"CREATE DATABASE IF NOT EXISTS {DatabaseName}");
		ExecuteNonQuery($"USE {DatabaseName}");
		foreach (var file in SchemaFiles)
		{
			ExecuteSqlFile(file);
		}
	}

	public void ExecuteNonQuery(string databaseIfNotExistsOtbHotelsTest, Action<MySqlCommand>? setupCommand = null)
	{
		using var command = _databaseConnection.CreateCommand();
		command.CommandText = databaseIfNotExistsOtbHotelsTest;
		setupCommand?.Invoke(command);
		var rows = command.ExecuteNonQuery();
		_outputHelper.WriteLine($"Executed SQL: {command.CommandText} - Rows affected: {rows}");
	}

	private void OpenConnection()
	{
		if (_databaseConnection.State != System.Data.ConnectionState.Open)
		{
			_databaseConnection.Open();
		}
	}

	private void CloseConnection()
	{
		if (_databaseConnection.State != System.Data.ConnectionState.Closed)
		{
			_databaseConnection.Close();
		}
	}

	[AfterScenario]
	public void CleanDatabase() => ExecuteNonQuery("DROP DATABASE IF EXISTS " + DatabaseName);

	public void Dispose()
	{
		CloseConnection();
		_databaseConnection.Dispose();
	}

	public async ValueTask DisposeAsync()
	{
		CloseConnection();
		await _databaseConnection.DisposeAsync();
	}

	protected void ExecuteSqlFile(string file) => ExecuteNonQuery(File.ReadAllText(file));
}

The work kicks off in the constructor. We set the host to localhost should we not have the TEST_DB_HOST environment variable (which is useful for CI). We then create our DB name, which uses a counter to keep it unique per test for the test session. The connection is then set up, opened, the database created and switched to. Finally any specified schema files are loaded so we can do any table setup and any data seeding we need.

Then, when the test finishes, the CleanDatabase() method is called which drops the temporary database. This mostly benefits local runs as in a CI environment the databases will be temporary anyway.

The class needs to be adorned with the Binding attribute to tell Reqnroll to load it.

Next we need to make use of it. Although the TransientDatabaseDriver isn't marked with abstract it is designed to be inherited. Unfortunately, limitations of Reqnroll mean that you can't have a non-static method marked with [AfterScenario] in an abstract class.

public class DatabaseDriver(IReqnrollOutputHelper outputHelper) : TransientDatabaseDriver(outputHelper)
{
	protected override string TestDbName => "test_db_name_override";
	protected override string[] SchemaFiles => ["table_schema.sql"];
}

This is all you need, and the overrides are optional.

This step is followed by overwriting the database setting in our application under test:

internal class HttpDriver
{
	private readonly WebApplicationFactory<Program> _factory;

	public HttpDriver(DatabaseDriver databaseDriver)
	{
		_factory = new WebApplicationFactory<Program>()
			.WithWebHostBuilder(builder =>
			{
				builder
					.UseEnvironment("Integration")
					.UseSetting("MySqlConnection:Database", databaseDriver.DatabaseName); <--- Here
			});
	}
}

Obviously the MySqlConnection:Database string should be set to whatever your application uses to determine the DB. If you have the whole connection string configured then you'd need to reconstruct that using databaseDriver.DatabaseName.

And that's it. That's all that needs to be done to enjoy transient data within your integration tests.

One more thing I'd like to highlight is the ExecuteNonQuery method. This will allow you to insert data such as part of a step within your test scenario.

[Binding]
internal class UserSteps(DatabaseDriver databaseDriver)
{
	[Given("the user exists with the following details:")]
	public void GivenTheUserExistsWithTheFollowingDetails(DataTable dataTable) {
		var users = dataTable.CreateSet<User>();
		foreach (var user in users)
		{
			ExecuteNonQuery(
				"INSERT INTO users (id, name, password) VALUES (@Id, @Name, @Password)",
				command =>
				{
					command.Parameters.AddWithValue("@Id", user.Id);
					command.Parameters.AddWithValue("@Name", user.Name);
					command.Parameters.AddWithValue("@Password", user.Password);
				});
		}
	}
}