Skip to content
Skills Library
Programming · Building & coding

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.

Licence
MIT
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

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. 1. Every list query needs an explicit limit
  2. 2. No N+1 from iterating async DB calls
  3. 3. Prefer explicit column selection over SELECT
  4. Anti-patterns adjacent to these rules
  5. Verifying query plans
Original SKILL.md headings · 111 lines, 953 words
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…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.