sql-authoringlisted

# SQL Authoring A query is correct, fast, and safe — in that order, none optional. Schema and index *design* belong to [db-design](../db-design/SKILL.md); this skill is the *query* craft: how to express intent so the planner picks an index, how to read the plan when it doesn't, and how to never hand an attacker a string-built statement. > Read an EXPLAIN plan without eyeballing it: > `psql -c 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>' | python3 scripts/analyze_plan.py` ## Always parameterize — no exceptions ```python # Wrong — SQL injection; one apostrophe in `name` and the query breaks or leaks cur.execute(f"SELECT * FROM users WHERE name = '{name}'") # Correct — the driver binds; the value never touches the SQL text cur.execute("SELECT * FROM users WHERE name = %s", (name,)) ``` String-built SQL is the #1 OWASP injection vector — the server-side rules are owned by [security-web](../security-web/SKILL.md). The query-craft rule: **values are always bind parameters; only identifiers you control (validated against an allow-list) are ever interpolated.** An ORM gives you this for free until you reach for `raw()` — then it's on you. ## Think in sets, not rows ```sql -- Wrong — N+1: one query per order, 1000 orders = 1001 round trips SELECT id FROM orders WHERE user_id = $1; -- then, per row: SELECT * FROM line_items WHERE order_id = $1; -- Correct — one query, the join does the work SELECT o.id, li.* FROM orders o JOIN line_items li ON li.order_id = o.id