SQL Conventions
Query style, migration patterns, naming conventions, and safety rules
Details
Language / Topic
_UUniversal
Category
Tooling
Rules
balanced
- Use uppercase for SQL keywords (`SELECT`, `FROM`, `WHERE`) and snake_case for table/column names.
- Always use parameterized queries or prepared statements — never interpolate user input into SQL strings.
- Avoid `SELECT *` — list columns explicitly to control output and prevent breakage on schema changes.
- Write migrations as versioned, sequential files. Every migration must have an `up` and `down` path.
- Add indexes on foreign keys, columns used in `WHERE`/`JOIN`/`ORDER BY`, and unique constraints.
- Use meaningful constraint names: `fk_orders_user_id`, `uq_users_email`, `chk_amount_positive`.
- Wrap multi-statement changes in explicit transactions — never leave partial writes on failure.
- Use `NOT NULL` with sensible defaults. Nullable columns should be an intentional design choice, not the default.
- Prefer `EXISTS` over `IN` for subqueries — it short-circuits and handles NULLs correctly.