12 — Persistence with PostgreSQL

Goal

Move task data out of memory into PostgreSQL so it survives a server restart.

Step 1: Start a local database

Create deploy/compose.yaml:

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: fieldnotes
      POSTGRES_PASSWORD: localdev
      POSTGRES_DB: fieldnotes
    ports:
      - "5432:5432"
    volumes:
      - fieldnotes-db:/var/lib/postgresql/data
volumes:
  fieldnotes-db:

Pin a current supported PostgreSQL image for your cohort. Start it from the repo root with docker compose -f deploy/compose.yaml up -d. Check docker compose -f deploy/compose.yaml ps until the database is ready.

Step 2: Create the table

Create server/migrations/001_tasks.sql:

CREATE TABLE IF NOT EXISTS tasks (
  id text PRIMARY KEY,
  project_id text NOT NULL,
  title text NOT NULL CHECK (length(title) BETWEEN 1 AND 120),
  description text NOT NULL DEFAULT '',
  status text NOT NULL CHECK (status IN ('TODO', 'IN_PROGRESS', 'DONE')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS tasks_project_updated_idx
  ON tasks (project_id, updated_at DESC, id);

Apply the migration and inspect the table:

Get-Content server/migrations/001_tasks.sql | docker compose -f deploy/compose.yaml exec -T db psql -U fieldnotes -d fieldnotes
docker compose -f deploy/compose.yaml exec db psql -U fieldnotes -d fieldnotes -c "\dt"

The table is the durable representation of a task. A primary key ensures IDs are unique; NOT NULL and CHECK constraints protect invariants even if a bug bypasses Go validation. The index makes listing by project and update time efficient. Migrations record schema changes as files so every environment can be brought to the same structure.

Step 3: Add the PostgreSQL driver

From the repo root, run go get github.com/jackc/pgx/v5/stdlib. Create server/internal/tasks/postgres.go:

package tasks

import (
    "context"
    "database/sql"
)

type PostgresRepository struct { db *sql.DB }

func NewPostgresRepository(db *sql.DB) *PostgresRepository {
    return &PostgresRepository{db: db}
}

func (r *PostgresRepository) Get(ctx context.Context, id string) (Task, error) {
    var task Task
    err := r.db.QueryRowContext(ctx,
        `SELECT id, title, status FROM tasks WHERE id = $1`, id,
    ).Scan(&task.ID, &task.Title, &task.Status)
    if err == sql.ErrNoRows { return Task{}, ErrNotFound }
    return task, err
}

func (r *PostgresRepository) Save(ctx context.Context, task Task) error {
    _, err := r.db.ExecContext(ctx,
        `INSERT INTO tasks (id, project_id, title, status)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title,
         status = EXCLUDED.status, updated_at = now()`,
        task.ID, "demo-project", task.Title, task.Status,
    )
    return err
}

This adapter maps only a few columns. Extend the domain type and query to map all fields. $1 placeholders keep user values separate from SQL text; never concatenate user input into SQL.

database/sql is a standard-library abstraction for connection pools and queries. The blank driver import registers the PostgreSQL driver under a name; sql.Open configures a pool but does not prove the database is reachable, so PingContext performs that check. QueryRowContext binds execution to the request context and Scan copies result columns into Go fields.

Step 4: Connect at startup

In server/cmd/api/main.go, import the pgx stdlib driver with a blank import, open sql.Open("pgx", os.Getenv("DATABASE_URL")), call PingContext with a timeout, and pass a PostgresRepository into the task service. Close the database on shutdown. Set DATABASE_URL in your terminal for local development; use the platform secret manager in production.

The repository adapter is the boundary between application behavior and SQL. Keeping SQL out of the gRPC handler lets the handler focus on translating protocol messages and lets repository tests focus on storage. The database handle is a pool shared by handlers; it is not one TCP connection per RPC.

Step 5: Verify durability

Create a task, stop only Go, start the server again, and fetch the task. The row should still exist. Stop the stack with docker compose -f deploy/compose.yaml down (without -v) and start it again; the named volume retains data. down -v removes the volume and erases local database data.

Checkpoint

Add a repository test against a disposable database. Confirm missing rows map to ErrNotFound and verify the query uses context-aware database methods.

Practice

Add team and project foreign keys in a second migration. Keep migrations additive and explain how a rollback would work.