SQL query patterns: parameterized queries, keyset pagination, UPSERT, window functions, CTEs, aggregation with FILTER, soft delete, audit trails, row-level security, and migration best practices. Use when writing or reviewing SQL queries and schema changes.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
SQL query patterns: parameterized queries, keyset pagination, UPSERT, window functions, CTEs, aggregation with FILTER, soft delete, audit trails, row-level security, and migration best practices. Use when writing or reviewing SQL queries and schema changes.
SQL Patterns Skill
When to Activate
Writing SQL queries for a new feature
Reviewing SQL for performance or security issues
Designing database schema for a new entity
Writing database migrations
Optimizing slow queries
Setting up row-level security or audit logging
Replacing OFFSET pagination with keyset cursors on a large or growing table
Adding UPSERT logic to eliminate check-then-insert race conditions in concurrent writes
Core Query Patterns
SELECT — always explicit columns
-- WRONG: hides schema changes, transfers unnecessary dataSELECT*FROM users WHERE id = $1;
-- CORRECTSELECT id, name, email, created_at FROM users WHERE id = $1;
Parameterized queries — non-negotiable
-- PostgreSQL ($N placeholders)SELECT id, name FROM users WHERE email = $ is_active $;
id, name users email ? is_active ?;
1
AND
=
2
-- MySQL/SQLite (? placeholders)
SELECT
FROM
WHERE
=
AND
=
Explicit JOIN types
-- INNER JOIN: only rows with matches in both tablesSELECT u.name, o.total
FROM users u
INNERJOIN orders o ON u.id = o.user_id;
-- LEFT JOIN: all users, even those with no ordersSELECT u.name, COALESCE(SUM(o.total), 0) AS lifetime_value
FROM users u
LEFTJOIN orders o ON u.id = o.user_id
GROUPBY u.id, u.name;
Pagination
Keyset (cursor) pagination — for large tables
-- First pageSELECT id, name, created_at
FROM users
WHERE is_active =TRUEORDERBY created_at DESC, id DESC
LIMIT 20;
-- Next page (cursor = last row's (created_at, id))SELECT id, name, created_at
FROM users
WHERE is_active =TRUEAND (created_at, id) < ($1, $2) -- cursor conditionORDERBY created_at DESC, ID DESC
LIMIT 20;
Why not OFFSET? OFFSET N reads and discards N rows — O(N). Keyset is O(log N) with a composite index.
-- Required index for keyset pagination aboveCREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC)
WHERE is_active =TRUE;
OFFSET pagination — only for small datasets
-- Acceptable when total count < ~10k rowsSELECT*FROM categories ORDERBY name LIMIT 50OFFSET $1;
Aggregation Patterns
Conditional counts with FILTER
SELECT
DATE_TRUNC('month', created_at) ASmonth,
COUNT(*) AS total_orders,
COUNT(*) FILTER (WHERE status ='completed') AS completed,
COUNT(*) FILTER (WHERE status ='refunded') AS refunded,
SUM(total) FILTER (WHERE status ='completed') AS revenue
FROM orders
WHERE created_at >= NOW() -INTERVAL'12 months'GROUPBY1ORDERBY1;
Window functions
-- Running totalSELECT
id,
amount,
SUM(amount) OVER (PARTITIONBY user_id ORDERBY created_at) AS running_total
FROM transactions;
-- Rank within groupSELECT
user_id,
product_id,
purchase_count,
RANK() OVER (PARTITIONBY user_id ORDERBY purchase_count DESC) AS rank
FROM user_product_stats;
-- Lag/lead for time-seriesSELECTdate,
revenue,
LAG(revenue, 1) OVER (ORDERBYdate) AS prev_revenue,
revenue -LAG(revenue, 1) OVER (ORDERBYdate) AS change
FROM daily_revenue;
CTEs (Common Table Expressions)
-- Readable multi-step queryWITH active_users AS (
SELECT id, name, email
FROM users
WHERE is_active =TRUEAND created_at >= NOW() -INTERVAL'90 days'
),
user_order_counts AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE created_at >= NOW() -INTERVAL'90 days'GROUPBY user_id
)
SELECT
u.name,
u.email,
COALESCE(o.order_count, 0) AS recent_orders
FROM active_users u
LEFTJOIN user_order_counts o ON u.id = o.user_id
ORDERBY recent_orders DESC;
UPSERT
-- PostgreSQL ON CONFLICTINSERT INTO user_preferences (user_id, key, value, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id, key)
DO UPDATESETvalue= EXCLUDED.value,
updated_at = NOW();
-- Insert only if not exists (no update needed)INSERT INTO feature_flags (key, enabled)
VALUES ($1, FALSE)
ON CONFLICT (key) DO NOTHING;
Schema Patterns
Standard table template
CREATE TABLE entities (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
-- domain columns here
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
deleted_at TIMESTAMPTZ -- NULL = active (soft delete)
);
-- Auto-update updated_atCREATETRIGGER set_updated_at
BEFORE UPDATEON entities
FOREACHROWEXECUTEFUNCTION trigger_set_timestamp();
Soft delete
-- DeleteUPDATE entities SET deleted_at = NOW() WHERE id = $1;
-- Query active recordsSELECT*FROM entities WHERE deleted_at ISNULL;
-- Convenience viewCREATEVIEW active_entities ASSELECT*FROM entities WHERE deleted_at ISNULL;
Index Strategy
-- Single column — most commonCREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite — for frequent multi-column filterCREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial — for filtered queries (much smaller index)CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status ='pending';
-- Expression index — for function-based lookupsCREATE INDEX idx_users_lower_email ON users(LOWER(email));
Index rules
Every foreign key column should have an index
Composite indexes: put the most selective column first
Partial indexes save space when queries always filter a boolean/status
Never over-index — each index slows INSERT/UPDATE/DELETE
EXPLAIN ANALYZE
Run on any query that might be slow:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, COUNT(o.id)
FROM users u
LEFTJOIN orders o ON u.id = o.user_id
WHERE u.is_active =TRUEGROUPBY u.id;
Look for:
Seq Scan on large tables — likely missing index
Hash Join vs Nested Loop — nested loop is bad for large result sets
Rows estimate vs actual rows — large mismatches indicate stale statistics (ANALYZE)
Anti-Patterns
String Interpolation Instead of Parameterized Queries
Wrong:
-- Application code building a raw query string
query = "SELECT * FROM users WHERE email = '" + email + "'";
-- email = "' OR '1'='1" → returns every row (SQL injection)
Correct:
-- PostgreSQLSELECT id, name FROM users WHERE email = $1;
-- MySQL / SQLiteSELECT id, name FROM users WHERE email = ?;
Why: String interpolation allows SQL injection; parameterized queries separate code from data and let the database engine handle escaping.
SELECT * in Production Queries
Wrong:
SELECT*FROM orders WHERE user_id = $1;
-- transfers unused columns, breaks if column order or name changes
Correct:
SELECT id, status, total, created_at FROM orders WHERE user_id = $1;
Why:SELECT * transfers columns the application never uses, breaks application code when columns are added or renamed, and prevents the query planner from using index-only scans.
OFFSET Pagination on Large Tables
Wrong:
-- Page 500 of results: reads and discards 9 980 rows before returning 20SELECT id, name FROM products ORDERBY created_at DESC LIMIT 20OFFSET9980;
Correct:
-- Keyset pagination: jump straight to the cursor positionSELECT id, name, created_at FROM products
WHERE created_at < $1-- cursor from last row of previous pageORDERBY created_at DESC
LIMIT 20;
Why:OFFSET N forces the database to read and discard N rows on every page; keyset pagination navigates directly to the cursor position using an index, keeping cost O(log N).
Implicit Comma Joins Instead of Explicit JOIN Syntax
Wrong:
SELECT u.name, o.total
FROM users u, orders o -- implicit cross joinWHERE u.id = o.user_id; -- filter buried in WHERE
Correct:
SELECT u.name, o.total
FROM users u
INNERJOIN orders o ON u.id = o.user_id;
Why: Implicit comma joins obscure intent, mix join conditions with filter conditions in WHERE, and are easy to accidentally omit — producing a full Cartesian product.
Check-Then-Insert Race Condition Instead of UPSERT
Wrong:
-- Application code: two round-trips, not atomicSELECT1FROM user_preferences WHERE user_id = $1AND key = $2;
-- race: another process inserts here → next statement fails with unique violationINSERT INTO user_preferences (user_id, key, value) VALUES ($1, $2, $3);
Correct:
INSERT INTO user_preferences (user_id, key, value, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (user_id, key)
DO UPDATESETvalue= EXCLUDED.value, updated_at = NOW();
Why: The check-then-insert pattern has a TOCTOU race condition and requires two network round-trips; ON CONFLICT DO UPDATE is atomic and idempotent.
Checklist
No SELECT * in production queries
All user input via parameterized placeholders ($1, ?)
