Drizzle ORM Complete Guide: The Lightweight Alternative to Prisma

// Drizzle is just SQL, but type-safe
const users = await db
  .select()
  .from(usersTable)
  .where(eq(usersTable.name, 'John'))

# Install Drizzle and database driver
npm install drizzle-orm pg
npm install -D drizzle-kit

# For MySQL
npm install drizzle-orm/mysql2

# For SQLite  
npm install drizzle-orm/better-sqlite3

// drizzle.config.ts
import type { Config } from 'drizzle-kit'

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config

// src/schema.ts
import { pgTable, serial, text, timestamp, integer, boolean } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  password: text('password').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  userId: integer('user_id')
    .references(() => users.id, { onDelete: 'cascade' })
    .notNull(),
  published: boolean('published').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

// Type inference
export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

// One-to-many
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
})

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  title: text('title').notNull(),
})

// Many-to-many with junction table
export const postsTags = pgTable('posts_tags', {
  postId: integer('post_id').references(() => posts.id),
  tagId: integer('tag_id').references(() => tags.id),
})

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
})

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
}, (table) => {
  return {
    // Composite index
    nameIdx: index('name_idx').on(table.name, table.email),
    // Partial index (via SQL)
  }
})

import { db } from './db'
import { users, posts } from './schema'
import { eq, desc, like, and, or, sql } from 'drizzle-orm'

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

// Select specific columns
const userNames = await db
  .select({ name: users.name, email: users.email })
  .from(users)

// Where clause
const john = await db
  .select()
  .from(users)
  .where(eq(users.name, 'John'))

// Multiple conditions
const result = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.name, 'John'),
      like(users.email, '%@example.com')
    )
  )

// Order and limit
const recentPosts = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)

// Join
const userWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))

// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    name: 'John',
    email: '[email protected]',
    password: 'hashed',
  })
  .returning()

// Multiple inserts
await db.insert(users).values([
  { name: 'John', email: '[email protected]', password: 'hash' },
  { name: 'Jane', email: '[email protected]', password: 'hash' },
])

// Upsert
await db
  .insert(users)
  .values({ name: 'John', email: '[email protected]', password: 'hash' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  })

// Update single row
const [updated] = await db
  .update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1))
  .returning()

// Update multiple rows
await db
  .update(posts)
  .set({ published: true })
  .where(eq(posts.userId, 1))

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

// Raw query
const result = await db.execute(sql`
  SELECT * FROM users 
  WHERE name = ${searchTerm}
`)

// Raw select
const users = await db
  .select()
  .from(sql`(SELECT * FROM users WHERE active = true) as active_users`)

// JSON column
const settings = pgTable('settings', {
  id: serial('id').primaryKey(),
  config: json('config').$type<{ theme: string; notifications: boolean }>(),
})

// Enum
const statusEnum = pgEnum('status', ['pending', 'active', 'completed'])

export const tasks = pgTable('tasks', {
  id: serial('id').primaryKey(),
  status: statusEnum('status').default('pending'),
})

import { db } from './db'
import { users, posts } from './schema'

await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'John', email: '[email protected]', password: 'hash' })
    .returning()
  
  await tx.insert(posts).values({
    title: 'My First Post',
    userId: user.id,
  })
  
  return user
})

// src/lib/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from '@/schema'

const queryClient = postgres(process.env.DATABASE_URL!)
export const db = drizzle(queryClient, { schema })

// src/app/actions.ts
'use server'

import { db } from '@/lib/db'
import { users } from '@/lib/schema'
import { eq } from 'drizzle-orm'

export async function getUser(id: number) {
  return db.select().from(users).where(eq(users.id, id)).limit(1)
}

export async function createUser(data: { name: string; email: string }) {
  return db.insert(users).values(data).returning()
}

// src/app/api/users/route.ts
import { db } from '@/lib/db'
import { users } from '@/lib/schema'
import { NextResponse } from 'next/server'

export async function GET() {
  const allUsers = await db.select().from(users)
  return NextResponse.json(allUsers)
}

# Generate migration from schema
npx drizzle-kit generate:pg

# Push schema to database
npx drizzle-kit push:pg

# Pull schema from database
npx drizzle-kit pull:pg

-- drizzle/0010_welcome_tales.sql
CREATE TABLE "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "email" text NOT NULL UNIQUE,
  "password" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL
);

// ✅ Good - Use inferred types
export type User = typeof users.$inferSelect

// ❌ Bad - Manual types
interface User {
  id: number
  name: string
}

// src/schema/index.ts
export * from './users'
export * from './posts'
export * from './comments'

// ✅ Good - Transaction ensures atomicity
await db.transaction(async (tx) => {
  await tx.update(balance).set({...})
  await tx.insert(transactionLog).values({...})
})

// ❌ Bad - Separate operations
await db.update(balance).set({...})
await db.insert(transactionLog).values({...})