Skip to main content

Database

How the API connects to PostgreSQL, manages migrations, and generates type-safe query code.

Schema-per-Module​

Each module owns a dedicated PostgreSQL schema. The recipe module uses the recipe schema, a future ingredient module would use ingredient, and so on. This keeps tables isolated between bounded contexts — no cross-module foreign keys.

All SQL uses schema-qualified table names (recipe.recipes, not just recipes). This is explicit and avoids any reliance on search_path configuration.

Migrations​

Migrations use goose with SQL files embedded in the Go binary.

File location​

Each module stores its migrations alongside its infra code:

internal/modules/recipe/infra/
  migrations/
    00001_create_schema.sql
    00002_create_recipes_table.sql
  migrations.go              # //go:embed migrations/*.sql

Per-module version tracking​

Each module gets its own goose version table (e.g., goose_recipe_version). Version numbers are scoped per module — every module starts at 00001 with no global coordination needed.

Writing a migration​

Create a new SQL file in the module's infra/migrations/ directory. Use the next sequential number:

-- +goose Up
CREATE TABLE recipe.ingredients (
    id        UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    recipe_id UUID        NOT NULL REFERENCES recipe.recipes(id),
    name      TEXT        NOT NULL,
    quantity  TEXT        NOT NULL
);

-- +goose Down
DROP TABLE IF EXISTS recipe.ingredients;

Always include a -- +goose Down section for rollbacks.

Running migrations​

Local development — either approach works:

# Standalone CLI (recommended)
cd apps/api && make migrate

# Or auto-migrate on startup
# Set DB_AUTO_MIGRATE=true in .env, then start the server

Production — always use the standalone migrate binary. Run it once before deploying new API instances to avoid race conditions:

go run ./cmd/migrate
# or: ./bin/migrate (if pre-built)

Query Generation with sqlc​

sqlc generates type-safe Go code from annotated SQL queries. This mirrors the OpenAPI-first approach used for HTTP endpoints — SQL is the source of truth.

File structure​

internal/modules/recipe/infra/
  queries/
    recipes.sql          # Annotated SQL queries
  sqlc/
    sqlc.yaml            # sqlc configuration
  db/
    db.go                # Generated (do not edit)
    models.go            # Generated (do not edit)
    recipes.sql.go       # Generated (do not edit)

Writing queries​

Add annotated SQL to a file in infra/queries/. Use schema-qualified table names:

-- name: ListRecipes :many
SELECT id, title, created_at, updated_at
FROM recipe.recipes
ORDER BY created_at DESC;

-- name: CreateRecipe :one
INSERT INTO recipe.recipes (title)
VALUES ($1)
RETURNING id, title, created_at, updated_at;

The annotation comments (-- name: ... :many/:one/:exec) tell sqlc what Go function signature to generate. See the sqlc docs for all options.

Generating code​

After changing queries or migrations:

cd apps/api && make sqlc

Or regenerate everything (sqlc + OpenAPI):

pnpm generate --filter=api

Using generated code in a repository​

The generated Queries struct accepts any DBTX interface (connection pool or transaction). The repository wraps it and maps between pgtype types and clean domain types:

func NewRecipeRepository(db recipedb.DBTX) *RecipeRepository {
    return &RecipeRepository{q: recipedb.New(db)}
}

The toDomain helper converts sqlc's pgtype.UUID / pgtype.Timestamptz to the domain's uuid.UUID / time.Time:

func toDomain(row recipedb.RecipeRecipe) domain.Recipe {
    return domain.Recipe{
        ID:        uuid.UUID(row.ID.Bytes),
        Title:     row.Title,
        CreatedAt: row.CreatedAt.Time,
        UpdatedAt: row.UpdatedAt.Time,
    }
}

Connection Pool​

A single pgxpool.Pool is created at startup in main.go and injected into each module's Register() function. Modules do not create their own connections.

Pool settings are configured via DB_* environment variables — see Getting Started for the full list.