golang-sqlc

sqlc (Go)

SQL is the contract. sqlc generates the Go. No query concatenated with input. Language: golang. HTTP: golang-apis. Scylla: golang-scylladb (do not mix the CQL model here).

Stack and scope

Item	Role
sqlc	Codegen from .sql
pgx	Postgres driver (preferred)
migrations	goose / golang-migrate / atlas — whichever the repo uses
sqlc.yaml	Engine, generated package

Do not introduce GORM into a feature if sqlc is already in the module.

Organization

db/
  migrations/
  queries/
    orders.sql
  sqlc.yaml
internal/db/             # pacote gerado (ou sqlc/)

• Named queries (-- name: GetOrder :one).
• Repository wrapping the generated code if domain is needed; otherwise the service calls Queries.

Non-negotiable rules

• Parameterized SQL ($1). Never interpolate ID/string into the query.
• Regenerate (sqlc generate) in CI; do not edit generated files.
• Versioned migrations; do not edit an applied migration.
• :one / :many / :exec . Be aware of pgx.ErrNoRows → ErrNotFound.
• Transaction: Begin + Queries.WithTx. Composite writes in the same tx.
• context.Context in every query.

-- name: GetOrderForUser :one
SELECT id, status, total_cents
FROM orders
WHERE id = $1 AND user_id = $2;

row, err := q.GetOrderForUser(ctx, db.GetOrderForUserParams{ID: id, UserID: uid})
if errors.Is(err, pgx.ErrNoRows) {
    return nil, ErrNotFound
}

Modeling

• Schema in the migration, not just in your head. Indexes for real WHERE/ORDER .
• Enums in Postgres or check constraints. sqlc maps them to Go types.
• Soft delete: WHERE deleted_at IS NULL in all product queries.
• Multi-tenant: tenant_id in the WHERE, always.

pgx

• pgxpool in the main. MaxConns aligned with golang-production.
• QueryRow via sqlc; copy protocol only with evidence (bulk).
• JSONB: explicit types ([]byte / json.RawMessage / struct).

Tests

• Testcontainers Postgres or CI DB. t.Parallel + isolated schema / transaction rollback.
• Cover no-rows, unique violation (23505).
• Do not mock the sqlc-generated code if the integration test is cheap.

Anti-patterns

• Editing *.sql.go by hand
• fmt.Sprintf("WHERE id = %s", id)
• A giant SELECT * for the API
• sqlc + GORM in the same aggregate without a boundary
• Forgetting user_id/tenant_id in the WHERE clause

Integration with other skills

• Go: golang
• APIs: golang-apis
• Production/pool: golang-production
• Tests: golang-unit-testing
• Analogous Rust: sqlx
• Engine: postgres
• Agent memory / <=>: pgvector

Completion criteria

• Named queries; generate in CI
• WHERE with ownership/tenant
• Tx on composite writes
• New migration if the schema changed
• ErrNoRows mapped; constraint tests