Integration tests with real dependencies

The unit tests of a monthly expense report passed against a fake store, and three tests of the real store failed on the same code with SQLite Error 1: 'no such column: amount_paise': its query named a column that the table does not have. A fake never runs SQL, so only a test against a real database could see the mistake.

You will test a repository against a real SQLite file, give every test a database and a folder of its own, start a real HTTP server inside the test process, and run PostgreSQL tests with Testcontainers where Docker is installed, keeping such tests isolated, safe to run in parallel, and out of the way of the quick unit tests.

A real database catches what a fake hides

MonthlyReport adds up a month’s expenses by category. Its unit tests give it an InMemoryExpenseStore, a fake that filters a list; the integration tests give SqliteExpenseStore, the store the app uses, a real database file. This session swaps in a store whose query says amount_paise where the table’s column is paise, and runs both kinds:

from session import run

# network: true. dotnet test restores Microsoft.Data.Sqlite 10.0.12, xunit.v3 4.0.1, Testcontainers.PostgreSql 4.15.0
# and Npgsql 10.0.3 from nuget.org into the session's own, empty home folder; ASP.NET Core comes with the SDK.
# variants/typo/SqliteExpenseStore.cs, whose query names a column the table does not have, replaces the real store,
# and the report's unit tests and the store's integration tests run. The run is meant to fail; --parallel none keeps
# the order of its reports the same in every run.
run("""
cd expenses
cp ../variants/typo/SqliteExpenseStore.cs src/Expenses/SqliteExpenseStore.cs
!dotnet test --project tests/ExpensesTests --filter-class Expenses.Tests.MonthlyReportTests --filter-class Expenses.Tests.SqliteExpenseStoreTests --parallel none
""", network=True)

$ cd expenses
$ cp ../variants/typo/SqliteExpenseStore.cs src/Expenses/SqliteExpenseStore.cs
$ dotnet test --project tests/ExpensesTests --filter-class Expenses.Tests.MonthlyReportTests --filter-class Expenses.Tests.SqliteExpenseStoreTests --parallel none
Running tests from expenses/tests/ExpensesTests/bin/Debug/net10.0/Expenses.Tests.dll (net10.0|arm64)
failed Expenses.Tests.SqliteExpenseStoreTests.Migrating_twice_keeps_the_data (…)
  from expenses/tests/ExpensesTests/bin/Debug/net10.0/Expenses.Tests.dll (net10.0|arm64)
  Xunit.MicrosoftTestingPlatform.XunitException: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such column: amount_paise'.
    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
    at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
    at Expenses.SqliteExpenseStore.InMonth(Int32 year, Int32 month) in expenses/src/Expenses/SqliteExpenseStore.cs:49
    at Expenses.Tests.SqliteExpenseStoreTests.Migrating_twice_keeps_the_data() in expenses/tests/ExpensesTests/SqliteExpenseStoreTests.cs:34
    at System.Reflection.MethodBaseInvoker.InterpretedInvoke_Method(Object obj, IntPtr* args)
    at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)
failed Expenses.Tests.SqliteExpenseStoreTests.Reads_back_the_month_it_stored (…)
  from expenses/tests/ExpensesTests/bin/Debug/net10.0/Expenses.Tests.dll (net10.0|arm64)
  Xunit.MicrosoftTestingPlatform.XunitException: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such column: amount_paise'.
    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
    at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
    at Expenses.SqliteExpenseStore.InMonth(Int32 year, Int32 month) in expenses/src/Expenses/SqliteExpenseStore.cs:49
    at Expenses.Tests.SqliteExpenseStoreTests.Reads_back_the_month_it_stored() in expenses/tests/ExpensesTests/SqliteExpenseStoreTests.cs:24
    at System.Reflection.MethodBaseInvoker.InterpretedInvoke_Method(Object obj, IntPtr* args)
    at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)
failed Expenses.Tests.SqliteExpenseStoreTests.The_report_adds_up_what_the_database_returns (…)
  from expenses/tests/ExpensesTests/bin/Debug/net10.0/Expenses.Tests.dll (net10.0|arm64)
  Xunit.MicrosoftTestingPlatform.XunitException: Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such column: amount_paise'.
    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
    at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
    at Expenses.SqliteExpenseStore.InMonth(Int32 year, Int32 month) in expenses/src/Expenses/SqliteExpenseStore.cs:49
    at Expenses.MonthlyReport.ByCategory(IExpenseStore store, Int32 year, Int32 month) in expenses/src/Expenses/MonthlyReport.cs:7
    at Expenses.Tests.SqliteExpenseStoreTests.The_report_adds_up_what_the_database_returns() in expenses/tests/ExpensesTests/SqliteExpenseStoreTests.cs:51

The two unit tests passed, because the fake never sees the query. Three of the four tests of the real store failed with SQLite’s own error, and the fourth, which only inserts, passed: the bug is in reading. That is what an integration testintegration test: A test that runs code together with a real dependency, such as a database file, a web server or another service, to catch the mistakes between them that a stand-in cannot show, such as SQL the database refuses. is for. It runs code together with the real thing it depends on, so it catches what a stand-in cannot: SQL that the database refuses, a schema that differs from the code, a constraint such as CHECK (paise > 0), the conversion of a date to text and back, and the wiring of configuration.

