DOMAIN:BACKEND:DRIZZLE_ORM

OWNER: urszula (Team Alfa), maxim (Team Bravo)
UPDATED: 2026-03-24
SCOPE: Drizzle ORM patterns for all GE client projects
VERSION: Drizzle ORM 0.38+ / Drizzle Kit 0.30+
DATABASE: PostgreSQL 15+

---

DRIZZLE:WHY_DRIZZLE

WHY_NOT_PRISMA:
- Prisma uses custom .prisma schema language — not TypeScript
- Prisma generates a heavy runtime client (~2MB)
- Prisma's query engine is a Rust binary — complicates deployment
- Prisma's type inference is slow for large schemas

WHY_DRIZZLE:
- Pure TypeScript schema — use functions, variables, generics
- Thin SQL wrapper — ~7.4KB, no runtime code generation
- Types inferred directly from schema — zero codegen step
- SQL-like query builder — if you know SQL, you know Drizzle
- Relational query API for typed nested joins
- Serverless/edge-friendly — no binary dependencies

---

DRIZZLE:SCHEMA_DEFINITION

TABLE_DEFINITION

// db/schema/users.ts
import { pgTable, text, timestamp, boolean, integer, uuid, index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  role: text('role', { enum: ['admin', 'user', 'viewer'] }).notNull().default('user'),
  isActive: boolean('is_active').notNull().default(true),
  loginCount: integer('login_count').notNull().default(0),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  index('users_email_idx').on(table.email),
  index('users_created_at_idx').on(table.createdAt),
]);

RULE: use uuid for primary keys with .defaultRandom() — never auto-increment serial
RULE: use text with enum option for string enums — type-safe, no separate enum table
RULE: always include createdAt and updatedAt timestamps with timezone
RULE: define indexes in the table callback — not as separate statements

REUSABLE_COLUMN_PATTERNS

// db/schema/_columns.ts
import { timestamp, uuid } from 'drizzle-orm/pg-core';

export const timestamps = {
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
};

export const softDelete = {
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
};

export const primaryId = {
  id: uuid('id').defaultRandom().primaryKey(),
};

// Usage
export const projects = pgTable('projects', {
  ...primaryId,
  name: text('name').notNull(),
  clientId: uuid('client_id').notNull().references(() => clients.id),
  ...timestamps,
  ...softDelete,
});

RELATIONS

// db/schema/relations.ts
import { relations } from 'drizzle-orm';
import { users } from './users';
import { projects } from './projects';
import { tasks } from './tasks';

export const usersRelations = relations(users, ({ many }) => ({
  projects: many(projects),
}));

export const projectsRelations = relations(projects, ({ one, many }) => ({
  owner: one(users, {
    fields: [projects.ownerId],
    references: [users.id],
  }),
  tasks: many(tasks),
}));

export const tasksRelations = relations(tasks, ({ one }) => ({
  project: one(projects, {
    fields: [tasks.projectId],
    references: [projects.id],
  }),
  assignee: one(users, {
    fields: [tasks.assigneeId],
    references: [users.id],
  }),
}));

TYPE_INFERENCE

// db/schema/types.ts
import { InferSelectModel, InferInsertModel } from 'drizzle-orm';
import { users } from './users';

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

// For partial updates
export type UserUpdate = Partial<Omit<NewUser, 'id' | 'createdAt'>>;

---

DRIZZLE:MIGRATIONS

WORKFLOW

DEVELOPMENT:
1. Edit schema TypeScript files
2. Run: npx drizzle-kit generate
3. Review generated SQL in drizzle/ directory
4. Run: npx drizzle-kit migrate (or migrate() in code)
5. Commit schema + migration files together

PRODUCTION:
1. Run migrations via migrate() function during app startup
2. NEVER use drizzle-kit push in production
3. ALWAYS review generated SQL before deploying

DRIZZLE_CONFIG

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './db/schema/*.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
});

PROGRAMMATIC_MIGRATION

// db/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { db } from './connection';

export async function runMigrations() {
  console.log('Running migrations...');
  await migrate(db, { migrationsFolder: './drizzle/migrations' });
  console.log('Migrations complete');
}

ZERO_DOWNTIME_MIGRATIONS

RULE: never rename or retype a column in a single step
RULE: use expand-contract pattern across multiple deploys
RULE: always create indexes CONCURRENTLY in production

EXPAND_CONTRACT_PATTERN (example: rename column "name" to "full_name"):

DEPLOY_1 (expand):
  1. Add new column "full_name"
  2. Backfill: UPDATE users SET full_name = name WHERE full_name IS NULL
  3. Application writes to BOTH name and full_name

