Embedded PostgreSQL for Development

Overview

Running integration tests against a real database — not mocks, not SQLite substitutes — is the gold standard for data access layer testing. But requiring developers to install and configure PostgreSQL locally, or depend on a shared staging database, introduces friction: “it works on my machine” divergence, version mismatches, port conflicts, and the overhead of managing a database service that’s only needed during test runs.

Embedded PostgreSQL libraries solve this by bundling a real PostgreSQL server binary that can be started programmatically, used for the duration of a test suite, and stopped cleanly afterward. In Go, the embedded-postgres library downloads a platform-specific PostgreSQL binary, starts it on an ephemeral port, and returns a connection string — all in a few lines of code. Each test run gets a fresh, isolated PostgreSQL instance with no leftover state from previous runs, no port conflicts with other services, and no dependency on the developer’s system configuration.

This topic covers how embedded PostgreSQL works under the hood, how to integrate it with Go test suites, patterns for per-test database isolation, and the tradeoffs between embedded PostgreSQL, Docker-based databases, and shared test databases. A typical Go service uses embedded-postgres to run its full integration test suite without any external dependencies.

Core Concepts

How It Works

The embedded-postgres library wraps the standard PostgreSQL server binary in a Go-friendly API. It handles downloading, extracting, configuring, starting, and stopping the server. From the application’s perspective, it’s connecting to a normal PostgreSQL server via a standard connection string.

Basic Setup

package testutil

import (
    "database/sql"
    "fmt"
    "testing"

    embeddedpostgres "github.com/fergusstrange/embedded-postgres"
    _ "github.com/lib/pq"
)

// StartTestDB starts an embedded PostgreSQL instance and returns
// a connection and a cleanup function.
func StartTestDB(t *testing.T) (*sql.DB, func()) {
    t.Helper()

    postgres := embeddedpostgres.NewDatabase(
        embeddedpostgres.DefaultConfig().
            Version(embeddedpostgres.V16).
            Port(0). // Use a random available port.
            Database("testdb").
            Username("test").
            Password("test"),
    )

    if err := postgres.Start(); err != nil {
        t.Fatalf("starting embedded postgres: %v", err)
    }

    port := postgres.GetPort()
    connStr := fmt.Sprintf(
        "postgres://test:test@localhost:%d/testdb?sslmode=disable",
        port,
    )

    db, err := sql.Open("postgres", connStr)
    if err != nil {
        postgres.Stop()
        t.Fatalf("connecting to embedded postgres: %v", err)
    }

    if err := db.Ping(); err != nil {
        db.Close()
        postgres.Stop()
        t.Fatalf("pinging embedded postgres: %v", err)
    }

    cleanup := func() {
        db.Close()
        if err := postgres.Stop(); err != nil {
            t.Errorf("stopping embedded postgres: %v", err)
        }
    }

    return db, cleanup
}

Integration with TestMain

package repository_test

import (
    "database/sql"
    "os"
    "testing"

    "example.com/app/internal/testutil"
    "example.com/app/internal/migrations"
)

var testDB *sql.DB

func TestMain(m *testing.M) {
    // Start embedded PostgreSQL once for the entire test package.
    db, cleanup := testutil.MustStartTestDB()
    defer cleanup()
    testDB = db

    // Apply migrations to get a production-equivalent schema.
    if _, err := migrations.Run(db); err != nil {
        panic("applying migrations: " + err.Error())
    }

    // Run all tests in this package.
    os.Exit(m.Run())
}

func TestCreateUser(t *testing.T) {
    // Clean up between tests by truncating tables.
    testutil.TruncateAll(t, testDB)

    repo := NewUserRepository(testDB)
    user, err := repo.Create(context.Background(), "alice", "alice@example.com")
    if err != nil {
        t.Fatalf("creating user: %v", err)
    }

    if user.Username != "alice" {
        t.Errorf("expected username 'alice', got %q", user.Username)
    }
}

func TestCreateUserDuplicateEmail(t *testing.T) {
    testutil.TruncateAll(t, testDB)

    repo := NewUserRepository(testDB)
    _, _ = repo.Create(context.Background(), "alice", "alice@example.com")

    // Second creation with the same email should fail.
    _, err := repo.Create(context.Background(), "bob", "alice@example.com")
    if err == nil {
        t.Fatal("expected error for duplicate email, got nil")
    }
}

Transaction-Based Test Isolation

package testutil

import (
    "context"
    "database/sql"
    "testing"
)

// WithTestTx runs the given function within a transaction that is
// always rolled back. This gives each test a clean database state
// without the overhead of truncating tables.
func WithTestTx(t *testing.T, db *sql.DB, fn func(tx *sql.Tx)) {
    t.Helper()

    tx, err := db.BeginTx(context.Background(), nil)
    if err != nil {
        t.Fatalf("beginning transaction: %v", err)
    }

    // Always roll back — even if the test passes.
    defer func() {
        if err := tx.Rollback(); err != nil && err != sql.ErrTxDone {
            t.Errorf("rolling back test transaction: %v", err)
        }
    }()

    fn(tx)
}

// Usage:
// func TestUserLookup(t *testing.T) {
//     WithTestTx(t, testDB, func(tx *sql.Tx) {
//         // Insert test data within the transaction.
//         tx.Exec("INSERT INTO users (username, email) VALUES ($1, $2)",
//             "alice", "alice@example.com")
//
//         // Test the lookup function, passing tx as the database handle.
//         user, err := LookupUser(tx, "alice")
//         // ... assertions ...
//
//         // Transaction is rolled back automatically — no cleanup needed.
//     })
// }

