SQL — policy & pitfalls

Baseline SQL knowledge (CTE, window functions, joins, DML, Flyway/Liquibase syntax) is assumed. This skill encodes policy and the traps that keep appearing in review — dialect-specific, blocking-behavior-specific, and optimizer-specific.

Setup Check (run first)

Before writing non-trivial SQL:

1. Dialect — identify the target (Postgres, MySQL, MariaDB, SQLite, SQL Server). Optimizer behavior, locking rules, and index features differ. Never assume Postgres semantics on MySQL or vice versa.
2. Migration tool — check db/migration/ (Flyway) or db/changelog/ (Liquibase). Both are immutable: already-applied migrations must NEVER be edited.
3. EXPLAIN access — if the DBHub MCP is configured, use it to run EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN FORMAT=JSON (MySQL) against a representative dataset. Advice without a real query plan is a guess.
4. Table sizes — query advice differs by orders of magnitude between 10K and 100M rows. Use DBHub (or pg_stat_user_tables / information_schema.tables) to check sizes when choosing pagination, indexing, and migration strategy.

DBHub MCP

When the DBHub MCP server is available (configured in mcp/.mcp.json), use it actively: