Atharva Pandey/Lesson 5: sqlc vs ORM vs Raw SQL — Pick your tradeoff, not your religion

// WRONG — raw SQL with stringly-typed Scan, no compile-time safety
func GetActiveUsersWithOrderTotals(db *sql.DB, ctx context.Context) ([]UserWithOrder, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT u.id, u.name, u.email, o.total
        FROM users u
        LEFT JOIN orders o ON o.id = (
            SELECT id FROM orders
            WHERE user_id = u.id
            ORDER BY created_at DESC
            LIMIT 1
        )
        WHERE u.active = true
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var results []UserWithOrder
    for rows.Next() {
        var r UserWithOrder
        // If you add a column to the SELECT, you must remember to add it here
        // If you reorder the columns, this silently scans into wrong fields
        // If o.total is NULL, this panics
        if err := rows.Scan(&r.ID, &r.Name, &r.Email, &r.Total); err != nil {
            return nil, err
        }
        results = append(results, r)
    }
    return results, rows.Err()
}

// WRONG — GORM with lazy loading, N+1 hiding behind convenience
type User struct {
    gorm.Model
    Name   string
    Email  string
    Active bool
    Orders []Order // GORM "hasMany" relationship
}

type Order struct {
    gorm.Model
    UserID int
    Total  int
}

func GetActiveUsersGORM(db *gorm.DB) ([]User, error) {
    var users []User
    // This runs SELECT * FROM users WHERE active = true
    // Then for EACH user, runs SELECT * FROM orders WHERE user_id = ?
    // That's the N+1 problem, disguised as convenience
    result := db.Where("active = ?", true).Preload("Orders").Find(&users)
    return users, result.Error
}

// Raw SQL: full control, type-safe scanning is manual discipline
func GetActiveUsersRaw(db *sql.DB, ctx context.Context) ([]UserWithOrder, error) {
    const query = `
        SELECT
            u.id,
            u.name,
            u.email,
            COALESCE(
                (SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1),
                0
            ) AS last_order_total
        FROM users u
        WHERE u.active = true
        ORDER BY u.name
    `
    rows, err := db.QueryContext(ctx, query)
    if err != nil {
        return nil, fmt.Errorf("query: %w", err)
    }
    defer rows.Close()

    var results []UserWithOrder
    for rows.Next() {
        var r UserWithOrder
        if err := rows.Scan(&r.ID, &r.Name, &r.Email, &r.LastOrderTotal); err != nil {
            return nil, fmt.Errorf("scan: %w", err)
        }
        results = append(results, r)
    }
    return results, rows.Err()
}
// Pro: you see exactly what SQL runs. Con: refactoring column order breaks Scan silently.

-- queries/users.sql
-- name: GetActiveUsersWithLastOrder :many
SELECT
    u.id,
    u.name,
    u.email,
    COALESCE(
        (SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1),
        0
    ) AS last_order_total
FROM users u
WHERE u.active = true
ORDER BY u.name;

// sqlc generates this — you never write it by hand
type GetActiveUsersWithLastOrderRow struct {
    ID             int32
    Name           string
    Email          string
    LastOrderTotal int32
}

func (q *Queries) GetActiveUsersWithLastOrder(ctx context.Context) ([]GetActiveUsersWithLastOrderRow, error) {
    rows, err := q.db.QueryContext(ctx, getActiveUsersWithLastOrder)
    // ... generated scanning code, 100% correct ...
}

// Your code:
func GetActiveUsers(ctx context.Context, q *db.Queries) ([]db.GetActiveUsersWithLastOrderRow, error) {
    return q.GetActiveUsersWithLastOrder(ctx)
    // That's it. The generated code handles scanning.
    // Change the SQL? Regenerate. Wrong SQL? Compile error.
}
// Pro: type-safe, no magic, compile-time SQL validation. Con: code-gen step, slightly more setup.

// GORM: used correctly with explicit Select and Joins instead of Preload
type UserWithOrder struct {
    ID             uint
    Name           string
    Email          string
    LastOrderTotal int
}

func GetActiveUsersGORMRight(db *gorm.DB) ([]UserWithOrder, error) {
    var results []UserWithOrder
    err := db.Model(&User{}).
        Select(`users.id, users.name, users.email,
            COALESCE((
                SELECT total FROM orders
                WHERE user_id = users.id
                ORDER BY created_at DESC LIMIT 1
            ), 0) AS last_order_total`).
        Where("users.active = ?", true).
        Order("users.name").
        Scan(&results).Error
    return results, err
    // Pro: familiar to teams coming from Rails/Django. Con: raw SQL in a string anyway,
    // plus GORM's abstraction overhead, plus magic that's hard to debug.
}