Master Drizzle ORM for type-safe database operations. Learn schema design, migrations, relations, transactions, prepared statements, and Next.js integration with PostgreSQL.
# 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.This drizzle prompt is ideal for developers working on:
By using this prompt, you can save hours of manual coding and ensure best practices are followed from the start. It's particularly valuable for teams looking to maintain consistency across their drizzle implementations.
Yes! All prompts on Antigravity AI Directory are free to use for both personal and commercial projects. No attribution required, though it's always appreciated.
This prompt works excellently with Claude, ChatGPT, Cursor, GitHub Copilot, and other modern AI coding assistants. For best results, use models with large context windows.
You can modify the prompt by adding specific requirements, constraints, or preferences. For drizzle projects, consider mentioning your framework version, coding style, and any specific libraries you're using.