[
  {
    "id": 1,
    "name": "orm-vs-sqlx-pgx-recommendation",
    "description": "Tests whether the model recommends sqlx/pgx over ORMs and explains why ORMs are harmful",
    "prompt": "I'm starting a new Go web application with PostgreSQL. I need to set up database access. What library should I use? I've heard GORM is popular and has good docs. Can you set up the database layer for me?",
    "trap": "Without the skill, the model may recommend GORM since it's popular and widely known. The skill explicitly says to avoid ORMs and use sqlx or pgx.",
    "assertions": [
      {"id": "1.1", "text": "Recommends sqlx or pgx instead of GORM"},
      {"id": "1.2", "text": "Explains why ORMs are problematic (N+1 queries, unpredictable SQL generation, magic hooks, or debugging difficulty)"},
      {"id": "1.3", "text": "Recommends pgx specifically for PostgreSQL-only projects due to performance advantage (30-50% faster)"},
      {"id": "1.4", "text": "Does NOT set up GORM or ent as the primary database library"},
      {"id": "1.5", "text": "Mentions that learning the ORM API is harder than learning SQL or that ORMs hide SQL"}
    ]
  },
  {
    "id": 2,
    "name": "exec-vs-query-for-non-select",
    "description": "Tests the subtle rule that db.Query must NOT be used for statements that don't return rows",
    "prompt": "Write a Go function that deletes expired sessions from a PostgreSQL database. Use sqlx. The function should delete all sessions where expires_at < now() and return the number of deleted rows.",
    "trap": "The model may use db.QueryContext or db.Query for the DELETE statement, which returns *Rows that must be closed. The skill says to use db.Exec for statements that don't return rows.",
    "assertions": [
      {"id": "2.1", "text": "Uses ExecContext (not QueryContext or Query) for the DELETE statement"},
      {"id": "2.2", "text": "Uses the *Context variant (ExecContext, not Exec)"},
      {"id": "2.3", "text": "Passes ctx to the database call"},
      {"id": "2.4", "text": "Retrieves RowsAffected() from the result to return the count"},
      {"id": "2.5", "text": "Uses parameterized query (not string concatenation)"}
    ]
  },
  {
    "id": 3,
    "name": "nullable-column-handling",
    "description": "Tests whether pointer fields are preferred for NULLable columns over sql.NullXxx types",
    "prompt": "I have a PostgreSQL users table with columns: id (int), name (text NOT NULL), bio (text, nullable), deleted_at (timestamptz, nullable). Write a Go struct that I can use with sqlx for scanning and also marshal to JSON properly. The bio should be omitted from JSON when NULL, and deleted_at should appear as null in JSON.",
    "trap": "Without the skill, the model may use sql.NullString and sql.NullTime which require custom JSON marshaling. The skill recommends pointer fields as the cleanest approach.",
    "assertions": [
      {"id": "3.1", "text": "Uses pointer types (*string for bio, *time.Time for deleted_at) rather than sql.NullString/sql.NullTime"},
      {"id": "3.2", "text": "Includes db struct tags for sqlx (db:\"column_name\")"},
      {"id": "3.3", "text": "Includes json struct tags"},
      {"id": "3.4", "text": "Uses json:\"bio,omitempty\" for bio (omitted when NULL)"},
      {"id": "3.5", "text": "Uses json:\"deleted_at\" without omitempty for deleted_at (appears as null)"}
    ]
  },
  {
    "id": 4,
    "name": "connection-pool-configuration",
    "description": "Tests that connection pool settings are configured with all four parameters and reasonable values",
    "prompt": "Write a Go function that creates a new sqlx database connection to PostgreSQL and returns *sqlx.DB. Just the basic setup, nothing fancy.",
    "trap": "Without the skill, the model often returns the *sqlx.DB without configuring the connection pool. The skill requires all four pool settings.",
    "assertions": [
      {"id": "4.1", "text": "Calls SetMaxOpenConns on the database connection"},
      {"id": "4.2", "text": "Calls SetMaxIdleConns on the database connection"},
      {"id": "4.3", "text": "Calls SetConnMaxLifetime on the database connection"},
      {"id": "4.4", "text": "Calls SetConnMaxIdleTime on the database connection"},
      {"id": "4.5", "text": "MaxIdleConns is less than or equal to MaxOpenConns"}
    ]
  },
  {
    "id": 5,
    "name": "rows-close-and-err-check",
    "description": "Tests proper handling of rows iteration: defer Close and rows.Err() check after loop",
    "prompt": "Write a Go function using sqlx that lists all active users (active = true) from a users table. Return a slice of User structs. Use db.QueryContext, not db.SelectContext — I want to handle scanning manually for learning purposes.",
    "trap": "Without the skill, the model may forget to defer rows.Close() or skip the rows.Err() check after iteration, both of which are required.",
    "assertions": [
      {"id": "5.1", "text": "Calls defer rows.Close() immediately after the QueryContext call (before the loop)"},
      {"id": "5.2", "text": "Checks rows.Err() after the for rows.Next() loop completes"},
      {"id": "5.3", "text": "Returns the error from rows.Err() if non-nil"},
      {"id": "5.4", "text": "Uses QueryContext (not Query) with a context parameter"},
      {"id": "5.5", "text": "Checks the error returned by QueryContext before proceeding"}
    ]
  },
  {
    "id": 6,
    "name": "errnorows-handling-pattern",
    "description": "Tests proper sql.ErrNoRows handling with domain error translation",
    "prompt": "Write a Go function GetUserByEmail(ctx context.Context, db *sqlx.DB, email string) that returns the user or an appropriate error when not found. Use sqlx.GetContext.",
    "trap": "Without the skill, the model may return the raw sql.ErrNoRows error directly or not distinguish it from other errors. The skill requires translating to a domain error.",
    "assertions": [
      {"id": "6.1", "text": "Uses errors.Is(err, sql.ErrNoRows) to check for not-found"},
      {"id": "6.2", "text": "Returns a domain-specific error (e.g. ErrUserNotFound) when no rows, NOT the raw sql.ErrNoRows"},
      {"id": "6.3", "text": "Wraps non-ErrNoRows errors with context using fmt.Errorf and %w"},
      {"id": "6.4", "text": "Uses GetContext (not Get) with the ctx parameter"},
      {"id": "6.5", "text": "Uses parameterized query placeholder ($1 or ?) not string concatenation"}
    ]
  },
  {
    "id": 7,
    "name": "transaction-with-defer-rollback",
    "description": "Tests the correct transaction pattern: BeginTxx, defer Rollback, Commit",
    "prompt": "Write a Go function TransferFunds(ctx, db, fromAccountID, toAccountID string, amount int) that transfers money between two accounts atomically. Use sqlx.",
    "trap": "Without the skill, the model may forget defer tx.Rollback(), use wrong isolation level for financial operations, or miss SELECT FOR UPDATE.",
    "assertions": [
      {"id": "7.1", "text": "Uses BeginTxx (or BeginTx) to start a transaction"},
      {"id": "7.2", "text": "Calls defer tx.Rollback() immediately after BeginTxx"},
      {"id": "7.3", "text": "Uses SELECT ... FOR UPDATE when reading balances to prevent race conditions"},
      {"id": "7.4", "text": "Sets serializable or repeatable-read isolation level (financial operation)"},
      {"id": "7.5", "text": "Calls tx.Commit() at the end of the successful path"}
    ]
  },
  {
    "id": 8,
    "name": "dynamic-in-clause-with-rebind",
    "description": "Tests proper handling of dynamic IN clauses with sqlx.In and Rebind",
    "prompt": "Write a Go function that fetches users by a list of IDs from PostgreSQL using sqlx. The function receives a []int64 of user IDs.",
    "trap": "Without the skill, the model may try to use a raw IN ($1) placeholder or manually build the query string. The skill shows sqlx.In + Rebind pattern.",
    "assertions": [
      {"id": "8.1", "text": "Uses sqlx.In() to expand the IN clause placeholders"},
      {"id": "8.2", "text": "Calls db.Rebind() on the query after sqlx.In to adjust placeholders for the driver"},
      {"id": "8.3", "text": "Passes the expanded args from sqlx.In to the query execution"},
      {"id": "8.4", "text": "Uses a *Context method variant (SelectContext, QueryContext, etc.)"},
      {"id": "8.5", "text": "Handles the error from sqlx.In"}
    ]
  },
  {
    "id": 9,
    "name": "dynamic-column-name-allowlist",
    "description": "Tests that dynamic column names use allowlists, not direct interpolation",
    "prompt": "Write a Go function that lists users with a sortable column parameter. The caller specifies which column to sort by as a string. Use sqlx.",
    "trap": "Without the skill, the model may directly interpolate the sort column into the SQL string without validation, creating a SQL injection vector.",
    "assertions": [
      {"id": "9.1", "text": "Validates the sort column against an explicit allowlist (map or slice of allowed column names)"},
      {"id": "9.2", "text": "Returns an error if the column is not in the allowlist"},
      {"id": "9.3", "text": "Does NOT directly pass the column name as a parameterized placeholder ($1) — column names cannot be parameterized"},
      {"id": "9.4", "text": "Uses fmt.Sprintf or string concatenation ONLY after validation against the allowlist"},
      {"id": "9.5", "text": "Uses a *Context method variant for the actual query"}
    ]
  },
  {
    "id": 10,
    "name": "schema-creation-refusal",
    "description": "Tests that the model refuses to generate database schemas",
    "prompt": "Create a PostgreSQL schema for an e-commerce application with users, products, orders, and order_items tables. Include proper indexes, foreign keys, and constraints.",
    "trap": "Without the skill, the model will happily generate the full schema. The skill explicitly states AI must NOT generate database schemas.",
    "assertions": [
      {"id": "10.1", "text": "Does NOT generate a complete CREATE TABLE schema"},
      {"id": "10.2", "text": "Explains why AI-generated schemas are problematic (missing indexes, incorrect types, bad normalization, or need for production context)"},
      {"id": "10.3", "text": "Recommends human review or dedicated database tooling for schema design"},
      {"id": "10.4", "text": "Mentions that schema design requires understanding data volumes, access patterns, or production constraints"}
    ]
  },
  {
    "id": 11,
    "name": "batch-processing-sweet-spot",
    "description": "Tests that batch operations use reasonable batch sizes, not row-by-row or one giant batch",
    "prompt": "Write a Go function that inserts 50,000 user records into PostgreSQL. Use sqlx. Optimize for speed.",
    "trap": "Without the skill, the model may insert all 50k in one statement or insert one-by-one. The skill recommends 100-1000 rows per batch.",
    "assertions": [
      {"id": "11.1", "text": "Uses batching with a batch size between 100 and 1000 rows"},
      {"id": "11.2", "text": "Does NOT insert all 50,000 rows in a single statement"},
      {"id": "11.3", "text": "Does NOT insert one row at a time in a loop"},
      {"id": "11.4", "text": "Uses NamedExecContext or a multi-row INSERT pattern"},
      {"id": "11.5", "text": "Handles errors per batch with context about which batch failed"}
    ]
  },
  {
    "id": 12,
    "name": "cursor-pagination-over-offset",
    "description": "Tests that cursor-based pagination is recommended over OFFSET for large datasets",
    "prompt": "Write a Go function that paginates through a large events table (millions of rows) ordered by created_at. The function should support page navigation.",
    "trap": "Without the skill, the model commonly uses OFFSET/LIMIT which degrades performance on deep pages. The skill requires cursor-based pagination.",
    "assertions": [
      {"id": "12.1", "text": "Uses cursor-based pagination (WHERE created_at > $1) instead of OFFSET"},
      {"id": "12.2", "text": "Explains why OFFSET is problematic (re-scans skipped rows, O(offset+limit))"},
      {"id": "12.3", "text": "Uses LIMIT with ORDER BY for the page size"},
      {"id": "12.4", "text": "Returns a cursor value (e.g. the last created_at) for the next page"},
      {"id": "12.5", "text": "Uses parameterized queries for the cursor value"}
    ]
  },
  {
    "id": 13,
    "name": "integration-test-with-build-tags",
    "description": "Tests proper database integration test setup with build tags and transaction rollback",
    "prompt": "Write integration tests for a Go repository layer that interacts with PostgreSQL. I want to test that Create and GetByID work correctly together.",
    "trap": "Without the skill, the model may not use build tags or may not wrap tests in transactions for cleanup. The skill requires both.",
    "assertions": [
      {"id": "13.1", "text": "Uses //go:build integration build tag to separate from unit tests"},
      {"id": "13.2", "text": "Uses transaction-based test isolation (begin tx in setup, rollback in teardown)"},
      {"id": "13.3", "text": "Does NOT test against a production database — uses a test DSN or testcontainers"},
      {"id": "13.4", "text": "Uses testify/suite or a similar setup/teardown pattern"},
      {"id": "13.5", "text": "Tests actual SQL correctness (not mocked — this is integration)"}
    ]
  },
  {
    "id": 14,
    "name": "avoid-hidden-sql-features",
    "description": "Tests that the model avoids triggers, views, stored procedures in application code",
    "prompt": "I want to automatically update an 'updated_at' column every time a row is modified in my users table. Should I create a PostgreSQL trigger for this? Also, I have a complex query that joins 5 tables — should I create a database view?",
    "trap": "Without the skill, the model will likely recommend triggers and views as standard PostgreSQL features. The skill says to avoid hidden SQL features.",
    "assertions": [
      {"id": "14.1", "text": "Advises against using triggers for updated_at in application code"},
      {"id": "14.2", "text": "Recommends setting updated_at explicitly in Go code instead"},
      {"id": "14.3", "text": "Advises against using views for the complex query"},
      {"id": "14.4", "text": "Explains that hidden SQL features create invisible side effects or make debugging harder"},
      {"id": "14.5", "text": "Recommends keeping SQL explicit and visible in Go code"}
    ]
  },
  {
    "id": 15,
    "name": "pgx-copy-for-bulk-postgres",
    "description": "Tests knowledge of pgx COPY protocol for maximum PostgreSQL bulk insert throughput",
    "prompt": "I need to insert 100,000 rows into PostgreSQL as fast as possible. I'm already using pgx. What's the fastest approach?",
    "trap": "Without the skill, the model may suggest multi-row INSERT statements. The skill teaches pgx.CopyFrom using the binary COPY protocol for maximum throughput.",
    "assertions": [
      {"id": "15.1", "text": "Recommends pgx.CopyFrom using the COPY protocol"},
      {"id": "15.2", "text": "Shows pgx.CopyFromRows or pgx.CopyFromSlice usage"},
      {"id": "15.3", "text": "Mentions that COPY is significantly faster than multi-row INSERT"},
      {"id": "15.4", "text": "Uses pgx.Identifier for the table name"},
      {"id": "15.5", "text": "Still recommends batching if the dataset is extremely large (to avoid memory issues)"}
    ]
  }
]

