Drizzle ORM Skill

Type-safe SQL ORM for TypeScript with excellent DX and performance.

Quick Start

Installation

npm

npm install drizzle-orm npm install -D drizzle-kit

pnpm

pnpm add drizzle-orm pnpm add -D drizzle-kit

yarn

yarn add drizzle-orm yarn add -D drizzle-kit

bun

bun add drizzle-orm bun add -D drizzle-kit

Database Drivers

PostgreSQL (Neon)

npm install @neondatabase/serverless

PostgreSQL (node-postgres)

npm install pg

PostgreSQL (postgres.js)

npm install postgres

MySQL

npm install mysql2

SQLite

npm install better-sqlite3

Project Structure

src/ ├── db/ │ ├── index.ts # DB connection │ ├── schema.ts # All schemas │ └── migrations/ # Generated migrations ├── drizzle.config.ts # Drizzle Kit config └── .env

Key Concepts

Concept Guide

Schema Definition reference/schema.md

Queries reference/queries.md

Relations reference/relations.md

Migrations reference/migrations.md

Examples

Pattern Guide

CRUD Operations examples/crud.md

Complex Queries examples/complex-queries.md

Transactions examples/transactions.md

With Better Auth examples/better-auth.md

Templates

Template Purpose

templates/schema.ts Schema template

templates/db.ts Database connection

templates/drizzle.config.ts Drizzle Kit config

Database Connection

Neon (Serverless)

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

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

Neon (With Connection Pooling)

import { Pool } from "@neondatabase/serverless"; import { drizzle } from "drizzle-orm/neon-serverless"; import * as schema from "./schema";

const pool = new Pool({ connectionString: process.env.DATABASE_URL }); export const db = drizzle(pool, { schema });

Node Postgres

import { Pool } from "pg"; import { drizzle } from "drizzle-orm/node-postgres"; import * as schema from "./schema";

const pool = new Pool({ connectionString: process.env.DATABASE_URL }); export const db = drizzle(pool, { schema });

Schema Definition

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

// Users table export const users = pgTable("users", { id: text("id").primaryKey(), email: varchar("email", { length: 255 }).notNull().unique(), name: text("name"), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), });

// Tasks table export const tasks = pgTable( "tasks", { id: serial("id").primaryKey(), title: varchar("title", { length: 200 }).notNull(), description: text("description"), completed: boolean("completed").default(false).notNull(), userId: text("user_id") .notNull() .references(() => users.id, { onDelete: "cascade" }), createdAt: timestamp("created_at").defaultNow().notNull(), updatedAt: timestamp("updated_at").defaultNow().notNull(), }, (table) => ({ userIdIdx: index("tasks_user_id_idx").on(table.userId), }) );

// Relations export const usersRelations = relations(users, ({ many }) => ({ tasks: many(tasks), }));

export const tasksRelations = relations(tasks, ({ one }) => ({ user: one(users, { fields: [tasks.userId], references: [users.id], }), }));

// Types export type User = typeof users.$inferSelect; export type NewUser = typeof users.$inferInsert; export type Task = typeof tasks.$inferSelect; export type NewTask = typeof tasks.$inferInsert;

Drizzle Kit Config

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

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

Migrations

Generate migration

npx drizzle-kit generate

Apply migrations

npx drizzle-kit migrate

Push schema directly (development)

npx drizzle-kit push

Open Drizzle Studio

npx drizzle-kit studio

CRUD Operations

Create

import { db } from "@/db"; import { tasks } from "@/db/schema";

// Insert one const task = await db .insert(tasks) .values({ title: "New task", userId: user.id, }) .returning();

// Insert many const newTasks = await db .insert(tasks) .values([ { title: "Task 1", userId: user.id }, { title: "Task 2", userId: user.id }, ]) .returning();

Read

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

// Get all tasks for user const userTasks = await db .select() .from(tasks) .where(eq(tasks.userId, user.id)) .orderBy(desc(tasks.createdAt));

// Get single task const task = await db .select() .from(tasks) .where(and(eq(tasks.id, taskId), eq(tasks.userId, user.id))) .limit(1);

// With relations const tasksWithUser = await db.query.tasks.findMany({ where: eq(tasks.userId, user.id), with: { user: true, }, });

Update

const updated = await db .update(tasks) .set({ completed: true, updatedAt: new Date(), }) .where(and(eq(tasks.id, taskId), eq(tasks.userId, user.id))) .returning();

Delete

await db .delete(tasks) .where(and(eq(tasks.id, taskId), eq(tasks.userId, user.id)));

Query Helpers

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

// Comparison eq(tasks.id, 1) // = ne(tasks.id, 1) // != gt(tasks.id, 1) // > gte(tasks.id, 1) // >= lt(tasks.id, 1) // < lte(tasks.id, 1) // <=

// String like(tasks.title, "%test%") // LIKE ilike(tasks.title, "%test%") // ILIKE (case-insensitive)

// Logical and(eq(tasks.userId, id), eq(tasks.completed, false)) or(eq(tasks.status, "pending"), eq(tasks.status, "active")) not(eq(tasks.completed, true))

// Null checks isNull(tasks.description) isNotNull(tasks.description)

// Arrays inArray(tasks.status, ["pending", "active"])

// Range between(tasks.createdAt, startDate, endDate)

// Raw SQL sql${tasks.title} || ' - ' || ${tasks.description}

Transactions

await db.transaction(async (tx) => { const [task] = await tx .insert(tasks) .values({ title: "New task", userId: user.id }) .returning();

await tx.insert(taskHistory).values({ taskId: task.id, action: "created", }); });

Server Actions (Next.js)

// app/actions/tasks.ts "use server";

import { db } from "@/db"; import { tasks } from "@/db/schema"; import { eq, and } from "drizzle-orm"; import { revalidatePath } from "next/cache"; import { auth } from "@/lib/auth";

export async function createTask(formData: FormData) { const session = await auth(); if (!session?.user) throw new Error("Unauthorized");

const title = formData.get("title") as string;

await db.insert(tasks).values({ title, userId: session.user.id, });

revalidatePath("/tasks"); }

export async function toggleTask(taskId: number) { const session = await auth(); if (!session?.user) throw new Error("Unauthorized");

const [task] = await db .select() .from(tasks) .where(and(eq(tasks.id, taskId), eq(tasks.userId, session.user.id)));

if (!task) throw new Error("Task not found");

await db .update(tasks) .set({ completed: !task.completed }) .where(eq(tasks.id, taskId));

revalidatePath("/tasks"); }