SQL

User preferences and memory live in ~/Clawic/data/sql/ (see setup.md on first use, memory-template.md for the file format). If you have data at an old location (~/sql/ or ~/clawic/sql/), move it to ~/Clawic/data/sql/.

When To Use

• Writing, reviewing, or optimizing SQL: queries, joins, CTEs, window functions, upserts
• Designing tables, keys, types, indexes, and constraints, or normalizing an existing model
• Diagnosing a slow query, a deadlock, a lock timeout, wrong totals, or duplicated rows
• Planning migrations and DDL that must not take a live database down
• Operating a database: backups, restores, monitoring, pooling, replication, partitioning
• Moving data in or out: CSV loads, dumps, engine-to-engine migrations
• Not for PostgreSQL server internals (vacuum tuning, work_mem, xid wraparound) — that is pg; not for ORM-level modeling in a framework — that is prisma

Quick Reference

Core Rules

1. Parameterize values; allowlist identifiers. Placeholders (?, $1) stop injection for values, but table/column names cannot be bound — when those are dynamic, check them against a hardcoded allowlist, never interpolate user input. Full attack surface, including LIKE and ORDER BY injection: security.md.
2. BIGINT (or UUIDv7) primary keys by default. INT overflows at 2,147,483,647 — at a sustained 100 inserts/s that is 2.1B ÷ 100/s ≈ 248 days, and the fix is an outage-grade type change. Random UUIDv4 keys fragment the B-tree; UUIDv7/ULID keep insert locality (modeling.md).
3. Index for the query shape: equality columns first, then range/sort. (user_id, created_at) serves WHERE user_id = ? AND created_at > ? and WHERE user_id = ? alone — never created_at alone. A sequential scan on a filter matching more than roughly 5-10% of rows is the planner being right, not broken.
4. Index every foreign key column yourself. MySQL/InnoDB creates the index automatically; PostgreSQL, SQLite, and SQL Server do not. Without it, every join on the FK and every parent DELETE (worse with ON DELETE CASCADE) scans the whole child table — the slowest delete in most schemas is this one missing index.
5. Transactions stay short and never wait on the outside world. No HTTP calls, no user input inside BEGIN...COMMIT: open transactions hold locks, and in PostgreSQL they also block vacuum, causing table bloat. Anything open past the >1 min monitoring threshold (operations.md) gets investigated.
6. NULL is three-valued. NOT IN (subquery) returns zero rows if the subquery yields a single NULL — use NOT EXISTS. x = NULL is never true — use IS NULL. COUNT(col) skips NULLs; COUNT(*) counts rows. Aggregates over zero rows return NULL, not 0 — wrap in COALESCE when a chart or invariant expects a number.
7. Types that avoid the next migration. Money → NUMERIC/DECIMAL (float money loses cents in aggregation); timestamps → TIMESTAMPTZ stored as UTC (datetime.md); strings → TEXT in PostgreSQL and SQLite (varchar(255) is a cargo-cult limit you will later raise); MySQL charset → utf8mb4 (MySQL's utf8 is 3-byte and rejects emoji).
8. Migrations are additive first. Rename/retype/drop happens over multiple deploys with both versions live in between (expand-migrate-contract, operations.md). A single-deploy column rename breaks every instance still running old code.
9. Rank before you tune. pg_stat_statements ordered by total_exec_time (or the MySQL slow query log digested by pt-query-digest) tells you which query costs the most overall — usually not the one someone complained about. Total cost = mean latency × call count: a 5 ms query called 10,000×/min (50 s/min) outranks a 2 s report run hourly. Optimizing an unranked query is guessing.

Reading EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;  -- PostgreSQL
EXPLAIN ANALYZE SELECT ...;                                         -- MySQL >=8.0.18
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;          -- SQLite
SET STATISTICS PROFILE ON;                                          -- SQL Server (or the graphical plan)

Read actual behavior, not just the plan. Plain EXPLAIN shows estimates only, and estimates are the part that lies.

• Seq Scan / type: ALL on a large table with a selective filter → missing or unusable index (→ Traps for what disables one)
• Rows Removed by Filter high → the index found candidates but the filter did the work; extend the index to cover the filter
• Estimated vs actual rows off by more than 10× → stale stats: run ANALYZE tablename;. Still off → the planner assumes column independence; declare the correlation (CREATE STATISTICS on PostgreSQL >=10, histogram on MySQL 8)
• Buffers: read large vs hit → data is coming from disk; recheck on a warm cache before concluding
• Nested Loop over thousands of outer rows → usually the >10× misestimate above feeding a bad join choice
• Node-by-node interpretation, join algorithms, and what to change for each: performance.md

Index Strategy

-- Composite: equality columns first, range/sort last (rule 3)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Covering: index-only scan, no heap fetch (PostgreSQL >=11, SQL Server INCLUDE)
CREATE INDEX idx_orders_user ON orders(user_id) INCLUDE (total);

-- Partial/filtered: index only the rows you query (PostgreSQL, SQLite, SQL Server)
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';

-- Expression: make a function sargable (MySQL >=8.0.13 supports functional indexes)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

• A plain B-tree on a low-cardinality column (status with 5 values) rarely helps; a partial index on the rare value you actually query does.
• An index on (a, b) already serves WHERE a = ? — creating a second index on (a) alone adds write cost and reclaims nothing. Check for redundant prefixes before adding.
• PostgreSQL with a non-C locale ignores B-tree indexes for LIKE 'term%' — add the text_pattern_ops opclass for prefix search.
• Index-only scans still hit the heap for pages not marked all-visible; if EXPLAIN shows Heap Fetches high, the table needs a VACUUM (operations.md).
• Every index taxes writes and consumes disk: drop unused ones (pg_stat_user_indexes where idx_scan = 0), but only after uptime covers a full business cycle — month-end reports use indexes nothing else does.

Portability

Date functions, quoting, NULL sort order, collation, and the rest of the divergences: dialects.md.

Output Gates

Before emitting SQL, verify:

• Every value is a placeholder, and every dynamic identifier came from an allowlist?
• UPDATE/DELETE has a WHERE, or the full-table effect is explicitly intended?
• Destructive DML was previewed as the equivalent SELECT first?
• No 1:N join feeds an aggregate without pre-aggregation, and no DISTINCT is papering over one?
• LIMIT/TOP has a deterministic ORDER BY with a unique tiebreaker?
• New table: primary key type per rule 2, timestamps with zone, uniqueness scoped to the right columns, every FK column indexed (rule 4)?
• DDL against a live table: lock_timeout set, and the change is expand-only?
• Every construct used exists in the target engine (→ Portability, dialects.md)?

Configuration

User-dependent variables. Defaults apply until the user states a preference; store them in ~/Clawic/data/sql/config.yaml.

Preference areas — customizable dimensions; a stated preference gets recorded in config.yaml and applied:

• Tooling — client (psql, mysql, sqlite3, sqlcmd, a GUI), migration runner, SQL linter/formatter, local database as a container vs installed service — affects operations.md and testing.md examples
• Conventions — index and constraint naming, keyword casing, CTE vs subquery preference, comment style in DDL — affects every emitted statement
• Platform — managed service (RDS, Aurora, Cloud SQL, Neon, PlanetScale) vs self-hosted, available extensions, collation and locale — affects which features may be assumed
• Safety posture — how much confirmation destructive DDL/DML needs, whether production credentials are ever used directly, read-only-by-default sessions — affects operations.md and the destructive_guard gate
• Output format — full runnable script vs snippet, whether a down/rollback migration accompanies every up, inline comments — affects the shape of every deliverable
• Work order — schema-first vs query-first design, review gate before a migration reaches production — affects the sequence in modeling.md and operations.md
• Integrations — ORM in use, warehouse/BI target, monitoring stack — affects orm.md and analytics.md advice
• Constraints — vetoed features (no triggers, no stored procedures, no vendor extensions), compliance regime (retention windows, GDPR erasure) — affects schemas.md and security.md choices
• Thresholds — the operational budgets a shop usually standardizes: lock_timeout, batch_size, retry cap on deadlock/serialization failures, pool sizing rule, and the alert levels (connection saturation, disk free, transaction age, backup age) — affects transactions.md retry loops and the Alert Thresholds table in operations.md
• Cadence — how often scheduled work runs: restore drills, rollup and materialized-view refresh, the trailing recompute window, data-quality assertion runs, automated partition creation, backup interval — affects operations.md, analytics.md, schemas.md, and testing.md schedules

Traps

Where Experts Disagree

• Surrogate vs natural keys. Surrogate (BIGINT/UUID) is the default: natural keys change, and a changing PK cascades into every child row. Natural keys legitimately win on pure junction tables and on immutable code tables (ISO currency, country) where the extra id buys nothing (modeling.md).
• Foreign keys at scale. Some high-write shops drop FK constraints because they add per-write lock and index cost and complicate online schema-change tooling. Default: keep them — orphan cleanup costs more than the writes saved. Drop only with a measured write bottleneck and integrity enforced elsewhere.
• Logic in the database. Triggers and stored procedures give atomicity nothing in the app can match and centralize rules across many clients; they are also invisible to code review, hard to test, and version-controlled poorly. Default: constraints and simple audit triggers in the database, business workflows in the application (schemas.md).
• Soft delete everywhere. Undo and audit are real requirements, but a deleted_at on every table poisons every query and every unique constraint. Default: hard delete plus an audit log; soft-delete only the tables users actually restore (schemas.md).

Related Skills

More Clawic skills, get them at https://clawic.com/skills/sql (install if the user confirms):

• pg — PostgreSQL server internals: vacuum, work_mem, wraparound
• mysql — MySQL and InnoDB specifics
• sqlite — SQLite concurrency, pragmas, and type affinity
• prisma — Node.js ORM modeling
• dbt — warehouse transformations and tests

Feedback

• If useful, star it: https://clawic.com/skills/sql
• Latest version: https://clawic.com/skills/sql

Part of Clawic, the verified skill library. Get this skill: https://clawic.com/skills/sql.