---
name: drizzle-orm-patterns
description: Provides comprehensive Drizzle ORM patterns for schema definition, CRUD operations, relations, queries, transactions, and migrations. Proactively use for any Drizzle ORM development including defining database schemas, writing type-safe queries, implementing relations, managing transactions, and setting up migrations with Drizzle Kit. Supports PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.
allowed-tools: Read, Write, Edit, Bash, Grep, Glob
---
# 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
1. **Identify your database dialect** - Choose PostgreSQL, MySQL, SQLite, MSSQL, or CockroachDB
2. **Define your schema** - Use the appropriate table function (pgTable, mysqlTable, etc.)
3. **Set up relations** - Define relations using `relations()` or `defineRelations()`
4. **Initialize the database client** - Create your Drizzle client with proper credentials
5. **Write queries** - Use the query builder for type-safe CRUD operations
6. **Handle transactions** - Wrap multi-step operations in transactions when needed
7. **Set up migrations** - Configure Drizzle Kit for schema management
## Examples
### Example 1: Basic Schema and Query
```typescript
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
```typescript
import { eq } from 'drizzle-orm';
// Insert
const [newUser] = await db.insert(users).values({
name: 'John',
email: '[email protected]',
}).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
```typescript
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](references/transactions.md) for advanced transaction patterns.
## Best Practices
1. **Type Safety**: Always use TypeScript and leverage `$inferInsert` / `$inferSelect`
2. **Relations**: Define relations using the relations() API for nested queries
3. **Transactions**: Use transactions for multi-step operations that must succeed together
4. **Migrations**: Use `generate` + `migrate` in production, `push` for development
5. **Indexes**: Add indexes on frequently queried columns and foreign keys
6. **Soft Deletes**: Use `deletedAt` timestamp instead of hard deletes when possible
7. **Pagination**: Use cursor-based pagination for large datasets
8. **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](references/schema-definition.md)** - Complete schema definition for all databases (PostgreSQL, MySQL, SQLite), column types, indexes, and constraints
- **[references/relations.md](references/relations.md)** - One-to-one, one-to-many, many-to-many relations with v1 and v2 syntax
- **[references/queries-joins-aggregations.md](references/queries-joins-aggregations.md)** - CRUD operations, query operators, joins, aggregations, and pagination
### Advanced Topics
- **[references/transactions.md](references/transactions.md)** - Transaction patterns, rollback handling, nested transactions
- **[references/migrations.md](references/migrations.md)** - Drizzle Kit configuration, CLI commands, migration workflow
- **[references/common-patterns.md](references/common-patterns.md)** - Soft delete, upsert, batch operations, full-text search, audit trails