skip to content

Real-Database Tests

Testcontainers runs a real PostgreSQL or SQL Server for the suite and Respawn deletes rows between tests, replacing in-memory fakes that hide SQL behaviour. Interviewers ask why fakes mislead.

part ofASP.NET Coreread it →
on this pageshow

explore

questions

5

How do you run ASP.NET Core integration tests for an order-management API against a real PostgreSQL started with Testcontainers?

level: middleimportance: must knowfreq 55%

answer

  1. one container for the whole run
  2. module builder with a pinned image
  3. StartAsync before the host is built
  4. GetConnectionString() carries the random port
  5. UseSetting on the factory's web host

basics

~10 s

Start a Testcontainers PostgreSqlBuilder container with a pinned image once per test run, pass its GetConnectionString() to WebApplicationFactory<Program> as the app's connection string, apply migrations once, and reset the data between tests.

solid answer

~40 s

I put a `PostgreSqlContainer` built with `new PostgreSqlBuilder("postgres:17-alpine")` inside a shared fixture, usually the `WebApplicationFactory<Program>` subclass itself. In xunit.v3 its `InitializeAsync` awaits `StartAsync()`, then touches `Services` so the host is built, opens a scope and runs `Database.MigrateAsync()` once. The factory's `ConfigureWebHost` override calls `builder.UseSetting("ConnectionStrings:Orders", _db.GetConnectionString())`, so the app's own `AddDbContext` registration connects to the container and Program.cs does not change. The string has to come from the container because the module maps port 5432 to a random free host port. One container serves a class, collection or assembly fixture rather than one per test, and the rows are cleared between tests with Respawn or a rolled-back transaction. `DisposeAsync` disposes the factory first and the container second.

code

csharp · 24 lines
csharp
public sealed class OrdersApiFactory : WebApplicationFactory<Program>, IAsyncLifetime
{
    private readonly PostgreSqlContainer _db =
        new PostgreSqlBuilder("postgres:17-alpine").Build();

    public string ConnectionString => _db.GetConnectionString();

    public async ValueTask InitializeAsync()
    {
        await _db.StartAsync();
        using var scope = Services.CreateScope();   // builds the host
        var orders = scope.ServiceProvider.GetRequiredService<OrdersDbContext>();
        await orders.Database.MigrateAsync();
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder) =>
        builder.UseSetting("ConnectionStrings:Orders", _db.GetConnectionString());

    public override async ValueTask DisposeAsync()
    {
        await base.DisposeAsync();
        await _db.DisposeAsync();
    }
}

go deeper

for a junior

Recall the four steps: build the container with a pinned image, start it, read its connection string, and give that string to WebApplicationFactory instead of the one in appsettings.json.

for a middle

Explain the ordering: the container must be running before the host is built, because ConfigureWebHost reads its mapped port, and migrations run once in the fixture rather than in every test.

for a senior

Show the cost model: one container shared by a fixture, data reset between tests, real migrations as part of the check, and a teardown order that disposes the factory before the container.

for a principal

Weigh where container tests sit in the suite: they are slower than in-process fakes, so decide which behaviours need the real engine and keep the rest in faster unit tests.

