Avoid ORM #

// Go code that looks clean
var users []User
db.Preload("Orders").Preload("Orders.Items").Find(&users)

// The SQL actually executed (with 100 users):
// Query 1: SELECT * FROM users
// Query 2: SELECT * FROM orders WHERE user_id IN (1,2,3,...,100)
// Query 3: SELECT * FROM order_items WHERE order_id IN (...)
//
// This is OK — proper Preload uses batch queries
// But notice Preload without scoping:

db.Preload("Orders").Find(&users)
// Query 2 above has no LIMIT or status filter
// If users have 500 orders each:
// → Query 2 returns 50,000 rows into application memory
// → Network transfer: possibly tens of MB
// → All of it goes into memory as Go slices
// Developers don't notice because the code looks like "one line"

// GORM: Find always does SELECT *
var products []Product
db.Where("category_id = ?", categoryID).Find(&products)

// The generated SQL:
// SELECT * FROM products WHERE category_id = 5
//
// The products table has columns:
// id, name, description (5KB TEXT), price, stock, weight,
// dimensions (JSON), metadata (JSON), image_urls (JSON),
// created_at, updated_at, deleted_at
//
// The API only needs: id, name, price
// But we pull every column including 5KB descriptions per product
// For 200 products: 200 × 5KB = 1MB of useless data per request

// GORM solution: use explicit Select()
db.Select("id, name, price").Where("category_id = ?", categoryID).Find(&products)
// SQL: SELECT id, name, price FROM products WHERE category_id = 5
// Better, but many developers forget to do this

// Soft delete in GORM: automatically adds WHERE deleted_at IS NULL
// to EVERY query
var orders []Order
db.Where("user_id = ?", userID).Find(&orders)
// SQL: SELECT * FROM orders WHERE user_id = 42 AND deleted_at IS NULL

// Problem: if your composite index is (user_id, status, created_at)
// but GORM appends deleted_at IS NULL at the end,
// the query planner may not use that index optimally
// because the WHERE column order differs from the index order

// You can't control the conditions GORM adds automatically
// without workarounds that dirty the code

// An example of GORM generating non-index-friendly conditions:
db.Where("DATE(created_at) = ?", today).Find(&orders)
// SQL: ... WHERE DATE(created_at) = '2026-04-18' AND deleted_at IS NULL
// DATE() on the column → index unused (covered in SQL Function Overuse)
// And this condition often appears because ORMs make "looks reasonable" writing easy

// Code that looks clean, but is dangerous
func GetUserList(ctx context.Context) ([]UserResponse, error) {
    var users []User
    db.Find(&users)  // 1 query

    var result []UserResponse
    for _, u := range users {
        // Accessing u.Profile triggers a new query if not loaded!
        // GORM lazy load: one query per user
        result = append(result, UserResponse{
            Name:    u.Name,
            City:    u.Profile.City,    // ← hidden query: SELECT * FROM profiles WHERE user_id = ?
            Country: u.Profile.Country, // ← no extra query (already loaded)
        })
    }
    return result, nil
}
// For 1,000 users: 1 + 1,000 = 1,001 queries
// Developers may not notice until they see the slow query log

// GORM with Preload (better):
db.Preload("Profile").Find(&users)
// 1 users query + 1 batch profiles query = 2 queries total
// But still SELECT * from both tables

// A real example: a product list endpoint with an ORM
func GetProducts(categoryID int) []ProductResponse {
    var products []Product
    db.Where("category_id = ?", categoryID).
       Where("deleted_at IS NULL").
       Find(&products)
    // Pulls ALL columns including: description (5KB TEXT),
    // metadata (2KB JSON), image_urls (1KB JSON), etc.

    var responses []ProductResponse
    for _, p := range products {
        responses = append(responses, ProductResponse{
            ID:    p.ID,
            Name:  p.Name,
            Price: p.Price,
            // Only 3 of the 15 loaded columns are needed!
        })
    }
    return responses
}

// The real cost for 200 products:
// Data pulled: 200 × (5KB + 2KB + 1KB + ...) = ~2MB
// Data used: 200 × (8B + 200B + 8B) = ~44KB
// Efficiency: ~2%
// The other 98% of data is discarded after mapping to DTOs

// The query needed: get the best-selling product per category
// (window function: RANK() OVER PARTITION BY)

// With GORM — can't be done cleanly:
// Must use Raw(), defeating the ORM's purpose
var result []struct {
    CategoryID int
    ProductID  int
    SalesRank  int
}
db.Raw(`
    SELECT category_id, id AS product_id,
           RANK() OVER (PARTITION BY category_id ORDER BY total_sold DESC) AS sales_rank
    FROM products
    WHERE status = 'active'
`, ).Scan(&result)
// You write raw SQL inside the ORM
// No ORM benefit here, only abstraction overhead

