SQL Safety Rules

Writes

1. Never write an UPDATE or DELETE without a WHERE clause. If you truly mean every row, say so explicitly in a comment on the line above and get it approved.
2. Count before you write. Run the SELECT with the same WHERE first and check the row count against what you expect. A number two orders of magnitude off means the predicate is wrong.
3. Wrap multi-statement writes in a transaction, and know whether your DDL is transactional. In Postgres it mostly is. In MySQL it mostly is not.
4. Bound every bulk write. Delete or update in batches with a LIMIT and a loop, not in one statement that locks a million rows.
5. Never write to production from an interactive session when a reviewed migration would do.

Reads

6. Every query on a large table needs an index-supported predicate. Check the plan. A sequential scan in a hot path is a future incident.
7. No SELECT * in application code. It breaks silently when a column is added and ships columns you do not need over the wire.
8. Paginate by a stable key, not by OFFSET. OFFSET on a large table gets slower the deeper it goes, and skips rows when data shifts underneath it.

Migrations

9. Every migration needs a down, or an explicit note that it is irreversible and why that is acceptable.
10. Adding a NOT NULL column with a default rewrites the table on older engines. Add nullable, backfill in batches, then add the constraint.
11. Never rename or drop a column in the same deploy that stops using it. Ship the code that ignores it, deploy, then drop in a later migration. Otherwise the old running version breaks the instant the migration lands.
12. Create indexes concurrently where the engine supports it. A plain CREATE INDEX takes a write lock for the duration.
13. Backfill outside the migration, in batches, with a resumable cursor. A backfill inside a migration holds a lock for as long as it runs.

Injection and identity

14. Parameterize every value. Always. String interpolation into SQL is the bug, even when the input "cannot" contain a quote.
15. Identifiers cannot be parameterized, so validate table and column names against an allowlist rather than passing them through.

Before running anything against production

16. Know the row count the statement will touch.
17. Know the lock it takes and for how long.
18. Know the rollback. If it is a restore from backup, know how long the restore takes and say so out loud before running.