Recipes

The recipes solve one specific problem at a time — complete, copy-paste code, with the theory of why right next to it. They are the practical complement to the Tutorial: the tutorial teaches you the concepts in order; the recipes show you how to apply them in real day-to-day situations.

How to read this

Each recipe is independent — jump straight to what you need. They all assume you have already been through the Tutorial (models, queries, execution).

Available

Recipe	Solves
Foreign keys & UNIQUE	FK, column UNIQUE and table constraints (composite/named), SQLAlchemy-style.
created_at / updated_at	Database-managed timestamps, without remembering to set them by hand.
Model mixins	withTimestamps, withSoftDelete, withAudit — the columns every table repeats, declared once.
Typed pagination	Paginated lists with total/pages, aligned with tempest-fastapi-sdk.
Aggregations & DISTINCT	count/sum/avg/min/max + typed GROUP BY and DISTINCT.
Upsert (ON CONFLICT)	Insert resolving a key conflict: DO NOTHING or DO UPDATE.
Active-record (opt-in)	save/update/delete/reload methods on a row, when you prefer it.
Unit of work (opt-in)	An identity map plus a batched flush, in one transaction. The default stays plain objects.
Logging & errors	See the SQL that runs (onQuery) and errors carrying the failing SQL/params.
Integrity errors (409)	Read the driver's error back into the constraint that refused the write.
Repository signals	preSave/postSave/preDelete/postDelete — react to writes without wrapping every call site.
Audit trail	One entry per change, with a before/after diff, in the same transaction.
Choosing the SQLite driver	node:sqlite (default) or better-sqlite3, via the engine option or the URL suffix.
Transactions and savepoints	Atomic operations with automatic commit/rollback and savepoints.
JSON and enum columns	Store typed objects and literal unions with type safety.
Custom column types	customType — Money, Temporal, branded ids: the conversion lives on the column.
Serialization (row ↔ JSON)	Convert rows to JSON and validate JSON back into a row.
Connecting to PostgreSQL	Swap SQLite for Postgres via the URL and tune the pool.
A durable queue on PostgreSQL	FOR UPDATE SKIP LOCKED, atomic counters and idempotency via a partial index.
Transactional outbox	Business row and event in one commit; a relay taking disjoint batches.
Column names	A snake_case schema behind a camelCase model, with no false drift.
PostgreSQL array columns	Typed text[]/integer[], with @>, <@ and &&.
Case-insensitive comparison	ieq for case-insensitive login — and the ilike trap.
Text search	Escaped, portable contains; fullText/fullTextRank with stemming on PostgreSQL.
Raw SQL at runtime	session.raw for the query the builder cannot yet express.
Expressions in where	Column vs column and SQL functions, to match a functional index.
Set operations	UNION, UNION ALL, INTERSECT, EXCEPT with branch shapes checked by the types.
Window functions	rowNumber, rank, lag/lead and windowed aggregates, through compute().
CTEs (WITH and WITH RECURSIVE)	Naming a query, and walking a tree in a single one.
MySQL: what changes	RETURNING via read-back, and what MySQL cannot do.

Looking for something bigger?

If you want to see it all put together in a project that runs, go to Examples: a Todo CLI, a blog with relations, a REST API, and the complete migrations workflow.