Drizzle Database Patterns

Overview

Implement database schemas and queries using Drizzle ORM following the project's established patterns for type-safe database access.

When to Use This Skill

• Creating or modifying database tables in packages/db/src/schema.ts

• Writing complex SQL queries with JOINs

• Defining table relations

• Working with database migrations

• Setting up test databases with PGlite

Table Definition Patterns

Basic Table with Typed IDs

// packages/db/src/schema.ts import { pgTable, text, timestamp, boolean, jsonb, index, uniqueIndex, unique, } from "drizzle-orm/pg-core"; import { createId } from "@paralleldrive/cuid2"; import type { UserId, UserRoleValue } from "@project/common";

export const usersTable = pgTable("users", { id: text("id") .primaryKey() .$defaultFn(() => createId()) .$type<UserId>(), // Branded type for type-safety name: text("name").notNull(), email: text("email").notNull().unique(), emailVerified: boolean("email_verified") .$defaultFn(() => false) .notNull(), createdAt: timestamp("created_at") .$defaultFn(() => new Date()) .notNull(), role: text("role").$type<UserRoleValue>(), // Union type for enums });

Table with Indexes (Third Argument)

export const sessionsTable = pgTable( "sessions", { id: text("id") .primaryKey() .$defaultFn(() => createId()), userId: text("user_id") .notNull() .references(() => usersTable.id, { onDelete: "cascade", }) .$type<UserId>(), // ... }, (table) => [ index("sessions_user_id_idx").on(table.userId), uniqueIndex("sessions_token_idx").on(table.token), unique().on(table.userId, table.projectId), // Composite unique ], );

JSONB Columns with Types

export const documentMetadataTable = pgTable("document_metadata", { configuration: jsonb("configuration").$type<DocumentConfig>(), tags: jsonb("tags").$type<string[]>(), metadata: jsonb("metadata").$type<Record<string, string>>(), });

Relations Definition

import { relations } from "drizzle-orm";

// One-to-many export const organizationsRelations = relations(organizationsTable, ({ many }) => ({ members: many(membersTable), projects: many(projectsTable), }));

// Many-to-one export const membersRelations = relations(membersTable, ({ one }) => ({ user: one(usersTable, { fields: [membersTable.userId], references: [usersTable.id], }), organization: one(organizationsTable, { fields: [membersTable.organizationId], references: [organizationsTable.id], }), }));

// Combined one + many export const projectsRelations = relations(projectsTable, ({ one, many }) => ({ organization: one(organizationsTable, { fields: [projectsTable.organizationId], references: [organizationsTable.id], }), members: many(projectMembersTable), }));

Query Patterns

Simple SELECT with Relations (Query API)

const userMemberships = await db.query.membersTable.findMany({ where: eq(membersTable.userId, userId), with: { organization: true }, });

SELECT with JOINs (Select API)

// INNER JOIN - required relationship const [result] = await db .select({ id: organizationsTable.id, name: organizationsTable.name, memberRole: membersTable.role, }) .from(organizationsTable) .innerJoin( membersTable, and(eq(membersTable.organizationId, organizationsTable.id), eq(membersTable.userId, userId)), ) .where(eq(organizationsTable.id, id)) .limit(1);

// LEFT JOIN - optional relationship const members = await db .select({ id: usersTable.id, name: usersTable.name, role: membersTable.role, }) .from(membersTable) .leftJoin(usersTable, eq(membersTable.userId, usersTable.id)) .where(eq(membersTable.organizationId, organizationId));

Complex Aggregation with Raw SQL

import { sql } from "drizzle-orm";

const result = await db .select({ orgId: organizationsTable.id, projects: sql<Array<{ id: string; name: string }>> COALESCE( json_agg( DISTINCT jsonb_build_object( 'id', ${projectsTable.id}, 'name', ${projectsTable.name} ) ) FILTER (WHERE ${projectsTable.id} IS NOT NULL), '[]' ) , }) .from(membersTable) .innerJoin(organizationsTable, eq(membersTable.organizationId, organizationsTable.id)) .leftJoin(projectsTable, eq(projectsTable.organizationId, organizationsTable.id)) .where(eq(membersTable.userId, userId)) .groupBy(organizationsTable.id);

Subquery in DELETE (inArray)

await db .delete(projectMembersTable) .where( and( eq(projectMembersTable.userId, userId), inArray( projectMembersTable.projectId, db .select({ id: projectsTable.id }) .from(projectsTable) .where(eq(projectsTable.organizationId, organizationId)), ), ), );

Type Inference Patterns

// Return type from table export async function seedUser(db: TestDb): Promise<typeof usersTable.$inferSelect> { const [user] = await db.insert(usersTable).values({...}).returning(); return user; }

// Partial update type const updateData: Partial<typeof documentMetadataTable.$inferInsert> = {}; if (input.name !== undefined) updateData.name = input.name;

// Array type from table const documents: Array<typeof documentsTable.$inferSelect> = [];

INSERT/UPDATE/DELETE Patterns

// INSERT with returning const [organization] = await db.insert(organizationsTable).values({ name }).returning();

// UPDATE with where const [updated] = await db .update(organizationsTable) .set({ name }) .where(eq(organizationsTable.id, id)) .returning();

// DELETE (cascades handled by FK) await db.delete(organizationsTable).where(eq(organizationsTable.id, organizationId));

Database Connection

// packages/db/src/index.ts import { drizzle } from "drizzle-orm/node-postgres"; import { Pool } from "pg"; import * as schema from "./schema";

export function connectDb(connectionString: string) { const pool = new Pool({ connectionString, min: 2, max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 5000, }); return drizzle(pool, { schema }); // Pass schema for relational queries }

export type Db = ReturnType<typeof connectDb>;

Testing with PGlite

// packages/db/src/testing.ts export { createTestDb, cleanupTestDb, type TestDb } from "./tests/setup"; export { seedUser, seedOrganization, seedProject } from "./tests/seed";

// Seed functions return typed records export async function seedUser(db: TestDb): Promise<typeof usersTable.$inferSelect> { const [user] = await db .insert(usersTable) .values({ name: "Test User", email: test-${createId()}@example.com, }) .returning(); return user; }

Key Rules

• Always use branded types for IDs: .$type<UserId>()

• Use INNER JOIN for required relationships, LEFT JOIN for optional

• Prefer single queries with JOINs over multiple queries

• Pass schema to drizzle() to enable relational queries

• Use .$defaultFn() for auto-generated values (IDs, timestamps)

• Foreign keys with cascade: references(() => table.id, { onDelete: "cascade" })