A Practical Guide to Using Drizzle ORM with Claude Code

What Is Drizzle ORM?

Drizzle ORM is a TypeScript-first, lightweight ORM. Its hallmark is that you write type-safe queries using SQL-like syntax. Combined with Claude Code, you can efficiently build your database layer.

Schema Definition

// db/schema.ts
import {
  pgTable,
  text,
  timestamp,
  boolean,
  integer,
  varchar,
  index,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const users = pgTable("users", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  avatar: text("avatar"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: index("email_idx").on(table.email),
}));

export const posts = pgTable("posts", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content").notNull(),
  published: boolean("published").default(false).notNull(),
  authorId: text("author_id").notNull().references(() => users.id),
  viewCount: integer("view_count").default(0).notNull(),
  publishedAt: timestamp("published_at"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  authorIdx: index("author_idx").on(table.authorId),
  publishedIdx: index("published_idx").on(table.published, table.publishedAt),
}));

export const comments = pgTable("comments", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  content: text("content").notNull(),
  authorId: text("author_id").notNull().references(() => users.id),
  postId: text("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Defining Relations

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

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

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

Query Operations

import { drizzle } from "drizzle-orm/node-postgres";
import { eq, and, like, desc, sql, count } from "drizzle-orm";
import * as schema from "./schema";

const db = drizzle(pool, { schema });

// Insert
async function createPost(data: {
  title: string;
  content: string;
  authorId: string;
}) {
  const [post] = await db
    .insert(posts)
    .values(data)
    .returning();

  return post;
}

// Search (with pagination)
async function getPosts(params: {
  page?: number;
  perPage?: number;
  search?: string;
}) {
  const { page = 1, perPage = 20, search } = params;

  const conditions = [eq(posts.published, true)];
  if (search) {
    conditions.push(like(posts.title, `%${search}%`));
  }

  const [data, [{ total }]] = await Promise.all([
    db
      .select({
        id: posts.id,
        title: posts.title,
        publishedAt: posts.publishedAt,
        authorName: users.name,
        commentCount: count(comments.id),
      })
      .from(posts)
      .leftJoin(users, eq(posts.authorId, users.id))
      .leftJoin(comments, eq(posts.id, comments.postId))
      .where(and(...conditions))
      .groupBy(posts.id, users.name)
      .orderBy(desc(posts.publishedAt))
      .limit(perPage)
      .offset((page - 1) * perPage),

    db
      .select({ total: count() })
      .from(posts)
      .where(and(...conditions)),
  ]);

  return { data, total, page, perPage };
}

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

  return updated;
}

Relational Queries API

// Prisma-like queries
async function getPostWithRelations(id: string) {
  return db.query.posts.findFirst({
    where: eq(posts.id, id),
    with: {
      author: {
        columns: { id: true, name: true, avatar: true },
      },
      comments: {
        with: {
          author: {
            columns: { id: true, name: true },
          },
        },
        orderBy: [desc(comments.createdAt)],
        limit: 10,
      },
    },
  });
}

Migrations

// 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;

# Generate migrations
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

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

Comparison With Prisma

Aspect	Drizzle	Prisma
Bundle size	Lightweight	Somewhat larger
Query style	SQL-like	Custom API
Edge support	Excellent	Limited
Schema definition	TypeScript	Custom DSL
Migrations	SQL generation	Fully managed

Using It With Claude Code

An example of asking Claude Code to implement Drizzle ORM. For a comparison with Prisma, see the complete Prisma ORM guide, and for database integration, see Supabase integration development.

Build the database layer with Drizzle ORM.
- PostgreSQL schema definitions
- CRUD queries with relations
- Pagination and search
- Migration configuration

For more on Drizzle ORM, see the official Drizzle ORM documentation. For how to use Claude Code, see the official documentation.

Summary

Drizzle ORM is an appealing ORM thanks to its SQL-like syntax and lightweight footprint. With Claude Code, you can efficiently implement type-safe queries and schema designs. It’s also well-suited for edge environments.