Microsoft’s guide to unit tests asks you to keep such infrastructure out of unit tests, because it makes them slow and brittle, and suggests keeping integration tests in a project of their own. This lesson keeps both in one project, so that its examples stay small, and marks each test with a Category trait, Unit or Integration, so a command can run either kind alone. SQLite runs inside the test process and needs nothing installed; a service that runs on PostgreSQL also needs its SQL tested against PostgreSQL itself, which Testcontainers can start in Docker for a test run.

Each test gets its own database and folder

A real dependency has state, and state shared between tests makes results depend on order. The store’s tests avoid that with TempDatabase, which gives each test a new folder under the temporary folder, with a database file in it, and deletes both when the test ends:

using Microsoft.Data.Sqlite;

namespace Expenses.Tests;

/// <summary>A SQLite database file of its own for one test, in a folder of its own, deleted when the test ends.</summary>
sealed class TempDatabase : IDisposable
{
    private readonly string _folder = Directory.CreateTempSubdirectory("expenses-").FullName;

    public string ConnectionString => $"Data Source={Path.Combine(_folder, "expenses.db")}";

    public void Dispose()
    {
        SqliteConnection.ClearAllPools();   // the pool keeps the file open otherwise
        Directory.Delete(_folder, recursive: true);
    }
}

namespace Expenses.Tests;

// Integration tests: the real SQL, against a real SQLite database file for each test.
[Trait("Category", "Integration")]
public sealed class SqliteExpenseStoreTests : IDisposable
{
    private readonly TempDatabase _database = new();
    private readonly SqliteExpenseStore _store;

    public SqliteExpenseStoreTests()
    {
        _store = new SqliteExpenseStore(_database.ConnectionString);
        _store.Migrate();
    }

    public void Dispose() => _database.Dispose();

    [Fact]
    public void Reads_back_the_month_it_stored()
    {
        _store.Add(new Expense("E1", new DateOnly(2026, 3, 2), "Food", 45_000));
        _store.Add(new Expense("E2", new DateOnly(2026, 4, 1), "Food", 9_999));

        Assert.Equal([new Expense("E1", new DateOnly(2026, 3, 2), "Food", 45_000)], _store.InMonth(2026, 3));
    }

    [Fact]
    public void Migrating_twice_keeps_the_data()
    {
        _store.Add(new Expense("E1", new DateOnly(2026, 3, 2), "Food", 45_000));

        _store.Migrate();

        Assert.Single(_store.InMonth(2026, 3));
    }

    [Fact]
    public void Refuses_an_amount_of_zero()
    {
        var error = Assert.Throws<Microsoft.Data.Sqlite.SqliteException>(
            () => _store.Add(new Expense("E1", new DateOnly(2026, 3, 2), "Food", 0)));
        Assert.Equal(19, error.SqliteErrorCode);   // SQLITE_CONSTRAINT
    }

    [Fact]
    public void The_report_adds_up_what_the_database_returns()
    {
        _store.Add(new Expense("E1", new DateOnly(2026, 3, 2), "Food", 45_000));
        _store.Add(new Expense("E2", new DateOnly(2026, 3, 9), "Travel", 1_20_000));

        Assert.Equal([("Travel", 1_20_000L), ("Food", 45_000L)], MonthlyReport.ByCategory(_store, 2026, 3));
    }
}

SqliteConnection.ClearAllPools() comes first because Microsoft.Data.Sqlite pools connections, and a pooled connection keeps the file open after the test has closed its own. An in-memory database is the other way to give each test its own: Microsoft’s documentation explains that Data Source=:memory: gives every connection a private database, which disappears when that connection closes. The sync client’s tests use one more real dependency, a web server started inside the test process, and share it between the tests of their class. Follow the life of the databases and of that server:

xUnit offers three ways to share something expensive instead:

• A class fixtureclass fixture: In xUnit, an object created once before the first test of a test class, handed to the class's constructor in every test and disposed after the last one, for setup too expensive to repeat for each test. is created once for the tests of one class and handed to its constructor.
• A collection fixture is shared by every class in one named test collection.
• An assembly fixture, new in xUnit v3, is shared by every test class of the test project.

Sharing has a price. xUnit runs test classes in parallel by default, so anything two classes share must be safe to use at the same time, and anything a fixture keeps between tests can leak from one test into the next.

Shared state, reproduced: two tests that pass alone and fail together Pro: opens with a Pro, Premium or Ultimate pass

A real HTTP server in the test process, on a free port Pro: opens with a Pro, Premium or Ultimate pass

PostgreSQL in Docker with Testcontainers, and keeping the suite fast Pro: opens with a Pro, Premium or Ultimate pass

Practise

These questions cover the first two sections: what a real database catches, and how each test gets a database of its own.

Interview questions

Key takeaways

• An integration test runs code with the real dependency, so it catches what a fake cannot: SQL, schemas, constraints and conversions.
• Give each integration test its own database file and folder, and clear the connection pool before deleting it.
• Share expensive objects through class, collection or assembly fixtures, never the state that tests change.