SQL Patterns

SQL Patterns

Best practices for writing safe, performant, and maintainable SQL. All examples use parameterized queries to prevent SQL injection.

Security-First Principles

Every query MUST use parameterized inputs. Never concatenate user input into SQL strings.

-- DANGEROUS: SQL injection vulnerability
SELECT * FROM users WHERE email = '" + email + "';

-- SAFE: Parameterized query
SELECT id, name, email FROM users WHERE email = $1;

Destructive Operation Safety

Never run destructive operations without a WHERE clause and explicit confirmation.

Always preview before modifying:

-- Step 1: Preview what will be affected
SELECT id, email, status FROM users WHERE status = 'inactive';

-- Step 2: Verify the count
SELECT COUNT(*) FROM users WHERE status = 'inactive';

-- Step 3: Only then modify (parameterized)
DELETE FROM users WHERE status = $1;  -- $1 = 'inactive'

Query Optimization

Using EXPLAIN

Always analyze query plans before deploying queries that touch large tables.

-- Basic explain
EXPLAIN SELECT id, name FROM users WHERE email = $1;

-- With execution stats (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > $1;

Reading EXPLAIN Output

Selective Column Queries

-- BAD: SELECT * fetches unnecessary data, breaks if schema changes
SELECT * FROM users WHERE status = $1;

-- GOOD: Explicit columns, only what you need
SELECT id, name, email, status FROM users WHERE status = $1;

Pagination

-- BAD: OFFSET-based pagination degrades with large offsets
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- GOOD: Cursor-based pagination using the last seen ID
SELECT id, name FROM products
WHERE id > $1
ORDER BY id
LIMIT 20;

Indexing Guide

Index Types

Composite Indexes

Column order matters. Place equality columns first, then range columns.

-- Query pattern: WHERE status = $1 AND created_at > $2
-- Index matches the query pattern: equality first, range second
CREATE INDEX idx_status_created ON orders (status, created_at);

-- This index will NOT help the query above efficiently:
CREATE INDEX idx_created_status ON orders (created_at, status);

When to Add Indexes

When NOT to Index

• Tables with fewer than ~1,000 rows (seq scan is faster)
• Columns that are rarely queried
• Columns with very low cardinality on large tables (unless partial index)
• Write-heavy tables where index maintenance cost exceeds read benefit

Schema Design

Normalization Quick Reference

When to Denormalize

Denormalize only when you have measured performance problems and understand the tradeoff.

Foreign Key Design

-- Always define foreign keys with explicit actions
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index foreign keys (PostgreSQL does NOT auto-index them)
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);

Migration Best Practices

Reversible Migrations

Every migration should have a rollback strategy.

-- Migration: up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Migration: down (rollback)
ALTER TABLE users DROP COLUMN phone;

Zero-Downtime Migration Patterns

Adding a column (safe):

-- Step 1: Add nullable column (no lock, no rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill in batches (avoid locking entire table)
UPDATE users SET phone = $1 WHERE id BETWEEN $2 AND $3;

-- Step 3: Add NOT NULL constraint after backfill (if needed)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Renaming a column (requires coordination):

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Backfill (batched)
UPDATE users SET full_name = name WHERE id BETWEEN $1 AND $2;

-- Step 3: Application reads from both, writes to both
-- Step 4: Switch application to new column only
-- Step 5: Drop old column in later migration
ALTER TABLE users DROP COLUMN name;

Migration Checklist

• Migration has a rollback script
• Large table changes use batched updates
• No ALTER TABLE ... ADD COLUMN ... DEFAULT on large tables (pre-PG 11)
• Index creation uses CONCURRENTLY on production tables
• Foreign key columns are indexed
• Migration tested on a copy of production data
• No data loss on rollback

-- SAFE: Concurrent index creation (doesn't lock writes)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- UNSAFE: Locks the table during creation
CREATE INDEX idx_users_email ON users (email);

Transaction Patterns

Isolation Levels

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts SET balance = balance - $1 WHERE id = $2;
UPDATE accounts SET balance = balance + $1 WHERE id = $3;

COMMIT;

Avoiding Deadlocks

Deadlocks occur when two transactions wait for each other's locks.

-- BAD: Transaction A locks row 1 then 2; Transaction B locks row 2 then 1
-- Transaction A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (concurrent):
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- DEADLOCK!

-- GOOD: Always lock rows in consistent order (by ID)
-- Both transactions lock id=1 first, then id=2
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - $1 WHERE id = 1;
UPDATE accounts SET balance = balance + $1 WHERE id = 2;
COMMIT;

Advisory Locks

Use for application-level mutual exclusion without table locks.

-- Acquire an advisory lock (blocks until available)
SELECT pg_advisory_lock($1);

-- Do exclusive work...

-- Release the lock
SELECT pg_advisory_unlock($1);

-- Non-blocking variant (returns true/false)
SELECT pg_try_advisory_lock($1);

Anti-Patterns

Parameterized Query Examples

Node.js (pg)

// SAFE: Parameterized query
const result = await pool.query(
  'SELECT id, name, email FROM users WHERE email = $1 AND active = $2',
  [email, true]
);

// SAFE: INSERT with RETURNING
const { rows } = await pool.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
  [name, email]
);

Python (psycopg2)

# SAFE: Parameterized query
cursor.execute(
    "SELECT id, name, email FROM users WHERE email = %s AND active = %s",
    (email, True)
)

# SAFE: Batch insert
from psycopg2.extras import execute_values
execute_values(
    cursor,
    "INSERT INTO users (name, email) VALUES %s",
    [(name, email) for name, email in user_data]
)

Go (database/sql)

// SAFE: Parameterized query
row := db.QueryRowContext(ctx,
    "SELECT id, name, email FROM users WHERE email = $1",
    email,
)

Quick Reference: Safe Defaults