SQL

SQL (language skill)

A knowledge skill, not an agent: it loads from context whenever SQL is being written or reviewed. Role agents (/be, /dba, /rev, /arch) route into it; it carries the language, the dialect references carry the engines.

Trigger

Load this skill when:

• Editing or creating a .sql file
• Writing or reviewing a schema migration
• A raw SQL string is embedded in application code, in any host language — sqlx/ rusqlite in Rust, JdbcTemplate or @Query(nativeQuery = true) on the JVM, sql tagged templates in JS/TS, DB-API strings in Python
• Designing an index, reading an EXPLAIN plan, or tuning a query shape
• Writing stored procedures, triggers, or views

Do NOT load for:

• ORM entity mapping where no SQL string is written — that is /be
• Analytics / warehouse / dbt modeling — that is /data

Context

SQL is code that happens to run in someone else's engine. It is reviewed like code, planned like code, and versioned like code — the fact that it executes inside a database server changes none of that. This skill enforces three outcomes: no injection surface anywhere a value meets a statement, no silent schema drift between what the migrations say and what the database is, and no query shape merged without plan evidence.

Documentation Lookup (MANDATORY)

Features are version-gated. A statement that parses on your laptop can fail — or silently behave differently — on the deployed server. Verify the target server's actual version before using a feature, with a live check, not the project README.

Version-check one-liners:

Current major lines (verify at write time — these drift):

• PostgreSQL 18 (2025-09).
• MySQL — 8.4 is the widely deployed LTS baseline; 9.7 the current LTS; 26.x the calendar-versioned Innovation line.
• Oracle AI Database 26ai — the 23ai family renumbered; on-prem GA January 2026.
• SQLite 3.51.3+ (2026-03, contains a WAL-reset database-corruption fix).

Use Context7 MCP / WebSearch for the target dialect and version before relying on any feature or default you have not verified on that version.

Volatility note. Managed platforms (RDS/Aurora, Cloud SQL, serverless Postgres offerings, hosted MySQL forks) often run one or two major versions behind the current release, and gate, rename, or pre-tune features. The version check above runs against the deployed server — that answer wins over any changelog.

Dialect selection (read first)

SQLite embedded from Rust also has a dedicated embedding reference on the Rust side: ../rust/references/sqlite-rusqlite.md (connection setup and pooling in code; the engine semantics stay in references/sqlite.md).

Two edge rules:

• A repo can be multi-dialect — a server engine plus embedded SQLite for local state is common. Load each branch for the files that touch it; do not average the dialects.
• ORM-generated SQL is /be's concern right up until someone writes or overrides a statement by hand — from that moment the statement is this skill's artifact like any other, review rules included.

The altitude contract

Three altitudes, one owner each:

• /arch decides which engines and topologies exist — one Postgres vs Postgres-plus-cache, sharding, multi-region. Its reference: ../../../architecture/solution-architect/references/data-and-storage.md.
• This skill owns the committed SQL artifacts — queries, DDL, migration files, their correctness, their plans, their dialect semantics.
• /dba operates the engine — executes risky migrations on live tables, replication/backup/vacuum/pooling, production plan regressions.

Boundary examples:

• This skill writes the expand-migrate-contract migration files; /dba runs the online-schema-change play on the two-terabyte table.
• This skill reads EXPLAIN before merge; /dba chases the plan that regressed in production.
• Transaction scope and isolation chosen in the code are this skill; connection-pool sizing and server tuning that make those choices survive load are /dba.
• Vector/ANN index tuning belongs to /dba's ../../data/dba/dba/references/vector-db-tuning.md — link to it, never duplicate it.

Core standards (BLOCKING at review)

1. SQL is code. Every statement lives in the repo, is reviewed like code, and lints clean (sqlfluff 4.x); nothing is pasted from a REPL into production. A statement that exists only in a session history is unreviewable, unrepeatable, and unrevertable.