## What the setup is for An order-management API stores orders, order lines and customers through an EF Core `OrdersDbContext` on PostgreSQL. A test that swaps in EF Core's in-memory provider no longer runs the SQL the app sends in production: raw SQL fails, PostgreSQL-specific translations are missing, string comparisons follow .NET rather than the database, and starting a transaction throws by default. **Testcontainers for .NET** starts a real PostgreSQL in Docker for the test run, so the same Npgsql provider, the same migrations and the same SQL are exercised. The test project needs the `Testcontainers.PostgreSql` package, `Microsoft.AspNetCore.Mvc.Testing` for `WebApplicationFactory<Program>`, and a running Docker engine. ## Building and starting the container The module builder `PostgreSqlBuilder` already knows how to run PostgreSQL: it binds container port 5432 to a **random free host port**, sets the database, user and password to `postgres`, waits until `pg_isready` succeeds, and turns durability off (`fsync=off`) because test data does not need to survive a crash. - **Pass the image explicitly**: `new PostgreSqlBuilder("postgres:17-alpine")`. In Testcontainers 4.15 the parameterless constructor is `[Obsolete]`; pin the major version production runs. - **`Build()` does not start anything**: it returns a `PostgreSqlContainer`. `await StartAsync()` pulls the image, creates and starts the container, and returns when the wait strategy passes. - **`GetConnectionString()`** returns `Host=...;Port=...;Database=...;Username=...;Password=...` with the mapped port. Called before `StartAsync`, it throws `InvalidOperationException`, because the container does not exist yet. ## Handing the connection string to the app The app should not know it is under test. Its Program.cs keeps its usual line, for example `builder.Services.AddDbContext<OrdersDbContext>(o => o.UseNpgsql(builder.Configuration.GetConnectionString("Orders")))`. The factory supplies a different value for `ConnectionStrings:Orders`: ```csharp protected override void ConfigureWebHost(IWebHostBuilder builder) => builder.UseSetting("ConnectionStrings:Orders", _db.GetConnectionString()); ``` With a minimal-hosting app, `WebApplicationFactory` turns host settings such as `UseSetting` values into command-line arguments for the entry point, so they win over appsettings.json and are visible even to code that reads configuration before `Build()`. Program.cs must pass `args` to `WebApplication.CreateBuilder`, which the templates do. ## Creating the schema once 1. `InitializeAsync` awaits `_db.StartAsync()`. 2. It reads `Services`, which builds the host; `ConfigureWebHost` runs now, after the container has a port. 3. It creates a scope, resolves `OrdersDbContext` and awaits `Database.MigrateAsync()`. 4. Tests then call `CreateClient()` and send real HTTP requests through the in-memory server. Running the real migrations, rather than `EnsureCreatedAsync()`, means the tests also prove that the migrations build the schema the model expects. ## Sharing the container, and alternatives A PostgreSQL container takes seconds to start, so starting one per test makes a suite of hundreds of tests unbearably slow. The usual shape is one container per test class (`IClassFixture<OrdersApiFactory>`), per collection, or per assembly with xunit.v3's `[assembly: AssemblyFixture(typeof(OrdersApiFactory))]`. Sharing the container means sharing the data, so each test starts by resetting it. | Option | What you write | When it fits | |---|---|---| | Factory implements `IAsyncLifetime` | container field, `StartAsync`, `UseSetting` | most API suites | | `DbContainerFixture<PostgreSqlBuilder, PostgreSqlContainer>` | override `Configure()` and `DbProviderFactory` | ADO.NET-level tests in xUnit | | `DbContainerTest<,>` | same overrides, one container per test | destructive tests that cannot share | `DbContainerFixture` and `DbContainerTest` come from the `Testcontainers.XunitV3` package (`Testcontainers.Xunit` for xUnit v2). They start and dispose the container through xUnit's lifetime, write container logs to xUnit's message sink, and expose `ConnectionString`, `CreateConnection()`, `OpenConnectionAsync()` and `CreateCommand()` backed by a `DbDataSource` built from the `DbProviderFactory` you supply, such as `NpgsqlFactory.Instance`. ## Tearing down `DisposeAsync` disposes the factory, which stops the host and its connection pool, and then disposes the container, which stops and removes it. If a test process dies before that, Testcontainers' resource reaper removes the container. ## Mistakes that show up in review - **A hard-coded address.** `Host=localhost;Port=5432` in an appsettings.Testing.json works on one laptop with a local PostgreSQL and fails everywhere else, because the container's host port is random. - **Reading the string too early.** A field initializer such as `private readonly string _cs = _db.GetConnectionString();` runs before `StartAsync` and throws `InvalidOperationException`. - **`EnsureCreatedAsync()` instead of migrations.** It builds the schema straight from the model, so a broken or missing migration passes the suite and fails in production. - **Disposing in the wrong order.** Stopping the container while the host still holds pooled connections produces connection errors during shutdown that hide real failures in the log. - **Test-only branches in Program.cs.** Starting a container from the app when the environment is `Testing` couples production code to Docker; the fixture owns the container, the app only reads configuration.

  • Why not start a new PostgreSQL container for every test?
    Starting PostgreSQL in Docker takes seconds, so one container per test multiplies the suite's run time by the number of tests. Sharing one container per class, collection or assembly and resetting the rows before each test gives the same isolation for a fraction of the cost. Testcontainers.XunitV3 still offers `DbContainerTest<,>` for the few destructive tests, such as ones that drop a schema, that cannot share.
  • What does Testcontainers.XunitV3's DbContainerFixture save you from writing?
    It implements xUnit's lifetime for you: it starts the container, keeps a start-up failure to rethrow when a test first reads `Container`, writes container logs to the `IMessageSink`, and disposes everything. You override `Configure()` to return `new PostgreSqlBuilder(image)` and the abstract `DbProviderFactory` property, for example `NpgsqlFactory.Instance`, and get `ConnectionString`, `OpenConnectionAsync()` and `CreateCommand()`. The `Configure(TBuilderEntity)` overload the docs page still shows is `[Obsolete]` in 4.15.
  • What changes when the order API runs on SQL Server instead?
    Use `Testcontainers.MsSql` and `new MsSqlBuilder("mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04")` or the image production runs. The wiring is identical: start, read `GetConnectionString()`, pass it with `UseSetting`, migrate once. The module logs in as `sa`, accepts the EULA through `ACCEPT_EULA=Y`, and its connection string carries `TrustServerCertificate=True` because the container's certificate is self-signed. When `WithDatabase` names a database other than `master`, the module creates it with a start-up callback, because the image has no setting for that.

  • Hard-code Host=localhost;Port=5432 in appsettings.Testing.json for the container.
  • Start a fresh container in every test class constructor by default.
  • Swap UseNpgsql for UseInMemoryDatabase in tests because both run the same queries.
  • Read GetConnectionString() in a field initializer before StartAsync() has run.
  • Write new PostgreSqlBuilder() and let Testcontainers choose the PostgreSQL version.
  • Add an IsEnvironment("Testing") branch in Program.cs that starts the container.

