Database — GOaT Stack

The GOaT stack uses PostgreSQL with sqlc — you write SQL, sqlc generates type-safe Go code. No ORM, no query builder, no magic.

Why sqlc?

You write real SQL. No DSL to learn.
Type-safe generated code. Compiler catches mismatches.
Zero runtime overhead. It’s just Go functions calling pgx.
Easy to debug. The query is right there in .sql.

Setup

model/sqlc.yaml:

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

Migrations

Number your migrations sequentially:

model/migrations/
├── 0001_init.up.sql
├── 0002_add_auth_fields.up.sql
├── 0003_add_game_slug.up.sql
└── 0004_tournaments.up.sql

-- 0001_init.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE games (
    id SERIAL PRIMARY KEY,
    slug TEXT UNIQUE NOT NULL,
    status TEXT NOT NULL DEFAULT 'lobby',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Run with golang-migrate or any migration tool:

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

Queries

model/queries.sql:

-- name: GetGameByID :one
SELECT * FROM games WHERE id = $1;

-- name: GetGameBySlug :one
SELECT * FROM games WHERE slug = $1;

-- name: CreateGame :one
INSERT INTO games (slug, status) VALUES ($1, $2) RETURNING *;

-- name: ListGamesByUser :many
SELECT g.* FROM games g
JOIN game_players gp ON gp.game_id = g.id
WHERE gp.user_id = $1
ORDER BY g.created_at DESC;

Generate:

sqlc generate

This creates internal/model/queries.sql.go with type-safe functions:

// Generated by sqlc
func (q *Queries) GetGameByID(ctx context.Context, id int32) (Game, error)
func (q *Queries) CreateGame(ctx context.Context, arg CreateGameParams) (Game, error)

Service Layer

Wrap sqlc queries in a service with business logic:

type GameService struct {
    db *pgxpool.Pool
}

func (s *GameService) CreateGame(ctx context.Context) (*model.Game, error) {
    slug := generateSlug() // Random 6-char code
    queries := model.New(s.db)
    return queries.CreateGame(ctx, model.CreateGameParams{
        Slug:   slug,
        Status: "lobby",
    })
}

Connection Pool

Use pgxpool for connection pooling:

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

Gotchas

Always use pgxpool, not raw pgx.Connect. You need connection pooling in production.
sqlc generates nullable types. A TEXT column that allows NULL becomes pgtype.Text, not string. Use NOT NULL in your schema when you can.
Migrations are up-only. Write forward-only migrations. If you need to undo, write a new migration.
Keep queries close to the schema. Both live in model/ so they evolve together.