DEPLOY_2 (migrate):
  1. Application reads from full_name, writes to both
  2. Verify all rows have full_name populated

DEPLOY_3 (contract):
  1. Application reads/writes only full_name
  2. Drop old column "name"

SAFE_INDEX_CREATION

-- In migration file, add CONCURRENTLY manually
-- drizzle-kit does NOT add CONCURRENTLY by default
CREATE INDEX CONCURRENTLY IF NOT EXISTS "users_email_idx" ON "users" ("email");

ANTI_PATTERN: drizzle-kit push in production — no migration files, no audit trail, no rollback
FIX: always drizzle-kit generate then drizzle-kit migrate

ANTI_PATTERN: trusting generated SQL without review — Drizzle may generate destructive DDL
FIX: always read the generated .sql file before deploying

ANTI_PATTERN: running migrations in every pod startup in multi-replica deployments
FIX: run migrations as a separate init container or one-time job

---

DRIZZLE:QUERY_PATTERNS

SELECT_QUERIES

// Simple select
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
});

// Select with conditions
const activeAdmins = await db.select()
  .from(users)
  .where(and(
    eq(users.role, 'admin'),
    eq(users.isActive, true),
  ));

// Select specific columns
const emailList = await db.select({
  id: users.id,
  email: users.email,
}).from(users);

RELATIONAL_QUERIES (AVOIDING N+1)

// BAD: N+1 query pattern
const projects = await db.select().from(projects);
for (const project of projects) {
  // This runs a query PER project — N+1
  project.tasks = await db.select().from(tasks).where(eq(tasks.projectId, project.id));
}

// GOOD: Relational query — single query with join
const projectsWithTasks = await db.query.projects.findMany({
  with: {
    tasks: true,
    owner: {
      columns: { id: true, name: true, email: true },
    },
  },
  where: eq(projects.isActive, true),
});

// GOOD: Explicit join when you need more control
const results = await db.select({
  project: projects,
  taskCount: count(tasks.id),
}).from(projects)
  .leftJoin(tasks, eq(projects.id, tasks.projectId))
  .groupBy(projects.id);

INSERT_PATTERNS

// Single insert with return
const [newUser] = await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice',
}).returning();

// Bulk insert
await db.insert(tasks).values([
  { title: 'Task 1', projectId },
  { title: 'Task 2', projectId },
  { title: 'Task 3', projectId },
]);

// Upsert (insert or update on conflict)
await db.insert(users)
  .values({ email: 'alice@example.com', name: 'Alice Updated' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice Updated', updatedAt: new Date() },
  });

RULE: always use onConflictDoUpdate for upserts — never select-then-insert (race condition)

UPDATE_AND_DELETE

// Update with returning
const [updated] = await db.update(users)
  .set({ name: 'New Name', updatedAt: new Date() })
  .where(eq(users.id, userId))
  .returning();

// Soft delete
await db.update(users)
  .set({ deletedAt: new Date(), isActive: false })
  .where(eq(users.id, userId));

---

DRIZZLE:QUERY_BUILDER_VS_RAW_SQL

CHECK: can Drizzle's query builder express this query?
IF: yes THEN: use query builder — type-safe, composable
IF: no (window functions, CTEs, complex aggregations) THEN: use sql`` tagged template

// Raw SQL with Drizzle's sql`` tag — still parameterized, still safe
import { sql } from 'drizzle-orm';

const result = await db.execute(sql`
  WITH monthly_stats AS (
    SELECT
      date_trunc('month', created_at) AS month,
      COUNT(*) AS task_count,
      AVG(EXTRACT(EPOCH FROM (completed_at - created_at))) AS avg_duration_sec
    FROM tasks
    WHERE project_id = ${projectId}
    GROUP BY date_trunc('month', created_at)
  )
  SELECT * FROM monthly_stats
  ORDER BY month DESC
  LIMIT 12
`);

RULE: sql` template literals are parameterized — safe from SQL injection ANTI_PATTERN:sql.raw(userInput)` — bypasses parameterization, allows injection
FIX: never use sql.raw() with any user-provided value

---

DRIZZLE:TRANSACTIONS

// Basic transaction
const result = await db.transaction(async (tx) => {
  const [project] = await tx.insert(projects).values({
    name: 'New Project',
    ownerId: userId,
  }).returning();

  await tx.insert(tasks).values({
    title: 'Initial task',
    projectId: project.id,
  });

  return project;
});

// Transaction with rollback
await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`balance - ${amount}` })
    .where(eq(accounts.id, fromAccountId));

  const [fromAccount] = await tx.select()
    .from(accounts)
    .where(eq(accounts.id, fromAccountId));

  if (fromAccount.balance < 0) {
    tx.rollback();  // Throws, rolls back transaction
  }

  await tx.update(accounts)
    .set({ balance: sql`balance + ${amount}` })
    .where(eq(accounts.id, toAccountId));
});

