Terminal
Terminal/Skill
Gives SQL query, indexing and migration rules that catch common traps, such as NOT IN with NULLs, large OFFSET pages and blocking schema changes.
By JetBrains
Works with
npx skills add jetbrains/junie-extensions --skill sql-patternsBaseline 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.
Before writing non-trivial SQL:
db/migration/ (Flyway) or db/changelog/ (Liquibase). Both are immutable: already-applied migrations must NEVER be edited.EXPLAIN (ANALYZE, BUFFERS) (Postgres) or EXPLAIN FORMAT=JSON (MySQL) against a representative dataset. Advice without a real query plan is a guess.pg_stat_user_tables / information_schema.tables) to check sizes when choosing pagination, indexing, and migration strategy.When the DBHub MCP server is available (configured in mcp/.mcp.json), use it actively:
EXPLAIN ANALYZE — run against real data to verify index usage and row estimates before declaring a query optimized.pg_stat_user_tables (Postgres) or information_schema.tables (MySQL) to determine pagination and migration strategy.Do not suggest schema changes or index additions without first confirming the current schema via DBHub or the codebase.
SELECT * in application code (breaks on schema change, pulls unused columns).NOT EXISTS / LEFT JOIN ... IS NULL instead of NOT IN when the subquery can return NULL (NOT IN with a single NULL returns zero rows — silently).WHERE id > :last ORDER BY id LIMIT n) for large / user-driven lists. OFFSET degrades as it grows.WHERE, JOIN ON, ORDER BY columns. Composite index order matters: leftmost columns usable, tail columns only with leading predicates.EXPLAIN ANALYZE before declaring a query "optimized" — optimizer choice depends on stats, table size, and dialect.NOT IN (SELECT ... ) on nullable columns. WHERE x NOT IN (NULL, 1, 2) returns empty. Use NOT EXISTS or LEFT JOIN ... IS NULL.WHERE. WHERE YEAR(created_at) = 2024 ignores the index — use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'. Or create a functional / expression index.OR across different columns in WHERE when one side isn't indexed — split into UNION ALL (or create a combined index).ALTER TABLE ... ADD COLUMN NOT NULL without default on a large table — locks the table, rewrites every row. Split into: add nullable column → backfill in batches → add NOT NULL constraint.CREATE INDEX on a large write-active table without CONCURRENTLY (Postgres) / ALGORITHM=INPLACE LOCK=NONE (MySQL 8.0) / WITH (ONLINE = ON) (SQL Server) — blocks writes for the duration.V3__add_email.sql breaks every environment. Add a new migration.SELECT COUNT(*) on large tables for pagination UIs — on Postgres it's expensive; index-only scan is possible only when the visibility map is fully up-to-date (Postgres 9.2+, write-heavy tables rarely qualify). Use approximate counts (pg_class.reltuples) or a "load more" cursor instead.DISTINCT to fix duplicate rows — it masks a broken JOIN. Fix the join.JOIN x ON x.id = y.id_str) — disables indexes, silently wrong if types differ. Match types.| Load when | File |
|---|---|
| Writing / reviewing Flyway / Liquibase migrations; running online schema changes | references/migrations.md |
| Diagnosing a slow query; choosing indexes; reading EXPLAIN output | references/performance.md |
When producing SQL:
-- Postgres only).When reviewing SQL: call out MUST-DO / MUST-NOT violations, point out NULL / type / locking traps, and suggest the minimal fix. Prefer EXPLAIN-verified advice over cargo-cult rules.