# SQL Conventions

Defaults below target PostgreSQL. MySQL-specific notes are marked.

## Formatting / style

- Keywords **lowercase**: `select`, `from`, `where`, `join`. (Some teams prefer uppercase — match the file you are editing; never mix in one statement.)
- All identifiers `snake_case`: tables, columns, indexes, constraints.
- Always use explicit `as` for column aliases: `select count(*) as user_count`.
- Always state the join type: `inner join`, `left join`, `cross join`. **Never** a bare `join`.
- One column per line in `select`; one table or join per line in `from`.
- Prefer **CTEs** (`with foo as (...)`) over deeply nested subqueries.
- Dates / timestamps in ISO 8601: `'2026-05-17'`, `'2026-05-17T12:00:00Z'`.

## Schema design

- Every table has an `id` primary key.
  - PostgreSQL: `id bigint generated always as identity primary key`.
  - MySQL: `id bigint unsigned not null auto_increment primary key`.
- Foreign keys named `<referenced_table>_id`: `user_id`, `order_id`.
- Singular table names (`user`, `order`, `invoice_line`) unless the project already uses plural — match, do not mix.
- `comment on table ... is '...'` for every table; `comment on column ...` for non-obvious columns. (MySQL: `comment '...'` inline.)
- Timestamps: `created_at`, `updated_at`, both `timestamptz` in Postgres. Keep timezone discipline explicit — store UTC, render in the user's zone at the edge.
- Soft delete: `deleted_at timestamptz null`. Index it if you query on it. Add a partial index `where deleted_at is null` for hot reads.
- Use `not null` aggressively. `null` should mean "unknown", not "default".
- Use enum or `check` constraints over free-text status columns.
- Composite indexes: column order matches your `where` / `order by` patterns; left-most prefix wins.

## Migrations

- One change per migration file. Name: `<timestamp>_<verb>_<noun>.sql` (e.g. `20260517_120000_add_user_deleted_at.sql`).
- Migrations are **forward-only** in production. Write a "down" migration only if your tool requires it and you genuinely intend to run it.
- Splitting a destructive change:
  1. Add nullable column.
  2. Backfill (separate deploy).
  3. Add `not null` / index `concurrently`.
  4. Drop the old column.
- `create index concurrently` in Postgres for hot tables — avoids the write lock.
- Application-deploy migrations **never** destroy data. Destructive operations are a separate, explicit step gated on backups.

## Query practices

- Always filter on indexed columns for OLTP queries. Verify with `explain` / `explain analyze`.
- Never `select *` in application code (migrations and ad-hoc inspection are fine).
- Parameterised queries from the application layer. String concatenation is SQL injection.
- `limit` every query that could return an unbounded set.
- `returning *` (Postgres) on `insert` / `update` / `delete` instead of a follow-up `select`.
- For pagination, prefer keyset (`where id > $last_seen`) over offset on large tables.

## MySQL-specific

- Engine: **InnoDB** (default). Charset: `utf8mb4`, collation `utf8mb4_0900_ai_ci` (8.0+).
- Quote identifiers with backticks when they collide with reserved words.
- `select ... for update` only inside an explicit transaction.
- `on duplicate key update` for upserts; in Postgres use `on conflict (...) do update`.

## Tests

- Run query tests against a **real** database via `testcontainers` or a disposable schema. In-memory SQLite is not equivalent to Postgres / MySQL — different SQL dialect, different isolation, different `null` semantics.
- Migrations themselves must be tested: apply from an empty schema in CI; assert the resulting structure.
- Seed test data via factory functions (one per table), not raw `insert` statements copy-pasted into each test.
- Verify performance assumptions with `explain` in CI for queries flagged as hot.

## Tools

- Formatting: `pg_format`, `sqlfluff`, `sql-formatter`.
- Linting / safety: `squawk` (Postgres migration linter — catches missing `concurrently`, locking foot-guns, etc.).
- Schema diff: `migra` (Postgres), `mysqldiff` (MySQL).