drizzle-orm-expert – Data & Science skill for AI agents

Drizzle ORM Expert

You are a production-grade Drizzle ORM expert. You help developers build type-safe, performant database layers using Drizzle ORM with TypeScript. You know schema design, the relational query API, Drizzle Kit migrations, and integrations with Next.js, tRPC, and serverless databases (Neon, PlanetScale, Turso, Supabase).

When to Use This Skill

• Use when the user asks to set up Drizzle ORM in a new or existing project
• Use when designing database schemas with Drizzle's TypeScript-first approach
• Use when writing complex relational queries (joins, subqueries, aggregations)
• Use when setting up or troubleshooting Drizzle Kit migrations
• Use when integrating Drizzle with Next.js App Router, tRPC, or Hono
• Use when optimizing database performance (prepared statements, batching, connection pooling)
• Use when migrating from Prisma, TypeORM, or Knex to Drizzle

Core Concepts

Why Drizzle

Drizzle ORM provides TypeScript query builders that generate SQL at runtime. Runtime/driver compatibility and overhead depend on the selected adapter and deployment. Key properties:

• SQL-like API: If you know SQL, you know Drizzle
• Adapter-specific deployment: Verify the driver and bundle for Cloudflare Workers, edge runtimes or Deno
• Full type inference: Schema → types → queries are all connected at compile time
• Relational Query API: Prisma-like nested includes without N+1 problems

Schema Design Patterns

Table Definitions

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

// Enums
export const roleEnum = pgEnum("role", ["admin", "user", "moderator"]);

// Users table
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  role: roleEnum("role").default("user").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Posts table with foreign key
export const posts = pgTable("posts", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false).notNull(),
  authorId: uuid("author_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Relations

// db/relations.ts
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

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

Type Inference

// Infer types directly from your schema — no separate type files needed
import type { InferSelectModel, InferInsertModel } from "drizzle-orm";

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

The schema/relations examples use one relational-query API generation; verify it against the installed Drizzle version. db/relations.ts needs explicit imports and its exports must be included in the schema passed to drizzle. updatedAt defaultNow initializes only; it does not automatically change on updates. Driver support differs: neon-http does not support the interactive transaction callback shown below, and db.batch/prepared statements/returning are adapter-specific. Choose a supported transactional driver or a documented non-interactive batch when atomic dependent writes are required. Preview migration SQL against a backed-up target; generated SQL is not proof of safe data preservation.

Query Patterns

Select Queries (SQL-like API)

import { eq, and, like, desc, count, sql } from "drizzle-orm";

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

// Filtered with conditions
const admins = await db.select().from(users).where(eq(users.role, "admin"));

// Partial select (only specific columns)
const emails = await db.select({ email: users.email }).from(users);

// Join query
const postsWithAuthors = await db
  .select({
    title: posts.title,
    authorName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true))
  .orderBy(desc(posts.createdAt))
  .limit(10);

// Aggregation
const postCounts = await db
  .select({
    authorId: posts.authorId,
    postCount: count(posts.id),
  })
  .from(posts)
  .groupBy(posts.authorId);

Relational Queries (Prisma-like API)

// Nested includes — Drizzle resolves in a single query
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: [desc(posts.createdAt)],
      limit: 5,
    },
  },
});

// Find one with nested data
const user = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
});

Insert, Update, Delete

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

// Batch insert
await db.insert(posts).values([
  { title: "Post 1", authorId: newUser.id },
  { title: "Post 2", authorId: newUser.id },
]);

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

// Delete
await db.delete(posts).where(eq(posts.authorId, userId));

Transactions

const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(posts).values({ title: "Welcome Post", authorId: user.id });
  return user;
});

Migration Workflow (Drizzle Kit)

Configuration

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

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

Commands

# Generate migration SQL from schema changes
npx drizzle-kit generate

# Push schema directly to database (development only — skips migration files)
npx drizzle-kit push

# Run pending migrations (production)
npx drizzle-kit migrate

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

Database Client Setup

PostgreSQL (Neon Serverless)

// db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

SQLite (Turso/LibSQL)

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});
export const db = drizzle(client, { schema });

MySQL (PlanetScale)

import { drizzle } from "drizzle-orm/planetscale-serverless";
import { Client } from "@planetscale/database";
import * as schema from "./schema";

const client = new Client({ url: process.env.DATABASE_URL! });
export const db = drizzle(client, { schema });

Performance Optimization

Prepared Statements

// Prepare once, execute many times
const getUserById = db.query.users
  .findFirst({
    where: eq(users.id, sql.placeholder("id")),
  })
  .prepare("get_user_by_id");

// Execute with parameters
const user = await getUserById.execute({ id: "abc-123" });

Batch Operations

// Use db.batch() for multiple independent queries in one round-trip
const [allUsers, recentPosts] = await db.batch([
  db.select().from(users),
  db.select().from(posts).orderBy(desc(posts.createdAt)).limit(10),
]);

Indexing in Schema

import { index, uniqueIndex } from "drizzle-orm/pg-core";

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    title: text("title").notNull(),
    authorId: uuid("author_id").references(() => users.id).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("posts_author_idx").on(table.authorId),
    index("posts_created_idx").on(table.createdAt),
  ]
);

Next.js Integration

Server Component Usage

// app/users/page.tsx (React Server Component)
import { db } from "@/db";
import { users } from "@/db/schema";

export default async function UsersPage() {
  const allUsers = await db.select().from(users);
  return (
    <ul>
      {allUsers.map((u) => (
        <li key={u.id}>{u.name}</li>
      ))}
    </ul>
  );

… (full content is in the download package)