# 🗄️ 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.

```tree
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:

```sql
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.

```go
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:

```go
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

```steps
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.

```flashcard
[
  { "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." }
]
```