sql-authoring

SQL Authoring

A query is correct, fast, and safe — in that order, none optional. Schema and index design belong to db-design; 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

# 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. 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

-- 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
WHERE o.user_id = $1;

N+1 is the most common real-world slowness. It hides behind ORM lazy-loading — for order in orders: order.items issues a query per iteration. Fix with a join, a prefetch/selectinload, or an IN (...) batch. Full recipes → references/query-patterns.md.

Index-aware querying (the query's half of the contract)

Index design is db-design's job; using one is yours. A query defeats its own index when it:

• wraps the indexed column in a function: WHERE lower(email) = $1 skips an index on email (needs an index on lower(email));
• leads with a wildcard: LIKE '%foo' cannot use a b-tree;
• mismatches type: WHERE id = '42' (text vs int) may force a cast + seq scan;
• ORs across columns the planner can't combine — often better as UNION.