# Database Performance

## Connection Pool Sizing

### Configuration

```go
db, err := sqlx.Connect("postgres", dsn)
if err != nil {
    return fmt.Errorf("connecting to database: %w", err)
}

db.SetMaxOpenConns(25)                  // total connections (match your DB capacity)
db.SetMaxIdleConns(10)                  // keep connections warm, reduce handshake overhead
db.SetConnMaxLifetime(5 * time.Minute)  // recycle connections (DNS changes, server restarts)
db.SetConnMaxIdleTime(1 * time.Minute)  // release idle connections back to the pool
```

| Setting | Too low | Too high |
| --- | --- | --- |
| `MaxOpenConns` | Requests queue waiting for conn | DB overwhelmed, context switches |
| `MaxIdleConns` | Cold connections, slow queries | Wasted memory holding idle conns |
| `ConnMaxLifetime` | Frequent reconnection overhead | Stale connections after failover |
| `ConnMaxIdleTime` | Same as MaxIdleConns too low | Idle conns consume server memory |

### Monitoring

Check pool stats in production to detect exhaustion:

```go
stats := db.Stats()
slog.Info("db pool",
    "open", stats.OpenConnections,
    "in_use", stats.InUse,
    "idle", stats.Idle,
    "wait_count", stats.WaitCount,        // total waits for a connection
    "wait_duration", stats.WaitDuration,  // total wait time
)
```

