🗄️ Database Patterns

"The database is the source of truth."
Go's `database/sql` package provides a robust foundation for database access.

---

🔌 database/sql Fundamentals

Connection Setup

import (
    "database/sql"
    _ "github.com/lib/pq"  // PostgreSQL driver
)

func Connect(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("open db: %w", err)
    }
    
    // Connection pool settings
    db.SetMaxOpenConns(25)                  // Max connections
    db.SetMaxIdleConns(10)                  // Keep-alive connections
    db.SetConnMaxLifetime(5 * time.Minute)  // Recycle connections
    db.SetConnMaxIdleTime(1 * time.Minute)  // Idle timeout
    
    // Verify connection
    if err := db.PingContext(context.Background()); err != nil {
        return nil, fmt.Errorf("ping db: %w", err)
    }
    
    return db, nil
}

Connection Pool Explained

┌─────────────────────────────────────────────────────────────┐
│                      sql.DB Connection Pool                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   MaxOpenConns = 25                                         │
│   ┌──────┐ ┌──────┐ ┌──────┐        ┌──────┐               │
│   │ Conn │ │ Conn │ │ Conn │  ...   │ Conn │               │
│   │  1   │ │  2   │ │  3   │        │  25  │               │
│   └──────┘ └──────┘ └──────┘        └──────┘               │
│      ↑                                                      │
│      │ MaxIdleConns = 10                                    │
│      │ (Keep these warm)                                    │
│                                                             │
│   ConnMaxLifetime = 5m                                      │
│   (Replace after 5 minutes even if healthy)                 │
│                                                             │
│   ConnMaxIdleTime = 1m                                      │
│   (Close if idle > 1 minute)                                │
│                                                             │
└─────────────────────────────────────────────────────────────┘

---

⚔️ Tradeoff: Database Libraries

Library	Type	Pros	Cons	When to Use
database/sql	Stdlib	Zero deps, control	Verbose, manual mapping	Maximum control
sqlx	Extension	Less boilerplate, scan structs	Still write SQL	Most projects
sqlc	Code gen	Type-safe, fast	Build step needed	Complex queries
GORM	ORM	Fast dev, migrations	Magic, performance	Rapid prototyping
ent	ORM	Type-safe, code gen	Learning curve	Complex schemas

---

📊 Patterns with sqlx (Recommended)

Basic CRUD

import "github.com/jmoiron/sqlx"

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

type UserRepository struct {
    db *sqlx.DB
}

func NewUserRepository(db *sqlx.DB) *UserRepository {
    return &UserRepository{db: db}
}

// Get by ID
func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    var user User
    err := r.db.GetContext(ctx, &user, 
        "SELECT id, name, email, created_at FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, ErrNotFound
        }
        return nil, fmt.Errorf("get user: %w", err)
    }
    return &user, nil
}

// List with pagination
func (r *UserRepository) List(ctx context.Context, limit, offset int) ([]User, error) {
    var users []User
    err := r.db.SelectContext(ctx, &users,
        "SELECT id, name, email, created_at FROM users ORDER BY id LIMIT $1 OFFSET $2",
        limit, offset)
    if err != nil {
        return nil, fmt.Errorf("list users: %w", err)
    }
    return users, nil
}

// Create
func (r *UserRepository) Create(ctx context.Context, user *User) error {
    query := `
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, $3)
        RETURNING id`
    
    err := r.db.QueryRowContext(ctx, query,
        user.Name, user.Email, time.Now()).Scan(&user.ID)
    if err != nil {
        return fmt.Errorf("create user: %w", err)
    }
    return nil
}

// Update
func (r *UserRepository) Update(ctx context.Context, user *User) error {
    query := `UPDATE users SET name = $1, email = $2 WHERE id = $3`
    
    result, err := r.db.ExecContext(ctx, query, user.Name, user.Email, user.ID)
    if err != nil {
        return fmt.Errorf("update user: %w", err)
    }
    
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

// Delete
func (r *UserRepository) Delete(ctx context.Context, id int64) error {
    result, err := r.db.ExecContext(ctx, "DELETE FROM users WHERE id = $1", id)
    if err != nil {
        return fmt.Errorf("delete user: %w", err)
    }
    
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

---

💰 Transaction Management

Basic Transaction

func (r *UserRepository) CreateWithProfile(ctx context.Context, user *User, profile *Profile) error {
    tx, err := r.db.BeginTxx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback()  // No-op if already committed
    
    // Insert user
    err = tx.QueryRowContext(ctx,
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
        user.Name, user.Email).Scan(&user.ID)
    if err != nil {
        return fmt.Errorf("insert user: %w", err)
    }
    
    // Insert profile
    _, err = tx.ExecContext(ctx,
        "INSERT INTO profiles (user_id, bio) VALUES ($1, $2)",
        user.ID, profile.Bio)
    if err != nil {
        return fmt.Errorf("insert profile: %w", err)
    }
    
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit: %w", err)
    }
    
    return nil
}

Transaction Helper Pattern

// TxFunc is a function that runs within a transaction
type TxFunc func(tx *sqlx.Tx) error

// WithTransaction executes fn within a transaction
func (r *Repository) WithTransaction(ctx context.Context, fn TxFunc) error {
    tx, err := r.db.BeginTxx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }
    
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        }
    }()
    
    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original: %w)", rbErr, err)
        }
        return err
    }
    
    return tx.Commit()
}

// Usage
err := repo.WithTransaction(ctx, func(tx *sqlx.Tx) error {
    if err := createUser(tx, user); err != nil {
        return err
    }
    if err := createProfile(tx, profile); err != nil {
        return err
    }
    return nil
})

---

