Drizzle ORM: Type-Safe Database for TypeScript

What is Drizzle ORM?

Drizzle is a lightweight, type-safe ORM for TypeScript. It provides SQL-like syntax with full type inference, making database operations safe and predictable.

Drizzle vs Other ORMs

Feature	Drizzle	Prisma	TypeORM
Type Safety	Excellent	Good	Moderate
Bundle Size	Tiny	Large	Large
SQL-like	Yes	No	Partial
Migrations	Built-in	CLI	Built-in

Getting Started

Installation

# Core package
npm install drizzle-orm

# Database driver (choose one)
npm install postgres  # PostgreSQL
npm install mysql2    # MySQL
npm install better-sqlite3  # SQLite

# Drizzle Kit for migrations
npm install -D drizzle-kit

Configuration

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

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

Database Connection

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

const client = postgres(process.env.DATABASE_URL!)

export const db = drizzle(client, { schema })

Schema Definition

Tables

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

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  username: varchar('username', { length: 50 }).notNull(),
  password: varchar('password', { length: 255 }).notNull(),
  role: varchar('role', { length: 20 }).default('user'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
})

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  content: text('content'),
  published: boolean('published').default(false),
  authorId: integer('author_id').references(() => users.id),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
})

// Comments table
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id').references(() => posts.id, { onDelete: 'cascade' }),
  userId: integer('user_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow()
})

// Products table with decimal
export const products = pgTable('products', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
  stock: integer('stock').default(0)
})

Relations

import { relations } from 'drizzle-orm'

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

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

// Comment relations
export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id]
  }),
  user: one(users, {
    fields: [comments.userId],
    references: [users.id]
  })
}))

Types

// Infer types from schema
import { 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>

export type Comment = InferSelectModel<typeof comments>
export type NewComment = InferInsertModel<typeof comments>

CRUD Operations

Insert

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

// Insert single record
const newUser = await db.insert(users).values({
  email: 'john@example.com',
  username: 'johndoe',
  password: 'hashedpassword'
}).returning()

// Insert with returning specific fields
const user = await db.insert(users).values({
  email: 'jane@example.com',
  username: 'janedoe',
  password: 'hashedpassword'
}).returning({ id: users.id, email: users.email })

// Insert multiple records
const newPosts = await db.insert(posts).values([
  { title: 'Post 1', slug: 'post-1', authorId: 1 },
  { title: 'Post 2', slug: 'post-2', authorId: 1 },
  { title: 'Post 3', slug: 'post-3', authorId: 2 }
]).returning()

// Insert with conflict handling (upsert)
import { sql } from 'drizzle-orm'

await db.insert(users)
  .values({ email: 'john@example.com', username: 'john', password: 'pass' })
  .onConflictDoUpdate({
    target: users.email,
    set: { username: 'john_updated' }
  })

// On conflict do nothing
await db.insert(users)
  .values({ email: 'john@example.com', username: 'john', password: 'pass' })
  .onConflictDoNothing()

Select

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

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

// Select with specific columns
const userEmails = await db.select({
  id: users.id,
  email: users.email
}).from(users)

// Select with where clause
const activeUsers = await db.select()
  .from(users)
  .where(eq(users.isActive, true))

// Complex filtering
const filteredPosts = await db.select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      gt(posts.createdAt, new Date('2024-01-01')),
      or(
        like(posts.title, '%TypeScript%'),
        like(posts.title, '%JavaScript%')
      )
    )
  )

// Ordering and pagination
const paginatedPosts = await db.select()
  .from(posts)
  .where(eq(posts.published, true))
  .orderBy(posts.createdAt)
  .limit(10)
  .offset(20)

// Multiple order by
import { desc, asc } from 'drizzle-orm'

const sortedPosts = await db.select()
  .from(posts)
  .orderBy(desc(posts.createdAt), asc(posts.title))

// Count
const postCount = await db.select({ count: sql<number>`count(*)` })
  .from(posts)
  .where(eq(posts.published, true))

// Group by
const postsByAuthor = await db.select({
  authorId: posts.authorId,
  postCount: sql<number>`count(*)`
})
  .from(posts)
  .groupBy(posts.authorId)

// Distinct
const uniqueAuthors = await db.selectDistinct({ authorId: posts.authorId })
  .from(posts)

