🗄️ Project: Postgres-Backed API with database/sql and Integration Tests

Quick Review

In the last lesson you got the HTTP surface right: handlers as interfaces, middleware as function composition, bounded body reads, and a graceful shutdown that drains in-flight requests. None of that matters if the data layer underneath it is sloppy, and this project is where the sloppiness usually shows up. We take the handler layer you already have and give it a real Postgres repository, then prove the thing works with tests that talk to a real database.

Project Brief

Build a small task-tracking API, three endpoints, one table. The point is not the feature set. The point is that ==the repository is where production Go services quietly succeed or fail==: connection pooling, context propagation, transaction boundaries, and parameterized SQL. Every one of those is invisible in a happy-path demo and every one of them bites you at 2am.

You will end with a store package that the handler layer depends on through a narrow interface, plus an integration test suite you can run with one command.

Requirements / Spec

Layout. Keep the wiring from the layout lesson; add a data layer and migrations.

cmd/api/main.go
internal/httpapi/
  handlers.go
  routes.go
internal/store/
  store.go        // Task type + Repository interface
  postgres.go     // SQL implementation
  postgres_test.go
migrations/
  0001_tasks.sql

Schema. One table is enough:

CREATE TABLE tasks (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT NOT NULL CHECK (length(title) > 0),
    done       BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Repository interface. Defined in store, consumed by httpapi. Four methods: Create, Get, List, Complete. Every method takes ctx context.Context as the first parameter and returns domain values and domain errors, never *sql.DB and never raw driver errors.

Connection setup. One *sql.DB for the whole process, created in main, tuned, pinged, closed on shutdown.

func OpenDB(ctx context.Context, dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, fmt.Errorf("open db: %w", err)
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(20)
	db.SetConnMaxLifetime(30 * time.Minute)
	db.SetConnMaxIdleTime(5 * time.Minute)

	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := db.PingContext(pingCtx); err != nil {
		db.Close()
		return nil, fmt.Errorf("ping db: %w", err)
	}
	return db, nil
}

sql.Open is lazy: it {{c1::validates the DSN and builds a pool handle}} but does not {{c2::open a connection}}. A nil error from it means nothing about the database being reachable. Ping, then close on failure.

Queries. Postgres placeholders are positional: {{c3::$1}}, $2, and so on. Never concatenate. QueryRowContext(...).Scan(...) returns sql.ErrNoRows for a missing row, which you translate to your own sentinel:

func (s *Store) Get(ctx context.Context, id int64) (Task, error) {
	const q = `SELECT id, title, done, created_at FROM tasks WHERE id = $1`
	var t Task
	err := s.db.QueryRowContext(ctx, q, id).Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt)
	switch {
	case errors.Is(err, sql.ErrNoRows):
		return Task{}, ErrNotFound
	case err != nil:
		return Task{}, fmt.Errorf("get task %d: %w", id, err)
	}
	return t, nil
}

Transactions. Complete is a single statement, but write it as a transaction anyway so you practise the idiom defer tx.Rollback() immediately after BeginTx. It is a safe no-op after a successful Commit, and it guarantees rollback on every early return.

Integration tests. A table-driven test file guarded by a build tag, skipped when TEST_DATABASE_URL is unset, so go test ./... stays fast on a laptop with no database.

Milestones

1. Write the migration and apply it to a local Postgres (docker run postgres:16, or your existing instance). Verify the table by hand.
2. Implement `OpenDB` and wire it into `main`. Log the ping failure and exit non-zero. Prove the shutdown path closes the pool.
3. Implement `Create` and `Get`. Use `RETURNING id, created_at` so you never need a second round trip.
4. Implement `List` with `defer rows.Close()` and a `rows.Err()` check after the loop.
5. Implement `Complete` as a transaction. Map the not-found and already-done cases to distinct domain errors.
6. Swap the handler layer from the in-memory stub to the `Repository` interface. Handlers should not change shape.
7. Add the integration test file: one helper that connects, runs migrations, and registers cleanup; then table-driven cases over it.
8. Run `go test -tags=integration ./...` with a real DSN and make it green.

