{"slug":"drizzle-orm-patterns","title":"Drizzle ORM: Schema Definition, Queries, Migrations, and Type Safety","tags":["drizzle","orm","postgresql","typescript","database","migrations"],"agent_summary":"Drizzle ORM patterns for PostgreSQL — schema definition, type-safe queries, relations, migrations workflow, transactions, and integration with Supabase and PlanetScale. Covers the Drizzle vs Prisma tradeoff.","trigger_phrases":["Drizzle ORM","drizzle-orm","Drizzle schema","Drizzle migration","Drizzle query","Drizzle relations","drizzle-kit","Drizzle PostgreSQL"],"runnable":false,"markdown":"\n## Overview\n\nDrizzle ORM provides SQL-like query building with full TypeScript type inference. Schema is the source of truth — types flow from schema definition, not from a separate type generator. Lighter than Prisma, closer to raw SQL.\n\n## Installation\n\n```bash\nnpm install drizzle-orm pg\nnpm install -D drizzle-kit @types/pg\n```\n\n## Schema Definition\n\n```typescript\n// src/db/schema.ts\nimport { pgTable, text, uuid, timestamp, boolean, integer, pgEnum } from \"drizzle-orm/pg-core\";\nimport { relations } from \"drizzle-orm\";\n\nexport const roleEnum = pgEnum(\"user_role\", [\"admin\", \"member\", \"viewer\"]);\n\nexport const users = pgTable(\"users\", {\n id: uuid(\"id\").primaryKey().defaultRandom(),\n clerkId: text(\"clerk_id\").notNull().unique(),\n email: text(\"email\").notNull(),\n firstName: text(\"first_name\"),\n lastName: text(\"last_name\"),\n role: roleEnum(\"role\").default(\"member\").notNull(),\n createdAt: timestamp(\"created_at\").defaultNow().notNull(),\n updatedAt: timestamp(\"updated_at\").defaultNow().notNull(),\n});\n\nexport const projects = pgTable(\"projects\", {\n id: uuid(\"id\").primaryKey().defaultRandom(),\n userId: uuid(\"user_id\").references(() => users.id, { onDelete: \"cascade\" }).notNull(),\n name: text(\"name\").notNull(),\n description: text(\"description\"),\n isPublic: boolean(\"is_public\").default(false).notNull(),\n createdAt: timestamp(\"created_at\").defaultNow().notNull(),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ many }) => ({\n projects: many(projects),\n}));\n\nexport const projectsRelations = relations(projects, ({ one }) => ({\n user: one(users, { fields: [projects.userId], references: [users.id] }),\n}));\n\n// Inferred types\nexport type User = typeof users.$inferSelect;\nexport type NewUser = typeof users.$inferInsert;\nexport type Project = typeof projects.$inferSelect;\nexport type NewProject = typeof projects.$inferInsert;\n```\n\n## Database Connection\n\n```typescript\n// src/db/index.ts\nimport { drizzle } from \"drizzle-orm/node-postgres\";\nimport { Pool } from \"pg\";\nimport * as schema from \"./schema\";\n\nconst pool = new Pool({\n connectionString: process.env.DATABASE_URL!,\n max: 10,\n idleTimeoutMillis: 30_000,\n});\n\nexport const db = drizzle(pool, { schema });\n```\n\n## Queries\n\n```typescript\nimport { db } from \"@/db\";\nimport { users, projects } from \"@/db/schema\";\nimport { eq, and, desc, like, count } from \"drizzle-orm\";\n\n// Select all\nconst allUsers = await db.select().from(users);\n\n// Select with filter\nconst activeUsers = await db\n .select({ id: users.id, email: users.email, role: users.role })\n .from(users)\n .where(eq(users.role, \"admin\"))\n .orderBy(desc(users.createdAt))\n .limit(50);\n\n// Find one\nconst user = await db\n .select()\n .from(users)\n .where(eq(users.clerkId, clerkId))\n .limit(1)\n .then(rows => rows[0] ?? null);\n\n// Count\nconst [{ total }] = await db\n .select({ total: count() })\n .from(projects)\n .where(eq(projects.userId, userId));\n\n// Search\nconst results = await db\n .select()\n .from(projects)\n .where(\n and(\n eq(projects.userId, userId),\n like(projects.name, `%${search}%`)\n )\n );\n```\n\n## With Relations\n\n```typescript\n// Join with relations (using drizzle query API)\nconst usersWithProjects = await db.query.users.findMany({\n with: {\n projects: {\n limit: 5,\n orderBy: desc(projects.createdAt),\n },\n },\n where: eq(users.role, \"member\"),\n limit: 20,\n});\n\n// Single user with projects\nconst userWithProjects = await db.query.users.findFirst({\n with: { projects: true },\n where: eq(users.id, userId),\n});\n```\n\n## Mutations\n\n```typescript\n// Insert\nconst [newProject] = await db\n .insert(projects)\n .values({\n userId,\n name: \"My Project\",\n description: \"Project description\",\n })\n .returning();\n\n// Update\nconst [updated] = await db\n .update(projects)\n .set({ name: \"New Name\", updatedAt: new Date() })\n .where(and(eq(projects.id, projectId), eq(projects.userId, userId)))\n .returning();\n\n// Delete\nawait db.delete(projects).where(eq(projects.id, projectId));\n\n// Upsert\nawait db\n .insert(users)\n .values({ clerkId, email, firstName, lastName })\n .onConflictDoUpdate({\n target: users.clerkId,\n set: { email, firstName, lastName, updatedAt: new Date() },\n });\n```\n\n## Transactions\n\n```typescript\nconst result = await db.transaction(async (tx) => {\n const [project] = await tx\n .insert(projects)\n .values({ userId, name: \"New Project\" })\n .returning();\n\n await tx.insert(projectMembers).values({\n projectId: project.id,\n userId,\n role: \"owner\",\n });\n\n return project;\n});\n```\n\n## Migrations with drizzle-kit\n\n```typescript\n// drizzle.config.ts\nimport type { Config } from \"drizzle-kit\";\n\nexport default {\n schema: \"./src/db/schema.ts\",\n out: \"./drizzle\",\n driver: \"pg\",\n dbCredentials: {\n connectionString: process.env.DATABASE_URL!,\n },\n} satisfies Config;\n```\n\n```bash\n# Generate migration from schema changes\nnpx drizzle-kit generate:pg\n\n# Apply migrations\nnpx drizzle-kit push:pg\n\n# Studio (visual DB explorer)\nnpx drizzle-kit studio\n```\n\n## Drizzle vs Prisma\n\n| | Drizzle | Prisma |\n|--|---------|--------|\n| Query style | SQL-like builder | Active Record |\n| Type generation | From schema (instant) | From schema (requires generate step) |\n| Bundle size | ~35KB | ~600KB |\n| Runtime performance | Faster (less abstraction) | Slightly slower |\n| Migrations | Manual with drizzle-kit | Auto with migrate |\n| Learning curve | Steeper (SQL knowledge helps) | Gentler |\n| Best for | SQL-heavy, performance-sensitive | Rapid prototyping, DX focus |\n\nUse Drizzle when you want SQL control and minimal overhead. Use Prisma when DX and auto-migrations matter more than bundle size.\n","html":"

Overview

\n

Drizzle ORM provides SQL-like query building with full TypeScript type inference. Schema is the source of truth — types flow from schema definition, not from a separate type generator. Lighter than Prisma, closer to raw SQL.

\n

Installation

\n
npm install drizzle-orm pg\nnpm install -D drizzle-kit @types/pg\n
\n

Schema Definition

\n
// src/db/schema.ts\nimport { pgTable, text, uuid, timestamp, boolean, integer, pgEnum } from \"drizzle-orm/pg-core\";\nimport { relations } from \"drizzle-orm\";\n\nexport const roleEnum = pgEnum(\"user_role\", [\"admin\", \"member\", \"viewer\"]);\n\nexport const users = pgTable(\"users\", {\n  id: uuid(\"id\").primaryKey().defaultRandom(),\n  clerkId: text(\"clerk_id\").notNull().unique(),\n  email: text(\"email\").notNull(),\n  firstName: text(\"first_name\"),\n  lastName: text(\"last_name\"),\n  role: roleEnum(\"role\").default(\"member\").notNull(),\n  createdAt: timestamp(\"created_at\").defaultNow().notNull(),\n  updatedAt: timestamp(\"updated_at\").defaultNow().notNull(),\n});\n\nexport const projects = pgTable(\"projects\", {\n  id: uuid(\"id\").primaryKey().defaultRandom(),\n  userId: uuid(\"user_id\").references(() => users.id, { onDelete: \"cascade\" }).notNull(),\n  name: text(\"name\").notNull(),\n  description: text(\"description\"),\n  isPublic: boolean(\"is_public\").default(false).notNull(),\n  createdAt: timestamp(\"created_at\").defaultNow().notNull(),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ many }) => ({\n  projects: many(projects),\n}));\n\nexport const projectsRelations = relations(projects, ({ one }) => ({\n  user: one(users, { fields: [projects.userId], references: [users.id] }),\n}));\n\n// Inferred types\nexport type User = typeof users.$inferSelect;\nexport type NewUser = typeof users.$inferInsert;\nexport type Project = typeof projects.$inferSelect;\nexport type NewProject = typeof projects.$inferInsert;\n
\n

Database Connection

\n
// src/db/index.ts\nimport { drizzle } from \"drizzle-orm/node-postgres\";\nimport { Pool } from \"pg\";\nimport * as schema from \"./schema\";\n\nconst pool = new Pool({\n  connectionString: process.env.DATABASE_URL!,\n  max: 10,\n  idleTimeoutMillis: 30_000,\n});\n\nexport const db = drizzle(pool, { schema });\n
\n

Queries

\n
import { db } from \"@/db\";\nimport { users, projects } from \"@/db/schema\";\nimport { eq, and, desc, like, count } from \"drizzle-orm\";\n\n// Select all\nconst allUsers = await db.select().from(users);\n\n// Select with filter\nconst activeUsers = await db\n  .select({ id: users.id, email: users.email, role: users.role })\n  .from(users)\n  .where(eq(users.role, \"admin\"))\n  .orderBy(desc(users.createdAt))\n  .limit(50);\n\n// Find one\nconst user = await db\n  .select()\n  .from(users)\n  .where(eq(users.clerkId, clerkId))\n  .limit(1)\n  .then(rows => rows[0] ?? null);\n\n// Count\nconst [{ total }] = await db\n  .select({ total: count() })\n  .from(projects)\n  .where(eq(projects.userId, userId));\n\n// Search\nconst results = await db\n  .select()\n  .from(projects)\n  .where(\n    and(\n      eq(projects.userId, userId),\n      like(projects.name, `%${search}%`)\n    )\n  );\n
\n

With Relations

\n
// Join with relations (using drizzle query API)\nconst usersWithProjects = await db.query.users.findMany({\n  with: {\n    projects: {\n      limit: 5,\n      orderBy: desc(projects.createdAt),\n    },\n  },\n  where: eq(users.role, \"member\"),\n  limit: 20,\n});\n\n// Single user with projects\nconst userWithProjects = await db.query.users.findFirst({\n  with: { projects: true },\n  where: eq(users.id, userId),\n});\n
\n

Mutations

\n
// Insert\nconst [newProject] = await db\n  .insert(projects)\n  .values({\n    userId,\n    name: \"My Project\",\n    description: \"Project description\",\n  })\n  .returning();\n\n// Update\nconst [updated] = await db\n  .update(projects)\n  .set({ name: \"New Name\", updatedAt: new Date() })\n  .where(and(eq(projects.id, projectId), eq(projects.userId, userId)))\n  .returning();\n\n// Delete\nawait db.delete(projects).where(eq(projects.id, projectId));\n\n// Upsert\nawait db\n  .insert(users)\n  .values({ clerkId, email, firstName, lastName })\n  .onConflictDoUpdate({\n    target: users.clerkId,\n    set: { email, firstName, lastName, updatedAt: new Date() },\n  });\n
\n

Transactions

\n
const result = await db.transaction(async (tx) => {\n  const [project] = await tx\n    .insert(projects)\n    .values({ userId, name: \"New Project\" })\n    .returning();\n\n  await tx.insert(projectMembers).values({\n    projectId: project.id,\n    userId,\n    role: \"owner\",\n  });\n\n  return project;\n});\n
\n

Migrations with drizzle-kit

\n
// drizzle.config.ts\nimport type { Config } from \"drizzle-kit\";\n\nexport default {\n  schema: \"./src/db/schema.ts\",\n  out: \"./drizzle\",\n  driver: \"pg\",\n  dbCredentials: {\n    connectionString: process.env.DATABASE_URL!,\n  },\n} satisfies Config;\n
\n
# Generate migration from schema changes\nnpx drizzle-kit generate:pg\n\n# Apply migrations\nnpx drizzle-kit push:pg\n\n# Studio (visual DB explorer)\nnpx drizzle-kit studio\n
\n

Drizzle vs Prisma

\n

| | Drizzle | Prisma |\n|--|---------|--------|\n| Query style | SQL-like builder | Active Record |\n| Type generation | From schema (instant) | From schema (requires generate step) |\n| Bundle size | ~35KB | ~600KB |\n| Runtime performance | Faster (less abstraction) | Slightly slower |\n| Migrations | Manual with drizzle-kit | Auto with migrate |\n| Learning curve | Steeper (SQL knowledge helps) | Gentler |\n| Best for | SQL-heavy, performance-sensitive | Rapid prototyping, DX focus |

\n

Use Drizzle when you want SQL control and minimal overhead. Use Prisma when DX and auto-migrations matter more than bundle size.

\n"}