Tempgres

Tempgres creates isolated PostgreSQL databases for integration tests by cloning a migrated template database.

It is useful when your tests need real PostgreSQL behavior, but you do not want to start a new PostgreSQL container for every test case or test class.

Tempgres is heavily inspired by pgtestdb, which applies the same PostgreSQL template-database idea in Go.

Note

pgtestdb supports creating and using a dedicated role for template and test databases. Tempgres does not include role management today but it can be added if theres ask for it.

Requirements

Tempgres needs an administrative connection string to a disposable PostgreSQL instance.

Do not point Tempgres at a shared development, staging, or production server.

For local development, this container is enough:

docker run -d --name tempgres-test --rm \
  -e POSTGRES_PASSWORD=postgres \
  -p 15432:5432 \
  --mount type=tmpfs,destination=/var/lib/postgresql,tmpfs-size=1g \
  postgres:18 \
  postgres \
  -c full_page_writes=off \
  -c shared_buffers=64MB \
  -c client_min_messages=warning

Use this as the provisioning connection string, either directly as TempgresOptions.AdminConnectionString or through TEMPGRES_CONNECTION_STRING in your tests:

Host=127.0.0.1;Port=15432;Username=postgres;Password=postgres;Include Error Detail=true

Your application tests should use the acquired TempgresDatabase.ConnectionString.

Usage

Notes

• Use a dedicated disposable PostgreSQL server for tests.
• Dispose acquired TempgresDatabase instances with await using whenever possible.
• Keep application connection pooling disabled for acquired database connection strings. Tempgres does this by default for returned connection strings.

EF Core

var factory = new TempgresDatabaseFactory(new TempgresOptions
{
    AdminConnectionString = "...",
    TemplateDatabaseName = "myapp_tests",
    Migrator = new EfCoreMigrator<AppDbContext>()
});

await using var database = await factory.AcquireAsync(cancellationToken);

var builder = new DbContextOptionsBuilder<AppDbContext>()
    .UseNpgsql(database.ConnectionString);

await using var db = new AppDbContext(builder.Options);

The first acquisition prepares the template database by running the migrator. Later acquisitions clone that template and return unique database names. Disposing the TempgresDatabase drops the test database.

Dependency Injection Without ASP.NET Core

For applications that use Microsoft.Extensions.DependencyInjection, acquire the database first and build the service provider from that database:

await using var app = await factory.AcquireServiceProviderLeaseAsync(database =>
{
    var services = new ServiceCollection();

    services.AddMyApplicationServices();
    services.AddDbContext<AppDbContext>(options => options.UseNpgsql(database.ConnectionString));

    return services.BuildServiceProvider(validateScopes: true);
}, cancellationToken);

await using var scope = app.Services.CreateAsyncScope();
var command = scope.ServiceProvider.GetRequiredService<ImportUsersCommand>();
await command.RunAsync(cancellationToken);

The service provider lease owns the temporary database above normal DI scopes. Disposing the lease disposes the service provider first, then drops the database.

EF Core + ASP.NET Core with WebApplicationFactory

For Web API tests, let the test fixture own the temporary database and configure the application to use the acquired connection string. The sample project demonstrates this pattern in IntegrationFixture.cs, TestEnvironment.cs, and OrderCheckoutTests.cs.

This setup is intentionally explicit today. I am interested in making the WebApplicationFactory experience more streamlined if there is a clean abstraction that preserves the ownership model.

EF Core + Seeded Template Data

Reference data can be seeded once into the template and cloned for every test database.

var options = new TempgresOptions
{
    AdminConnectionString = "...",
    TemplateDatabaseName = "myapp_seeded"
};

options.UseEfCoreMigrations<AppDbContext>(
    contextFactory: dbOptions => new AppDbContext(dbOptions),
    seedAsync: async (db, cancellationToken) =>
    {
        db.Roles.Add(new Role { Name = "Admin" });
        db.OrderStatuses.Add(new OrderStatus { Code = "submitted" });
        await db.SaveChangesAsync(cancellationToken);
    });

Conceptual Model

1. Tempgres resolves a template name from TemplateDatabaseName plus the migrator hash.
2. It takes a PostgreSQL advisory lock so concurrent test workers do not create the same template twice.
3. It creates the template database and runs migrations/seeding once.
4. It marks the template as complete.
5. Each test calls AcquireAsync, which creates a unique database with CREATE DATABASE ... WITH TEMPLATE ....
6. Disposing the acquired database drops only that test database.

Debugging Failed Tests

Tempgres drops a test database when its TempgresDatabase is disposed.

When debugging a failure, you can temporarily avoid disposing the acquired database and connect to TempgresDatabase.ConnectionString. The database name is also available as TempgresDatabase.DatabaseName.

CI Setup

In GitHub Actions, run PostgreSQL as a service or start the Docker container before dotnet test. The key is to expose a stable host/port and pass the admin connection string to the tests.

For example, for GitHub Actions workflows:

services:
  postgres:
    image: postgres:18-alpine
    env:
      POSTGRES_PASSWORD: postgres
    ports:
      - 15432:5432
    options: >-
      --tmpfs /var/lib/postgresql:rw,size=1g
      --health-cmd "pg_isready -U postgres"
      --health-interval 5s
      --health-timeout 3s
      --health-retries 5

env:
  TEMPGRES_CONNECTION_STRING: Host=127.0.0.1;Port=15432;Username=postgres;Password=postgres;Include Error Detail=true