Testing against real Postgres and shipping with Docker

At the end of Part 2 the service had a typed data layer, but two jobs were still manual: running migrations, and checking that the queries actually work. This last part automates both, sets up CI, and gets the Docker image ready for production.

Run migrations on startup

Running goose by hand is fine on a laptop. In production it’s one more step someone forgets during a deploy. Instead, embed the migration files in the binary and apply them when the service starts. Go’s embed package can only see files in or below the package’s own folder, so the embed lives next to the migrations:

sql/schema/embed.go

package schema

import "embed"

// FS holds every migration so the binary can apply them itself.
//
//go:embed *.sql
var FS embed.FS

internal/migrate/migrate.go

package migrate

import (
	"database/sql"
	"fmt"

	_ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver
	"github.com/pressly/goose/v3"

	"github.com/natnael-alemayehu/notes-api/sql/schema"
)

// Up applies every migration that hasn't run yet.
func Up(databaseURL string) error {
	db, err := sql.Open("pgx", databaseURL)
	if err != nil {
		return fmt.Errorf("open database: %w", err)
	}
	defer db.Close()

	goose.SetBaseFS(schema.FS)
	if err := goose.SetDialect("postgres"); err != nil {
		return fmt.Errorf("set dialect: %w", err)
	}
	if err := goose.Up(db, "."); err != nil {
		return fmt.Errorf("run migrations: %w", err)
	}
	return nil
}

Then call it in main, before creating the pool:

cmd/api/main.go

func main() {
	ctx := context.Background()
	databaseURL := os.Getenv("DATABASE_URL")

	// Apply pending migrations before serving any traffic.
	if err := migrate.Up(databaseURL); err != nil {
		log.Fatalf("migrate: %v", err)
	}

	pool, err := pgxpool.New(ctx, databaseURL)
	if err != nil {
		log.Fatalf("create pool: %v", err)
	}
	defer pool.Close()

	// ...the rest of main is unchanged from Part 1
}

goose records applied migrations in a goose_db_version table, so restarting the service never applies anything twice. If you run several replicas, apply migrations as a separate step in your deploy instead, so two instances starting at the same moment don’t race each other.

Integration tests with Testcontainers

I don’t mock the database in these tests. sqlc already checks the SQL against the schema when it generates code, so what’s left to verify is behavior: constraints, defaults, ordering. That needs a real Postgres, and Testcontainers starts one in Docker for the test run and removes it afterwards.

terminal

go get github.com/testcontainers/testcontainers-go/modules/postgres

internal/database/database_test.go

package database_test

import (
	"context"
	"testing"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/testcontainers/testcontainers-go"
	"github.com/testcontainers/testcontainers-go/modules/postgres"
	"github.com/testcontainers/testcontainers-go/wait"

	"github.com/natnael-alemayehu/notes-api/internal/migrate"
)

func setupDB(t *testing.T) *pgxpool.Pool {
	t.Helper()
	if testing.Short() {
		t.Skip("skipping integration test in -short mode")
	}
	ctx := context.Background()

	container, err := postgres.Run(ctx, "postgres:17-alpine",
		postgres.WithDatabase("notes_test"),
		postgres.WithUsername("test"),
		postgres.WithPassword("test"),
		testcontainers.WithWaitStrategy(
			wait.ForLog("database system is ready to accept connections").
				WithOccurrence(2).
				WithStartupTimeout(30*time.Second),
		),
	)
	if err != nil {
		t.Fatalf("start postgres: %v", err)
	}
	t.Cleanup(func() { _ = container.Terminate(context.Background()) })

	dsn, err := container.ConnectionString(ctx, "sslmode=disable")
	if err != nil {
		t.Fatalf("connection string: %v", err)
	}
	if err := migrate.Up(dsn); err != nil {
		t.Fatalf("migrate: %v", err)
	}

	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		t.Fatalf("create pool: %v", err)
	}
	t.Cleanup(pool.Close)
	return pool
}

The wait strategy looks for the ready message twice because the Postgres image starts once to run its init scripts and then restarts. Waiting for only the first message is a classic source of flaky tests.

With that helper in place, a test reads like ordinary application code:

internal/database/users_test.go

package database_test

import (
	"context"
	"errors"
	"testing"

	"github.com/jackc/pgx/v5/pgconn"

	"github.com/natnael-alemayehu/notes-api/internal/database"
)

func TestCreateUser_RejectsDuplicateEmail(t *testing.T) {
	pool := setupDB(t)
	q := database.New(pool)
	ctx := context.Background()

	params := database.CreateUserParams{Email: "ada@example.com", Name: "Ada"}

	created, err := q.CreateUser(ctx, params)
	if err != nil {
		t.Fatalf("create user: %v", err)
	}

	got, err := q.GetUserByEmail(ctx, params.Email)
	if err != nil {
		t.Fatalf("get user: %v", err)
	}
	if got.ID != created.ID {
		t.Errorf("got user %v, want %v", got.ID, created.ID)
	}

	_, err = q.CreateUser(ctx, params)
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) || pgErr.Code != "23505" {
		t.Fatalf("expected a unique violation, got %v", err)
	}
}

Starting a container takes a few seconds, so for a bigger suite I start one per package in TestMain and let each test create its own data. go test -short ./... skips the integration tests when you want a quick run without Docker.

Keep generated code honest in CI

It’s easy to edit a query and forget to run sqlc generate. sqlc diff exits with an error when the generated code doesn’t match the SQL, which makes it a good CI check. Here’s the whole workflow:

.github/workflows/ci.yml

name: ci

on:
  push:
    branches: [main]
  pull_request:

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-go@v5
        with:
          go-version-file: go.mod

      - name: Check generated code is up to date
        run: |
          go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest # pin to your local version
          sqlc diff

      - name: Test
        run: go test -race ./...

GitHub’s Ubuntu runners come with Docker installed, so the Testcontainers tests run in CI without extra setup. Pin sqlc to the same version you use locally so both places generate identical code.

A production-ready image

The multi-stage Dockerfile from Part 1 is already close. Three more habits make the service behave well in production:

Shut down gracefully. Deploys stop containers with SIGTERM. Catch it and let in-flight requests finish instead of cutting them off mid-response.
Probe /healthz from the platform. The distroless image has no shell or curl, so Docker’s own HEALTHCHECK can’t call the endpoint. Let ECS, Kubernetes or your load balancer do it instead.
Keep secrets out of the image. Pass DATABASE_URL at runtime, and use sslmode=require when the database isn’t on your private network.

Here’s graceful shutdown with only the standard library:

cmd/api/main.go

srv := &http.Server{
	Addr:              ":" + os.Getenv("PORT"),
	Handler:           mux,
	ReadHeaderTimeout: 5 * time.Second,
}

go func() {
	if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
		log.Fatalf("server: %v", err)
	}
}()

// Block until the platform asks us to stop.
sigCtx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
defer stop()
<-sigCtx.Done()

// Give in-flight requests up to 10 seconds to finish.
shutdownCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
if err := srv.Shutdown(shutdownCtx); err != nil {
	log.Printf("shutdown: %v", err)
}

Next up

That’s a solid foundation: Compose for a local Postgres, goose for schema changes, sqlc for a data layer the compiler checks, tests against a real database, and a small image that shuts down cleanly. It’s what I reach for when I start a new Go service, and it goes a long way before anything fancier is needed.

It still has no idea who’s calling it, though. Right now anyone can read or write anyone else’s notes. Part 4 fixes that with password-based login and server-side sessions, and the two parts after it cover authorization and rotating session IDs.