pipx install sqlfluff
sqlfluff lint --dialect postgres <paths>    # or: mysql | oracle | sqlite

2. Parameterized-only. Values enter via bind parameters, always — string-assembled SQL is a BLOCKING finding. Identifiers (table/column names) cannot be bound: map them through a static allowlist, never through user input. LIKE patterns escape % and _ before binding.

BAD  (BLOCKING)   "SELECT * FROM users WHERE email = '" + email + "'"
GOOD              "SELECT id, email FROM users WHERE email = ?"    bind(email)
ORDER BY column   sort_col = ALLOWED_SORTS[input]    -- static map; identifiers can't be bound
LIKE pattern      bind("%" + escape_like(term) + "%")  -- escape % and _ inside term

3. Migrations are versioned and linear. Timestamp- or sequence-numbered files; the applied set is tracked in the database (schema_migrations table; PRAGMA user_version for SQLite); an applied migration is never edited — a fix is a new migration. The repo states its rollback policy: the default is forward-only with restore-tested backups; write down-migrations only if you actually rehearse them, because an unrehearsed down-migration is a second untested change. Concurrent branches can mint colliding numbers — the branch that merges second renumbers; the in-database applied set is what makes a collision loud instead of silent.

4. Expand-migrate-contract for anything a live app reads. Additive change first → dual-write/backfill (batched) → switch reads → contract in a later release. NOT NULL and type changes are always staged: add nullable → backfill → validate → enforce. The one-release big-bang ALTER is how a deploy takes the product down.

release N:    ADD COLUMN new_col NULL                  (expand — additive, instant)
release N:    dual-write from the app; backfill in batches
release N+1:  switch reads to new_col; validate no NULLs remain
release N+2:  enforce NOT NULL; drop the old column    (contract)

5. Transaction scope is deliberate. One unit of work per business action; never a user or network wait inside an open transaction; the isolation level is chosen and commented wherever it matters; retries on serialization failure exist wherever SERIALIZABLE (or snapshot conflicts) can fire. A transaction held across an HTTP call is a lock held across someone else's outage. The comment shape when isolation matters:

-- REPEATABLE READ: balance check and debit must see one snapshot.
-- Serialization-failure retry lives in the caller (max 3 attempts).

6. EXPLAIN-before-merge. Every new query shape ships with plan evidence at realistic cardinality — a hundred-row dev table lies about everything (the planner seq-scans it, and is right to). The scratch-container check below is the cheap way to get honest evidence before merge.

7. Every index is justified. The migration adding an index names the query it serves, in a comment; unused indexes get removed — each one taxes every write. The N+1 workflow: dev-environment query logging on; a loop containing a query is a review smell; fix with joins, batched IN, or window functions.

8. NULL is a third truth value. Every predicate over a nullable column is reviewed for NULL behaviour: NOT IN with a NULL in the subquery returns nothing; aggregates skip NULLs; = NULL is never true; and UNIQUE-with-NULLs differs per dialect — Postgres, MySQL, and SQLite allow repeated NULLs in a UNIQUE column (Postgres 15+ can opt out with NULLS NOT DISTINCT), while Oracle does not index an all-NULL key at all; the dialect references carry the details. If you have not stated what a NULL does to the predicate, you have not finished the query.

9. Test data is managed. Deterministic factories/fixtures; each test isolated by a rolled-back transaction or a throwaway schema; never production dumps with PII — a test database is not an exemption from data protection.

10. Zero schema drift. Migrations are the single source of truth for schema. Any second DDL surface — ORM model definitions, bootstrap IF NOT EXISTS blocks — is verified equal to migration-head by a test, or removed. Two schema sources become two answers to "what does this table look like", and the stale one is always the one someone reads. The test shape: dump the migrated schema and the second surface's schema, normalize, diff — an empty diff or a red build.

The scratch-container empirical check

