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 trails