Acceptance Criteria

  • go build ./... and go vet ./... are clean, and go test ./... passes without a database.
  • go test -tags=integration ./... passes against a real Postgres, and skips cleanly (not fails, not hangs) when TEST_DATABASE_URL is missing.
  • No SQL string in the codebase contains a formatted user value. Grep for + and Sprintf near queries and come up empty.
  • Every repository method takes a context, and cancelling the request cancels the query.
  • Get on a missing ID returns your ErrNotFound, and the handler maps it to 404.
  • A duplicate or constraint violation maps to a domain ErrConflict, not a 500.
  • The pool settings are explicit in code, not left at defaults.
  • main closes the pool after the HTTP server finishes shutting down.

Hints & Common Pitfalls

[!WARNING] SetMaxIdleConns defaults to 2 while SetMaxOpenConns defaults to unlimited. Under bursty load that combination churns connections constantly. Set idle equal to max open, and keep ConnMaxLifetime shorter than any idle timeout on your pooler or Postgres itself.

The classic mistake list:

  • Forgetting defer rows.Close() and leaking connections until the pool starves. Do it on the line after the error check, every time.
  • Not checking rows.Err(). Iteration stops early on a network error and rows.Next() just returns false. Your loop looks successful and returns half the data.
  • Running t.Parallel() against one shared database with a BIGSERIAL. Tests interleave, sequences shift, and assertions about ID 1 fail intermittently. Either drop parallelism or give each test its own schema.
  • Doing work inside the transaction that does not need the lock. Keep transactions short; do HTTP calls and logging outside.
  • Ignoring context in Commit. If the client disconnects mid-transaction, the context cancels and the commit fails. That is correct behaviour, but log it rather than swallowing it.
  • Defining a giant repository interface "just in case." Four methods the handler actually calls beats fifteen speculative ones. Interfaces belong where they are consumed.

[!TIP] Wrap every repository error with fmt.Errorf("verb noun: %w", err) and then decide at the handler layer which domain errors become 404, 409, or 500. That way the SQL never leaks into HTTP status decisions.

A last one on the pool: one *sql.DB per process, shared. Creating a sql.DB inside a handler gives every request its own pool, and nothing good follows.

Summary & Next Steps

You now have the whole vertical slice: handler, middleware, repository, real Postgres, and a test suite that exercises the SQL rather than a mock of it. The habits worth keeping are small ones: context everywhere, placeholders always, rollback deferred, errors wrapped and translated at the boundary, pool configured on purpose.

The checkpoint for this module is next, and it will poke at exactly these seams: middleware ordering, shutdown, and whether your repository returns domain errors or accidents. After that we leave HTTP behind for a bit and expose a gRPC endpoint alongside it, which means the repository layer you just built gets a second consumer.

[
  { "front": "What does sql.Open actually do?", "back": "Validates the DSN and returns a pool handle; it does not open a connection." },
  { "front": "How do you prove the database is reachable after sql.Open?", "back": "Call db.PingContext with a timeout, and close the pool if it fails." },
  { "front": "Which placeholder style does Postgres use with database/sql?", "back": "Positional $1, $2, $3 and so on, never string concatenation." },
  { "front": "What must you check after a rows.Next() loop?", "back": "rows.Err(), because iteration can stop early on a network error." },
  { "front": "How does QueryRow report that no row matched?", "back": "Scan returns sql.ErrNoRows, which you translate to a domain ErrNotFound." },
  { "front": "What is the idiomatic transaction cleanup line?", "back": "defer tx.Rollback() right after BeginTx; it is a no-op after a successful Commit." },
  { "front": "Why set ConnMaxLifetime on the pool?", "back": "So connections recycle before Postgres or a proxy silently drops them." },
  { "front": "What does SetMaxOpenConns bound?", "back": "The number of simultaneously open connections; extra callers block until context cancellation." },
  { "front": "Where should the repository interface be defined?", "back": "In the consuming package, small and limited to the methods actually called." },
  { "front": "How do you keep integration tests out of the default test run?", "back": "A //go:build integration tag plus a skip when TEST_DATABASE_URL is unset." },
  { "front": "What should a unique-constraint violation become?", "back": "A domain error such as ErrConflict, detected from the driver SQLSTATE (23505)." },
  { "front": "Why is t.Parallel risky with a shared test database?", "back": "Tests interleave and mutate shared rows and sequences, so they fail intermittently." },
  { "front": "Why pass context.Context into every repository method?", "back": "So request deadlines and client cancellation propagate down to the query." },
  { "front": "How many *sql.DB handles should a service create?", "back": "One per process, created in main, tuned, and closed on shutdown." }
]