Back to Home
Drizzle ORM logoDrizzle ORM INTERMEDIATE

Drizzle ORM

Drizzle ORM cheat sheet with schema definition, queries, relations, migrations, and TypeScript-first database management examples.

12 min read
drizzleormtypescriptsqldatabasepostgresqlmysqlsqlite

Database Setup

Connect Drizzle ORM to PostgreSQL, MySQL, or SQLite using the unified drizzle() API.

PostgreSQL Setup

Connect to PostgreSQL using node-postgres or postgres.js drivers.

typescript
💡 Pass schema to drizzle() to enable relational queries
⚡ Use connection pooling (Pool) in production
📌 Install: npm i drizzle-orm pg @types/pg
🟢 postgres.js is recommended for serverless
setuppostgresqlconnection

MySQL Setup

Connect to MySQL using mysql2 driver.

typescript
💡 Install: npm i drizzle-orm mysql2
⚡ Use createPool() for connection reuse
📌 MySQL uses mysqlTable instead of pgTable
🟢 Works with PlanetScale serverless driver too
setupmysqlconnection

SQLite Setup

Connect to SQLite using better-sqlite3 or libsql drivers.

typescript
💡 Install: npm i drizzle-orm better-sqlite3
⚡ SQLite is great for local dev and embedded apps
📌 Turso uses libSQL driver for edge deployments
🟢 SQLite uses sqliteTable instead of pgTable
setupsqliteconnectionturso

Drizzle Config File

Configure drizzle-kit for migrations and studio.

typescript
💡 Install: npm i -D drizzle-kit
⚡ Use defineConfig() for type-safe configuration
📌 Set dialect to match your database engine
🟢 tablesFilter helps with multi-tenant schemas
configdrizzle-kitsetup

Schema Definition

Define tables, columns, types, and constraints using Drizzle's type-safe schema builders.

Basic Table & Column Types

Define a table with common column types and constraints.

typescript
💡 Use $type<T>() to add TS typing to json cols
⚡ $onUpdate is a JS callback, not a DB trigger
📌 Column name string maps to actual DB column name
🟢 Use .notNull() to prevent null — safer types
schemacolumnstypestable

Enums & Custom Types

Define PostgreSQL enums and use them in table schemas.

📄 Codetypescript
💡 pgEnum creates a real PostgreSQL ENUM type
⚡ Enum values are type-safe in TS automatically
📌 MySQL uses mysqlEnum() defined inline on column
🟢 Drizzle infers TS union type from enum values
schemaenumtypes

Indexes & Constraints

Add indexes, unique constraints, and composite keys to tables.

📄 Codetypescript
💡 Third arg to pgTable defines indexes/constraints
⚡ Composite indexes speed up multi-column queries
📌 uniqueIndex() creates a UNIQUE index in the DB
🟢 Return an array of indexes from the callback
schemaindexesconstraints

Foreign Keys

Define foreign key references between tables.

📄 Codetypescript
💡 .references() creates a DB-level foreign key
⚡ onDelete: 'cascade' auto-deletes child rows
📌 Options: cascade, restrict, no action, set null
🟢 FK is separate from Drizzle relations (ORM)
schemaforeign-keyreferences

Type Inference from Schema

Extract TypeScript types from your Drizzle schema definitions.

typescript
💡 InferSelectModel = what DB returns on select
⚡ InferInsertModel makes defaults optional
📌 Use these types in your app layer for safety
🟢 Also available as users.$inferSelect shorthand
schematypesinference

🔗 Relations

Define relationships between tables for Drizzle's relational query builder.

One-to-Many Relation

Define a one-to-many relationship between users and posts.

📄 Codetypescript
💡 Relations are ORM-level, not DB constraints
⚡ defineRelations() is the v2 API (recommended)
📌 from = FK column, to = target PK column
🟢 Pass schema to drizzle() to use relations
relationsone-to-many

One-to-One Relation

Define a one-to-one relationship between users and profiles.

📄 Codetypescript
💡 r.one on both sides makes it one-to-one
⚡ Add .unique() on FK column to enforce in DB
📌 from/to go on the FK-owning side only
🟢 The non-FK side just uses r.one.table()
relationsone-to-one

Many-to-Many Relation

Define a many-to-many relationship using a junction table.

📄 Codetypescript
💡 v2 through() replaces junction table relations
⚡ No need to define relations on junction table
📌 Query directly: with: { tags: true }
🟢 Junction table still needs FK references
relationsmany-to-manyjunction

Querying with Relations

Use the relational query builder to fetch nested data.

typescript
💡 db.query.* requires schema passed to drizzle()
⚡ v2 many-to-many queries skip junction tables
📌 Use columns: {} to select specific fields
🟢 findFirst returns single record or undefined
relationsquerywith

Select Queries

Build type-safe SELECT queries with filters, ordering, and pagination.

Basic Select

Select all or specific columns from a table.

typescript
💡 Empty select() returns all columns typed
⚡ Partial select returns only chosen columns
📌 Use sql tag for raw SQL expressions
🟢 selectDistinct() for unique value queries
selectquerycolumns

Where Clauses & Operators

Filter queries with comparison and logical operators.

typescript
💡 ilike is PostgreSQL-only (case-insensitive)
⚡ All operators imported from 'drizzle-orm'
📌 and()/or() accept any number of conditions
🟢 Operators return SQL typed — fully composable
selectwhereoperatorsfilter

