Przejdź do treści
Biblioteka skilli
Programowanie · Budowanie i kod

sql-query-conventions

/sql-query-conventions

Ustala konwencje odczytu danych dla surowego SQL, PostgREST i ORM, czyli zapytań SELECT i projektu stronicowania.

Repozytorium
claude-config ↗
Licencja
MIT
Ostatnia zmiana repozytorium
27.10.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 27.10.2026, bez odwołań do wycofanych modeli i API.

Licencja

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. 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
Sekcje z nagłówków SKILL.md, w oryginale · 111 linii, 953 słów
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.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.