dbaitestdb-testv0.3.0

Database integration testing for .NET

Write the database test once.
Run it on every engine.

A .NET library 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.

Get startedView on GitHub
  • Docker running
  • .NET 8 or newer
  • MIT licensed

why

Every database test suite hits the same six problems. Most teams solve them by hand.

  1. 01 · Where does the test database come from?

    A throwaway container per engine

    Started on first use and shared by the run. The containers are removed as soon as the run ends.

  2. 02 · How does the schema get there?

    Schema from files on disk

    Applied in order, with each engine's own splitting rules. A schema that only partly applies fails loudly at setup.

  3. 03 · A test dropped a column and the next test failed on it

    A shape guard

    Fails the test that dropped or re-typed a shared database object, naming the object and the change, instead of blaming the next test.

  4. 04 · One test's rows leaked into the next

    Cleanup between tests

    CleanAsync() empties every table whatever the foreign keys, and resets identity counters.

  5. 05 · "The suite is slow"

    Per-test timings

    Set DBTEST_TIMING=1 to write per-test timings to a TSV file. Off by default.

  6. 06 · The same assertions written three times

    One set of assertions

    The engine sits behind one abstract class, so the same assertions are not written three times.

one test body, three engines

Tests written once. One small class per engine.

The tests are written once, in an abstract class. One small class per engine says which engine to use and where its schema lives.

CustomerTests.cs
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);
    }
}
PostgresCustomerTests.cs
[TestClass]
public sealed class PostgresCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new PostgreSqlEngine();
    protected override string SchemaRoot => "Schema/postgres";
}
MsSqlCustomerTests.cs
[TestClass]
public sealed class MsSqlCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new MsSqlEngine();
    protected override string SchemaRoot => "Schema/mssql";
}
MySqlCustomerTests.cs
[TestClass]
public sealed class MySqlCustomerTests : CustomerTests
{
    protected override DbEngine CreateEngine() => new MySqlEngine();
    protected override string SchemaRoot => "Schema/mysql";
}

Two overrides: that is the whole cost of another engine.

install

The MSTest base class, plus an engine package for each database you test against.

bash
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
dotnet add package DbTest.MsSql.Dacpac  # deploy a .dacpac (SQL Server database projects)

DbTest.Core alone gives the harness without any test framework.

TestRun.cs · once per test project
// 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();
}

how it works

Four steps to a green run.

  1. Install

    Add DbTest.MSTest and an engine package for each database. Docker must be running.

  2. Point it at a schema

    Keep your SQL files in a directory per engine and set SchemaRoot. Files are applied in the order of their paths.

  3. Write the tests once

    Derive from DbTestBase in an abstract class, then add one [TestClass] per engine.

  4. Run dotnet test

    Containers start on first use, the shape guard checks every test, and the containers are removed when the run ends.

the shape guard

Blame the test that broke it. Not the one after.

When a test drops or re-types a shared database object, the shape guard fails that test and names the object and the change. The tests after it are not blamed.

How the shape guard works
✗ FAILED
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)

SQL Server database projects

DbTest.MsSql.Dacpac

A database project (SSDT or Microsoft.Build.Sql) has objects that depend on each other across folders, so no file order applies them. DbTest.MsSql.Dacpac deploys the built .dacpac with DacFx, in dependency order.

See the sample project

Stop mocking the database. Start testing it.

dotnet add package DbTest.MSTest
Read the docsView on GitHub