Database

Use PostgreSQL, sqlc, and migrations to model the domain honestly.

Use PostgreSQL with sqlc. Write SQL yourself, keep migrations in version control, and let generated Go code handle the type-safe call surface. No ORM, no hidden query planner, no app-level schema DSL.

Start With The Domain

Model the domain honestly before you generate code.

  • use tables and column names that match the business language
  • add database constraints for rules the database can enforce
  • make nullability intentional, not accidental
  • let services own workflows and decisions around queries

The database is not a dumb persistence bucket. It should reject impossible states even if a handler or service regresses.

sqlc Setup

Keep schema and queries together under model/:

version: "2"
sql:
  - engine: "postgresql"
    queries: "queries.sql"
    schema: "migrations/"
    gen:
      go:
        package: "model"
        out: "../internal/model"
        sql_package: "pgx/v5"

Generate after schema or query changes:

sqlc generate

Migrations

Treat migrations as the source of truth for schema evolution.

model/
├── migrations/
│   ├── 0001_init.up.sql
│   ├── 0002_add_game_owner.up.sql
│   └── 0003_add_completed_at.up.sql
├── queries.sql
└── sqlc.yaml

Example:

CREATE TABLE games (
    id BIGSERIAL PRIMARY KEY,
    slug TEXT NOT NULL UNIQUE,
    owner_user_id BIGINT NOT NULL REFERENCES users(id),
    status TEXT NOT NULL CHECK (status IN ('lobby', 'live', 'finished')),
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (
        (status = 'finished' AND completed_at IS NOT NULL) OR
        (status <> 'finished' AND completed_at IS NULL)
    )
);

That schema does three useful things:

  • NOT NULL documents required data
  • UNIQUE, REFERENCES, and CHECK constraints protect invariants
  • the completed_at rule keeps lifecycle fields internally consistent

Run migrations with the tool your app standardizes on:

migrate -path model/migrations -database "$DATABASE_URL" up

Nullability Is A Design Decision

Every nullable column becomes part of the generated Go API. If a field can be NULL, sqlc will generate a nullable type, and every caller now has to handle that state.

Use NULL only when the domain truly has an absent value:

  • completed_at can be NULL until a game finishes
  • display_name might be NULL if profiles are optional
  • email_verified_at can express “not yet verified”

Do not use NULL for:

  • fields that are required but not populated yet
  • values that really want an explicit enum or status column
  • “maybe we will need this later” placeholders

If most reads assume a value is always present, make the column NOT NULL and set it correctly at write time.

Write Queries That Match The Surface

Keep queries explicit and narrow:

-- name: GetGameByID :one
SELECT id, slug, owner_user_id, status, completed_at, created_at, updated_at
FROM games
WHERE id = $1;

-- name: GetGameBySlug :one
SELECT id, slug, owner_user_id, status, completed_at, created_at, updated_at
FROM games
WHERE slug = $1;

-- name: CreateGame :one
INSERT INTO games (slug, owner_user_id, status)
VALUES ($1, $2, 'lobby')
RETURNING id, slug, owner_user_id, status, completed_at, created_at, updated_at;

-- name: MarkGameFinished :exec
UPDATE games
SET status = 'finished',
    completed_at = NOW(),
    updated_at = NOW()
WHERE id = $1;

Prefer named queries that correspond to real use cases. Avoid dumping every possible join into one generated package just because sqlc makes it easy.

Services Own Workflows

sqlc packages are storage primitives. They should not become your application boundary.

Keep orchestration in services:

type GameService struct {
    queries *model.Queries
}

func (s *GameService) FinishGame(ctx context.Context, gameID int64) error {
    game, err := s.queries.GetGameByID(ctx, gameID)
    if err != nil {
        return err
    }
    if game.Status != "live" {
        return ErrGameNotLive
    }
    return s.queries.MarkGameFinished(ctx, gameID)
}

That split matters:

  • handlers should call services, not raw generated queries
  • services decide which queries belong to a workflow
  • provider or transport concerns stay out of storage code
  • query reuse stays intentional instead of leaking everywhere

If a query is only meaningful inside one workflow, the service for that workflow should own when and why it runs.

Constraints Beat Duplicated Validation

Validate early in handlers and services for good UX, then still enforce the rule in SQL.

Common constraints worth adding:

  • foreign keys for ownership and parent-child relationships
  • unique indexes for stable identifiers like slugs
  • CHECK constraints for bounded enums and lifecycle rules
  • NOT NULL for fields that must exist on every row

Application validation gives better error messages. Database constraints keep the data true when multiple code paths write to the same tables.

Connection And Transactions

Use pgxpool and pass context through:

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

Use transactions in the service layer when a workflow spans multiple writes or read-then-write decisions. Do not hide transactional behavior in random handlers.

Gotchas

  • SELECT * couples query shape to future schema edits. Prefer explicit columns.
  • Generated nullable types are a signal from the schema. Fix the schema first if the API feels noisy.
  • Do not let handlers stitch together several sqlc calls for business logic. Move that into a service.
  • Add constraints in migrations when the rule becomes real. Do not rely on comments or conventions alone.