If `WaitCount` keeps climbing, increase `MaxOpenConns` or optimize slow queries.

### Prometheus Metrics

Use a custom Prometheus collector to export pool metrics on-demand (scales to multiple pools automatically):

```go
type DBCollector struct {
    pools map[string]*sqlx.DB
}

func NewDBCollector(pools map[string]*sqlx.DB) *DBCollector {
    return &DBCollector{pools: pools}
}

func (c *DBCollector) Describe(ch chan<- *prometheus.Desc) {
    ch <- prometheus.NewDesc("db_open_connections", "Number of open connections", []string{"pool"}, nil)
    ch <- prometheus.NewDesc("db_in_use_connections", "Connections currently in use", []string{"pool"}, nil)
    ch <- prometheus.NewDesc("db_idle_connections", "Idle connections in pool", []string{"pool"}, nil)
    ch <- prometheus.NewDesc("db_total_latency_seconds", "Total latency for a connection", []string{"pool"}, nil)
}

func (c *DBCollector) Collect(ch chan<- prometheus.Metric) {
    for poolName, db := range c.pools {
        stats := db.Stats()

        ch <- prometheus.MustNewConstMetric(
            prometheus.NewDesc("db_open_connections", "Number of open connections", []string{"pool"}, nil),
            prometheus.GaugeValue, float64(stats.OpenConnections), poolName)

        ch <- prometheus.MustNewConstMetric(
            prometheus.NewDesc("db_in_use_connections", "Connections currently in use", []string{"pool"}, nil),
            prometheus.GaugeValue, float64(stats.InUse), poolName)

        ch <- prometheus.MustNewConstMetric(
            prometheus.NewDesc("db_idle_connections", "Idle connections in pool", []string{"pool"}, nil),
            prometheus.GaugeValue, float64(stats.Idle), poolName)

        ch <- prometheus.MustNewConstMetric(
            prometheus.NewDesc("db_wait_duration_seconds_total", "Total connection wait duration", []string{"pool"}, nil),
            prometheus.CounterValue, stats.WaitDuration.Seconds(), poolName)
    }
}

func init() {
    pools := map[string]*sqlx.DB{
        "primary": mainDB,
        "replica": replicaDB,
    }
    prometheus.MustRegister(NewDBCollector(pools))
}
```

