What are the setup & configuration in Drizzle ORM?

npm i drizzle-orm — Install Drizzle ORM; npm i -D drizzle-kit — Install Drizzle Kit (migrations CLI); npm i @neondatabase/serverless — Neon Postgres driver; npm i postgres — postgres.js driver; npm i better-sqlite3 — SQLite driver; npm i @libsql/client — Turso (libSQL) driver; npx drizzle-kit generate — Generate SQL migration files; npx drizzle-kit migrate — Run pending migrations; npx drizzle-kit push — Push schema directly (dev only); npx drizzle-kit studio — Open Drizzle Studio (GUI)

What are the schema definition (postgresql) in Drizzle ORM?

import { pgTable, serial, text, integer, boolean, timestamp } from "drizzle-orm/pg-core" — Import column types; export const users = pgTable("users", { ... }) — Define a table; id: serial("id").primaryKey() — Auto-increment primary key; name: text("name").notNull() — Required text column; email: text("email").unique() — Unique constraint; age: integer("age").default(0) — Integer with default value; active: boolean("active").default(true) — Boolean with default; createdAt: timestamp("created_at").defaultNow() — Timestamp with default now(); role: text("role", { enum: ["admin", "user"] }) — Enum-like text column; bio: text("bio") — Nullable text column (default)

What are the schema definition (sqlite) in Drizzle ORM?

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core" — Import SQLite types; export const users = sqliteTable("users", { ... }) — Define SQLite table; id: integer("id").primaryKey({ autoIncrement: true }) — Auto-increment PK; name: text("name").notNull() — Required text; createdAt: integer("created_at", { mode: "timestamp" }) — Timestamp as integer

Drizzle ORM Cheat Sheet

Quick reference for Drizzle ORM — schema definition, queries, relations, migrations, and database operations. Type-safe SQL for TypeScript developers.

Setup & Configuration Schema Definition (PostgreSQL) Schema Definition (SQLite) Select Queries Insert / Update / Delete Relations & Joins Transactions & Raw SQL

Setup & Configuration

npm i drizzle-orm	Install Drizzle ORM
npm i -D drizzle-kit	Install Drizzle Kit (migrations CLI)
npm i @neondatabase/serverless	Neon Postgres driver
npm i postgres	postgres.js driver
npm i better-sqlite3	SQLite driver
npm i @libsql/client	Turso (libSQL) driver
npx drizzle-kit generate	Generate SQL migration files
npx drizzle-kit migrate	Run pending migrations
npx drizzle-kit push	Push schema directly (dev only)
npx drizzle-kit studio	Open Drizzle Studio (GUI)

Schema Definition (PostgreSQL)

import { pgTable, serial, text, integer, boolean, timestamp } from "drizzle-orm/pg-core"	Import column types
export const users = pgTable("users", { ... })	Define a table
id: serial("id").primaryKey()	Auto-increment primary key
name: text("name").notNull()	Required text column
email: text("email").unique()	Unique constraint
age: integer("age").default(0)	Integer with default value
active: boolean("active").default(true)	Boolean with default
createdAt: timestamp("created_at").defaultNow()	Timestamp with default now()
role: text("role", { enum: ["admin", "user"] })	Enum-like text column
bio: text("bio")	Nullable text column (default)

Schema Definition (SQLite)

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core"	Import SQLite types
export const users = sqliteTable("users", { ... })	Define SQLite table
id: integer("id").primaryKey({ autoIncrement: true })	Auto-increment PK
name: text("name").notNull()	Required text
createdAt: integer("created_at", { mode: "timestamp" })	Timestamp as integer

Select Queries

const users = await db.select().from(usersTable)	Select all rows
db.select().from(users).where(eq(users.id, 1))	Where clause (equals)
db.select({ name: users.name }).from(users)	Select specific columns
where(and(eq(users.role, "admin"), gt(users.age, 18)))	AND condition
where(or(eq(users.role, "admin"), eq(users.role, "mod")))	OR condition
where(like(users.name, "%john%"))	LIKE pattern match
where(inArray(users.id, [1, 2, 3]))	IN array
where(isNull(users.deletedAt))	IS NULL check
.orderBy(desc(users.createdAt))	Order by descending
.limit(10).offset(20)	Pagination

Insert / Update / Delete

await db.insert(users).values({ name: "John", email: "[email protected]" })	Insert one row
await db.insert(users).values([...]).returning()	Insert many, return inserted
.onConflictDoUpdate({ target: users.email, set: { name: "New" } })	Upsert on conflict
.onConflictDoNothing()	Skip on conflict
await db.update(users).set({ name: "Jane" }).where(eq(users.id, 1))	Update rows
await db.delete(users).where(eq(users.id, 1))	Delete rows
await db.delete(users).where(lt(users.createdAt, cutoff))	Delete with condition

Relations & Joins

export const usersRelations = relations(users, ({ many }) => ({ posts: many(posts) }))	One-to-many relation
export const postsRelations = relations(posts, ({ one }) => ({ author: one(users, { fields: [posts.authorId], references: [users.id] }) }))	Many-to-one relation
db.query.users.findMany({ with: { posts: true } })	Query with relations (relational queries)
db.query.users.findFirst({ where: eq(users.id, 1), with: { posts: true } })	Find one with relations
db.select().from(users).leftJoin(posts, eq(users.id, posts.authorId))	Left join (SQL-like)
db.select().from(users).innerJoin(posts, eq(users.id, posts.authorId))	Inner join

Transactions & Raw SQL

await db.transaction(async (tx) => { ... })	Database transaction
tx.rollback()	Rollback transaction
import { sql } from "drizzle-orm"	Import sql template tag
db.execute(sql`SELECT * FROM users WHERE id = ${id}`)	Raw SQL query
sql`count(*)`.as("total")	SQL expression with alias
db.select({ count: sql`count(*)` }).from(users)	Aggregate query

Try It Live

Test these patterns with our free SQL Formatter. No signup needed.

Open SQL Formatter →

Step-by-Step Guide

How to Format SQL Online

Read Guide →

More Cheat Sheets

Regex Cheat Sheet Git Cheat Sheet Markdown Cheat Sheet HTTP Status Codes Cheat Sheet