1
# SQL Safety Rules
2
3
## Writes
4
5
1. **Never write an UPDATE or DELETE without a WHERE clause.** If you truly mean every
6
   row, say so explicitly in a comment on the line above and get it approved.
7
2. **Count before you write.** Run the SELECT with the same WHERE first and check the
8
   row count against what you expect. A number two orders of magnitude off means the
9
   predicate is wrong.
10
3. **Wrap multi-statement writes in a transaction**, and know whether your DDL is
11
   transactional. In Postgres it mostly is. In MySQL it mostly is not.
12
4. **Bound every bulk write.** Delete or update in batches with a LIMIT and a loop, not
13
   in one statement that locks a million rows.
14
5. **Never write to production from an interactive session** when a reviewed migration
15
   would do.
16
17
## Reads
18
19
6. **Every query on a large table needs an index-supported predicate.** Check the plan.
20
   A sequential scan in a hot path is a future incident.
21
7. **No SELECT * in application code.** It breaks silently when a column is added and
22
   ships columns you do not need over the wire.
23
8. **Paginate by a stable key, not by OFFSET.** OFFSET on a large table gets slower the
24
   deeper it goes, and skips rows when data shifts underneath it.
25
26
## Migrations
27
28
9. **Every migration needs a down, or an explicit note that it is irreversible** and
29
   why that is acceptable.
30
10. **Adding a NOT NULL column with a default rewrites the table** on older engines.
31
    Add nullable, backfill in batches, then add the constraint.
32
11. **Never rename or drop a column in the same deploy that stops using it.** Ship the
33
    code that ignores it, deploy, then drop in a later migration. Otherwise the old
34
    running version breaks the instant the migration lands.
35
12. **Create indexes concurrently** where the engine supports it. A plain CREATE INDEX
36
    takes a write lock for the duration.
37
13. **Backfill outside the migration**, in batches, with a resumable cursor. A backfill
38
    inside a migration holds a lock for as long as it runs.
39
40
## Injection and identity
41
42
14. **Parameterize every value. Always.** String interpolation into SQL is the bug, even
43
    when the input "cannot" contain a quote.
44
15. **Identifiers cannot be parameterized**, so validate table and column names against
45
    an allowlist rather than passing them through.
46
47
## Before running anything against production
48
49
16. **Know the row count** the statement will touch.
50
17. **Know the lock** it takes and for how long.
51
18. **Know the rollback.** If it is a restore from backup, know how long the restore
52
    takes and say so out loud before running.
53