**Collector advantages:**

- Metrics are collected on-demand during scrapes (no background goroutine)
- Always returns current state (no stale data between scrapes)
- Scales to multiple pools automatically
- Lower memory footprint (no metric state in memory)

**Alert thresholds:**

- Open connections approaching `MaxOpenConns` → risk of request queuing
- Wait count climbing steadily → pool is exhausted, increase `MaxOpenConns`
- Idle connections too high → reduce `MaxIdleConns` or lower `ConnMaxIdleTime`

## Batch Processing

Avoid two extremes:

- **Row-by-row** — N round trips for N rows, extremely slow
- **One giant batch** — locks tables, consumes memory, can timeout and block other queries

### Sweet spot: 100–1,000 rows per batch

Adjust based on row size and database load. Larger rows → smaller batches.

### Batch INSERT with sqlx

```go
func insertUsersBatch(ctx context.Context, db *sqlx.DB, users []User) error {
    const batchSize = 500
    for i := 0; i < len(users); i += batchSize {
        end := min(i+batchSize, len(users))
        batch := users[i:end]

        _, err := db.NamedExecContext(ctx, `INSERT INTO users (name, email) VALUES (:name, :email)`, batch)
        if err != nil {
            return fmt.Errorf("inserting users batch %d-%d: %w", i, end, err)
        }
    }
    return nil
}
```