// Better to go straight to sqlx:
sqlx.SelectContext(ctx, db, &result, `
    SELECT category_id, id AS product_id,
           RANK() OVER (PARTITION BY category_id ORDER BY total_sold DESC) AS sales_rank
    FROM products
    WHERE status = 'active'
`)
// Clearer, no useless ORM layer

// GORM soft delete adds automatic conditions
// that can disturb carefully designed indexes

// The index designed for the pagination query:
// CREATE INDEX idx_orders ON orders (user_id, status, created_at DESC)

// The generated GORM query:
// SELECT * FROM orders
// WHERE user_id = 42 AND status = 'paid'
// AND deleted_at IS NULL   ← added by GORM
// ORDER BY created_at DESC
// LIMIT 20

// Problem: the deleted_at IS NULL condition interrupts the optimal
// column order for the index (user_id, status, created_at)
// The index may still be used but not as optimally as if
// deleted_at were also in the index or the condition absent

// === The GORM approach ===
type Order struct {
    gorm.Model
    UserID  uint
    Total   float64
    Status  string
    User    User `gorm:"foreignKey:UserID"`
}

func GetOrdersGORM(userID uint, status string) ([]Order, error) {
    var orders []Order
    result := db.Preload("User").
        Where("user_id = ? AND status = ?", userID, status).
        Order("created_at DESC").
        Limit(20).
        Find(&orders)
    return orders, result.Error
}
// Generated SQL:
// SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'
//   AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 20
// SELECT * FROM users WHERE id IN (42)  ← batch Preload
//
// Hidden problems:
// - SELECT * from orders (all columns) + SELECT * from users
// - deleted_at IS NULL added automatically
// - Not easy to pick specific columns

// === The sqlx approach ===
type OrderWithUser struct {
    OrderID   int64   `db:"order_id"`
    Total     float64 `db:"total"`
    Status    string  `db:"status"`
    CreatedAt time.Time `db:"created_at"`
    UserName  string  `db:"user_name"`
    UserEmail string  `db:"user_email"`
}

func GetOrdersSQLX(ctx context.Context, db *sqlx.DB, userID int64, status string) ([]OrderWithUser, error) {
    var orders []OrderWithUser
    err := db.SelectContext(ctx, &orders, `
        SELECT
            o.id          AS order_id,
            o.total,
            o.status,
            o.created_at,
            u.name        AS user_name,
            u.email       AS user_email
        FROM orders o
        JOIN users u ON o.user_id = u.id
        WHERE o.user_id = ?
          AND o.status   = ?
          AND o.deleted_at IS NULL
        ORDER BY o.created_at DESC
        LIMIT 20
    `, userID, status)
    return orders, err
}
// The executed SQL: exactly what was written
// Only the needed columns are pulled
// A JOIN in one query — no second query for users
// Explicit conditions with full control

// GORM remains useful for simple CRUD operations
// that don't need specific columns or complex queries

// Examples suitable for GORM:
func CreateUser(user *User) error {
    return db.Create(user).Error  // a simple INSERT
}

func UpdateUserStatus(userID uint, status string) error {
    return db.Model(&User{}).
        Where("id = ?", userID).
        Update("status", status).Error
    // UPDATE users SET status = ?, updated_at = ? WHERE id = ?
}

func DeleteUser(userID uint) error {
    return db.Delete(&User{}, userID).Error
    // UPDATE users SET deleted_at = ? WHERE id = ?  (soft delete)
}

// For the operations above, GORM simplifies the code
// and produces adequate queries

// A repository using GORM for writes and sqlx for complex reads
type OrderRepository struct {
    gormDB *gorm.DB    // for write operations
    sqlxDB *sqlx.DB    // for read operations needing control
}

// WRITE: use GORM — simple and safe
func (r *OrderRepository) Create(ctx context.Context, order *Order) error {
    return r.gormDB.WithContext(ctx).Create(order).Error
}

func (r *OrderRepository) UpdateStatus(ctx context.Context, orderID int64, status string) error {
    return r.gormDB.WithContext(ctx).
        Model(&Order{}).
        Where("id = ?", orderID).
        Update("status", status).Error
}

// SIMPLE READS: GORM is fine
func (r *OrderRepository) FindByID(ctx context.Context, id int64) (*Order, error) {
    var order Order
    err := r.gormDB.WithContext(ctx).First(&order, id).Error
    return &order, err
}

// COMPLEX READS: use sqlx with explicit SQL
func (r *OrderRepository) GetUserOrderSummary(ctx context.Context, userID int64) ([]OrderSummary, error) {
    var summaries []OrderSummary
    err := r.sqlxDB.SelectContext(ctx, &summaries, `
        SELECT
            o.id,
            o.status,
            o.total,
            o.created_at,
            COUNT(oi.id)         AS item_count,
            SUM(oi.qty)          AS total_qty,
            p.name               AS first_product_name
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.id
        JOIN products p ON p.id = oi.product_id
        WHERE o.user_id = ?
          AND o.deleted_at IS NULL
        GROUP BY o.id, o.status, o.total, o.created_at, p.name
        ORDER BY o.created_at DESC
        LIMIT 10
    `, userID)
    return summaries, err
}

