sql-query-conventions
/sql-query-conventions
Read-path SQL conventions for raw SQL, PostgREST, and SQL-shaped ORMs. TRIGGER when: writing or modifying a SELECT query, PostgREST .from()/.select() chain, list-returning ORM call, or query-shape / pagination design. DO NOT TRIGGER when: writing write-path (INSERT/UPDATE/DELETE), DDL-only migrations, test fixtures, or document/KV stores.
- Author
- jcdendrite ↗
- Repository
- claude-config ↗
- Licence
- MIT
- Last repository change
- 27.10.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
Read-path SQL conventions for raw SQL, PostgREST, and SQL-shaped ORMs. TRIGGER when: writing or modifying a SELECT query, PostgREST .from()/.select() chain, list-returning ORM call, or query-shape / pagination design. DO NOT TRIGGER when: writing write-path (INSERT/UPDATE/DELETE), DDL-only migrations, test fixtures, or document/KV stores.
Original author description
Read-path SQL conventions for raw SQL, PostgREST, and SQL-shaped ORMs. TRIGGER when: writing or modifying a SELECT query, PostgREST .from()/.select() chain, list-returning ORM call, or query-shape / pagination design. DO NOT TRIGGER when: writing write-path (INSERT/UPDATE/DELETE), DDL-only migrations, test fixtures, or document/KV stores.
How it works
- 1. Every list query needs an explicit limit
- 2. No N+1 from iterating async DB calls
- 3. Prefer explicit column selection over SELECT
- Anti-patterns adjacent to these rules
- Verifying query plans
SKILL.md preview
---
name: sql-query-conventions
description: >
Read-path SQL conventions for raw SQL, PostgREST, and SQL-shaped ORMs.
TRIGGER when: writing or modifying a SELECT
query, PostgREST .from()/.select() chain, list-returning ORM call, or
query-shape / pagination design. DO NOT TRIGGER when: writing
write-path (INSERT/UPDATE/DELETE), DDL-only migrations, test fixtures,
or document/KV stores.
user-invocable: false
---
# SQL Query Conventions
Read-path conventions for relational stores. Each rule below names an
AI anti-pattern that scales badly and is easy to miss in review because
the query "works" at current data volume.
## 1. Every list query needs an explicit limit
Never emit an unbounded list query against a table that can grow. Always
set a limit — `LIMIT N` in SQL, `.limit(N)` / `.range(start, end)` in
PostgREST, `take` / `first` in ORMs. Do not rely on server defaults (no
ceiling in PostgREST; unbounded for raw Postgres).
- If the caller supplies a page size, validate it against a hard max.
- **Single-row queries need the right tool.** `.single()` (PostgREST)
*errors* if the result is not exactly one row — use it when the
caller expects exactly one and wants to surface the violation.
`.maybeSingle()` / `LIMIT 1` returns zero-or-one silently — use that
when absence is a valid outcome. Conflating them teaches a bug either
way (spurious errors on zero rows, or silently-wrong answers on
multiple).
- **Stable pagination requires a total-ordered `ORDER BY`** with a
deterministic tiebreaker (primary key) — without one, `LIMIT`/`OFFSET`
can shuffle rows between pages.
- **Prefer keyset pagination over offset for large page numbers.**
`OFFSET N` is O(N) in Postgres — the database must scan and discard N
rows. Keyset form: `WHERE (created_at, id) < (:last_ts, :last_id)
ORDER BY created_at DESC, id DESC LIMIT N`. Constant cost per page.
Why: unbounded queries DoS the database and caller memory once data
volume outgrows what dev/test exercises.
## 2. No N+1 from iterating async DB calls
Never loop over records with per-item database calls. This is the most
common AI anti-pattern in data-access code.
```
# WRONG
for id in ids:
row = await db.from('table').select().eq('id', id)
results.append(row)
```
Fix by batching:
- **SQL/PostgREST:** `WHERE id IN (:ids)` / `.in('id', ids)`.
- **Joins:** if the loop is "for each row, fetch related row," use a
join.
- **Complex batching:** wrap in a server-side function / stored
procedure.
**If the list can grow unbounded, chunk it.** `IN`-list limits by
backend:
| Backend | Practical ceiling | When to switch approach |
|---|---|---|
| Postgres (driver bind params) | ~1,000–5,000 | Use `= ANY($1::int[])` — one bind param, unbounded by protocol |
| PostgREST / Supabase `.in()` | ~few hundred | URL-length bound (nginx/Kong default ~4–8 KB); switch to an RPC taking an array param |
| BigQuery | ~1,000 | Switch to `UNNEST(@ids)` or JOIN against a temp/staging table — 1 MB query text limit, planner broadcasts big IN lists |
| Snowflake | 16,384 IN expressions | Switch to `ARRAY_CONTAINS` / temp-table join well before the hard limit |
Why: N+1 loops scale O(N) round-trips — fine at test-data volumes,
ruinous at production volume — and big IN lists add planner cost and
round-trip-size limits on top.
## 3. Prefer explicit column selection over `SELECT *`
When the table may grow wide columns (blobs, JSON, vectors, or
PII-sensitive fields), select only the columns the caller needs.
- Use `SELECT col1, col2` / `.select('col1, col2')` for read paths with a known shape.
- `SELECT *` is fine for internal admin tooling and tests.
- When a new wide column is added, callers that use `SELECT *` silently start fetching extra bytes — detectable only by someone spotting the bandwidth change.
Why: `SELECT *` couples read-path performance to schema evolution
instead of localizing that coupling to the callsite that needs the new
column.
## Anti-patterns adjacent to these rules
- **Check-then-insert:** `SELECT ... EXISTS` followed by `INSERT` is racy. Use `INSERT ... ON CONFLICT` with a unique constraint.
- **Read-then-write without locking:** in a transaction that reads a
row, decides based on it, then updates it, use `SELECT ... FOR
UPDATE` — without the lock, a concurrent writer can change the row
between your SELECT and UPDATE and your decision runs on stale data.
- **`COUNT(*)` for existence checks:** use `EXISTS (...)` / `SELECT 1
... LIMIT 1` instead — both short-circuit on the first row, while
`COUNT(*)` scans every match; reserve `COUNT(*)` for actual counts,
where a matching index and `WHERE` clause let Postgres plan an
index-only scan.
- **Unindexed filter / sort columns:** if a list query filters or orders by a column that isn't indexed, call it out. Add a migration, or document the known scan if it's acceptable.
- **Reading a whole table to compute in app code what belongs in the database:** if the answer is a scalar, an aggregate, or a ranked/partitioned result, compute it in SQL — do not fetch rows to JavaScript/Python and reduce there. Specifically: `GROUP BY` for aggregates, `ROW_NUMBER() / RANK() / DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)` for ranking or "first N per group," `SUM/AVG/MIN/MAX` with `FILTER (WHERE ...)` for conditional aggregates. Most of these exist because AI agents don't reach for window functions by default.
## Verifying query plans
When a query involves a new index, a large `IN` list, a join order that
could be wrong, or a filter on a column whose selectivity is unclear,
verify with `EXPLAIN` (plan only) or `EXPLAIN ANALYZE` (plan + actual
execution) before declaring the query good. Specifically check:
- Did the planner use the expected index (`Index Scan` / `Index Only
Scan`) rather than a `Seq Scan`?
- Is the join order sensible? A `Hash Join` over 10M rows when a
`Nested Loop` over 20 indexed rows was intended is a red flag.
- Does the `Rows` estimate match reality (from `ANALYZE` output)? A
large misestimate means out-of-date stats — run `ANALYZE <table>`.
Don't skip this step on the basis of "the query is simple" — planner
surprises on simple queries are how indexes silently stop being used.
Versions
The database has no other skills named sql-query-conventions.
Database snapshot: 13.11.2026 · package version d63c7b6c1ac7 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 90drizzle-orm-best-practices
Dialect-aware Drizzle ORM conventions for safe query, mutation, and transaction boundaries. Use when writing, debugging, or reviewing schemas, queries, transactions, or raw SQL. - 100sql-optimization
Diagnoses and fixes slow SQL using real query plans, index design, sargable rewrites, keyset pagination and ORM fixes such as N+1 queries. Use when a query, endpoint, report or job is slow, database CPU or IO is high, an EXPLAIN plan needs reading, an index needs designing, or… - 90orm-tradeoffs
Use ORMs for their productivity while avoiding N+1 queries and knowing when to drop to raw SQL. Use when working with an ORM or debugging the performance problems ORMs quietly cause. - 100php-sql-conventions
ACTIVATE when writing SQL queries in PHP code, using Doctrine DBAL, or formatting database queries. ACTIVATE for 'SQL', 'query', 'DBAL', 'JOIN', 'SELECT'. Covers: query direction convention (start from known entity), nowdoc formatting for SQL, column listing, JOIN ordering. DO… - 95drizzle-conventions
ACTIVATE when defining database schemas, writing queries, creating migrations, or implementing repositories with Drizzle ORM. ACTIVATE for 'Drizzle', 'pgTable', 'drizzle-kit', 'db.query', 'db.select', '$inferSelect', 'migration'. Covers: schema definition with pgTable,… - 90dev10x-db:sql-handoff
Use when a write (UPDATE, INSERT, DELETE, DDL) must reach a database the agent can only read through dev10x-db:db-psql — so the supervisor gets one paste-safe statement aimed at the right database, and the agent proves the effect before building on it. TRIGGER when: the agent…