### Bulk INSERT with pgx (PostgreSQL COPY protocol)

For maximum throughput on PostgreSQL, use `pgx.CopyFrom` which uses the binary COPY protocol — significantly faster than multi-row INSERT:

```go
rows := make([][]any, len(users))
for i, u := range users {
    rows[i] = []any{u.Name, u.Email}
}
_, err := pool.CopyFrom(ctx,
    pgx.Identifier{"users"},
    []string{"name", "email"},
    pgx.CopyFromRows(rows),
)
```

### Cursor-based pagination (avoid OFFSET)

For reading large datasets, use cursor-based pagination instead of `OFFSET`. OFFSET re-scans skipped rows, getting slower as you paginate deeper:

```go
// ✗ Bad — OFFSET re-scans rows, O(offset + limit)
SELECT * FROM events ORDER BY created_at LIMIT 100 OFFSET 10000

// ✓ Good — cursor-based, O(limit) regardless of depth
SELECT * FROM events WHERE created_at > $1 ORDER BY created_at LIMIT 100
```

## Indexing Strategy

**Never create or drop indexes yourself.** Index changes affect production query performance and write throughput. Always suggest to the developer and let them decide.

### Use SQL MCP to check existing indexes

When a SQL MCP tool is available, query the database to check existing indexes before suggesting new ones:

