drizzle-orm

$2a

Basic Setup

// db/schema.ts

import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

  id: serial('id').primaryKey(),

  email: text('email').notNull().unique(),

  name: text('name').notNull(),

  createdAt: timestamp('created_at').defaultNow(),

});

// db/client.ts

import { drizzle } from 'drizzle-orm/node-postgres';

import { Pool } from 'pg';

import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

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

First Query

import { db } from './db/client';

import { users } from './db/schema';

import { eq } from 'drizzle-orm';

// Insert

const newUser = await db.insert(users).values({

  email: 'user@example.com',

  name: 'John Doe',

}).returning();

// Select

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

// Where

const user = await db.select().from(users).where(eq(users.id, 1));

// Update

await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));

// Delete

await db.delete(users).where(eq(users.id, 1));

Schema Definition

Column Types Reference

PostgreSQL

MySQL

SQLite

TypeScript

serial()

serial()

integer()

number

text()

text()

text()

string

integer()

int()

integer()

number

boolean()

boolean()

integer()

boolean

timestamp()

datetime()

integer()

Date

json()

json()

text()

unknown

uuid()

varchar(36)

text()

string

Common Schema Patterns

import { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

  id: serial('id').primaryKey(),

  email: varchar('email', { length: 255 }).notNull().unique(),

  passwordHash: varchar('password_hash', { length: 255 }).notNull(),

  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),

  metadata: json('metadata').$type<{ theme: string; locale: string }>(),

  isActive: boolean('is_active').default(true),

  createdAt: timestamp('created_at').defaultNow().notNull(),

  updatedAt: timestamp('updated_at').defaultNow().notNull(),

}, (table) => ({

  emailIdx: unique('email_unique_idx').on(table.email),

}));

// Infer TypeScript types

type User = typeof users.$inferSelect;

type NewUser = typeof users.$inferInsert;

Relations

One-to-Many

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';

import { relations } from 'drizzle-orm';

export const authors = pgTable('authors', {

  id: serial('id').primaryKey(),

  name: text('name').notNull(),

});

export const posts = pgTable('posts', {

  id: serial('id').primaryKey(),

  title: text('title').notNull(),

  authorId: integer('author_id').notNull().references(() => authors.id),

});

export const authorsRelations = relations(authors, ({ many }) => ({

  posts: many(posts),

}));

export const postsRelations = relations(posts, ({ one }) => ({

  author: one(authors, {

    fields: [posts.authorId],

    references: [authors.id],

  }),

}));

// Query with relations

const authorsWithPosts = await db.query.authors.findMany({

  with: { posts: true },

});

Many-to-Many

export const users = pgTable('users', {

  id: serial('id').primaryKey(),

  name: text('name').notNull(),

});

export const groups = pgTable('groups', {

  id: serial('id').primaryKey(),

  name: text('name').notNull(),

});

export const usersToGroups = pgTable('users_to_groups', {

  userId: integer('user_id').notNull().references(() => users.id),

  groupId: integer('group_id').notNull().references(() => groups.id),

}, (table) => ({

  pk: primaryKey({ columns: [table.userId, table.groupId] }),

}));

export const usersRelations = relations(users, ({ many }) => ({

  groups: many(usersToGroups),

}));

export const groupsRelations = relations(groups, ({ many }) => ({

  users: many(usersToGroups),

}));

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({

  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),

  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),

}));

Queries

Filtering

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

// Equality

await db.select().from(users).where(eq(users.email, 'user@example.com'));

// Comparison

await db.select().from(users).where(gt(users.id, 10));

// Pattern matching

await db.select().from(users).where(like(users.name, '%John%'));

// Multiple conditions

await db.select().from(users).where(

  and(

    eq(users.role, 'admin'),

    gt(users.createdAt, new Date('2024-01-01'))

  )

);

// IN clause

await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// NULL checks

await db.select().from(users).where(isNull(users.deletedAt));

Joins

import { eq } from 'drizzle-orm';

// Inner join

const result = await db

  .select({

    user: users,

    post: posts,

  })

  .from(users)

  .innerJoin(posts, eq(users.id, posts.authorId));

// Left join

const result = await db

  .select({

    user: users,

    post: posts,

  })

  .from(users)

  .leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins with aggregation

import { count, sql } from 'drizzle-orm';

const result = await db

  .select({

    authorName: authors.name,

    postCount: count(posts.id),

  })

  .from(authors)

  .leftJoin(posts, eq(authors.id, posts.authorId))

  .groupBy(authors.id);

Pagination & Sorting

import { desc, asc } from 'drizzle-orm';

// Order by

await db.select().from(users).orderBy(desc(users.createdAt));

// Limit & offset

await db.select().from(users).limit(10).offset(20);

// Pagination helper

function paginate(page: number, pageSize: number = 10) {

  return db.select().from(users)

    .limit(pageSize)

    .offset(page * pageSize);

}

Transactions

// Auto-rollback on error

await db.transaction(async (tx) => {

  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });

  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });

  // If any query fails, entire transaction rolls back

});

// Manual control

const tx = db.transaction(async (tx) => {

  const user = await tx.insert(users).values({ ... }).returning();

  if (!user) {

    tx.rollback();

    return;

  }

  await tx.insert(posts).values({ authorId: user.id });

});

Migrations

Drizzle Kit Configuration

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

Migration Workflow

# Generate migration

npx drizzle-kit generate

# View SQL

cat drizzle/0000_migration.sql

# Apply migration

npx drizzle-kit migrate

# Introspect existing database

npx drizzle-kit introspect

# Drizzle Studio (database GUI)

npx drizzle-kit studio

Example Migration

-- drizzle/0000_initial.sql

CREATE TABLE IF NOT EXISTS "users" (

  "id" serial PRIMARY KEY NOT NULL,

  "email" varchar(255) NOT NULL,

  "name" text NOT NULL,

  "created_at" timestamp DEFAULT now() NOT NULL,

  CONSTRAINT "users_email_unique" UNIQUE("email")

);

Navigation

Detailed References

-

🏗️ Advanced Schemas - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.

-

🔍 Query Patterns - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.

-

⚡ Performance - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.

-

🔄 vs Prisma - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.

Red Flags

Stop and reconsider if:

• Using any or unknown for JSON columns without type annotation

• Building raw SQL strings without using sql template (SQL injection risk)

• Not using transactions for multi-step data modifications

• Fetching all rows without pagination in production queries

• Missing indexes on foreign keys or frequently queried columns

• Using select() without specifying columns for large tables

Performance Benefits vs Prisma

Metric

Drizzle

Prisma

Bundle Size

~35KB

~230KB

Cold Start

~10ms

~250ms

Query Speed

Baseline

~2-3x slower

Memory

~10MB

~50MB

Type Generation

Runtime inference

Build-time generation

Integration

• typescript-core: Type-safe schema inference with satisfies

• nextjs-core: Server Actions, Route Handlers, Middleware integration

• Database Migration: Safe schema evolution patterns

Related Skills

When using Drizzle, these skills enhance your workflow:

• prisma: Alternative ORM comparison: Drizzle vs Prisma trade-offs

• typescript: Advanced TypeScript patterns for type-safe queries

• nextjs: Drizzle with Next.js Server Actions and API routes

• sqlalchemy: SQLAlchemy patterns for Python developers learning Drizzle

[Full documentation available in these skills if deployed in your bundle]