Drizzle Orm Patterns

Rules:

• Use uuid for all primary keys with defaultRandom()
• Use timestamp with { withTimezone: true } always
• Include created_at and updated_at on all tables
• Use text() for strings (not varchar unless there's a specific constraint)
• Use notNull() explicitly — never rely on database defaults for nullability

Type Inference

import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

export type Exam = InferSelectModel<typeof exams>;
export type NewExam = InferInsertModel<typeof exams>;

Export both select and insert types from every schema file.

Query Patterns

Select with conditions

const result = await db
  .select()
  .from(exams)
  .where(and(
    eq(exams.is_active, true),
    eq(exams.workspace_id, tenantContext.workspaceId),
  ));

Insert

const [created] = await db
  .insert(exams)
  .values({ title: 'New Exam', duration_minutes: 60 })
  .returning();

Update

const [updated] = await db
  .update(exams)
  .set({ title: 'Updated Title', updated_at: new Date() })
  .where(eq(exams.id, examId))
  .returning();

Always use .returning() for insert/update operations.

Tenant-Scoped Repository Pattern

export function createExamRepository(db: TenantDb) {
  return {
    async findById(id: string): Promise<Exam | null> {
      const [exam] = await db
        .select()
        .from(exams)
        .where(eq(exams.id, id))
        .limit(1);
      return exam ?? null;
    },
    
    async create(data: NewExam): Promise<Exam> {
      const [created] = await db
        .insert(exams)
        .values(data)
        .returning();
      return created;
    },
  };
}

Rules:

• Repository receives db (tenant-scoped connection) as parameter
• Repository NEVER resolves tenant context — it's passed in
• Repository functions are pure data access — no business logic
• Repository NEVER performs cross-tenant queries

Relation Definitions

import { relations } from 'drizzle-orm';

export const examsRelations = relations(exams, ({ many }) => ({
  questions: many(questions),
  attempts: many(attempts),
}));

Define relations alongside schema tables for type-safe joins.

Forbidden Patterns

• No raw SQL unless absolutely necessary (use sql template tag when required)
• No cross-tenant joins — each tenant has its own database
• No SELECT * — explicitly select needed columns for performance
• No schema definitions in packages/ — schemas live in apps/api/src/db/
• No Drizzle client instantiation outside tenant resolver — use the pooled connection

Migration Generation

bun drizzle-kit generate          # Generate migration from schema diff
bun drizzle-kit generate --name=add-exam-field   # Named migration

Always review generated SQL before committing. See db-migration-governance skill for migration safety rules.

Verdict Protocol

VERDICT: PASS   — schema and queries follow Drizzle patterns
VERDICT: BLOCKED — forbidden pattern detected (specify which)