/go-database

name: go-database
description: "Use when writing, reviewing, or debugging Go code that talks to a SQL database (PostgreSQL, MySQL, MariaDB, SQLite). Covers library choice (database/sql, sqlx, sqlc, pgx, GORM trade-offs), parameterized queries, context propagation, NULL handling, scanning, transactions and isolation, connection pool tuning, and migration tooling. Apply when adding repository code, refactoring SQL, or auditing for missing rows.Close()/QueryContext."
license: MIT
compatibility: "Designed for Claude Code or similar AI coding agents. Requires Go 1.21+. Library-agnostic: applies to database/sql, sqlx, sqlc, pgx."
allowed-tools: Read Edit Write Glob Grep Bash(go:*) Bash(golangci-lint:*)

Go Database

Go's `database/sql` is a thin, driver-pluggable foundation. Most projects layer one of `sqlx`, `sqlc`, or `pgx` on top for ergonomics. ORMs (GORM, ent) trade SQL visibility for one less line of code — a bad trade in production.

Core Rules

1. **SQL is the source of truth.** It is reviewed, version-controlled, and explained in code. Magic ORM queries are the opposite. 2. **Always parameterize.** `$1`/`?` placeholders, never string concatenation. The driver handles escaping; you cannot. 3. **Every I/O call takes `ctx`.** `QueryContext`, `ExecContext`, `GetContext`. No context = no timeout = a stuck handler. 4. **Distinguish "not found" from "error".** `errors.Is(err, sql.ErrNoRows)` is a domain signal, not a failure. 5. **Close rows.** `defer rows.Close()` immediately after `QueryContext`. Forgetting it leaks a pool connection. 6. **Configure the pool.** Default `MaxOpenConns` is unlimited — a runaway request rate exhausts the DB.

Library Decision

| Library | Best for | Struct scanning | Code-gen | |---|---|---|---| | `database/sql` | Minimal deps, multi-driver portability | Manual `Scan` | No | | `sqlx` | Sweetens `database/sql` ergonomics | `StructScan`, `Get`, `Select` | No | | `sqlc` | Type-safe queries derived from `.sql` files | Generated structs and funcs | Yes | | `pgx` (v5) | PostgreSQL-only, 30-50% faster, native types | `pgx.RowToStructByName` | No | | GORM / ent | **Avoid** in new code | Reflection | Yes |

**Why not ORMs.**

• Generated queries are unpredictable; N+1 problems are invisible at the call site.
• Hooks (`BeforeCreate`, `AfterUpdate`) create implicit state machines.
• Schema migrations entangle with application code.
• Learning the ORM API is harder than learning SQL, and the abstraction leaks at every interesting query.

> Read [references/library-tradeoffs.md](references/library-tradeoffs.md) when picking between sqlx, sqlc, and pgx for a new project.

Parameterized Queries

// VERY BAD — SQL injection.
q := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// Good — placeholder, driver-escaped.
err := db.GetContext(ctx, &u, "SELECT id, email FROM users WHERE email = $1", email)

Dynamic `IN` clauses

q, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil { return fmt.Errorf("expanding IN: %w", err) }
q = db.Rebind(q)                            // $1, $2, ... for Postgres
err = db.SelectContext(ctx, &users, q, args...)

Dynamic column names

Placeholders cannot stand in for identifiers. Use an allowlist:

allowed := map[string]bool{"name": true, "email": true, "created_at": true}
if !allowed[sortCol] {
    return fmt.Errorf("invalid sort column: %s", sortCol)
}
q := fmt.Sprintf("SELECT id, name FROM users ORDER BY %s", sortCol)

Context Propagation

// Bad — query runs to completion even if the client disconnected.
rows, err := db.Query("SELECT ...")

// Good — driver cancels the query on ctx.Done().
rows, err := db.QueryContext(ctx, "SELECT ...")

Every I/O method takes `ctx` first. Pass the request context through service → repository.

Error Handling

err := r.db.GetContext(ctx, &u, "SELECT ... WHERE id = $1", id)
switch {
case errors.Is(err, sql.ErrNoRows):
    return nil, ErrUserNotFound           // domain error
case err != nil:
    return nil, fmt.Errorf("get user %s: %w", id, err)
}

Always close rows

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil { return fmt.Errorf("query: %w", err) }
defer rows.Close()
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Name); err != nil { return fmt.Errorf("scan: %w", err) }
    users = append(users, u)
}
if err := rows.Err(); err != nil { return fmt.Errorf("iterate: %w", err) }

Three error checks (`Query`, `Scan`, `rows.Err()`) — missing the third hides truncated iteration.

NULL Columns and Scanning

type User struct {
    ID    string         `db:"id"`
    Email string         `db:"email"`
    Bio   *string        `db:"bio"`      // nullable → pointer
    Login sql.NullTime   `db:"last_login"`
}

Pointer fields work cleanly with JSON marshaling and with sqlx `StructScan`. Use `sql.NullXxx` when you need to distinguish "not set" from "zero value" at the SQL layer.

> Read [references/scanning.md](references/scanning.md) for sqlx tags, pgx `RowToStructByName`, and `sql.Null*` patterns.

Transactions and Isolation

Wrap related writes in `db.BeginTxx(ctx, &sql.TxOptions{Isolation: ...})`, rollback on every error path, commit only on success. Use `SELECT ... FOR UPDATE` when reading data you intend to modify — otherwise a concurrent writer races you. See [references/transactions.md](references/transactions.md) for isolation levels, retryable serialization errors, and the UnitOfWork pattern.

Connection Pool

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)

`MaxOpenConns` should be ≤ the DB server's `max_connections` divided by replica count, with headroom for migrations and other consumers.

Migrations

Do **not** generate migration SQL with this skill. Schema design needs human judgment about indexes, foreign keys, and dat