Provides comprehensive Drizzle ORM patterns for schema definition, CRUD operations, relations, queries, transactions, and migrations. Proactively use for any…
Drizzle ORM Patterns
Overview
Expert guide for building type-safe database applications with Drizzle ORM. Covers schema definition, relations, queries, transactions, and migrations for all supported databases.
When to Use
Defining database schemas with tables, columns, and constraints
Creating relations between tables (one-to-one, one-to-many, many-to-many)
Writing type-safe CRUD queries
Implementing complex joins and aggregations
Managing database transactions with rollback
Setting up migrations with Drizzle Kit
Working with PostgreSQL, MySQL, SQLite, MSSQL, or CockroachDB
Quick Reference
Database
Table Function
Import
PostgreSQL
pgTable()
drizzle-orm/pg-core
MySQL
mysqlTable()
drizzle-orm/mysql-core
SQLite
sqliteTable()
drizzle-orm/sqlite-core
MSSQL
mssqlTable()
drizzle-orm/mssql-core
Operation
Method
Example
Insert
db.insert()
db.insert(users).values({...})
Select
db.select()
db.select().from(users).where(eq(...))
Update
db.update()
db.update(users).set({...}).where(...)
Delete
db.delete()
db.delete(users).where(...)
Transaction
db.transaction()
db.transaction(async (tx) => {...})
Instructions
Identify your database dialect - Choose PostgreSQL, MySQL, SQLite, MSSQL, or CockroachDB
Define your schema - Use the appropriate table function (pgTable, mysqlTable, etc.)
Set up relations - Define relations using relations() or defineRelations()
Initialize the database client - Create your Drizzle client with proper credentials
Write queries - Use the query builder for type-safe CRUD operations
Handle transactions - Wrap multi-step operations in transactions when needed
Set up migrations - Configure Drizzle Kit for schema management
Examples
Example 1: Basic Schema and Query
import { pgTable, serial, text } from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq } from 'drizzle-orm';
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull().unique(),
});
const db = drizzle(process.env.DATABASE_URL);
const [user] = await db.select().from(users).where(eq(users.id, 1));
Example 2: CRUD Operations
import { eq } from 'drizzle-orm';
// Insert
const [newUser] = await db.insert(users).values({
name: 'John',
email: 'john@example.com',
}).returning();
// Update
await db.update(users)
.set({ name: 'John Updated' })
.where(eq(users.id, 1));
// Delete
await db.delete(users).where(eq(users.id, 1));
Example 3: Transaction with Rollback
await db.transaction(async (tx) => {
const [from] = await tx.select().from(accounts)
.where(eq(accounts.userId, fromId));
if (from.balance < amount) {
tx.rollback();
}
await tx.update(accounts)
.set({ balance: sql`${accounts.balance} - ${amount}` })
.where(eq(accounts.userId, fromId));
});
See references/transactions.md for advanced transaction patterns.
Best Practices
Type Safety: Always use TypeScript and leverage $inferInsert / $inferSelect
Relations: Define relations using the relations() API for nested queries
Transactions: Use transactions for multi-step operations that must succeed together
Migrations: Use generate + migrate in production, push for development
Indexes: Add indexes on frequently queried columns and foreign keys
Soft Deletes: Use deletedAt timestamp instead of hard deletes when possible
Pagination: Use cursor-based pagination for large datasets
Query Optimization: Use .limit() and .where() to fetch only needed data
Constraints and Warnings
Foreign Key Constraints: Always define references using arrow functions () => table.column to avoid circular dependency issues
Transaction Rollback: Calling tx.rollback() throws an exception - use try/catch if needed
Returning Clauses: Not all databases support .returning() - check your dialect compatibility
Batch Operations: Large batch inserts may hit database limits - chunk into smaller batches
Migrations in Production: Always test migrations in staging before applying to production
References
Core Concepts
references/schema-definition.md - Complete schema definition for all databases (PostgreSQL, MySQL, SQLite), column types, indexes, and constraints
references/relations.md - One-to-one, one-to-many, many-to-many relations with v1 and v2 syntax
references/queries-joins-aggregations.md - CRUD operations, query operators, joins, aggregations, and pagination
Advanced Topics
references/transactions.md - Transaction patterns, rollback handling, nested transactions
references/migrations.md - Drizzle Kit configuration, CLI commands, migration workflow
references/common-patterns.md - Soft delete, upsert, batch operations, full-text search, audit trailsdon't have the plugin yet? install it then click "run inline in claude" again.