```sql
-- PostgreSQL: list indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Check for unused indexes (low scan count relative to writes)
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY idx_scan;
```

### When to suggest adding indexes

- Foreign key columns (PostgreSQL does NOT auto-index foreign keys)
- Columns frequently used in `WHERE`, `JOIN`, or `ORDER BY`
- Composite indexes for multi-column queries (leftmost column is most selective)
- Partial indexes for filtered queries (`WHERE active = true`)

### When to suggest removing indexes

- Indexes with near-zero `idx_scan` count (nobody reads them)
- Duplicate indexes (same columns in same order)
- Indexes on write-heavy tables that slow down INSERT/UPDATE/DELETE
- Wide composite indexes where a narrower one would suffice

Always present findings as suggestions with data (scan counts, table size), never execute DDL yourself.

## Query Performance Tips

- **`EXPLAIN ANALYZE`** before optimizing — measure, don't guess
- **List columns explicitly** — avoid `SELECT *`, it fetches unnecessary data and breaks struct scanning when schema changes
- **Use `LIMIT`** for pagination, always with an `ORDER BY`
- **Prefer `EXISTS` over `COUNT`** for existence checks — `EXISTS` stops at the first match
- **Avoid N+1 queries** — use `JOIN` or batch `WHERE id IN (...)` instead of querying in a loop
- **Suggest improvements, never execute them** — performance changes (indexes, query rewrites, configuration) need human review in context of production data and workload patterns

Batch operations SHOULD use 100–1,000 rows per batch — adjust based on row size and database load. Cursor-based pagination MUST replace `OFFSET` for large datasets — the cursor column MUST be chosen based on actual indexes (e.g., `created_at`, `user_id`). NEVER create indexes blindly — check existing indexes, measure with `EXPLAIN ANALYZE`, and present findings as suggestions. N+1 queries MUST be eliminated — use `JOIN` or batch `WHERE id IN (...)`.

→ See `samber/cc-skills-golang@golang-observability` skill for database metrics and query monitoring. → See `samber/cc-skills@promql-cli` skill for querying pool metrics (`db_open_connections`, `db_in_use_connections`, `db_idle_connections`) via CLI.

# Struct Scanning and NULLable Columns

## Struct Scanning with sqlx

Tag struct fields with `db:"column_name"` for sqlx:

```go
type User struct {
    ID        int64      `db:"id"`
    Name      string     `db:"name"`
    Email     string     `db:"email"`
    DeletedAt *time.Time `db:"deleted_at"` // NULLable
}

// Single row
var user User
err := db.GetContext(ctx, &user, "SELECT id, name, email, deleted_at FROM users WHERE id = $1", id)

// Multiple rows
var users []User
err := db.SelectContext(ctx, &users, "SELECT id, name, email, deleted_at FROM users WHERE active = true")
```

## Struct Scanning with pgx

With pgx (v5+), use `pgx.CollectRows` for automatic struct mapping:

```go
rows, err := pool.Query(ctx, "SELECT id, name, email FROM users WHERE active = true")
if err != nil {
    return fmt.Errorf("querying users: %w", err)
}
users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])
```

## JSON Marshaling

Struct tags for both database and JSON work together. Pointer fields marshal to `null` in JSON when NULL in the database:

```go
type User struct {
    ID        int64      `db:"id"         json:"id"`
    Name      string     `db:"name"       json:"name"`
    Email     string     `db:"email"      json:"email"`
    Bio       *string    `db:"bio"        json:"bio,omitempty"` // NULL → omitted in JSON
    DeletedAt *time.Time `db:"deleted_at" json:"deleted_at"`    // NULL → null in JSON
}
```

## NULLable Columns

Three approaches, from most to least recommended:

**1. Pointer fields (recommended)** — clean, works with JSON marshaling:

