golang-database-patterns

What this skill does

# Go Database Patterns

## Overview

Go's database ecosystem provides multiple layers of abstraction for SQL database integration. From the standard library's `database/sql` to enhanced libraries like `sqlx` and PostgreSQL-optimized `pgx`, developers can choose the right tool for their performance and ergonomics needs.

**Key Features:**
- 🔌 **database/sql**: Standard interface for any SQL database
- 🚀 **sqlx**: Convenience methods with struct scanning and named queries
- 🐘 **pgx**: PostgreSQL-native driver with maximum performance
- 📦 **Repository Pattern**: Interface-based data access for testability
- 🔄 **Migrations**: Schema versioning with golang-migrate
- ⚡ **Connection Pooling**: Production-ready connection management
- 🔒 **Transaction Safety**: Context-aware transaction handling

## When to Use This Skill

Activate this skill when:
- Building CRUD operations with type safety
- Implementing data access layers for web services
- Managing database schema evolution across environments
- Optimizing database connection pooling for production
- Testing database code with mock repositories
- Handling concurrent database access patterns
- Migrating from ORMs to SQL-first approaches
- Integrating PostgreSQL-specific features (COPY, LISTEN/NOTIFY)

## Core Database Libraries

### Decision Tree: Choosing Your Database Library

```
┌─────────────────────────────────────┐
│ What database are you using?       │
└──────────────┬──────────────────────┘
               │
    ┌──────────┴──────────┐
    │                     │
PostgreSQL            Other SQL DB
    │                     │
    ▼                     ▼
┌─────────────────┐   Use database/sql
│ Need max perf?  │   + sqlx for convenience
└─────┬───────────┘
      │
   ┌──┴──┐
  Yes    No
   │      │
  pgx   sqlx + pq driver
```

**Use database/sql when:**
- Working with any SQL database (MySQL, SQLite, PostgreSQL, etc.)
- Need database portability
- Want standard library stability with no dependencies

**Use sqlx when:**
- Want convenience methods (Get, Select, StructScan)
- Need named parameter queries
- Using IN clause expansion
- Prefer less boilerplate than database/sql

**Use pgx when:**
- PostgreSQL-only application
- Need maximum performance (30-50% faster than lib/pq)
- Want advanced PostgreSQL features (COPY, LISTEN/NOTIFY, prepared statement caching)
- Building high-throughput systems

### database/sql: The Standard Foundation

**Core Concepts:**

```go
package main

import (
    "context"
    "database/sql"
    "time"

    _ "github.com/lib/pq" // PostgreSQL driver
)

func setupDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, err
    }

    // Connection pooling configuration
    db.SetMaxOpenConns(25)                 // Max open connections
    db.SetMaxIdleConns(5)                  // Max idle connections
    db.SetConnMaxLifetime(5 * time.Minute) // Max connection lifetime
    db.SetConnMaxIdleTime(1 * time.Minute) // Max idle time

    // Verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    if err := db.PingContext(ctx); err != nil {
        return nil, err
    }

    return db, nil
}
```

**Key Patterns:**

```go
// Query single row
func GetUserByID(ctx context.Context, db *sql.DB, id int) (*User, error) {
    var user User
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`

    err := db.QueryRowContext(ctx, query, id).Scan(
        &user.ID, &user.Name, &user.Email, &user.CreatedAt,
    )

    if err == sql.ErrNoRows {
        return nil, ErrUserNotFound // Custom error
    }
    if err != nil {
        return nil, fmt.Errorf("query user: %w", err)
    }

    return &user, nil
}

// Query multiple rows
func ListActiveUsers(ctx context.Context, db *sql.DB) ([]User, error) {
    query := `SELECT id, name, email, created_at FROM users WHERE active = true`

    rows, err := db.QueryContext(ctx, query)
    if err != nil {
        return nil, fmt.Errorf("query users: %w", err)
    }
    defer rows.Close() // CRITICAL: Always close rows

    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt); err != nil {
            return nil, fmt.Errorf("scan user: %w", err)
        }
        users = append(users, user)
    }

    // Check for errors during iteration
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("iterate users: %w", err)
    }

    return users, nil
}
```

### sqlx: Ergonomic Extensions

**Installation:**
```bash
go get github.com/jmoiron/sqlx
```

**Core Features:**

```go
package main

import (
    "context"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

// Get single struct
func GetUserByID(ctx context.Context, db *sqlx.DB, id int) (*User, error) {
    var user User
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`

    err := db.GetContext(ctx, &user, query, id)
    if err == sql.ErrNoRows {
        return nil, ErrUserNotFound
    }
    return &user, err
}

// Select multiple structs
func ListUsers(ctx context.Context, db *sqlx.DB, limit int) ([]User, error) {
    var users []User
    query := `SELECT id, name, email, created_at FROM users LIMIT $1`

    err := db.SelectContext(ctx, &users, query, limit)
    return users, err
}

// Named queries
func FindUsersByName(ctx context.Context, db *sqlx.DB, name string) ([]User, error) {
    var users []User
    query := `SELECT * FROM users WHERE name LIKE :name || '%'`

    nstmt, err := db.PrepareNamedContext(ctx, query)
    if err != nil {
        return nil, err
    }
    defer nstmt.Close()

    err = nstmt.SelectContext(ctx, &users, map[string]interface{}{"name": name})
    return users, err
}

// IN clause expansion
func GetUsersByIDs(ctx context.Context, db *sqlx.DB, ids []int) ([]User, error) {
    var users []User
    query, args, err := sqlx.In(`SELECT * FROM users WHERE id IN (?)`, ids)
    if err != nil {
        return nil, err
    }

    // Rebind for PostgreSQL ($1, $2, ...) vs MySQL (?, ?, ...)
    query = db.Rebind(query)

    err = db.SelectContext(ctx, &users, query, args...)
    return users, err
}
```

### pgx: PostgreSQL-Native Performance

**Installation:**
```bash
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool
```

**Connection Pool Setup:**

```go
package main

import (
    "context"
    "fmt"

    "github.com/jackc/pgx/v5/pgxpool"
)

func setupPgxPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, fmt.Errorf("parse config: %w", err)
    }

    // Connection pool tuning
    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = 1 * time.Hour
    config.MaxConnIdleTime = 30 * time.Minute
    config.HealthCheckPeriod = 1 * time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, fmt.Errorf("create pool: %w", err)
    }

    // Verify connectivity
    if err := pool.Ping(ctx); err != nil {
        return nil, fmt.Errorf("ping: %w", err)
    }

    return pool, nil
}
```

**Query Patterns:**

```go
// Query single row
func GetUser(ctx context.Context, pool *pgxpool.Pool, id int) (*User, error) {
    var user User
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`

    err := pool.QueryRow(ctx, query, id).Scan(
        &user.ID, &user.Name, &user.Email, &user.CreatedAt,
    )

    if err == pgx.ErrNoRows {
        return nil, ErrUserNotFound
    }
    return &user, err
}

// Batch operations (pgx-specific optimization)
func BatchInsertUsers(ctx context.Context, pool *pgxpool.Pool, users []User) error {
    batch := &pgx.Batch{}
    query