Go + sqlx + Postgres

# CLAUDE.md — Go + sqlx + Postgres

## Stack

- **pgx** as the driver (use `stdlib` adapter for `database/sql`, or pgx native).
- **sqlx** for ergonomic struct scanning and named parameters.
- **golang-migrate** or **goose** for migrations. Pick one; commit to it.
- No ORM — sqlx hits the sweet spot of typed but explicit.

## Connection

```go
db, err := sqlx.Open("pgx", dsn)
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)
```

- Always set pool sizes. Defaults are unsuitable for any real load.
- One `*sqlx.DB` per app, passed via constructor injection.

## Queries

- Named parameters via `:name`:
  ```go
  rows, err := db.NamedQueryContext(ctx, `SELECT * FROM users WHERE id = :id`, map[string]any{"id": id})
  ```
- Struct scanning with `db` tags:
  ```go
  type User struct {
      ID    string `db:"id"`
      Email string `db:"email"`
  }
  err := db.GetContext(ctx, &user, `SELECT id, email FROM users WHERE id = $1`, id)
  ```
- Always pass `ctx`. A query without context can outlive its caller.

## Inserts & updates

- Use `INSERT ... RETURNING` to fetch generated IDs in one round trip.
- Bulk inserts: build a single multi-row `INSERT` or use `pgx.CopyFrom` for >1000 rows.
- For upserts: `INSERT ... ON CONFLICT DO UPDATE`. Don't roundtrip "select then insert".

## Transactions

```go
tx, err := db.BeginTxx(ctx, nil)
if err != nil { return err }
defer tx.Rollback() // no-op if committed
// ... do work ...
return tx.Commit()
```

- The `defer tx.Rollback()` pattern is safe: rollback after commit is a no-op.
- Pass `*sqlx.Tx` (or an interface that both `*sqlx.DB` and `*sqlx.Tx` satisfy) to functions that should work in either.

## Migrations

- Migrations are SQL files: `0001_create_users.up.sql`, `0001_create_users.down.sql`.
- Apply on deploy as a separate step, not at app boot.
- Idempotent SQL where possible (`CREATE TABLE IF NOT EXISTS`) — the migration tool tracks applied versions, but defensive SQL helps recover from bad states.

## Performance

- Add indexes for every WHERE/ORDER you actually query. Validate with `EXPLAIN (ANALYZE, BUFFERS)`.
- Connection acquisition is fast but not free — long-running transactions hold connections, which holds the pool.
- For read-heavy workloads, consider a separate read pool pointed at a replica.

## Don't

- Don't `db.Query` and forget to `Close` the rows. Use `defer rows.Close()` immediately after.
- Don't build SQL with `fmt.Sprintf` and user input — that's SQL injection. Always parameters.
- Don't ignore `rows.Err()` after the loop. The iteration error surfaces there.
- Don't share a `*sqlx.Tx` across goroutines. It's not safe.