/drizzle-orm-patterns

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

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](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