Drizzle ORM Guide (80-20)

The 20% you need to know that covers 80% of use cases.

Installation

PostgreSQL

npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

MySQL

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

SQLite

npm install drizzle-orm better-sqlite3
npm install -D drizzle-kit @types/better-sqlite3

Project Setup

Create drizzle.config.ts:

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql', // or 'mysql', 'sqlite'
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
})

Define Schema

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  age: integer('age'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
})

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

Database Connection

PostgreSQL

// src/db/index.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 })

SQLite

import { drizzle } from 'drizzle-orm/better-sqlite3'
import Database from 'better-sqlite3'
import * as schema from './schema'

const sqlite = new Database('sqlite.db')
export const db = drizzle(sqlite, { schema })

Migrations

Generate Migration

npx drizzle-kit generate

This creates SQL migration files in ./drizzle folder.

Run Migrations

npx drizzle-kit migrate

Push Schema (Development)

Skip migrations, directly push schema changes:

npx drizzle-kit push

Warning

Use push only in development. Use migrations in production.

CRUD Operations

Insert

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

// Insert one
const newUser = await db.insert(users).values({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
}).returning()

console.log(newUser) // Returns inserted row

// Insert multiple
await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
])

Select

import { eq, gt, and, or } from 'drizzle-orm'

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

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

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

// Multiple conditions
const filteredUsers = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      gt(users.age, 18)
    )
  )

// OR condition
const someUsers = await db
  .select()
  .from(users)
  .where(
    or(
      eq(users.name, 'John'),
      eq(users.name, 'Jane')
    )
  )

// Limit & Offset
const paginatedUsers = await db
  .select()
  .from(users)
  .limit(10)
  .offset(20)

// Order by
const sortedUsers = await db
  .select()
  .from(users)
  .orderBy(users.createdAt)

Update

// Update with condition
await db
  .update(users)
  .set({ age: 31 })
  .where(eq(users.email, 'john@example.com'))

// Update and return
const updated = await db
  .update(users)
  .set({ isActive: false })
  .where(eq(users.id, 1))
  .returning()

Delete

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

// Delete and return
const deleted = await db
  .delete(users)
  .where(eq(users.email, 'john@example.com'))
  .returning()

Operators

Operator	Usage	SQL
eq	eq(users.id, 1)	id = 1
ne	ne(users.age, 30)	age != 30
gt	gt(users.age, 18)	age > 18
gte	gte(users.age, 18)	age >= 18
lt	lt(users.age, 65)	age < 65
lte	lte(users.age, 65)	age <= 65
like	like(users.name, '%John%')	name LIKE '%John%'
isNull	isNull(users.age)	age IS NULL
isNotNull	isNotNull(users.age)	age IS NOT NULL
and	and(eq(...), gt(...))	AND
or	or(eq(...), eq(...))	OR

Relationships

Define Relations

import { relations } from 'drizzle-orm'

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

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

Query with Joins

// Manual join
const usersWithPosts = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))

// With relations (requires schema)
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
})

// Nested relations
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: {
      where: eq(posts.title, 'Hello'),
    },
  },
})

Column Types

PostgreSQL

import { 
  serial, integer, bigint, real, doublePrecision,
  text, varchar, char, boolean,
  date, timestamp, time, interval,
  json, jsonb, uuid
} from 'drizzle-orm/pg-core'

export const example = pgTable('example', {
  id: serial('id').primaryKey(),
  num: integer('num'),
  bigNum: bigint('big_num', { mode: 'number' }),
  price: real('price'),
  name: text('name'),
  email: varchar('email', { length: 255 }),
  isActive: boolean('is_active'),
  birthDate: date('birth_date'),
  createdAt: timestamp('created_at').defaultNow(),
  metadata: jsonb('metadata'),
  uuid: uuid('uuid').defaultRandom(),
})

Modifiers

// Not null
name: text('name').notNull()

// Unique
email: text('email').unique()

// Default value
isActive: boolean('is_active').default(true)
createdAt: timestamp('created_at').defaultNow()

// Primary key
id: serial('id').primaryKey()

// Foreign key
userId: integer('user_id').references(() => users.id)

// On delete cascade
userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' })

Transactions

await db.transaction(async (tx) => {
  const user = await tx.insert(users).values({
    name: 'John',
    email: 'john@example.com',
  }).returning()

  await tx.insert(posts).values({
    title: 'First Post',
    userId: user[0].id,
  })
})

If any query fails, the entire transaction is rolled back.

Raw SQL

import { sql } from 'drizzle-orm'

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

// With select
const users = await db
  .select()
  .from(users)
  .where(sql`age > 18 AND name LIKE '%John%'`)

TypeScript Types

import { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import { users } from './schema'

// Infer types from schema
type User = InferSelectModel<typeof users>
type NewUser = InferInsertModel<typeof users>

// Usage
const user: User = {
  id: 1,
  name: 'John',
  email: 'john@example.com',
  age: 30,
  isActive: true,
  createdAt: new Date(),
}

const newUser: NewUser = {
  name: 'Jane',
  email: 'jane@example.com',
  // Optional fields can be omitted
}

Best Practices

1. Use schema file - Keep all table definitions in one place
2. Define relations - Makes queries easier
3. Use migrations - Track database changes
4. Type inference - Let TypeScript infer types from schema
5. Transactions - Use for multiple related operations
6. Indexes - Add for frequently queried columns
7. Environment variables - Never hardcode database URLs

Example: Full Setup

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

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

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

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

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' }),
  createdAt: timestamp('created_at').defaultNow(),
})

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

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

// src/db/index.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 })

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

// Create user
const [user] = await db.insert(users).values({
  name: 'John Doe',
  email: 'john@example.com',
}).returning()

// Create post
await db.insert(posts).values({
  title: 'Hello World',
  content: 'My first post',
  userId: user.id,
})

// Get user with posts
const userWithPosts = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.id, user.id),
  with: {
    posts: true,
  },
})

console.log(userWithPosts)

Quick Start

1. Install: npm install drizzle-orm pg drizzle-kit
2. Create schema file
3. Run: npx drizzle-kit generate then npx drizzle-kit migrate
4. Start querying!