sql-idioms

SQL Idioms and Patterns

SQL rewards set-based thinking, explicit joins, and query plan awareness. Idiomatic SQL = readable, performant, migration-safe.

Scope: SQL coding idioms. For database design principles, load @.agents/rules/database-design-principles.md.

Query Patterns

1. CTEs over subqueries for readability:

   -- ✅ CTE — readable, debuggable
   WITH active_tasks AS (
       SELECT id, title, priority, user_id
       FROM tasks
       WHERE status = 'active'
   )
   SELECT u.name, COUNT(at.id) AS task_count
   FROM users u
   JOIN active_tasks at ON u.id = at.user_id
   GROUP BY u.name;
   
   -- ❌ Nested subquery — hard to read
   SELECT u.name, (SELECT COUNT(*) FROM tasks t WHERE t.user_id = u.id AND t.status = 'active')
   FROM users u;
   

2. Window functions for ranking, running totals:

   SELECT title, priority,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
   FROM tasks;
   

3. Explicit JOIN syntax — never implicit joins in WHERE.

4. Parameterized queries — never string concatenation. (See .agents/rules/security-mandate.md.)

Migration Safety

For migration strategy (additive-first, two-phase drops, reversibility), see @.agents/rules/database-design-principles.md § Migrations.

1. Index creation: CONCURRENTLY on PostgreSQL for zero-downtime.
2. Idempotent DDL — use IF NOT EXISTS for tables/indexes; DO $$ ... pg_constraint check ... $$ for constraints.

Index Strategy

1. Choose the right index type:

   • B-tree (default): =, <, >, BETWEEN, IN, IS NULL
   • GIN: arrays, JSONB (@>), full-text search (@@)
   • GiST: geometric data, range types, nearest-neighbor (KNN)
   • BRIN: large time-series tables (10-100x smaller than B-tree)
   • Hash: equality-only (marginally faster than B-tree for =)

2. Composite indexes — column order matters:

   -- Equality columns first, range columns last (leftmost prefix rule)
   CREATE INDEX idx ON orders (status, created_at);
   -- Works for: WHERE status = 'pending'
   -- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
   -- Does NOT work for: WHERE created_at > '2024-01-01' (alone)