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