drizzle

Define tables with pgTable using plural snake_case names, a text or uuid surrogate primary key with $defaultFn, foreign keys with onDelete cascade, and spread standard timestamps from the helpers file. Put business uniqueness in a uniqueIndex rather than a composite primary key.

Use the db.select() builder with explicit leftJoin, groupBy, and count helpers instead of the relational db.query API with findMany or with clauses. The relational API generates fragile lateral joins with json_build_array that are hard to debug.

No, use a single-column surrogate primary key and enforce uniqueness with a uniqueIndex. Composite primary keys cannot include nullable columns, so expanding the uniqueness scope later forces a full primary key rebuild migration.

The convention is to avoid pgEnum because adding or removing members requires migrations. Use text or varchar columns with a TypeScript value type via $type instead, keeping the value types in a shared domain types module.

Run bun run db:generate to create the migration, rename the file meaningfully, then edit the SQL to use defensive clauses like ADD COLUMN IF NOT EXISTS, DROP TABLE IF EXISTS, and CREATE INDEX IF NOT EXISTS so migrations can be safely re-run.

Keep raw SQL only for features Drizzle cannot express, such as recursive CTEs with WITH RECURSIVE. Tighten it by using schema references in interpolations, scoping queries to the user, defining a narrow row interface, and adding regression tests.