SQL & Database Best Practices
Database mistakes are often invisible at small scale — a missing index, an N+1 query pattern — and become serious performance or correctness problems only once real data volume arrives, by which point they're harder to fix.
Last updated 2026-09-08
Always use parameterized queries, never string-concatenated SQL
Building a query by concatenating user input directly into SQL text is the classic SQL injection vulnerability. Parameterized queries (or an ORM using them under the hood) separate query structure from data entirely, closing this off.
Index columns used in WHERE, JOIN, and ORDER BY clauses
A query filtering or sorting on an unindexed column forces a full table scan, which is fine on a small table and increasingly slow as the table grows. Index the columns your actual query patterns rely on.
Avoid N+1 query patterns
Fetching a list, then separately querying related data per item in a loop, produces far more round-trips than a single join or a batched query would — a very common ORM-usage mistake that's easy to miss in development with small datasets.
Use transactions for multi-step operations that must succeed or fail together
A multi-step write (deduct from one account, credit another) that isn't wrapped in a transaction can leave data in a partially-completed, inconsistent state if it fails partway through.
Design schemas with appropriate normalization, but don't over-normalize
Excessive normalization can force expensive multi-table joins for common queries. A reasonable degree of denormalization for genuinely read-heavy, rarely-changing data is a legitimate tradeoff, not automatically a mistake.
Common Mistakes to Avoid
- ⚠String-concatenated SQL queries built from user input, open to SQL injection
- ⚠No index on columns that are actually filtered, joined, or sorted on in production queries
- ⚠N+1 query patterns from an ORM's lazy-loading relationships used inside a loop
- ⚠Multi-step writes with no transaction, risking a partially-completed state on failure
- ⚠Storing dates/times without timezone information, causing ambiguity later
Frequently Asked Questions
Do ORMs prevent SQL injection automatically?
Generally yes, when used as intended — most modern ORMs parameterize queries under the hood. The risk reappears if you drop into raw SQL strings or string-interpolate values into an ORM's raw-query methods.
How do I know if a query actually needs an index?
Check the query's execution plan (EXPLAIN in most databases) — it shows whether the database is doing a full table scan versus using an index, which is the definitive way to know rather than guessing based on table size alone.