go-database

Files (6)

• SKILL.md
• locking-and-retries.md · references/
• performance.md · references/
• query-patterns.md · references/
• testing.md · references/
• tooling.md · references/

SKILL.md (first lines)

# Go Database Patterns

Database access is where most Go services spend their complexity budget.
Get connection management, transactions, and query patterns right.

> Community default. A project skill or agent-config rule that explicitly
> supersedes `go-database` takes precedence over this one.

Detailed reference material, loaded on demand:

- `references/query-patterns.md` — full query/scan/rows patterns, null
  handling, N+1 avoidance, connection-leak examples.
- `references/tooling.md` — repository pattern implementation, sqlc
  annotated queries, migration tooling and rules.
- `references/locking-and-retries.md` — `FOR UPDATE`/`SKIP LOCKED`,
  optimistic versioning, retrying `40001`/`40P01`, constraint errors.
- `references/performance.md` — COPY and batched inserts, keyset
  pagination, pool statistics, `sql.Null[T]`.
- `references/testing.md` — choosing fakes vs a real database,
  testcontainers setup, isolation strategies.

Read a reference file only when the summary below is not enough.

## 1. Connection Management

Configure the pool explicitly — the default is unbounded connections:

```go
func OpenDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("open db: %w", err)
    }

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

    if err := db.PingContext(context.Background()); err != nil {
        return nil, fmt.Errorf("ping db: %w", err)
    }

    return db, nil
}
```

| Setting | Guideline |
|---|---|
| `MaxOpenConns` | Match your DB's max connections / number of app instances |
| `MaxIdleConns` | 40-50% of MaxOpenConns |
| `ConnMaxLifetime` | 5-10 minutes (prevents stale connections behind load balancers) |
| `ConnMaxIdleTime` | 1-2 minutes |

## 2. Query Rules

1. **Parameterized queries only** — string concatenation into SQL is an
   injection vulnerability, no exceptions.
2. **Always pass context** — use the `*Context` variants
   (`QueryContext`, `QueryRowContext`, `ExecContext`) so queries respect
   cancellation and timeouts.
3. **`defer rows.Close()`** immediately after the error check, and check
   `rows.Err()` after the iteration loop.
4. **Handle `sql.ErrNoRows` explicitly** with `errors.Is`, mapping it to
   a domain error like `ErrUserNotFound`.

```go
var user User
err := db.QueryRowContext(ctx,
    "SELECT id, name, email FROM users WHERE id = $1", id,
).Scan(&user.ID, &user.Name, &user.Email)

if errors.Is(err, sql.ErrNoRows) {
    return nil, ErrUserNotFound
}
if err != nil {
    return nil, fmt.Errorf("get user %s: %w", id, err)
}
```

Multi-row iteration patterns: `references/query-patterns.md`.

## 3. Transactions

Use a helper that guarantees rollback on error:

```go
func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) (err error) {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer func() {
        if p := recover(); p != nil {
            _ = tx.Rollback() // release the connection, then keep panicking
            panic(p)
        }
        if err != nil {
            if rbErr := tx.Rollback(); rbErr != nil && !errors.Is(rbErr, sql.ErrTxDone) {
                err = errors.Join(err, fmt.Errorf("rollback: %w", rbErr))
            }
        }
    }()

    if err := fn(tx); err != nil {
        return err
    }
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit tx: %w", err)
    }
    return nil
}
```

The deferred rollback covers a panic inside `fn`, which would otherwise
leave the transaction open and its connection out of the pool until the
context ends. `errors.Join` keeps both errors inspectable.

Set isolation explicitly for critical operations:

From eduardo-sl/go-agent-skills, MIT.

More from eduardo-sl/go-agent-skills