SQL coding best practices and patterns. Use when working with SQL files, writing migrations, or reviewing schema design.
This skill defines rules for writing correct, maintainable, and production-safe SQL — covering schema design, migrations, queries, and indexing.
EXPLAIN ANALYZE to verify query plans before merging slow queriesTIMESTAMPTZ (with timezone) over TIMESTAMP for any datetime columnSERIAL PRIMARY KEY for surrogate keysTEXT over VARCHAR(n) unless a hard limit is meaningful (e.g. VARCHAR(45) for IP addresses, VARCHAR(500) for tokens)TIMESTAMPTZ NOT NULL DEFAULT NOW() for audit timestamps (, )created_atcomputed_atDEFAULT NULL when adding optional columnsUNIQUE constraints on the table, not just application-level validationCHECK constraints to enforce domain rules at the DB level (e.g. state machines, enum-like values)Model lifecycle states as a VARCHAR column with a CHECK constraint listing all valid values:
ADD COLUMN state VARCHAR(20) NOT NULL DEFAULT 'created'
CHECK (state IN ('created', 'answering', 'submitted', 'completed', 'expired'));
COMMENT ON COLUMN quizzes.state IS 'Quiz lifecycle: created → answering → submitted → completed (or expired)';
Document the transition flow in a COMMENT ON COLUMN.
ON DELETE CASCADE on child tables when child rows are meaningless without the parentJSONB (not JSON) for structured data that may be queried or indexedscores, question_sequence)NN-description.sql (e.g. 21-add-ip-address.sql)IF EXISTS / IF NOT EXISTS on DROP and CREATE INDEX in migrations to make them idempotentWHERE session_token IS NOT NULL)ON CONFLICT ... DO UPDATE (upsert) instead of separate SELECT + INSERT/UPDATEWITH) to make complex queries readable; avoid deeply nested subqueriesCOALESCE to handle NULLs explicitly in calculations rather than relying on implicit NULL propagationEXTRACT(EPOCH FROM interval) * 1000 for millisecond durations from timestamp deltasLAG() window function for computing deltas between consecutive rows (e.g. answer timing)SELECT * in application queries — always list columns explicitlyDEFAULT on a large live table (it locks the table in old PG versions)TIMESTAMP without timezone — always use TIMESTAMPTZJSON — always JSONBCOUNT(*) to check existence — use EXISTS (SELECT 1 FROM ...) insteadSELECT, FROM, WHERE, INSERT, ON DELETE CASCADE, etc.snake_caseCREATE TABLE and ALTER TABLE ADD COLUMN blocks-- Migration: Short description of what this migration does
--
-- Purpose: Why this change is needed
-- Impact: What tables/columns/indexes are affected
-- Trade-off: Any integrity or performance trade-off (if applicable)
ALTER TABLE quizzes
ADD COLUMN ip_address VARCHAR(45) DEFAULT NULL;
-- Partial index: only index rows where token is set
CREATE INDEX idx_quizzes_session_token ON quizzes(session_token) WHERE session_token IS NOT NULL;
-- Composite index for analytics time-range queries
CREATE INDEX idx_answers_timing ON answers(quiz_id, answered_at);
INSERT INTO answers (quiz_id, question_id, choice_id)
VALUES ($1, $2, $3)
ON CONFLICT (quiz_id, question_id)
DO UPDATE SET choice_id = EXCLUDED.choice_id;
WITH ordered_answers AS (
SELECT
question_id,
answered_at,
LAG(answered_at) OVER (ORDER BY answered_at, id) AS prev_answered_at
FROM answers
WHERE quiz_id = $1
)
SELECT EXTRACT(EPOCH FROM (
answered_at - COALESCE(prev_answered_at, (SELECT created_at FROM quizzes WHERE id = $1))
)) * 1000
FROM ordered_answers
WHERE question_id = $2;
CHECK constraints must cover all valid values of an enum-like columnVARCHAR(45) to support both IPv4 and IPv6conf/initdb/CHECK constraint, drop the old one first (DROP CONSTRAINT IF EXISTS) then add the new oneINNER JOIN / LEFT JOIN — never implicit comma joinsEXPLAIN ANALYZE before shipping