Veli Keçeci
Back to all posts
·5 min read

Integration tests for .NET with a real database: fast, isolated, reliable

Your unit tests are green, yet the first request in the integration environment fails. Most teams go through this at least once. The cause is usually that the tests imitate an important part of the real system: the database. An in-memory fake repository knows nothing about the constraints, translation behaviour and concurrency rules of a real SQL engine.

In this post I describe how to keep integration tests for ASP.NET Core APIs fast, isolated and reliable while running them against a real database. The examples use C# and EF Core, but the ideas carry over to other stacks.

Why in-memory fakes are not enough

EF Core's in-memory provider is convenient, but it does not behave like a relational database. It does not test foreign key constraints, unique index violations, transaction isolation levels, or the translation of LINQ expressions into real SQL in the same way. A query that works in memory can fail at runtime in production because it cannot be translated.

Testing with in-memory SQLite is one step closer, but it is still a different engine. If production runs PostgreSQL or SQL Server, some of the differences stay hidden. The rule is simple: a test is worth as much as its confidence is close to reality. If you are testing data access, use the same engine.

A real database in a container

To keep tests independent of a manually installed server, it is common to start the database as a container inside the test process. Libraries such as Testcontainers manage this from code: they start the container, hand you the connection string and remove it when finished.

public sealed class DatabaseFixture : IAsyncLifetime
{
    private readonly PostgreSqlContainer _container =
        new PostgreSqlBuilder().Build();

    public string ConnectionString => _container.GetConnectionString();

    public async Task InitializeAsync()
    {
        await _container.StartAsync();
        await using var db = CreateContext();
        await db.Database.MigrateAsync();
    }

    public Task DisposeAsync() => _container.DisposeAsync().AsTask();

    public AppDbContext CreateContext() =>
        new(new DbContextOptionsBuilder<AppDbContext>()
            .UseNpgsql(ConnectionString).Options);
}

An important detail: build the schema with real migrations, not EnsureCreated. That way the migrations themselves are verified on every test run. Starting a container is expensive, so start it once per test collection, not once per test.

End-to-end requests with WebApplicationFactory

To test the API at the HTTP level, use WebApplicationFactory<TEntryPoint>. The application starts in-process and the test sends requests through an HttpClient. The only thing you need to do is point the connection string at the test database.

public sealed class ApiFactory(string connectionString)
    : WebApplicationFactory<Program>
{
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.ConfigureServices(services =>
        {
            services.RemoveAll<DbContextOptions<AppDbContext>>();
            services.AddDbContext<AppDbContext>(o =>
                o.UseNpgsql(connectionString));
        });
    }
}

With this approach routing, model binding, validation, authorization and data access all run as they do in production. Replace only the dependencies beyond your boundary, such as email delivery or a third-party HTTP service, with test doubles. Do not replace your own database.

Test isolation: tests must not affect each other

If tests on a shared database can see each other's data, results become order-dependent and debugging gets hard. There are three common strategies:

  • Open a transaction per test and roll it back. Fast, but it can be fragile if the application manages its own transactions or uses several connections.
  • Clean the tables after each test. Simple and easy to understand. You need to watch the foreign key order.
  • A separate schema or database per test. The strongest isolation, but with a higher setup cost.

In practice I recommend the second approach. A small helper that clears the tables runs before each test. There are also ready-made libraries that derive the deletion order from the schema; consider them instead of writing it from scratch.

public async Task ResetAsync()
{
    await using var db = CreateContext();
    await db.Database.ExecuteSqlRawAsync(
        "TRUNCATE TABLE orders, customers RESTART IDENTITY CASCADE");
}

Keeping the test readable

A common problem with integration tests is that the setup code overshadows the actual purpose. Write small helpers that produce test data and reduce the body of the test to three steps: arrange the state, send the request, verify the result.

[Fact]
public async Task Order_is_created_and_listed()
{
    await _db.ResetAsync();
    var customerId = await _db.SeedCustomerAsync("Alice");

    var response = await _client.PostAsJsonAsync("/orders",
        new { customerId, items = new[] { new { sku = "A1", qty = 2 } } });

    response.StatusCode.Should().Be(HttpStatusCode.Created);

    var list = await _client.GetFromJsonAsync<List<OrderDto>>("/orders");
    list.Should().ContainSingle(o => o.CustomerId == customerId);
}

Do not end the verification with the status code alone. Confirm that the data was really written and read back, with a second request or a direct database query. Otherwise the endpoint returns 201, the record is missing, and the test still passes.

Staying fast: managing the cost in CI

The main objection to real-database tests is that they are slow. The objection is partly right, but it can be managed:

  • Start the container once per collection through a shared fixture, not once per class.
  • Apply migrations once; between tests, reset only the data.
  • Instead of running tests that share a container in parallel, split them into collections or give each collection its own database.
  • Layer your tests: fast unit tests for business rules, a smaller number of integration tests for queries and end-to-end flows.

On the CI side you need an environment that can run containers. Pulling or caching the image beforehand can noticeably shorten the first run. Measure the effect in your own pipeline; the gain depends on the environment.

Common mistakes

The first is treating integration tests as a tool that covers everything. Writing an HTTP-level test for every business rule slows the suite down. Keep integration tests for the critical flows that show the components working together correctly.

The second is not controlling time and randomness in tests. If DateTime.UtcNow is used directly, tests break at particular times of day. Abstract the clock and fix it in the test.

The third is leaving the output of a failing test too thin. On failure, add the response body and the relevant data to the message. Having to reproduce a CI failure locally lowers the value of the suite.

Closing

Integration tests written against a real database catch, during testing, the bugs that fakes hide: missing indexes, broken migrations, queries that cannot be translated. Only three habits are needed: use the same engine as production, start every test from a clean state, and keep the cost low with shared fixtures.

Start small. Write a single WebApplicationFactory test for your most critical flow, get it working and grow the suite from there. Confidence grows not with the number of tests, but with what the tests actually exercise.