```go
type User struct {
    ID        int64      `db:"id"    json:"id"`
    Name      string     `db:"name"  json:"name"`
    DeletedAt *time.Time `db:"deleted_at" json:"deleted_at"` // nil when NULL
}
// Check: if user.DeletedAt != nil { ... }
```

**2. `sql.NullXxx` types** or `sql.Null[T]` generic — explicit but verbose, requires custom JSON marshaling:

```go
type User struct {
    ID        int64          `db:"id"`
    Bio       sql.NullString `db:"bio"`
}
// Check: if user.Bio.Valid { use(user.Bio.String) }
```

**3. `COALESCE` in SQL** — moves NULL handling to the query:

```sql
SELECT id, COALESCE(bio, '') AS bio FROM users WHERE id = $1
```

# Testing Database Code

## Unit Tests with Mocks

Define a repository interface so business logic can be tested without a database. Mock the interface with `testify/mock`:

```go
// Repository interface — the contract
type UserRepository interface {
    GetByID(ctx context.Context, id int64) (*User, bool, error)
    Create(ctx context.Context, user *User) error
}

// Production implementation
type pgUserRepository struct {
    db *sqlx.DB
}

func (r *pgUserRepository) GetByID(ctx context.Context, id int64) (*User, bool, error) {
    var user User
    err := r.db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, false, nil
        }
        return nil, false, fmt.Errorf("querying user %d: %w", id, err)
    }
    return &user, true, nil
}
```

### Mock for service-layer tests

```go
type mockUserRepo struct {
    mock.Mock
}

func (m *mockUserRepo) GetByID(ctx context.Context, id int64) (*User, error) {
    args := m.Called(ctx, id)
    if args.Get(0) == nil {
        return nil, args.Error(1)
    }
    return args.Get(0).(*User), args.Error(1)
}

func TestUserService_GetUser(t *testing.T) {
    repo := new(mockUserRepo)
    svc := NewUserService(repo)

    expected := &User{ID: 1, Name: "Alice", Email: "alice@example.com"}
    repo.On("GetByID", mock.Anything, int64(1)).Return(expected, nil)

    user, err := svc.GetUser(context.Background(), 1)
    require.NoError(t, err)
    assert.Equal(t, expected, user)
    repo.AssertExpectations(t)
}

func TestUserService_GetUser_NotFound(t *testing.T) {
    repo := new(mockUserRepo)
    svc := NewUserService(repo)

    repo.On("GetByID", mock.Anything, int64(999)).Return(nil, ErrUserNotFound)

    user, err := svc.GetUser(context.Background(), 999)
    assert.Nil(t, user)
    assert.ErrorIs(t, err, ErrUserNotFound)
}
```

Unit tests verify business logic, not SQL correctness. They run fast and without external dependencies.

## sqlmock for Query-Level Testing

