📖 Guide
Drizzle ORM — Complete Reference
Complete Drizzle ORM cheat sheet covering schema definition, queries, relations, joins, migrations, and TypeScript patterns.
83 commands across 9 categories
Schema DefinitionColumn TypesRelationsQueries (Select, Insert, Update, Delete)Filters & OperatorsJoinsMigrationsIndexesCommon Patterns
Schema Definition
| Command | Description |
|---|---|
pgTable('name', { ... })e.g. export const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name') }) | Define a PostgreSQL table schema |
mysqlTable('name', { ... }) | Define a MySQL table schema |
sqliteTable('name', { ... }) | Define a SQLite table schema |
.primaryKey() | Mark a column as the primary key |
.notNull() | Add NOT NULL constraint to a column |
.default(value)e.g. createdAt: timestamp('created_at').defaultNow() | Set a default value for a column |
.unique() | Add a UNIQUE constraint to a column |
.references(() => table.column)e.g. userId: integer('user_id').references(() => users.id) | Define a foreign key reference |
primaryKey({ columns: [t.a, t.b] }) | Define a composite primary key |
uniqueIndex('name').on(table.col) | Create a named unique index on a column |
Column Types
| Command | Description |
|---|---|
serial('name') | Auto-incrementing integer (PostgreSQL) |
integer('name') | Integer column |
text('name') | Variable-length text column |
varchar('name', { length: 255 }) | Variable-length string with max length |
boolean('name') | Boolean true/false column |
timestamp('name')e.g. timestamp('created_at', { withTimezone: true }).defaultNow() | Timestamp column. Use .defaultNow() for auto-set |
json('name') | JSON column — stores as text, no querying inside |
jsonb('name') | JSONB column (PostgreSQL) — binary JSON, supports operators |
decimal('name', { precision, scale })e.g. decimal('price', { precision: 10, scale: 2 }) | Exact decimal number |
pgEnum('name', [...])e.g. export const roleEnum = pgEnum('role', ['admin', 'user', 'guest']) | Define a PostgreSQL enum type |
Relations
| Command | Description |
|---|---|
relations(table, ({ one, many }) => ({...})) | Define relations for a table using the relations helper |
one(targetTable, { fields, references })e.g. author: one(users, { fields: [posts.authorId], references: [users.id] }) | Define a one-to-one or many-to-one relation |
many(targetTable)e.g. posts: many(posts) | Define a one-to-many relation |
relationName: 'name'e.g. one(users, { fields: [posts.authorId], references: [users.id], relationName: 'author' }) | Disambiguate multiple relations to the same table |
db.query.table.findFirst({ with })e.g. db.query.users.findFirst({ with: { posts: true } }) | Eagerly load relations using the with clause |
db.query.table.findMany({ with })e.g. db.query.posts.findMany({ with: { author: true, comments: { with: { user: true } } } }) | Load all records with relations |
Queries (Select, Insert, Update, Delete)
| Command | Description |
|---|---|
db.select().from(table)e.g. const allUsers = await db.select().from(users) | Select all columns from a table |
db.select({ id: table.id }).from(table) | Select specific columns |
db.insert(table).values({...})e.g. await db.insert(users).values({ name: 'Alice', email: 'alice@test.com' }) | Insert a single row |
db.insert(table).values([...]).returning() | Insert multiple rows and return inserted data |
db.insert(table).values({...}).onConflictDoUpdate({...})e.g. db.insert(users).values(data).onConflictDoUpdate({ target: users.email, set: { name: data.name } }) | Upsert — insert or update on conflict |
db.update(table).set({...}).where(condition)e.g. await db.update(users).set({ name: 'Bob' }).where(eq(users.id, 1)) | Update rows matching a condition |
db.delete(table).where(condition)e.g. await db.delete(users).where(eq(users.id, 1)) | Delete rows matching a condition |
.returning() | Return affected rows after insert/update/delete (PostgreSQL) |
db.query.table.findFirst({...})e.g. await db.query.users.findFirst({ where: eq(users.id, 1), with: { posts: true } }) | Find first matching row using query API |
db.query.table.findMany({...}) | Find all matching rows with relations, ordering, limits |
Filters & Operators
| Command | Description |
|---|---|
eq(column, value)e.g. where: eq(users.id, 1) | Equal to |
ne(column, value) | Not equal to |
gt(column, value) | Greater than |
gte(column, value) | Greater than or equal to |
lt(column, value) | Less than |
lte(column, value) | Less than or equal to |
like(column, pattern)e.g. like(users.name, '%alice%') | SQL LIKE pattern match |
ilike(column, pattern) | Case-insensitive LIKE (PostgreSQL) |
inArray(column, values)e.g. inArray(users.role, ['admin', 'moderator']) | IN clause — match any value in array |
isNull(column) / isNotNull(column) | Check for NULL values |
and(...conditions)e.g. and(eq(users.role, 'admin'), gt(users.age, 18)) | Combine conditions with AND |
or(...conditions) | Combine conditions with OR |
not(condition) | Negate a condition |
between(column, min, max) | BETWEEN operator for range queries |
sql`...`e.g. where: sql`lower(name) = 'alice'` | Raw SQL expression for custom filters |
Joins
| Command | Description |
|---|---|
.innerJoin(table, condition)e.g. db.select().from(posts).innerJoin(users, eq(posts.authorId, users.id)) | Inner join — only matching rows from both tables |
.leftJoin(table, condition) | Left join — all rows from left table, matching from right |
.rightJoin(table, condition) | Right join — all rows from right table, matching from left |
.fullJoin(table, condition) | Full outer join — all rows from both tables |
Select with join aliasese.g. db.select({ postTitle: posts.title, authorName: users.name }).from(posts).leftJoin(users, eq(posts.authorId, users.id)) | Access joined columns via table references |
.groupBy(column)e.g. db.select({ role: users.role, count: count() }).from(users).groupBy(users.role) | Group results by column(s) |
.having(condition) | Filter groups after GROUP BY |
.orderBy(asc(col) | desc(col))e.g. .orderBy(desc(users.createdAt)) | Sort results |
.limit(n).offset(m)e.g. .limit(10).offset(20) // page 3 | Paginate results |
Migrations
| Command | Description |
|---|---|
npx drizzle-kit generate | Generate SQL migration files from schema changes |
npx drizzle-kit push | Push schema directly to database without migration files (dev only) |
npx drizzle-kit migrate | Run pending migrations against the database |
npx drizzle-kit studio | Open Drizzle Studio — browser-based database GUI |
npx drizzle-kit introspect | Generate schema TypeScript from an existing database |
npx drizzle-kit drop | Drop a specific migration file |
npx drizzle-kit check | Check for schema conflicts and inconsistencies |
migrate(db, { migrationsFolder })e.g. import { migrate } from 'drizzle-orm/postgres-js/migrator' | Run migrations programmatically |
Indexes
| Command | Description |
|---|---|
index('name').on(table.column) | Create a basic index on a column |
uniqueIndex('name').on(table.column) | Create a unique index |
index('name').on(table.col1, table.col2) | Create a composite index on multiple columns |
index('name').using('gin')e.g. index('search_idx').using('gin').on(table.searchVector) | Specify index type (PostgreSQL: btree, hash, gin, gist) |
pgTable with indexes callbacke.g. pgTable('users', {...}, (t) => [index('email_idx').on(t.email)]) | Define indexes inline in table definition |
.where(sql'...')e.g. index('active_idx').on(table.email).where(sql'active = true') | Create a partial/filtered index |
Common Patterns
| Command | Description |
|---|---|
drizzle(client)e.g. import { drizzle } from 'drizzle-orm/postgres-js'; const db = drizzle(sql); | Initialize Drizzle with your database client |
drizzle(client, { schema })e.g. const db = drizzle(sql, { schema: { users, posts } }) | Initialize with schema for relational queries |
db.transaction(async (tx) => {...})e.g. await db.transaction(async (tx) => { await tx.insert(...); await tx.update(...); }) | Execute queries in a transaction |
sql operator for raw SQLe.g. sql'NOW()' or sql'COALESCE(name, 'unknown')' | Use the sql tagged template for raw expressions |
.prepare('name') | Create a prepared statement for repeated queries |
count() / sum() / avg() / min() / max()e.g. db.select({ total: count() }).from(users) | Aggregate functions |
alias(table, 'name')e.g. const parent = alias(categories, 'parent') | Create a table alias for self-joins |
Type inference: InferSelectModel<typeof table>e.g. type User = InferSelectModel<typeof users> | Get TypeScript type from schema |
InferInsertModel<typeof table>e.g. type NewUser = InferInsertModel<typeof users> | Get insert type (optional fields for defaults) |
📖 Free, searchable command reference. Bookmark this page for quick access.