A conclusion reasoned from code about what rows will do — which end a LIMIT truncates, what a NULL does to a predicate, whether two columns can desync — is a hypothesis until it runs. This extends the verify-landed precedent: a scratch database and four lines of SQL settle in ninety seconds what argument gets wrong across several rounds. Build the smallest table that can exhibit the question, then ask it.

Reach for it whenever the open question is one of:

• which end a LIMIT truncates when the ORDER BY has no tiebreaker
• what a NULL does to a predicate, an aggregate, or a unique constraint
• what ON CONFLICT / upsert actually does to the non-listed columns
• collation, case, or truncation behaviour on the target version
• what a trigger fires on — and what it sees in old/new

Rules of the check:

• Never run it against a shared dev server — the point is a throwaway engine at a known version, matching the deployment target.
• Seed data deterministically inside the heredoc; the reviewer must be able to rerun the exact check from the PR text.
• Paste the output into the PR next to the claim it supports. Reasoning that was never executed is labelled as reasoning.

Postgres

The password is not optional — the official image refuses to start without POSTGRES_PASSWORD, so a bare docker run postgres fails and the check never happens.

docker run --rm -d --name scratch-pg -e POSTGRES_PASSWORD=scratch postgres:18
until docker exec scratch-pg pg_isready -U postgres -q; do sleep 1; done
docker exec -i scratch-pg psql -U postgres -v <<'SQL'
-- build the smallest table that can exhibit the question, then ask it
CREATE TABLE t (id int PRIMARY KEY, v text);
INSERT INTO t VALUES (1, NULL), (2, 'x');
SELECT count(*) FROM t WHERE v NOT IN (SELECT v FROM t);   -- 0: NULL poisons NOT IN
SQL
docker rm -f scratch-pg

MySQL

Initialization takes tens of seconds — poll before piping SQL, or the check silently runs against a server that is not up yet.

docker run --rm -d --name scratch-my -e MYSQL_ROOT_PASSWORD=scratch mysql:8.4
until docker exec scratch-my mysqladmin ping -uroot -pscratch --silent 2>/dev/null; do sleep 2; done
docker exec -i scratch-my mysql -uroot -pscratch <<'SQL'
CREATE DATABASE scratch;
USE scratch;
-- smallest table that can exhibit the question, then ask it
SQL
docker rm -f scratch-my

SQLite

No container needed — the engine is a binary on your machine.

sqlite3 :memory: <<'SQL'
CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT) STRICT;
INSERT INTO t VALUES (1, NULL), (2, 'x');
SELECT count(*) FROM t WHERE v NOT IN (SELECT v FROM t);
SQL

Oracle

gvenzl/oracle-free is an example image, not an endorsement; the licence terms are Oracle's. The first pull is multiple gigabytes and startup takes minutes. If no Oracle instance is reachable and the pull is not worth it, skipping the empirical check for Oracle is acceptable — say so ("unverified on Oracle") rather than guessing and presenting the guess as a result.

docker run --rm -d --name scratch-ora -e ORACLE_PASSWORD=scratch gvenzl/oracle-free
until docker exec scratch-ora healthcheck.sh 2>/dev/null; do sleep 10; done
docker exec -i scratch-ora sqlplus -s system/scratch@localhost/FREEPDB1 <<'SQL'
-- smallest table that can exhibit the question, then ask it
SQL
docker rm -f scratch-ora

Templates

Migration file header

Every migration file starts with this header. A migration whose header cannot be filled in is a migration whose consequences have not been thought through.

-- migration: 20260809143000_orders_status_index
-- purpose:   open-orders dashboard list; the status+created_at scan was sequential
-- phase:     expand (contract: none — additive only)
-- index:     idx_orders_status_created serves
--            SELECT ... FROM orders WHERE status = $1 ORDER BY created_at DESC LIMIT $2
-- locks:     CREATE INDEX CONCURRENTLY — no table-level write lock expected
-- rollback:  forward-only (repo policy); DROP INDEX CONCURRENTLY is the rehearsed undo
CREATE INDEX CONCURRENTLY idx_orders_status_created
    ON orders (status, created_at DESC);

