Guides Drizzle ORM for Quorum—schema layout in server/db/schema.ts, snake_case columns, explicit relations, and type-safe query patterns (select, insert, update, joins, relational query API). Use when writing or reviewing DB access, tRPC handlers that query Postgres, Drizzle relations, or when the user mentions schema, SQL, or type-safe queries.
server/db/schema.ts (canonical). If tables are not implemented yet, use hoa-platform-dev-doc.md §15 as the intended shape—still match project rules (snake_case columns, explicit relations()).server/db/index.ts (typically ctx.db in tRPC).| Rule | Detail |
|---|---|
| Columns | snake_case in the database string; TypeScript property names follow Drizzle column defs (often camelCase in code, e.g. communityId maps to community_id). |
| Tables | pgTable('table_name', { ... }) — plural/snake table names as in §15 (community, unit, resident, …). |
| Enums | pgEnum exports align with Zod z.enum([...]) in routers. |
| FKs | .references(() => otherTable.id) on columns. Self-references may need (): typeof vote => vote.id style for inference. |
| Uniqueness | Composite uniques via table callback + unique().on(...) (see ballot in §15). |
Always define relations() for every table that is joined or loaded via db.query.*. Mirror FK direction: one / many with fields / references.
import { eq, and, or, desc, asc, sql, inArray, isNull } from 'drizzle-orm'
import * as schema from '~/server/db/schema'
// or: import { resident, household, unit, ... } from '~/server/db/schema'
Adjust import alias to match the project (~/server vs relative).
db.select().from(table).where(...) so selected columns infer row types..select({ id: table.id, name: table.name }) for stable return types and smaller payloads.Promise<SomeDto>.const rows = await db
.select({
id: schema.resident.id,
email: schema.resident.email,
})
.from(schema.resident)
.where(eq(schema.resident.householdId, householdId))
.orderBy(desc(schema.resident.createdAt))
.limit(50)
.from(a).innerJoin(b, eq(a.fk, b.id)).leftJoin + isNull / or as needed.inArray(column, ids) (guard empty array—short-circuit or skip clause).sql template only when operators are awkward in the query builder; prefer Drizzle helpers first (project rule: no raw SQL unless necessary).db.insert(table).values({ ... }).returning() — prefer .returning({ id: table.id }) to get typed keys.db.update(table).set({ ... }).where(eq(table.id, id)).returning().hidden, archivedAt, status) if the domain uses them.db.transaction(async (tx) => { ... }) when multiple writes must succeed or fail together.db.query)Use when you want nested shapes without manual joins:
await db.query.resident.findFirst({
where: eq(schema.resident.id, id),
with: {
household: {
with: { unit: true },
},
},
})
Requirements: relations() must exist for resident → household → unit. Name keys in with to match relation property names on the schema relations object.
| Goal | Pattern |
|---|---|
| Scoped by community | Join through unit / household / communityId on the entity (see §15 FKs). |
| Pagination | limit + offset or cursor on (createdAt, id) with and / lt / gt. |
| “Exists” | exists subquery via sql or separate select + check; avoid N+1 in loops. |
| Count only | db.select({ count: sql<number>count(*)::int }).from(...) or db.$count if configured. |
TRPCError (see new-router skill).sql strings—use bound parameters / eq, inArray, etc.hoa-platform-dev-doc.md §15..cursor/skills/new-router/SKILL.md.