DbTest.Core 0.2.0

db-test

A .NET library for database integration testing that runs the same test body against SQL Server, PostgreSQL and MySQL.

Every run gets a throwaway database container. Nothing touches a shared, staging or production database.

using DbTest.Core.Engine;
using DbTest.MsSql;
using DbTest.MSTest;
using DbTest.MySql;
using DbTest.PostgreSql;

// The tests, written once. No engine name appears in them.
public abstract class CustomerTests : DbTestBase
{
    [TestMethod]
    public async Task Inserted_Customer_Should_Be_Counted()
    {
        await Harness.CleanAsync();
        await Harness.ExecuteAsync(
            "INSERT INTO customers (email, display_name) VALUES (@email, @name)",
            [new DbParameterValue("@email", "ada@example.test"), new DbParameterValue("@name", "Ada")]);

        var count = await Harness.QueryScalarAsync<int>(
            "SELECT COUNT(*) FROM customers WHERE email = @email",
            [new DbParameterValue("@email", "ada@example.test")]);

        Assert.AreEqual(1, count);
    }
}

// One class per engine: which engine, and where its schema lives.
[TestClass]
public sealed class PostgresCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new PostgreSqlEngine();
    protected override string SchemaRoot => "Schema/postgres";
}

[TestClass]
public sealed class MsSqlCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new MsSqlEngine();
    protected override string SchemaRoot => "Schema/mssql";
}

[TestClass]
public sealed class MySqlCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new MySqlEngine();
    protected override string SchemaRoot => "Schema/mysql";
}

// Once per test project: tests share a container, so they run one at a time, and the
// containers are removed as soon as the run ends.
[assembly: DoNotParallelize]

[TestClass]
public static class TestRun
{
    [AssemblyCleanup]
    public static Task Cleanup() => DbTestRun.DisposeAllAsync();
}

Why

A database test suite has to solve the same handful of problems, and most teams solve them by hand:

Install

dotnet add package DbTest.MSTest        # base class for MSTest (brings DbTest.Core)
dotnet add package DbTest.MsSql         # SQL Server engine
dotnet add package DbTest.PostgreSql    # PostgreSQL engine
dotnet add package DbTest.MySql         # MySQL engine

DbTest.Core alone gives the harness without any test framework. The CLI:

dotnet tool install --global DbTest.Cli

Requirements

• Docker must be running. The harness starts real containers through Testcontainers.
• .NET 8 or newer. The packages ship a net8.0 and a net10.0 asset: .NET 8 and 9 resolve the net8.0 one, .NET 10 and later the net10.0 one. The CLI rolls forward, so it runs on whichever of those runtimes is installed.

The schema

Point the harness at a directory of SQL files — through SchemaRoot in the base class, or directly:

await using var harness = await DbTestHarness.StartAsync(
    new PostgreSqlEngine(),
    schemaRoot: "Schema/postgres");

Copy the files next to the test binaries so a relative path works:

<None Include="Schema\**\*" CopyToOutputDirectory="PreserveNewest" />

Files are discovered recursively, ordered by their relative path (001_tables.sql before 002_procedures.sql — use leading zeros), and applied one after another. Each engine applies them its own way:

The splitters are real scanners: a GO or a delimiter inside a string literal, a quoted identifier or a comment is never a separator, and a column named delimiter_char is not a DELIMITER directive.

Excluded files are reported, never silently dropped:

var load = SchemaLoader.Load("Schema/postgres", new SchemaLoadOptions
{
    ExcludedDirectories = ["maintenance", "scripts"],
    ExcludedFileNamePatterns = ["^create_", "^fix_"],
    OrderedDirectories = ["tables", "procedures"],   // each with its subdirectories, first
});

foreach (var exclusion in load.Excluded)
{
    Console.WriteLine($"{exclusion.RelativePath} skipped: {exclusion.Reason}");
}

