drizzle-orm-patterns

SKILL.md

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