📝 Prepared Statements

When to Use

// ✅ Good: Frequently executed query
type UserRepository struct {
    db          *sqlx.DB
    getByIDStmt *sqlx.Stmt
    listStmt    *sqlx.Stmt
}

func NewUserRepository(db *sqlx.DB) (*UserRepository, error) {
    getByID, err := db.Preparex("SELECT * FROM users WHERE id = $1")
    if err != nil {
        return nil, err
    }
    
    list, err := db.Preparex("SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2")
    if err != nil {
        return nil, err
    }
    
    return &UserRepository{
        db:          db,
        getByIDStmt: getByID,
        listStmt:    list,
    }, nil
}

func (r *UserRepository) GetByID(ctx context.Context, id int64) (*User, error) {
    var user User
    err := r.getByIDStmt.GetContext(ctx, &user, id)
    return &user, err
}

func (r *UserRepository) Close() {
    r.getByIDStmt.Close()
    r.listStmt.Close()
}

---

🔄 sqlc: Type-Safe Queries (Recommended)

Configuration

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "queries/"
    schema: "schema/"
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: true

Schema & Queries

-- schema/users.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- queries/users.sql

-- name: GetUser :one
SELECT * FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2;

-- name: CreateUser :one
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING *;

-- name: UpdateUser :exec
UPDATE users SET name = $2, email = $3 WHERE id = $1;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

Generated Code Usage

# Generate code
$ sqlc generate

// Usage - fully type-safe!
import "myapp/internal/db"

func main() {
    conn, _ := pgx.Connect(ctx, dsn)
    queries := db.New(conn)
    
    // Type-safe, auto-completed
    user, err := queries.CreateUser(ctx, db.CreateUserParams{
        Name:  "Alice",
        Email: "alice@example.com",
    })
    
    // Returns generated User type
    fmt.Println(user.ID, user.Name)
}

---

🐘 GORM (When Speed > Control)

import "gorm.io/gorm"

type User struct {
    ID        uint   `gorm:"primaryKey"`
    Name      string `gorm:"size:255"`
    Email     string `gorm:"uniqueIndex"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`  // Soft delete
}

func main() {
    db, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    
    // Auto-migrate (dev only!)
    db.AutoMigrate(&User{})
    
    // Create
    user := User{Name: "Alice", Email: "alice@example.com"}
    db.Create(&user)
    
    // Read
    var found User
    db.First(&found, user.ID)
    
    // Update
    db.Model(&found).Update("Name", "Alice Smith")
    
    // Delete (soft delete)
    db.Delete(&found)
}

🔥 GORM Pitfalls

Issue	Problem	Solution
N+1 queries	Preload creates extra queries	Use Joins
Silent failures	Errors not checked	Always check db.Error
Migration in prod	AutoMigrate is dangerous	Use versioned migrations
Performance	Reflection overhead	Use sqlc for hot paths

---

💻 Engineering Example: Repository Pattern with sqlc

// internal/repository/user.go
package repository

import (
    "context"
    "errors"
    
    "github.com/jackc/pgx/v5"
    "myapp/internal/db"
)

var ErrNotFound = errors.New("user not found")

type UserRepository interface {
    GetByID(ctx context.Context, id int64) (*db.User, error)
    GetByEmail(ctx context.Context, email string) (*db.User, error)
    Create(ctx context.Context, params db.CreateUserParams) (*db.User, error)
    Update(ctx context.Context, id int64, params UpdateUserParams) error
    Delete(ctx context.Context, id int64) error
}

type userRepository struct {
    queries *db.Queries
}

func NewUserRepository(conn *pgx.Conn) UserRepository {
    return &userRepository{
        queries: db.New(conn),
    }
}

func (r *userRepository) GetByID(ctx context.Context, id int64) (*db.User, error) {
    user, err := r.queries.GetUser(ctx, id)
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return nil, ErrNotFound
        }
        return nil, fmt.Errorf("get user by id: %w", err)
    }
    return &user, nil
}

func (r *userRepository) Create(ctx context.Context, params db.CreateUserParams) (*db.User, error) {
    user, err := r.queries.CreateUser(ctx, params)
    if err != nil {
        // Check for unique violation
        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && pgErr.Code == "23505" {
            return nil, ErrEmailExists
        }
        return nil, fmt.Errorf("create user: %w", err)
    }
    return &user, nil
}

// ... other methods

---

✅ Ship-to-Prod Checklist

Connection Management

• [ ] Connection pool configured (MaxOpenConns, MaxIdleConns)
• [ ] ConnMaxLifetime set (5min recommended)
• [ ] Context timeout passed to all queries
• [ ] Health check pings database

Query Safety

• [ ] Parameterized queries (no string concatenation)
• [ ] Error handling for sql.ErrNoRows
• [ ] Transactions for multi-statement operations
• [ ] Prepared statements for hot paths

Performance

• [ ] Indexes on frequently queried columns
• [ ] EXPLAIN ANALYZE for slow queries
• [ ] Connection pool metrics monitored
• [ ] Query timeout configured

Operations

• [ ] Migrations versioned và testable
• [ ] Backup strategy in place
• [ ] Read replicas for read-heavy workloads
• [ ] Connection string secrets externalized

---

📊 Summary

Pattern	Use Case
database/sql	Maximum control, zero deps
sqlx	Most projects, less boilerplate
sqlc	Type-safe, complex queries
GORM	Rapid prototyping, simple CRUD
Prepared Statements	Frequently executed queries
Transactions	Multi-statement atomicity

---

➡️ Tiếp theo

Database patterns nắm vững rồi! Tiếp theo: Observability - Logging, metrics, và tracing.