Assumes ASP.NET Core 10 with Microsoft.AspNetCore.Mvc.Testing 10.0.12, xunit.v3 (IAsyncLifetime returns ValueTask) and Testcontainers 4.15, whose module builders take the image as a constructor argument.

open as a page

With Testcontainers for .NET, what does new PostgreSqlBuilder("postgres:17").Build() give you, and how does a test connect to it?

level: juniorimportance: should knowfreq 42%

basics

~20 s

Build() returns a PostgreSqlContainer that is configured but not running. After await StartAsync(), GetConnectionString() returns a Host, Port, Database, Username and Password string pointing at the container's randomly mapped port, which the test passes to its DbContext or app.

open as a page

Your WebApplicationFactory adds the Testcontainers connection string with ConfigureAppConfiguration, yet the ASP.NET Core 10 app still connects to localhost:5432 — why, and how do you fix it?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Program.cs reads the connection string into a local before builder.Build(), but ConfigureAppConfiguration sources from WebApplicationFactory are applied only at Build. Use builder.UseSetting or ConfigureHostConfiguration instead, which reach the app as command-line arguments before Program.cs runs.

open as a page

In ASP.NET Core database tests, when does a per-test rolled-back transaction work instead of Respawn, and why does it fail through WebApplicationFactory?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Rollback works when the test owns the connection: open it, begin a transaction, attach the DbContext with UseTransaction, and dispose without committing. Through WebApplicationFactory each request uses its own DbContext and pooled connection, so its writes commit outside the test's transaction.

open as a page

With Respawn 7, how do you reset a Testcontainers PostgreSQL database between ASP.NET Core integration tests, and what does ResetAsync actually run?

level: seniorimportance: should knowfreq 38%

basics

~20 s

After migrations, call Respawner.CreateAsync(openConnection, options) once; it reads the tables and foreign keys and builds a delete script. Before each test, ResetAsync(connection) runs it: one TRUNCATE ... CASCADE on PostgreSQL, ordered DELETEs on SQL Server.

open as a page