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

sqlx

pgx

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

database/sql

Scan

sqlx

StructScan

pgx

pgx.RowToStructByName

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

Struct Scanning and NULLable Columns

Use

db:"column_name"

pgx.CollectRows

pgx.RowToStructByName

*string

*time.Time

Error Handling

func GetUser(id string) (*User, error) {
    var user User

    err := db.GetContext(ctx, &user, "SELECT id, name FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrUserNotFound // translate to domain error
        }
        return nil, fmt.Errorf("querying user %s: %w", id, err)
    }

    return &user, nil
}

or:

func GetUser(id string) (u *User, exists bool, err error) {
    var user User

    err := db.GetContext(ctx, &user, "SELECT id, name FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, false, nil // "no user" is not a technical error, but a domain error
        }
        return nil, false, fmt.Errorf("querying user %s: %w", id, err)
    }

    return &user, true, nil
}

Always close rows

rows, err := db.QueryContext(ctx, "SELECT id, name FROM users")
if err != nil {
    return fmt.Errorf("querying users: %w", err)
}
defer rows.Close() // prevents connection leaks

for rows.Next() {
    // ...
}
if err := rows.Err(); err != nil { // always check after iteration
    return fmt.Errorf("iterating users: %w", err)
}

Common database error patterns

errors.Is(err, sql.ErrNoRows)

err != nil

db.PingContext

40001

errors.Is(err, context.Canceled)

Context Propagation

Always use the

*Context

// ✗ Bad — no context, query runs until completion even if client disconnects
db.Query("SELECT ...")

// ✓ Good — respects context cancellation and timeouts
db.QueryContext(ctx, "SELECT ...")

For context patterns in depth, see the

samber/cc-skills-golang@golang-context

Transactions, Isolation Levels, and Locking

For transaction patterns, isolation levels,

SELECT FOR UPDATE

Connection Pool

db.SetMaxOpenConns(25)              // limit total connections
db.SetMaxIdleConns(10)              // keep warm connections ready
db.SetConnMaxLifetime(5 * time.Minute)  // recycle stale connections
db.SetConnMaxIdleTime(1 * time.Minute)  // close idle connections faster

For sizing guidance and formulas, see Database Performance.

Migrations

Use an external migration tool. Schema changes require human review with understanding of data volumes, existing indexes, foreign keys, and production constraints.

Recommended tools:

• golang-migrate — CLI + Go library, supports all major databases
• Flyway — JVM-based, widely used in enterprise environments
• Atlas — modern, declarative schema management

Migration SQL should be written and reviewed by humans, versioned in source control, and applied through CI/CD pipelines.

Avoid Hidden SQL Features

Do not rely on triggers, views, materialized views, stored procedures, or row-level security in application code — they create invisible side effects and make debugging impossible. Keep SQL explicit and visible in Go where it can be tested and version-controlled.

Schema Creation

This skill does NOT cover schema creation. AI-generated schemas are often subtly wrong — missing indexes, incorrect column types, bad normalization, or missing constraints. Schema design requires understanding data volumes, access patterns, query profiles, and business constraints. Use dedicated database tooling and human review.

Deep Dives

• Transactions — Transaction boundaries, isolation levels, deadlock prevention,

  SELECT FOR UPDATE

• Testing Database Code — Mock connections, integration tests with containers, fixtures, schema setup/teardown
• Database Performance — Connection pool sizing, batch processing, indexing strategy, query optimization
• Struct Scanning — Struct tags, NULLable column handling, JSON marshaling patterns

Cross-References

• → See

  samber/cc-skills-golang@golang-security

  skill for SQL injection prevention patterns
• → See

  samber/cc-skills-golang@golang-context

  skill for context propagation to database operations
• → See

  samber/cc-skills-golang@golang-error-handling

  skill for database error wrapping patterns
• → See

  samber/cc-skills-golang@golang-testing

  skill for database integration test patterns

References

• database/sql tutorial
• sqlx
• pgx
• golang-migrate