Joins

// Inner join
const postsWithAuthors = await db.select({
  post: posts,
  author: users
})
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))

// Left join
const postsWithOptionalAuthor = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))

// Multiple joins
const postsWithDetails = await db.select({
  postId: posts.id,
  postTitle: posts.title,
  authorName: users.username,
  commentCount: sql<number>`count(${comments.id})`
})
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(posts.id, comments.postId))
  .groupBy(posts.id, users.username)

Query with Relations

// Using query API for relations
const postsWithRelations = await db.query.posts.findMany({
  with: {
    author: true,
    comments: {
      with: {
        user: true
      }
    }
  },
  where: eq(posts.published, true),
  orderBy: desc(posts.createdAt),
  limit: 10
})

// Find one
const post = await db.query.posts.findFirst({
  where: eq(posts.slug, 'my-post'),
  with: {
    author: {
      columns: {
        id: true,
        username: true
      }
    }
  }
})

// Nested filtering
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      limit: 5
    }
  }
})

Update

// Update single record
await db.update(users)
  .set({ isActive: false })
  .where(eq(users.id, 1))

// Update with returning
const updatedUser = await db.update(users)
  .set({
    username: 'newusername',
    updatedAt: new Date()
  })
  .where(eq(users.id, 1))
  .returning()

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

// Increment value
await db.update(products)
  .set({
    stock: sql`${products.stock} - 1`
  })
  .where(eq(products.id, productId))

Delete

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

// Delete with returning
const deletedUsers = await db.delete(users)
  .where(eq(users.isActive, false))
  .returning()

// Delete all (dangerous!)
await db.delete(posts)

Transactions

// Basic transaction
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({
    email: 'new@example.com',
    username: 'newuser',
    password: 'password'
  }).returning()

  await tx.insert(posts).values({
    title: 'Welcome Post',
    slug: 'welcome',
    authorId: user.id
  })
})

// Transaction with rollback
try {
  await db.transaction(async (tx) => {
    await tx.update(products)
      .set({ stock: sql`${products.stock} - 1` })
      .where(eq(products.id, productId))

    const [product] = await tx.select()
      .from(products)
      .where(eq(products.id, productId))

    if (product.stock < 0) {
      tx.rollback()
    }

    await tx.insert(orders).values({
      productId,
      quantity: 1
    })
  })
} catch (error) {
  console.log('Transaction rolled back')
}

Migrations

# Generate migration
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema (development)
npx drizzle-kit push

# Studio (GUI)
npx drizzle-kit studio

Migration File

// drizzle/0001_create_users.ts
import { sql } from 'drizzle-orm'
import { pgTable, serial, varchar, timestamp } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow()
})

Prepared Statements

import { placeholder } from 'drizzle-orm'

// Create prepared statement
const getUserByEmail = db.select()
  .from(users)
  .where(eq(users.email, placeholder('email')))
  .prepare('get_user_by_email')

// Execute with parameters
const user = await getUserByEmail.execute({ email: 'john@example.com' })

// Prepared insert
const createUser = db.insert(users)
  .values({
    email: placeholder('email'),
    username: placeholder('username'),
    password: placeholder('password')
  })
  .prepare('create_user')

await createUser.execute({
  email: 'new@example.com',
  username: 'newuser',
  password: 'hashedpassword'
})

Custom Types

import { customType } from 'drizzle-orm/pg-core'

// Custom JSON type
const json = <T>() => customType<{ data: T; driverData: string }>({
  dataType() {
    return 'json'
  },
  toDriver(value: T): string {
    return JSON.stringify(value)
  },
  fromDriver(value: string): T {
    return JSON.parse(value)
  }
})

// Use in schema
export const settings = pgTable('settings', {
  id: serial('id').primaryKey(),
  config: json<{ theme: string; notifications: boolean }>()('config')
})

Summary

Feature	Syntax
Select	db.select().from(table)
Insert	db.insert(table).values({})
Update	db.update(table).set({})
Delete	db.delete(table).where()
Relations	db.query.table.findMany({ with: {} })
Transactions	db.transaction(async (tx) => {})

Drizzle ORM provides type-safe, SQL-like database operations with excellent TypeScript integration.