Per-Test Database Creation

package testutil

import (
    "database/sql"
    "fmt"
    "strings"
    "testing"

    _ "github.com/lib/pq"
)

// CreateTestDatabase creates a fresh database within the running
// PostgreSQL instance, returning a connection to it. The database
// is dropped when the test completes.
func CreateTestDatabase(t *testing.T, adminDB *sql.DB, port int) *sql.DB {
    t.Helper()

    // Generate a unique database name from the test name.
    dbName := strings.ToLower(strings.ReplaceAll(t.Name(), "/", "_"))
    dbName = fmt.Sprintf("test_%s_%d", dbName, port)

    _, err := adminDB.Exec(fmt.Sprintf("CREATE DATABASE %s", dbName))
    if err != nil {
        t.Fatalf("creating test database %s: %v", dbName, err)
    }

    connStr := fmt.Sprintf(
        "postgres://test:test@localhost:%d/%s?sslmode=disable",
        port, dbName,
    )
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        t.Fatalf("connecting to test database: %v", err)
    }

    t.Cleanup(func() {
        db.Close()
        adminDB.Exec(fmt.Sprintf("DROP DATABASE IF EXISTS %s", dbName))
    })

    return db
}

Practical Applications

A typical Go service uses embedded-postgres as the foundation of its integration testing strategy. Every test package that interacts with the database starts an embedded PostgreSQL instance in TestMain, applies migrations, and runs all tests against a real PostgreSQL engine. This approach has caught several classes of bugs that mock-based testing would have missed: incorrect SQL syntax that only PostgreSQL rejects (but mock drivers accept), unique constraint violations that depend on real index behavior, and transaction isolation issues that only manifest under actual MVCC semantics.

CI runs use the same embedded-postgres approach. The GitHub Actions workflow doesn’t need a PostgreSQL service container — the test binary downloads and manages its own database. This simplifies the CI configuration (no service health checks, no port mapping) and ensures the CI environment is identical to the developer’s local environment. The PostgreSQL binary is cached in GitHub Actions’ cache, so subsequent CI runs skip the download step.

For development workflows, embedded-postgres enables “clone and test” onboarding: a new developer clones the repository, runs make test, and the full integration test suite passes without any setup beyond having Go installed. This reduced onboarding time from “half a day configuring local services” to “five minutes for the first test run.”

Implementation Patterns

Caching PostgreSQL Binaries

The embedded-postgres library caches downloaded PostgreSQL binaries in a default cache directory (~/.embedded-postgres-go). In CI, persist this directory across runs to avoid re-downloading the ~50MB binary on every build. In GitHub Actions, use the actions/cache step with the cache key tied to the PostgreSQL version.

# .github/workflows/test.yml
- uses: actions/cache@v4
  with:
    path: ~/.embedded-postgres-go
    key: embedded-postgres-v16-${{ runner.os }}-${{ runner.arch }}

- run: go test ./...

Configuring PostgreSQL Settings

The embedded instance can be configured with custom PostgreSQL settings for testing edge cases — lower work_mem to test sort spills, higher log_min_duration_statement to suppress noisy logs, or custom shared_preload_libraries to test extensions.

postgres := embeddedpostgres.NewDatabase(
    embeddedpostgres.DefaultConfig().
        Version(embeddedpostgres.V16).
        Port(0).
        StartParameters(map[string]string{
            "work_mem":                    "4MB",
            "log_min_duration_statement":  "1000", // Log queries > 1s
            "max_connections":             "20",
        }),
)

Parallel Test Packages

Go runs test packages in parallel by default. Since each package starts its own embedded PostgreSQL on a random port, there are no conflicts. However, each instance consumes memory (~50MB). For projects with many test packages, limit parallelism with -p 4 to avoid memory pressure on CI runners with limited RAM.

Common Pitfalls

Slow First Run Due to Download

The first time embedded-postgres runs on a machine, it downloads the PostgreSQL binary (~50MB). In CI without caching, this adds 10-30 seconds to every build. Always cache the download directory in CI. For developer machines, the download happens once and is cached permanently, but new team members may be surprised by the initial delay — document it in the project README.

Port Conflicts with Hardcoded Ports

Using a fixed port (e.g., 5433) seems simpler than random ports, but it causes failures when multiple test suites run simultaneously or when the developer has a local PostgreSQL running on that port. Always use Port(0) for random port allocation. The only valid reason for a fixed port is debugging a specific test where you need to connect with psql — and even then, use an environment variable override rather than a hardcoded value.

Data Directory Cleanup Failures

Embedded PostgreSQL creates a temporary data directory. If the test process is killed (SIGKILL, CI timeout) without running the cleanup function, the data directory and lock files may persist, causing the next test run to fail with “data directory already in use.” Add a pre-test cleanup step that removes stale data directories, or use a temporary directory that the OS cleans up automatically.

Platform-Specific Binary Issues

On Apple Silicon (M1/M2/M3), ensure the embedded-postgres library version you’re using ships arm64 binaries. Older versions only shipped amd64 binaries that required Rosetta 2 emulation, causing performance issues and occasional crashes. Similarly, Alpine Linux CI images may not work if the prebuilt binaries are compiled against glibc — use Debian-based images in CI.

Forgetting to Stop the Server

If a test panics after starting the embedded server but before registering the cleanup function, the PostgreSQL process is orphaned. It continues running, holding its port, until the OS eventually reclaims it. Use t.Cleanup() immediately after starting the server, before any test logic that might panic, to ensure cleanup is always registered.

Further Reading