RULE: keep transactions short — long transactions hold locks
RULE: never await external APIs inside transactions (network calls)
RULE: always handle tx.rollback() — it throws, catch it if needed

---

DRIZZLE:CONNECTION_POOLING

WITH_POSTGRES_JS

// db/connection.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const connectionString = process.env.DATABASE_URL!;

// Connection pool configuration
const sql = postgres(connectionString, {
  max: 20,                    // max connections in pool
  idle_timeout: 20,           // close idle connections after 20s
  connect_timeout: 10,        // timeout for new connections
  max_lifetime: 60 * 30,     // max connection lifetime 30min
  prepare: true,              // use prepared statements (faster)
});

export const db = drizzle(sql, { schema });

// Graceful shutdown — drain pool
export async function closeDatabase() {
  await sql.end();
}

POOL_SIZING_GUIDE

FORMULA: pool_size = (cpu_cores * 2) + effective_spindle_count
TYPICAL: 4 cores = ~10 connections
RULE: for most GE projects, 10-20 connections is appropriate
NEVER: set pool > 50 — PostgreSQL has diminishing returns beyond this
SERVERLESS: use connection pooler (PgBouncer or Neon) — set max to 5

ANTI_PATTERN: creating a new connection per request (no pooling)
FIX: use postgres() pool once at app startup, reuse via db instance

ANTI_PATTERN: pool size of 100+ — causes PostgreSQL contention
FIX: 10-20 connections for typical workloads, use EXPLAIN ANALYZE to tune

---

DRIZZLE:INDEXING

// In table definition
export const tasks = pgTable('tasks', {
  id: uuid('id').defaultRandom().primaryKey(),
  projectId: uuid('project_id').notNull().references(() => projects.id),
  assigneeId: uuid('assignee_id').references(() => users.id),
  status: text('status', { enum: ['todo', 'in_progress', 'done'] }).notNull().default('todo'),
  priority: integer('priority').notNull().default(0),
  title: text('title').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
}, (table) => [
  // Index on foreign keys — always
  index('tasks_project_id_idx').on(table.projectId),
  index('tasks_assignee_id_idx').on(table.assigneeId),
  // Composite index for common query pattern
  index('tasks_project_status_idx').on(table.projectId, table.status),
  // Partial index for active tasks
  index('tasks_active_idx').on(table.status).where(sql`status != 'done'`),
]);

RULE: always index foreign key columns
RULE: composite indexes — put most selective column first, or match query filter order
RULE: use jsonb() not json() for JSON columns — jsonb is indexed and faster
RULE: use partial indexes for common WHERE conditions (e.g., WHERE status = 'active')

---

DRIZZLE:COMMON_PITFALLS

ANTI_PATTERN: N+1 queries in loops
FIX: use relational queries db.query.table.findMany({ with: { relation: true } })

ANTI_PATTERN: missing indexes on WHERE/JOIN columns
FIX: check EXPLAIN ANALYZE output — sequential scan on large tables = missing index
TOOL: EXPLAIN ANALYZE SELECT * FROM tasks WHERE project_id = 'uuid'

ANTI_PATTERN: not using onConflictDoUpdate for upserts
FIX: db.insert().values().onConflictDoUpdate() — atomic, race-condition-free

ANTI_PATTERN: putting business logic inside schema files
FIX: schema files define structure only — business logic goes in service files

ANTI_PATTERN: using .default(sqlnow()) without { withTimezone: true }
FIX: always use timestamp('col', { withTimezone: true }) — avoids timezone bugs

ANTI_PATTERN: forgetting to update updatedAt on UPDATE queries
FIX: always include updatedAt: new Date() in .set() calls, or use database trigger

ANTI_PATTERN: selecting all columns when only a few are needed
FIX: use db.select({ id: users.id, name: users.name }) for read-heavy queries

---

DRIZZLE:AGENTIC_CHECKLIST

ON_WRITING_DATABASE_CODE:
1. CHECK: is schema defined with proper types, indexes, and relations?
2. CHECK: are foreign keys indexed?
3. CHECK: are queries using relational API for joins (not loops)?
4. CHECK: are upserts using onConflictDoUpdate?
5. CHECK: are transactions used for multi-step writes?
6. CHECK: is connection pool configured (not creating connections per request)?
7. CHECK: does migration SQL look safe? (review before applying)
8. CHECK: are timestamps using withTimezone: true?
9. RUN: vitest to verify query results match expected shapes
10. IF: new table or column THEN: generate migration with drizzle-kit