Ordering, Limit & Offset

Sort results and implement pagination with limit/offset.

📄 Codetypescript
💡 asc/desc imported from 'drizzle-orm'
⚡ Pass multiple columns for multi-level sorting
📌 Always use orderBy with limit for consistency
🟢 Cursor-based pagination is better at scale
selectorderpaginationlimit

✏️ Insert / Update / Delete

Mutate data with type-safe insert, update, and delete operations.

Insert Rows

Insert single or multiple rows with optional returning clause.

typescript
💡 .returning() is PostgreSQL and SQLite only
⚡ Bulk insert with array of values is efficient
📌 Defaults are applied automatically by the DB
🟢 Destructure the array to get a single result
insertreturningbulk

Upsert (On Conflict)

Insert or update on conflict using onConflictDoUpdate or onConflictDoNothing.

typescript
💡 target must be a unique or PK column(s)
⚡ sql`excluded.*` references incoming values
📌 onConflictDoNothing silently skips duplicates
🟢 Combine with .returning() to get final row
insertupsertconflict

Update Rows

Update existing rows with type-safe set and where clauses.

typescript
💡 .set() accepts partial object — only named cols
⚡ Use sql tag for increment/decrement operations
📌 Always add .where() to avoid updating all rows
🟢 .returning() gives you the updated row(s)
updatesetreturning

Delete Rows

Delete rows from tables with type-safe conditions.

📄 Codetypescript
💡 Always use .where() to prevent deleting all!
⚡ .returning() gets deleted rows (PG/SQLite)
📌 Cascade deletes depend on FK onDelete setting
🟢 Combine with and()/or() for precise targeting
deletereturning

🔗 Joins

Combine data from multiple tables using SQL-level joins.

Inner Join

Return rows that have matching values in both tables.

📄 Codetypescript
💡 innerJoin excludes rows with no match
⚡ Specify columns in select() to avoid clashes
📌 Join condition uses eq() like where clauses
🟢 Result type is inferred from selected columns
joininner-join

Left / Right / Full Join

Return all rows from one or both tables, even without matches.

typescript
💡 Left join makes right-side columns nullable
⚡ Chain multiple joins for complex queries
📌 Full join makes both sides nullable
🟢 Drizzle adjusts TS types per join type
joinleft-joinright-joinfull-join

📊 Aggregations

Perform aggregate calculations with groupBy and having clauses.

Aggregate Functions

Use count, sum, avg, min, and max for data aggregation.

typescript
💡 count() with no args counts all rows
⚡ count(column) counts non-null values only
📌 sum/avg return string — cast if needed
🟢 Use sql tag for count(distinct ...) queries
aggregationcountsumavg

Group By & Having

Group results and filter aggregated data.

📄 Codetypescript
💡 groupBy groups rows before aggregation
⚡ having filters after aggregation (vs where)
📌 groupBy all non-aggregate selected columns
🟢 Combine with joins for cross-table aggregates
aggregationgroup-byhaving

🔒 Transactions

Execute multiple operations atomically with transaction support.

Basic Transaction

Wrap multiple queries in an ACID transaction that auto-rolls back on error.

typescript
💡 Use tx instead of db inside transactions
⚡ Throwing an error auto-triggers rollback
📌 tx.rollback() for manual abort when needed
🟢 Transaction return value becomes result
transactionrollbackatomic

Nested Transactions (Savepoints)

Use nested transactions that create savepoints for partial rollback.

📄 Codetypescript
💡 Nested tx.transaction() creates a SAVEPOINT
⚡ Only nested changes roll back on failure
📌 Outer transaction continues after nested fail
🟢 Wrap nested tx in try/catch to handle errors
transactionnestedsavepoint

Prepared Statements & Raw SQL

Optimize query performance with prepared statements and raw SQL expressions.

Prepared Statements

Pre-compile queries for repeated execution with different parameters.

typescript
💡 Prepared stmts skip query planning on reuse
⚡ Use placeholder() for dynamic parameter slots
📌 Name the prepared stmt for DB-level caching
🟢 Works with select, insert, update, and delete
preparedplaceholderperformance

Raw SQL & Dynamic Queries

Use raw SQL expressions and build dynamic queries conditionally.

typescript
💡 sql tag auto-parameterizes to prevent injection
⚡ Use sql<Type> to type raw SQL results
📌 $dynamic() enables conditional query building
🟢 Drizzle escapes all interpolated values safely
raw-sqldynamicperformance

📦 Migrations

Manage database schema changes with drizzle-kit CLI commands.

Generate & Run Migrations

Generate SQL migration files from schema changes and apply them.

📄 Codebash
💡 generate creates SQL files in your out dir
⚡ push is great for prototyping — skips files
📌 migrate runs pending SQL migration files
🟢 studio opens a GUI at https://local.drizzle.studio
migrationsdrizzle-kitcli

Migration Workflow

Typical development workflow with schema changes and migrations.

📄 Codebash
💡 Always review generated SQL before migrating
⚡ check validates migration consistency
📌 drop removes last migration file if unapplied
🟢 Commit migration files to version control
migrationsworkflowdrizzle-kit

Programmatic Migrations

Run migrations from your application code at startup.

📄 Codetypescript
💡 Import migrate from your specific driver path
⚡ Runs all pending migrations in order
📌 Safe to call on every startup — skips applied
🟢 Great for serverless or Docker deployments
migrationsprogrammaticstartup