drizzle

Define tables with plural snake_case names, a single-column surrogate primary key using text with idGenerator or uuid with defaultRandom, snake_case columns, cascade foreign keys, and spread timestamps from the helpers module. Put business uniqueness in a uniqueIndex rather than a composite primary key.

Always use the db.select() builder API and never the db.query relational API like findMany or findFirst with with clauses. The relational API generates complex lateral joins with json_build_array that are fragile and hard to debug.

Drizzle supports composite primary keys, but this style guide forbids them on new tables including junction tables. Use a surrogate uuid primary key with a uniqueIndex on the pair, since composite PKs cannot include nullable columns and force rebuilds when uniqueness scope grows.

Default to text or varchar columns with a TypeScript value type via $type instead of pgEnum. Database enums require migrations to add members and are awkward to rename or remove, so reserve pgEnum for effectively immutable value sets with explicit justification.

Prefer Drizzle builders and use expression-level sql<T> only for features lacking helpers like JSON paths, casts, and CASE. Keep raw execute queries only for cases like recursive CTEs, with schema references in interpolations, explicit user scoping, and a narrow row interface.

Loose JSONB types hide the data contract and make downstream access untyped. Define a concrete interface describing the expected shape, and do not add speculative metadata columns at all until a concrete writer ships alongside them.