db-patterns

Database Patterns

Current State

• Latest migration: !ls backend/migrations/*.up.sql | sort | tail -1 | xargs basename
• Total migrations: !ls backend/migrations/*.up.sql 2>/dev/null | wc -l | tr -d ' '
• Repository count: !ls backend/internal/repo/postgres/*_repo.go 2>/dev/null | wc -l | tr -d ' '

Stack

• PostgreSQL 16
• Driver: jackc/pgx/v5 (connection pool via pgxpool)
• No ORM — raw SQL queries

Repository Pattern

type UserRepo struct {
    pool *pgxpool.Pool
}

func NewUserRepo(pool *pgxpool.Pool) *UserRepo {
    return &UserRepo{pool: pool}
}

// Nil-safety check
func (r *UserRepo) Ready() bool {
    return r != nil && r.pool != nil
}

Common Query Patterns

SELECT single row

func (r *UserRepo) GetByID(ctx context.Context, id int64) (*domain.User, error) {
    var u domain.User
    err := r.pool.QueryRow(ctx, `
        SELECT id, name, email, created_at
        FROM users
        WHERE id = $1
    `, id).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
    if err != nil {
        if errors.Is(err, pgx.ErrNoRows) {
            return nil, nil // or return specific ErrNotFound
        }
        return nil, fmt.Errorf("UserRepo.GetByID: %w", err)
    }
    return &u, nil
}

SELECT multiple rows

func (r *UserRepo) List(ctx context.Context, limit, offset int) ([]domain.User, error) {
    rows, err := r.pool.Query(ctx, `
        SELECT id, name, email, created_at
        FROM users
        ORDER BY created_at DESC
        LIMIT $1 OFFSET $2
    `, limit, offset)
    if err != nil {
        return nil, fmt.Errorf("UserRepo.List: %w", err)
    }
    defer rows.Close()

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

INSERT

func (r *UserRepo) Create(ctx context.Context, u *domain.User) error {
    _, err := r.pool.Exec(ctx, `
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, now())
    `, u.Name, u.Email)
    if err != nil {
        return fmt.Errorf("UserRepo.Create: %w", err)
    }
    return nil
}