Applied-set tracking table

-- The one legitimate IF NOT EXISTS: the tracker's own bootstrap.
CREATE TABLE IF NOT EXISTS schema_migrations (
    version    VARCHAR(64) PRIMARY KEY,  -- the migration file's timestamp/sequence id
    applied_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

SQLite projects use PRAGMA user_version instead — the pattern is in references/sqlite.md.

Batched backfill

-- One bounded batch, keyed by a PK range — the portable shape. (A LIMIT subquery
-- over the target table is NOT portable: MySQL rejects updating a table selected
-- in its own subquery (error 1093), and Oracle has no LIMIT.) The runner advances
-- :last_id by the range width each pass until past MAX(id), pausing between
-- batches and watching replica lag. Never one UPDATE across the whole table:
-- that is one giant transaction, one giant lock, one giant replication event.
UPDATE orders SET status_v2 = status
WHERE status_v2 IS NULL
  AND id >  :last_id
  AND id <= :last_id + 10000;

Keyset pagination

-- First page
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: strictly after the last row seen — cost independent of depth,
-- and the (created_at, id) tiebreaker makes the ordering deterministic.
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Row-value comparison (a, b) < ($1, $2) works on Postgres and SQLite (3.15+). MySQL parses it but does not use the composite index for it, and Oracle rejects it outright — on those two, write the expanded form:

WHERE created_at < :1 OR (created_at = :1 AND id < :2)

Workflow note

This skill owns no gates. Schema/migration changes are an ARCH_APPROVED trigger in the workflow — the workflow-engine decides whether the gate fires, and /dba reviews migration safety when it does. New query shapes carry their EXPLAIN evidence into the code review; the reviewer's pass is references/sql-review.md.

Proportionality still applies: a one-line fix to an existing query does not summon the full gauntlet. But a migration — any migration — at least states its lock expectation and its rollback stance, because those are the two questions the incident review will ask.

Checklist

Before writing

• Target server version confirmed with a live check, not assumed
• The dialect reference for the target engine loaded
• NULL and failure cases listed for every predicate over a nullable column
• Plan expectation stated ("index scan on X", "hash join at N rows")

Before merge

• sqlfluff lint clean on the correct dialect
• EXPLAIN output at realistic cardinality attached to the PR
• Any claim about row behaviour backed by a scratch check — or explicitly labelled unverified, with the reason
• Migration linear — no renumbering, no edits to applied files — with a complete header
• Backfills batched, with a stated batch size and pause between batches

Anti-Patterns to Avoid

Deep-dive references (load on demand)

Load the branch the current file touches — not the whole directory.

• references/postgres.md — Postgres semantics: MVCC/vacuum implications for SQL, lock-aware DDL, plan reading, PG-specific features by version.
• references/mysql.md — MySQL/InnoDB semantics: online DDL algorithms, gap locking, replication-aware migration constraints.
• references/oracle.md — Oracle semantics: PL/SQL, DDL auto-commit, optimizer behaviour, 23ai/26ai feature gates.
• references/sqlite.md — SQLite semantics: single-writer/WAL, the per-connection pragma contract, user_version migrations, STRICT tables, FTS5.
• references/sql-review.md — the reviewer's pass: no-config lint/grep commands, migration checklist, fixed severity mapping.
• ../rust/references/sqlite-rusqlite.md — embedding SQLite from Rust (driver-side templates; pairs with references/sqlite.md).
• ../../data/dba/dba/references/vector-db-tuning.md — /dba's ANN/vector index tuning; link, never duplicate.
• ../../../architecture/solution-architect/references/data-and-storage.md — /arch's engine and topology decisions this skill implements but does not make.