sql-best-practices

SQL Best Practices — Every Query a Masterpiece

This skill enforces SQL quality standards: proper indentation, N+1 prevention, parameterized queries, index-friendly patterns, and security.

SQL Indentation Standard

EVERY SQL query MUST be properly indented:

-- ✅ CORRECT: Properly formatted
SELECT
    u.id,
    u.name,
    u.email,
    u.created_at,
    r.name  AS role_name,
    d.name  AS department_name
FROM
    users u
    INNER JOIN roles r
        ON r.id = u.role_id
    LEFT JOIN departments d
        ON d.id = u.department_id
WHERE
    u.deleted_at IS NULL
    AND u.status = 'active'
    AND u.created_at >= '2024-01-01'
ORDER BY
    u.created_at DESC
LIMIT 20
OFFSET 0;

-- ❌ WRONG: No formatting
select id,name,email from users where deleted_at is null and status='active' order by created_at desc limit 20;

Formatting Rules

1. Keywords: UPPERCASE (SELECT, FROM, WHERE, JOIN, etc.)
2. Indentation: Each clause on its own line, indented
3. Columns: One per line in SELECT
4. JOIN conditions: Indented under the JOIN
5. WHERE conditions: Aligned AND/OR
6. Commas: Leading or trailing — pick one, be consistent

N+1 Prevention Patterns

Detection Pattern

If you see a LOOP containing a QUERY, it's likely N+1.
Count queries: 1 (initial) + N (loop) = N+1 problem.

Solution: Eager Loading

-- ❌ N+1: One query per order for items
SELECT * FROM orders WHERE user_id = 123;
-- Then for EACH order:
SELECT * FROM order_items WHERE order_id = ?;

-- ✅ Single query with JOIN
SELECT
    o.id           AS order_id,
    o.total        AS order_total,
    o.status       AS order_status,
    oi.id          AS item_id,
    oi.product_name,
    oi.quantity,
    oi.unit_price
FROM
    orders o
    INNER JOIN order_items oi
        ON oi.order_id = o.id
WHERE
    o.user_id = 123
    AND o.deleted_at IS NULL
ORDER BY
    o.created_at DESC,
    oi.id ASC;

Solution: Batch with IN

-- ❌ N+1: Loop over IDs
SELECT * FROM products WHERE id = 1;
SELECT * FROM products WHERE id = 2;
SELECT * FROM products WHERE id = 3;

-- ✅ Batch: Single query
SELECT
    id,
    name,
    sku,
    price,
    stock_quantity
FROM
    products
WHERE
    id IN (1, 2, 3)
    AND deleted_at IS NULL;