sql-query-conventions
/sql-query-conventions
Ustala konwencje odczytu danych dla surowego SQL, PostgREST i ORM, czyli zapytań SELECT i projektu stronicowania.
- Autor
- jcdendrite ↗
- Repozytorium
- claude-config ↗
- Licencja
- MIT
- Ostatnia zmiana repozytorium
- 27.10.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 1
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 27.10.2026, bez odwołań do wycofanych modeli i API.
MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.
Co robi i kiedy
Ustala konwencje odczytu danych dla surowego SQL, PostgREST i ORM, czyli zapytań SELECT i projektu stronicowania.
Oryginalny opis (EN)
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.
Jak działa
- 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
Podgląd SKILL.md
---
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.
Wersje
W bazie nie ma innych skilli o nazwie sql-query-conventions.
Migawka bazy z 13.11.2026 · wersja paczki d63c7b6c1ac7 (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 90drizzle-orm-best-practices
Dostarcza konwencje Drizzle ORM zależne od dialektu dla bezpiecznych granic zapytań, mutacji i transakcji, przy pisaniu i przeglądzie schematów, zapytań, transakcji i surowego SQL. - 100sql-optimization
Diagnozuje i przyspiesza wolny SQL na podstawie planów zapytań, projektu indeksów i przepisania zapytań, także przy ORM. - 90orm-tradeoffs
Pomaga korzystać z ORM dla wydajności pracy, unikać zapytań N+1 i rozpoznawać, kiedy sięgnąć po surowy SQL, także przy problemach z wydajnością powodowanych przez ORM. - 100php-sql-conventions
Podpowiada konwencje zapytań SQL w PHP i Doctrine DBAL: kierunek zapytania od znanej encji, nowdoc i kolejność JOIN. - 95drizzle-conventions
Pilnuje konwencji Drizzle ORM przy schematach pgTable, zapytaniach, migracjach i repozytoriach w TypeScript. - 90dev10x-db:sql-handoff
Przekazuje zapis do bazy, której agent może tylko odczytać, jako jedno gotowe polecenie SQL dla nadzorcy, i sprawdza jego efekt.