Drizzle Orm

First Query

import { db } from "./db/client";
import { users } from "./db/schema";
import { eq } from "drizzle-orm";

// Insert
const newUser = await db
  .insert(users)
  .values({
    email: "[email protected]",
    name: "John Doe",
  })
  .returning();

// Select
const allUsers = await db.select().from(users);

// Where
const user = await db.select().from(users).where(eq(users.id, 1));

// Update
await db.update(users).set({ name: "Jane Doe" }).where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

Schema Definition

Column Types Reference

Common Schema Patterns

import {
  pgTable,
  serial,
  text,
  varchar,
  integer,
  boolean,
  timestamp,
  json,
  unique,
} from "drizzle-orm/pg-core";

export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    passwordHash: varchar("password_hash", { length: 255 }).notNull(),
    role: text("role", { enum: ["admin", "user", "guest"] }).default("user"),
    metadata: json("metadata").$type<{ theme: string; locale: string }>(),
    isActive: boolean("is_active").default(true),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    emailIdx: unique("email_unique_idx").on(table.email),
  }),
);

// Infer TypeScript types
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

Relations

One-to-Many

import { pgTable, serial, text, integer } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const authors = pgTable("authors", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => authors.id),
});

export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(authors, {
    fields: [posts.authorId],
    references: [authors.id],
  }),
}));

// Query with relations
const authorsWithPosts = await db.query.authors.findMany({
  with: { posts: true },
});

Many-to-Many

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

export const groups = pgTable("groups", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
});

export const usersToGroups = pgTable(
  "users_to_groups",
  {
    userId: integer("user_id")
      .notNull()
      .references(() => users.id),
    groupId: integer("group_id")
      .notNull()
      .references(() => groups.id),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.userId, table.groupId] }),
  }),
);

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

export const groupsRelations = relations(groups, ({ many }) => ({
  users: many(usersToGroups),
}));

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
}));

Queries

Filtering

import {
  eq,
  ne,
  gt,
  gte,
  lt,
  lte,
  like,
  ilike,
  inArray,
  isNull,
  isNotNull,
  and,
  or,
  between,
} from "drizzle-orm";

// Equality
await db.select().from(users).where(eq(users.email, "[email protected]"));

// Comparison
await db.select().from(users).where(gt(users.id, 10));

// Pattern matching
await db.select().from(users).where(like(users.name, "%John%"));

// Multiple conditions
await db
  .select()
  .from(users)
  .where(and(eq(users.role, "admin"), gt(users.createdAt, new Date("2024-01-01"))));

// IN clause
await db
  .select()
  .from(users)
  .where(inArray(users.id, [1, 2, 3]));

// NULL checks
await db.select().from(users).where(isNull(users.deletedAt));

Joins

import { eq } from "drizzle-orm";

// Inner join
const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// Left join
const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins with aggregation
import { count, sql } from "drizzle-orm";

const result = await db
  .select({
    authorName: authors.name,
    postCount: count(posts.id),
  })
  .from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id);

Pagination & Sorting

import { desc, asc } from "drizzle-orm";

// Order by
await db.select().from(users).orderBy(desc(users.createdAt));

// Limit & offset
await db.select().from(users).limit(10).offset(20);

// Pagination helper
function paginate(page: number, pageSize: number = 10) {
  return db
    .select()
    .from(users)
    .limit(pageSize)
    .offset(page * pageSize);
}

Transactions

// Auto-rollback on error
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: '[email protected]', name: 'John' });
  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });
  // If any query fails, entire transaction rolls back
});

// Manual control
const tx = db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ ... }).returning();

  if (!user) {
    tx.rollback();
    return;
  }

  await tx.insert(posts).values({ authorId: user.id });
});

Migrations

Drizzle Kit Configuration

// drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Migration Workflow

# Generate migration
npx drizzle-kit generate

# View SQL
cat drizzle/0000_migration.sql

# Apply migration
npx drizzle-kit migrate

# Introspect existing database
npx drizzle-kit introspect

# Drizzle Studio (database GUI)
npx drizzle-kit studio

Example Migration

-- drizzle/0000_initial.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255) NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "users_email_unique" UNIQUE("email")
);

Navigation

Detailed References

• 🏗️ Advanced Schemas - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.

• 🔍 Query Patterns - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.

• ⚡ Performance - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.

• 🔄 vs Prisma - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.

Red Flags

Stop and reconsider if:

• Using any or unknown for JSON columns without type annotation
• Building raw SQL strings without using sql template (SQL injection risk)
• Not using transactions for multi-step data modifications
• Fetching all rows without pagination in production queries
• Missing indexes on foreign keys or frequently queried columns
• Using select() without specifying columns for large tables

Performance Benefits vs Prisma

Integration

• typescript-core: Type-safe schema inference with satisfies
• nextjs-core: Server Actions, Route Handlers, Middleware integration
• Database Migration: Safe schema evolution patterns

Related Skills

When using Drizzle, these skills enhance your workflow:

• prisma: Alternative ORM comparison: Drizzle vs Prisma trade-offs
• typescript: Advanced TypeScript patterns for type-safe queries
• nextjs: Drizzle with Next.js Server Actions and API routes
• sqlalchemy: SQLAlchemy patterns for Python developers learning Drizzle

[Full documentation available in these skills if deployed in your bundle]