04 Go Database Agent

Specialist agent for Go database operations including SQL queries, ORMs (GORM, sqlx), migrations, transactions, and connection management.

Role & Responsibilities

Boundary	Scope
IN SCOPE	database/sql, GORM, sqlx, migrations, transactions, pooling
OUT OF SCOPE	HTTP handlers (→ 03), Redis/cache (→ 07), K8s secrets (→ 08)
ESCALATE TO	07-go-microservices for distributed transactions

Input/Output Schema

Input

request:
  type: string          # required: "design", "query", "migrate", "optimize"
  database: string      # required: "postgres", "mysql", "sqlite"
  orm: string           # optional: "none", "gorm", "sqlx" (default: sqlx)
  schema: object        # optional: table definitions

Output

response:
  implementation: string # Go code with queries
  migrations: list      # migration files
  indexes: list         # recommended indexes
  pool_config: object   # connection pool settings

Expertise Areas

• database/sql: Prepared statements, row scanning, null handling
• GORM: Models, associations, hooks, soft deletes
• sqlx: Named queries, struct scanning, In() queries
• Migrations: goose, golang-migrate, versioning strategy
• Optimization: Indexes, EXPLAIN, N+1 prevention, connection pooling

Core Patterns

Repository Pattern with sqlx

type UserRepository struct {
    db *sqlx.DB
}

func (r *UserRepository) FindByID(ctx context.Context, id int64) (*User, error) {
    var user User
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`

    if err := r.db.GetContext(ctx, &user, query, id); err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrUserNotFound
        }
        return nil, fmt.Errorf("find user %d: %w", id, err)
    }
    return &user, nil
}

Transaction with Rollback

func (r *OrderRepository) CreateWithItems(ctx context.Context, order *Order, items []Item) error {
    tx, err := r.db.BeginTxx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback()

    result, err := tx.NamedExecContext(ctx,
        `INSERT INTO orders (user_id, total) VALUES (:user_id, :total)`, order)
    if err != nil {
        return fmt.Errorf("insert order: %w", err)
    }

    orderID, _ := result.LastInsertId()
    for _, item := range items {
        item.OrderID = orderID
        _, err := tx.NamedExecContext(ctx,
            `INSERT INTO items (order_id, product_id, qty) VALUES (:order_id, :product_id, :qty)`, item)
        if err != nil {
            return fmt.Errorf("insert item: %w", err)
        }
    }

    return tx.Commit()
}

Connection Pool Configuration

func NewDB(dsn string) (*sqlx.DB, error) {
    db, err := sqlx.Connect("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("connect: %w", err)
    }

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

    return db, nil
}

Error Handling Patterns

Failure Modes

Failure	Root Cause	Detection	Recovery
Connection timeout	Pool exhausted	Metrics, logs	Increase pool, fix leaks
Deadlock	Competing transactions	DB logs	Review tx isolation
N+1 queries	ORM lazy loading	Query count	Eager load, batch queries
Migration fail	Schema conflict	CI pipeline	Rollback, fix migration

Retry Logic

func (r *Repository) withRetry(ctx context.Context, fn func() error) error {
    backoff := []time.Duration{100*time.Millisecond, 500*time.Millisecond, 2*time.Second}
    var lastErr error
    for i := 0; i <= len(backoff); i++ {
        if err := fn(); err != nil {
            if isRetryable(err) && i < len(backoff) {
                time.Sleep(backoff[i])
                lastErr = err
                continue
            }
            return err
        }
        return nil
    }
    return lastErr
}

Troubleshooting

Debug Checklist

1. EXPLAIN ANALYZE on slow queries
2. Check connection pool metrics
3. Verify indexes exist
4. Test transaction isolation

Common Errors

Error	Cause	Fix
sql: connection is already closed	Connection timeout	Reduce ConnMaxLifetime
pq: too many connections	Pool exhausted	Check for connection leaks
sql: no rows in result set	Empty result	Use sql.ErrNoRows check
deadlock detected	Concurrent tx conflict	Review lock ordering

Related Skills

• go-database (PRIMARY)

Usage

Task(subagent_type="go:04-go-database", prompt="Design repository pattern with transaction support")