// READS WITH PAGINATION AND DYNAMIC FILTERS
func (r *OrderRepository) List(ctx context.Context, params ListOrderParams) ([]OrderListItem, error) {
    // Build the query safely with parameters
    query := `
        SELECT o.id, o.total, o.status, o.created_at, u.name AS user_name
        FROM orders o
        JOIN users u ON o.user_id = u.id
        WHERE o.deleted_at IS NULL
    `
    args := []interface{}{}

    if params.Status != "" {
        query += " AND o.status = ?"
        args = append(args, params.Status)
    }
    if params.UserID != 0 {
        query += " AND o.user_id = ?"
        args = append(args, params.UserID)
    }
    if !params.StartDate.IsZero() {
        query += " AND o.created_at >= ?"
        args = append(args, params.StartDate)
    }

    query += " ORDER BY o.created_at DESC LIMIT ? OFFSET ?"
    args = append(args, params.Limit, params.Offset)

    var items []OrderListItem
    err := r.sqlxDB.SelectContext(ctx, &items, query, args...)
    return items, err
}

// Define separate structs for each read need
// Not one User/Order struct used for everything

// For list endpoints — only the columns shown in the table
type OrderListItem struct {
    ID        int64     `db:"id"`
    Total     float64   `db:"total"`
    Status    string    `db:"status"`
    CreatedAt time.Time `db:"created_at"`
    UserName  string    `db:"user_name"`
}

// For detail endpoints — more columns
type OrderDetail struct {
    ID           int64          `db:"id"`
    Total        float64        `db:"total"`
    Status       string         `db:"status"`
    CreatedAt    time.Time      `db:"created_at"`
    UpdatedAt    time.Time      `db:"updated_at"`
    UserName     string         `db:"user_name"`
    UserEmail    string         `db:"user_email"`
    ShippingAddr string         `db:"shipping_addr"`
}

// For analytics/reports — aggregation columns
type OrderAnalytics struct {
    Date         string  `db:"order_date"`
    TotalRevenue float64 `db:"total_revenue"`
    OrderCount   int     `db:"order_count"`
    AvgOrderSize float64 `db:"avg_order_size"`
}

// Each struct only has the fields truly needed
// SQL queries can be written with SELECTs exactly matching the structs
// No overfetching, no mapping that discards data

// GORM: enable SQL logging in development
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
    // LogMode(logger.Info) → log all queries
    // LogMode(logger.Warn) → only log slow queries
    // LogMode(logger.Silent) → no logging (production default)
})

// sqlx with a custom logger wrapper
type LoggingDB struct {
    *sqlx.DB
    log *zap.Logger
}

func (ldb *LoggingDB) SelectContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error {
    start := time.Now()
    err := ldb.DB.SelectContext(ctx, dest, query, args...)
    ldb.log.Debug("sql",
        zap.String("query", query),
        zap.Any("args", args),
        zap.Duration("elapsed", time.Since(start)),
        zap.Error(err),
    )
    return err
}

// The recommended workflow:
// 1. Write ORM / sqlx code
// 2. Run with logging enabled
// 3. Read the generated SQL
// 4. Run EXPLAIN for important queries
// 5. Only then commit to the repo

// ✗ Anti-pattern 1: Find() without Select() on tables with large columns
db.Where("category_id = ?", id).Find(&products)
// → SELECT * including description, metadata, etc. columns
// ✓ Solution: db.Select("id, name, price").Where(...).Find(&products)
// or use sqlx with an explicit SELECT

// ✗ Anti-pattern 2: accessing relationships without Preload (lazy loading N+1)
for _, u := range users {
    fmt.Println(u.Orders)  // triggers a query per user
}
// ✓ Solution: db.Preload("Orders").Find(&users)
// or better: sqlx with a JOIN or batch IN

// ✗ Anti-pattern 3: ORMs for analytics queries needing aggregation
db.Raw("SELECT DATE(created_at) as date, SUM(total) as revenue, COUNT(*) as count FROM orders GROUP BY DATE(created_at)").Scan(&result)
// Using Raw() → the ORM benefit is gone, but abstraction overhead remains
// ✓ Solution: use sqlx directly

// ✗ Anti-pattern 4: never enabling SQL logging
// Developers don't know what queries are executed
// ✓ Solution: enable logger.Info in GORM or make a logging wrapper for sqlx
// Make this mandatory in development environments

// ✗ Anti-pattern 5: mapping directly to a large entity for API responses
var orders []Order
db.Find(&orders)  // pulls every column from every relationship
json.NewEncoder(w).Encode(orders)
// ✓ Solution: define separate DTOs for each use case,
// SELECT only the columns in those DTOs