A schema that only partly applied fails loudly at setup rather than surfacing later as unrelated test failures: StartAsync throws a SchemaApplyException listing every broken file.

The shape guard

A shared container means a shared schema. A test that drops or re-types a table leaves the next test failing for the wrong reason — and the failure points at the victim, not the culprit.

The guard snapshots every table, view and routine after the schema is applied. DbTestBase re-reads them after every test and fails the test that changed one:

Assert.Fail failed. The test left a shared database object reshaped. Restore it before the
test ends (the shape guard compares against the schema as applied):
[ShapeGuard] CustomerTests.Adds_A_Column left dbo.customers columns changed (id,email) -> (id,email,probe)

The culprit is blamed once; the tests after it are not. An object that is put back is forgotten, so the next test to break it is blamed again. Objects a test creates are not watched — only the schema as applied.

It detects and blames. It does not repair: a silent repair hides the leak from the next run, and the next run is exactly when you want to see it. Override FailOnShapeLeak to only log the leak. Without the base class:

var leaks = await harness.CheckShapeAsync("CustomerTests.Adds_A_Column");

Cleanup

await harness.CleanAsync();

Empties every user table, whatever the foreign keys between them, and puts every identity column back to its seed, so the next row gets the same id on every engine. (MySQL keeps no seed apart from the counter: its tables restart at 1.)

Queries

var count = await harness.QueryScalarAsync<int>("SELECT COUNT(*) FROM orders");
var newest = await harness.QueryScalarAsync<DateTimeOffset?>("SELECT MAX(placed_at) FROM orders");

QueryScalarAsync<T> reads the first column of the first row; SQL NULL or no row gives default. It absorbs the drivers' differences: COUNT(*) is int on SQL Server and bigint elsewhere, MySQL returns a CHAR(36) where SQL Server returns a uniqueidentifier, and nullable targets work. For anything richer, open a connection:

await using var connection = await harness.OpenConnectionAsync();

Diagnostics

DBTEST_TIMING=1 dotnet test

With DbTest.MSTest, writes one row per test to dbtest-timing.tsv next to the test binaries (or to DBTEST_TIMING_FILE). Off by default; no file is written unless it is switched on.

The CLI

dbtest engines                                         # list the registered engines
dbtest up --engine postgres --schema Schema/postgres   # start, apply, keep running until Ctrl+C
dbtest schema --engine mysql --schema Schema/mysql     # apply a schema and list what it created

Layout

src/DbTest.Core          engine-agnostic: the DbEngine base class, schema loading, statement
                         splitting, the harness, the shape guard, diagnostics
src/DbTest.MSTest        DbTestBase and DbTestRun for MSTest
src/DbTest.MsSql         SQL Server engine
src/DbTest.PostgreSql    PostgreSQL engine
src/DbTest.MySql         MySQL engine
src/DbTest.Cli           the `dbtest` command
tests/DbTest.Core.Tests  unit tests, no Docker
tests/DbTest.Integration.Tests
                         one shared test body, run against all three engines

Writing an engine

Derive from DbEngine and implement seven members: the name, the default image, a connection factory, container startup, object listing, cleaning, and splitting one schema file into commands. Applying a schema, running statements, binding parameters and converting scalars are inherited, as are schema discovery, ordering, exclusion and the shape guard.

DbEngine is an abstract class rather than an interface so new capabilities can arrive as virtual members with a default, without breaking an engine written outside this repository.

Sponsors

db-test is maintained by dbaitest. Sponsorship pays for the Docker minutes the integration suite burns on every pull request, and for the ADO.NET edge cases — a GO batch inside a stored procedure, a TRUNCATE that a foreign key refuses — that nobody else wants to own.

Contributing

Every change goes through a pull request. See CONTRIBUTING.md for the workflow, the checks that run, and the merge policy. Security issues: see SECURITY.md. Changes per release: CHANGELOG.md.

License

MIT. See LICENSE.