Drizzle ORM Modern Database Complete Guide

# Drizzle ORM Modern Database Guide

Build type-safe database applications with Drizzle ORM featuring schema-first design, migrations, relations, and seamless Next.js integration.

## Setup and Configuration

### Installation

```bash
npm install drizzle-orm postgres
npm install -D drizzle-kit
```

### Database Connection

```typescript
// lib/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL!;

// For query purposes
const queryClient = postgres(connectionString);
export const db = drizzle(queryClient, { schema });

// For migrations
const migrationClient = postgres(connectionString, { max: 1 });
export const migrationDb = drizzle(migrationClient);
```

### Drizzle Config

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

export default {
  schema: "./lib/db/schema.ts",
  out: "./lib/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
} satisfies Config;
```

## Schema Definition

### Complete Schema Example

```typescript
// lib/db/schema.ts
import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  integer,
  decimal,
  pgEnum,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

// Enums
export const userRoleEnum = pgEnum("user_role", ["user", "admin", "moderator"]);
export const orderStatusEnum = pgEnum("order_status", [
  "pending",
  "processing",
  "shipped",
  "delivered",
  "cancelled",
]);

// Users table
export const users = pgTable(
  "users",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    name: varchar("name", { length: 255 }),
    passwordHash: text("password_hash"),
    role: userRoleEnum("role").default("user").notNull(),
    emailVerified: boolean("email_verified").default(false),
    avatarUrl: text("avatar_url"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => ({
    emailIdx: uniqueIndex("users_email_idx").on(table.email),
    roleIdx: index("users_role_idx").on(table.role),
  })
);

// Posts table
export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: varchar("title", { length: 255 }).notNull(),
    slug: varchar("slug", { length: 255 }).notNull().unique(),
    content: text("content").notNull(),
    excerpt: text("excerpt"),
    published: boolean("published").default(false),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
    publishedAt: timestamp("published_at"),
  },
  (table) => ({
    slugIdx: uniqueIndex("posts_slug_idx").on(table.slug),
    authorIdx: index("posts_author_idx").on(table.authorId),
    publishedIdx: index("posts_published_idx").on(table.published),
  })
);

// Products table
export const products = pgTable("products", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  price: decimal("price", { precision: 10, scale: 2 }).notNull(),
  stock: integer("stock").default(0).notNull(),
  categoryId: uuid("category_id").references(() => categories.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Categories table
export const categories = pgTable("categories", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 100 }).notNull(),
  slug: varchar("slug", { length: 100 }).notNull().unique(),
  parentId: uuid("parent_id"),
});

// Orders table
export const orders = pgTable("orders", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),
  status: orderStatusEnum("status").default("pending").notNull(),
  total: decimal("total", { precision: 10, scale: 2 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

// Order items table
export const orderItems = pgTable("order_items", {
  id: uuid("id").primaryKey().defaultRandom(),
  orderId: uuid("order_id")
    .notNull()
    .references(() => orders.id, { onDelete: "cascade" }),
  productId: uuid("product_id")
    .notNull()
    .references(() => products.id),
  quantity: integer("quantity").notNull(),
  price: decimal("price", { precision: 10, scale: 2 }).notNull(),
});
```

## Relations

```typescript
// lib/db/relations.ts
import { relations } from "drizzle-orm";
import { users, posts, orders, orderItems, products, categories } from "./schema";

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

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

export const ordersRelations = relations(orders, ({ one, many }) => ({
  user: one(users, {
    fields: [orders.userId],
    references: [users.id],
  }),
  items: many(orderItems),
}));

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
  }),
  product: one(products, {
    fields: [orderItems.productId],
    references: [products.id],
  }),
}));

export const productsRelations = relations(products, ({ one }) => ({
  category: one(categories, {
    fields: [products.categoryId],
    references: [categories.id],
  }),
}));

export const categoriesRelations = relations(categories, ({ one, many }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: "subcategories",
  }),
  children: many(categories, { relationName: "subcategories" }),
  products: many(products),
}));
```

## Query Examples

### CRUD Operations

```typescript
// lib/db/queries/users.ts
import { eq, and, or, like, desc, asc, sql } from "drizzle-orm";
import { db } from "../index";
import { users, posts } from "../schema";

// Create
export async function createUser(data: {
  email: string;
  name?: string;
  passwordHash: string;
}) {
  const [user] = await db
    .insert(users)
    .values(data)
    .returning();
  return user;
}

// Read with relations
export async function getUserWithPosts(userId: string) {
  return db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      posts: {
        where: eq(posts.published, true),
        orderBy: desc(posts.createdAt),
        limit: 10,
      },
    },
  });
}

// Update
export async function updateUser(
  userId: string,
  data: Partial<typeof users.$inferInsert>
) {
  const [updated] = await db
    .update(users)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(users.id, userId))
    .returning();
  return updated;
}

// Delete
export async function deleteUser(userId: string) {
  await db.delete(users).where(eq(users.id, userId));
}

// Complex query with filters
export async function searchUsers(options: {
  search?: string;
  role?: "user" | "admin" | "moderator";
  limit?: number;
  offset?: number;
}) {
  const { search, role, limit = 20, offset = 0 } = options;

  const conditions = [];

  if (search) {
    conditions.push(
      or(
        like(users.email, `%${search}%`),
        like(users.name, `%${search}%`)
      )
    );
  }

  if (role) {
    conditions.push(eq(users.role, role));
  }

  return db
    .select()
    .from(users)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .limit(limit)
    .offset(offset)
    .orderBy(desc(users.createdAt));
}
```

## Transactions

```typescript
// lib/db/queries/orders.ts
import { db } from "../index";
import { orders, orderItems, products } from "../schema";
import { eq, sql } from "drizzle-orm";

export async function createOrder(
  userId: string,
  items: Array<{ productId: string; quantity: number }>
) {
  return db.transaction(async (tx) => {
    // Calculate total and verify stock
    let total = 0;
    const itemsWithPrices = [];

    for (const item of items) {
      const [product] = await tx
        .select()
        .from(products)
        .where(eq(products.id, item.productId))
        .for("update"); // Lock row

      if (!product || product.stock < item.quantity) {
        throw new Error(`Insufficient stock for ${product?.name || item.productId}`);
      }

      const itemTotal = Number(product.price) * item.quantity;
      total += itemTotal;
      itemsWithPrices.push({ ...item, price: product.price });

      // Update stock
      await tx
        .update(products)
        .set({ stock: sql`${products.stock} - ${item.quantity}` })
        .where(eq(products.id, item.productId));
    }

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({ userId, total: total.toString(), status: "pending" })
      .returning();

    // Create order items
    await tx.insert(orderItems).values(
      itemsWithPrices.map((item) => ({
        orderId: order.id,
        productId: item.productId,
        quantity: item.quantity,
        price: item.price,
      }))
    );

    return order;
  });
}
```

## Migrations

```bash
# Generate migration
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Open Drizzle Studio
npx drizzle-kit studio
```

## Type Inference

```typescript
// lib/db/types.ts
import { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { users, posts, orders } from "./schema";

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;
export type Post = InferSelectModel<typeof posts>;
export type NewPost = InferInsertModel<typeof posts>;
export type Order = InferSelectModel<typeof orders>;
```

This Drizzle ORM guide covers schema design, relations, transactions, migrations, and type-safe queries.