/golang-database

name: golang-database
description: "Comprehensive guide for Go database access — parameterized queries, struct scanning, NULLable columns, transactions, isolation levels, SELECT FOR UPDATE, connection pool, batch processing, context propagation, and migration tooling. Use when writing, reviewing, or debugging Golang code that interacts with PostgreSQL, MariaDB, MySQL, or SQLite; for database testing; or for questions about database/sql, sqlx, or pgx. Does NOT generate database schemas or migration SQL."
user-invocable: true
license: MIT
compatibility: Designed for Claude Code or similar AI coding agents, and for projects using Golang.
metadata:
  author: samber
  version: "1.2.1"
  openclaw:
    emoji: "🗄"
    homepage: https://github.com/samber/cc-skills-golang
    requires:
      bins:
        - go
    install: []
allowed-tools: Read Edit Write Glob Grep Bash(go:*) Bash(golangci-lint:*) Bash(git:*) Agent AskUserQuestion

**Persona:** You are a Go backend engineer who writes safe, explicit, and observable database code. You treat SQL as a first-class language — no ORMs, no magic — and you catch data integrity issues at the boundary, not deep in the application.

**Modes:**

• **Write mode** — generating new repository functions, query helpers, or transaction wrappers: follow the skill's sequential instructions; launch a background agent to grep for existing query patterns and naming conventions in the codebase before generating new code.
• **Review/debug mode** — auditing or debugging existing database code: use a sub-agent to scan for missing `rows.Close()`, un-parameterized queries, missing context propagation, and absent error checks in parallel with reading the business logic.

> **Community default.** A company skill that explicitly supersedes `samber/cc-skills-golang@golang-database` skill takes precedence.

Go Database Best Practices

Go's `database/sql` provides a solid foundation for database access. Use `sqlx` or `pgx` on top of it for ergonomics — never an ORM.

When using sqlx or pgx, refer to the library's official documentation and code examples for current API signatures.

Best Practices Summary

1. **Use sqlx or pgx, not ORMs** — ORMs hide SQL, generate unpredictable queries, and make debugging harder 2. Queries MUST use parameterized placeholders — NEVER concatenate user input into SQL strings 3. Context MUST be passed to all database operations — use `*Context` method variants (`QueryContext`, `ExecContext`, `GetContext`) 4. `sql.ErrNoRows` MUST be handled explicitly — distinguish "not found" from real errors using `errors.Is` 5. Rows MUST be closed after iteration — `defer rows.Close()` immediately after `QueryContext` calls 6. NEVER use `db.Query` for statements that don't return rows — `Query` returns `*Rows` which must be closed; if you forget, the connection leaks back to the pool. Use `db.Exec` instead 7. **Use transactions for multi-statement operations** — wrap related writes in `BeginTxx`/`Commit` 8. **Use `SELECT ... FOR UPDATE`** when reading data you intend to modify — prevents race conditions 9. **Set custom isolation levels** when default READ COMMITTED is insufficient (e.g., serializable for financial operations) 10. **Handle NULLable columns** with pointer fields (`*string`, `*int`) or `sql.NullXxx` types 11. Connection pool MUST be configured — `SetMaxOpenConns`, `SetMaxIdleConns`, `SetConnMaxLifetime`, `SetConnMaxIdleTime` 12. **Use external tools for migrations** — golang-migrate or Flyway, never hand-rolled or AI-generated migration SQL 13. **Batch operations in reasonable sizes** — not row-by-row (too many round trips), not millions at once (locks and memory) 14. **Never create or modify database schemas** — a schema that looks correct on toy data can create hotspots, lock contention, or missing indexes under real production load. Schema design requires understanding of data volumes, access patterns, and production constraints that AI does not have 15. **Avoid hidden SQL features** — do not rely on triggers, views, materialized views, stored procedures, or row-level security in application code

Library Choice

| Library | Best for | Struct scanning | PostgreSQL-specific | | --- | --- | --- | --- | | `database/sql` | Portability, minimal deps | Manual `Scan` | No | | `sqlx` | Multi-database projects | `StructScan` | No | | `pgx` | PostgreSQL (30-50% faster) | `pgx.RowToStructByName` | Yes (COPY, LISTEN, arrays) | | GORM/ent | **Avoid** | Magic | Abstracted away |

**Why NOT ORMs:**

• Unpredictable query generation — N+1 problems you cannot see in code
• Magic hooks and callbacks (BeforeCreate, AfterUpdate) make debugging harder
• Schema migrations coupled to application code
• Learning the ORM API is harder than learning SQL, and the abstraction leaks

Parameterized Queries

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

// ✓ Good — parameterized (PostgreSQL)
var user User
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = $1", email)

// ✓ Good — parameterized (MySQL)
err := db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE email = ?", email)

Dynamic IN clauses

query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil {
    return fmt.Errorf("building IN clause: %w", err)
}
query = db.Rebind(query) // adjust placeholders for your driver
err = db.SelectContext(ctx, &users, query, args...)

Dynamic column names

Never interpolate column names from user input. 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)
}
query := fmt.Sprintf("SELECT id, name, email FROM users ORDER BY %s", sortCol)

For more injection prevention patterns, see the `samber/cc-skills-golang@golang-security` skill.

Struct Scanning and NULLable Columns