When you need to verify exact SQL without a real database, use [DATA-DOG/go-sqlmock](https://github.com/DATA-DOG/go-sqlmock):

```go
func TestGetByID_sqlmock(t *testing.T) {
    db, mock, err := sqlmock.New()
    require.NoError(t, err)
    defer db.Close()

    sqlxDB := sqlx.NewDb(db, "postgres")
    repo := &pgUserRepository{db: sqlxDB}

    rows := sqlmock.NewRows([]string{"id", "name", "email"}).
        AddRow(1, "Alice", "alice@example.com")
    mock.ExpectQuery("SELECT id, name, email FROM users WHERE id = \\$1").
        WithArgs(1).
        WillReturnRows(rows)

    user, err := repo.GetByID(context.Background(), 1)
    require.NoError(t, err)
    assert.Equal(t, "Alice", user.Name)
    assert.NoError(t, mock.ExpectationsWereMet())
}
```

sqlmock is useful for verifying query structure and error handling paths, but it does not validate that your SQL is correct against a real database schema.

## Integration Tests

Integration tests run against a real database. Gate them with build tags so `go test ./...` skips them by default:

```go
//go:build integration

package repository_test

import (
    "testing"
    "github.com/stretchr/testify/suite"
)

type UserRepoSuite struct {
    suite.Suite
    db *sqlx.DB
    tx *sqlx.Tx
}

func (s *UserRepoSuite) SetupSuite() {
    dsn := os.Getenv("TEST_DATABASE_URL") // e.g., postgres://test:test@localhost:5432/testdb?sslmode=disable
    db, err := sqlx.Connect("postgres", dsn)
    s.Require().NoError(err)
    s.db = db
    // Run migrations here if needed
}

func (s *UserRepoSuite) TearDownSuite() {
    s.db.Close()
}

func (s *UserRepoSuite) SetupTest() {
    tx, err := s.db.Beginx()
    s.Require().NoError(err)
    s.tx = tx
}

func (s *UserRepoSuite) TearDownTest() {
    s.tx.Rollback() // rolls back all changes — each test starts clean
}

func (s *UserRepoSuite) TestCreateAndGet() {
    repo := NewUserRepository(s.tx)
    user := &User{Name: "Alice", Email: "alice@example.com"}

    err := repo.Create(context.Background(), user)
    s.Require().NoError(err)
    s.NotZero(user.ID)

    got, err := repo.GetByID(context.Background(), user.ID)
    s.Require().NoError(err)
    s.Equal("Alice", got.Name)
}

func TestUserRepoSuite(t *testing.T) {
    suite.Run(t, new(UserRepoSuite))
}
```

Run integration tests:

```bash
go test -tags=integration -v ./internal/repository/...
```

### Test database with testcontainers-go

For CI environments without a pre-existing database:

```go
func (s *UserRepoSuite) SetupSuite() {
    ctx := context.Background()
    container, err := postgres.Run(ctx, "postgres:16-alpine",
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
        testcontainers.WithWaitStrategy(
            wait.ForLog("database system is ready to accept connections").
                WithOccurrence(2).
                WithStartupTimeout(30*time.Second),
        ),
    )
    s.Require().NoError(err)
    s.container = container

    connStr, err := container.ConnectionString(ctx, "sslmode=disable")
    s.Require().NoError(err)
    s.db, err = sqlx.Connect("postgres", connStr)
    s.Require().NoError(err)
}
```

## What to Test

| What                      | Unit test (mock) | Integration test |
| ------------------------- | :--------------: | :--------------: |
| Business logic            |        ✓         |                  |
| SQL correctness           |                  |        ✓         |
| Error paths (not found)   |        ✓         |        ✓         |
| Transaction boundaries    |                  |        ✓         |
| NULL handling round-trips |                  |        ✓         |
| Constraint violations     |                  |        ✓         |
| Query performance         |                  | ✓ (with EXPLAIN) |

Unit tests MUST use mocks (interface mocks or sqlmock) — no real database connections. Integration tests MUST use build tags (`//go:build integration`) to separate from unit tests. Integration tests SHOULD use testcontainers-go for reproducible database environments in CI. NEVER test against production databases.

→ See `samber/cc-skills-golang@golang-testing` skill for general test patterns and CI configuration.

# Transactions, Isolation Levels, and Locking

## Basic transaction pattern

```go
tx, err := db.BeginTxx(ctx, nil) // default isolation (READ COMMITTED)
if err != nil {
    return fmt.Errorf("beginning transaction: %w", err)
}
defer tx.Rollback() // no-op if already committed

// ... execute queries using tx ...

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

## Custom isolation level

```go
tx, err := db.BeginTxx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable, // strongest guarantee
})
```

| Level | Use when |
| --- | --- |
| `LevelReadCommitted` | Default — good for most operations |
| `LevelRepeatableRead` | Need consistent reads within a transaction |
| `LevelSerializable` | Financial operations, inventory, anything with strict consistency |

## SELECT FOR UPDATE — prevent race conditions

```go
var balance int
err := tx.GetContext(ctx, &balance, "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", accountID)
// Row is locked until tx.Commit() or tx.Rollback()
```

Use `FOR UPDATE` when you read a value, compute something from it, and then write it back. Without the lock, concurrent transactions can read stale data.

## Locking variants

| Clause | Effect |
| --- | --- |
| `FOR UPDATE` | Locks rows for write — other transactions block on same rows |
| `FOR UPDATE NOWAIT` | Same, but fails immediately instead of waiting |
| `FOR SHARE` | Locks rows for read — prevents writes but allows other reads |