SQL Guide

Applies to: PostgreSQL 15+, MySQL 8+, Database Migrations, Query Optimization

Core Principles

1. Parameterized Queries Always: Never concatenate user input into SQL strings -- use bind parameters ($1, ?, :name) without exception
2. Explicit Over Implicit: Name all constraints, specify column lists in INSERT, avoid SELECT * in production code
3. Migrations Are Immutable: Once applied to a shared environment, never modify a migration -- create a new one
4. Indexes Are Not Free: Every index speeds reads but slows writes; justify each index with a query plan
5. Transactions Are Boundaries: Keep transactions short, choose the correct isolation level, and always handle rollback

Guardrails

Naming Conventions

• Tables: snake_case, plural (users, order_items, audit_logs)
• Columns: snake_case, singular (email, created_at, is_active)
• Primary keys: id (integer or UUID depending on project convention)