Drizzle Orm Patterns

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

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

Drizzle ORM Patterns - Best Practices

Best practices, constraints, and warnings for using Drizzle ORM effectively.

Table of Contents

• Best Practices
• Constraints and Warnings
• Performance Tips
• Security Considerations

---

Best Practices

1. Type Safety

Always use TypeScript and leverage $inferInsert / $inferSelect for complete type safety.

// Infer types from schema
type NewUser = typeof users.$inferInsert;
type User = typeof users.$inferSelect;

// Use in function signatures
async function createUser(data: typeof users.$inferInsert) {
  return db.insert(users).values(data).returning();
}

async function getUser(id: number): Promise<typeof users.$inferSelect | undefined> {
  const [user] = await db.select().from(users).where(eq(users.id, id));
  return user;
}

2. Relations

Define relations using the relations() API to enable nested queries and maintain referential integrity.

// Good: Define both sides of the relation
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

3. Transactions

Use transactions for multi-step operations that must succeed together.

// Good: Transfer with transaction
await db.transaction(async (tx) => {
  await tx.update(accounts).set({ balance: fromBalance - amount }).where(eq(accounts.id, fromId));
  await tx.update(accounts).set({ balance: toBalance + amount }).where(eq(accounts.id, toId));
});

// Bad: No transaction - partial failure possible
await db.update(accounts).set({ balance: fromBalance - amount }).where(eq(accounts.id, fromId));
await db.update(accounts).set({ balance: toBalance + amount }).where(eq(accounts.id, toId));

4. Migrations

Use the appropriate migration strategy for each environment:

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

5. Indexes

Add indexes on frequently queried columns and foreign keys.

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}, (table) => [
  index('author_idx').on(table.authorId),    // For filtering by author
  index('created_idx').on(table.createdAt),  // For sorting by date
]);

6. Soft Deletes

Use deletedAt timestamp instead of hard deletes when data retention is required.

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  deletedAt: timestamp('deleted_at'),
});

// Always filter deleted records
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

7. Pagination

Use cursor-based pagination for large datasets to avoid OFFSET performance issues.

// Good: Cursor-based (efficient for large datasets)
const users = await db
  .select()
  .from(users)
  .where(gt(users.id, lastId))
  .orderBy(asc(users.id))
  .limit(10);

// Acceptable: OFFSET-based (okay for small datasets)
const users = await db
  .select()
  .from(users)
  .orderBy(asc(users.id))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

8. Query Optimization

Use .limit() and .where() to fetch only needed data.

// Good: Specific columns and limit
const userNames = await db
  .select({ name: users.name })
  .from(users)
  .where(eq(users.verified, true))
  .limit(10);

// Bad: Selecting all columns and rows
const allUsers = await db.select().from(users);

---

Constraints and Warnings

Foreign Key Constraints

Always define references using arrow functions () => table.column to avoid circular dependency issues.

// Good: Arrow function prevents circular dependency
authorId: integer('author_id').references(() => users.id),

// Bad: Direct reference can cause issues
authorId: integer('author_id').references(users.id),

Transaction Rollback

Calling tx.rollback() throws an exception. Use try/catch if you need to handle this gracefully.

// Rollback throws - handle if needed
try {
  await db.transaction(async (tx) => {
    if (insufficientFunds) {
      tx.rollback();
    }
  });
} catch (error) {
  // Transaction was rolled back
}

Returning Clauses

Not all databases support .returning(). Check your dialect compatibility:

Type Inference

For newer type-safe patterns, use InferSelectModel and InferInsertModel from drizzle-orm:

import { InferSelectModel, InferInsertModel } from 'drizzle-orm';

type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;

Batch Operations

Large batch inserts may hit database limits. Chunk into smaller batches:

// Good: Chunked batch insert
const BATCH_SIZE = 1000;
for (let i = 0; i < users.length; i += BATCH_SIZE) {
  const batch = users.slice(i, i + BATCH_SIZE);
  await db.insert(users).values(batch);
}

// Bad: Single large batch may fail
await db.insert(users).values(veryLargeArray);

Migrations in Production

Always test migrations in staging before applying to production:

# 1. Backup database first
pg_dump $DATABASE_URL > backup.sql

# 2. Test migration in staging
npx drizzle-kit migrate

# 3. Verify application compatibility
npm run test

# 4. Apply to production during maintenance window
npx drizzle-kit migrate

Soft Delete Queries

Remember to always filter deletedAt IS NULL in queries:

// Good: Explicitly filter soft-deleted
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

// Bad: Returns all including deleted
const allUsers = await db.select().from(users);

---

Performance Tips

Connection Pooling

Use connection pooling for production workloads:

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum pool size
});

const db = drizzle(pool);

Prepared Statements

Drizzle automatically uses prepared statements. Avoid dynamic query building when possible:

// Good: Static query (prepared statement)
await db.select().from(users).where(eq(users.id, userId));

// Acceptable: Dynamic with caution
const conditions = [eq(users.active, true)];
if (name) conditions.push(like(users.name, `%${name}%`));
await db.select().from(users).where(and(...conditions));

Select Only Needed Columns

// Good: Select specific columns
const { name, email } = await db
  .select({ name: users.name, email: users.email })
  .from(users)
  .where(eq(users.id, 1));

// Bad: Select all columns
const [user] = await db.select().from(users).where(eq(users.id, 1));

---

Security Considerations

SQL Injection Prevention

Drizzle ORM prevents SQL injection through parameterized queries. Never concatenate user input:

// Safe: Parameterized query
await db.select().from(users).where(eq(users.name, userInput));

// Dangerous: Never do this
await db.execute(`SELECT * FROM users WHERE name = '${userInput}'`);

Environment Variables

Never commit database credentials to version control:

// drizzle.config.ts
export default defineConfig({
  dbCredentials: {
    url: process.env.DATABASE_URL!, // Use env var
  },
});

// NOT: url: 'postgres://user:password@localhost/db'

Row Level Security (PostgreSQL)

For multi-tenant applications, consider PostgreSQL RLS:

-- Enable RLS on table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.current_tenant')::int);

Common Patterns - Complete Reference

Soft Delete

import { isNull } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  deletedAt: timestamp('deleted_at'),
});

// Query non-deleted only
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

// Soft delete
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, id));

// Restore soft-deleted
await db
  .update(users)
  .set({ deletedAt: null })
  .where(eq(users.id, id));

Upsert (Update or Insert)

import { onConflict } from 'drizzle-orm';

// PostgreSQL upsert
await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflict(onConflict(users.email).doUpdateSet({
    name: excluded.name,
  }));

// MySQL upsert
await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onDuplicateKeyUpdate({ set: { name: 'John Updated' } });

Batch Operations

// Batch insert with chunking
async function batchInsert(items: any[]) {
  const chunkSize = 100;
  for (let i = 0; i < items.length; i += chunkSize) {
    const chunk = items.slice(i, i + chunkSize);
    await db.insert(users).values(chunk);
  }
}

// Batch update using upsert
const updates = batch.map(item => ({
  id: item.id,
  name: item.name,
}));
await db.insert(users).values(updates).onConflictDoNothing();

Pagination with Total Count

async function paginate(page: number, pageSize: number) {
  const offset = (page - 1) * pageSize;

  const [data, [{ count }]] = await Promise.all([
    db.select().from(users)
      .limit(pageSize)
      .offset(offset)
      .orderBy(asc(users.id)),
    db.select({ count: count() }).from(users)
  ]);

  return { data, count, page, pageSize };
}

Full-Text Search

// PostgreSQL full-text search
import { sql, tsVector } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  searchText: tsVector('search_text').generatedAlwaysAs(
    sql`to_tsvector('english', coalesce(${posts.title}, '') || ' ' || coalesce(${posts.content}, ''))`
  ),
});

// Search query
const results = await db.select()
  .from(posts)
  .where(sql`${posts.searchText} @@ to_tsquery('english', ${searchQuery})`);

Audit Trail

export const auditLog = pgTable('audit_log', {
  id: serial('id').primaryKey(),
  tableName: text('table_name').notNull(),
  recordId: integer('record_id').notNull(),
  action: text('action').notNull(), // 'insert', 'update', 'delete'
  oldValues: json('old_values'),
  newValues: json('new_values'),
  changedBy: integer('changed_by'),
  changedAt: timestamp('changed_at').defaultNow(),
});

// Usage in update
await db.transaction(async (tx) => {
  const [oldRecord] = await tx.select().from(users).where(eq(users.id, id));

  await tx.update(users).set({ name: 'New Name' }).where(eq(users.id, id));

  await tx.insert(auditLog).values({
    tableName: 'users',
    recordId: id,
    action: 'update',
    oldValues: oldRecord,
    newValues: { name: 'New Name' },
    changedBy: userId,
  });
});

Conditional Updates

import { sql } from 'drizzle-orm';

// Increment counter
await db.update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(eq(posts.id, postId));

// Conditional update (only if value is greater)
await db.update(users)
  .set({ score: sql`GREATEST(${users.score}, ${newScore})` })
  .where(eq(users.id, userId));

Type Inference Examples

// Infer insert type
type NewUser = typeof users.$inferInsert;
// { id?: number; name: string; email: string; createdAt?: Date }

// Infer select type
type User = typeof users.$inferSelect;
// { id: number; name: string; email: string; createdAt: Date }

// Use in functions
async function createUser(data: typeof users.$inferInsert) {
  return db.insert(users).values(data).returning();
}

async function getUser(id: number): Promise<typeof users.$inferSelect> {
  const [user] = await db.select().from(users).where(eq(users.id, id));
  return user;
}

Drizzle ORM Patterns - Examples

Complete working examples for common Drizzle ORM use cases. For detailed patterns, see patterns.md.

Table of Contents

• Complete Schema with Relations
• CRUD Operations
• Transaction with Rollback

---

Example 1: Complete Schema with Relations

A complete example showing how to define tables with foreign keys and set up bidirectional relations.

import { pgTable, serial, text, integer, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Define tables
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').defaultNow(),
});

// Define relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

Usage with Relations

// Get user with their posts
const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, 1),
  with: {
    posts: true,
  },
});

// Get posts with their author
const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: true,
  },
});

---

Example 2: CRUD Operations

Basic Create, Read, Update, Delete operations with Drizzle ORM.

import { eq } from 'drizzle-orm';

// Insert a new user
const [newUser] = await db.insert(users).values({
  name: 'John',
  email: 'john@example.com',
}).returning();

// Select user by email
const [user] = await db.select().from(users).where(eq(users.email, 'john@example.com'));

// Update user name
const [updated] = await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1))
  .returning();

// Delete user
await db.delete(users).where(eq(users.id, 1));

Bulk Operations

// Insert multiple users
const newUsers = await db.insert(users).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]).returning();

// Select multiple users with filter
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.verified, true));

---

Example 3: Transaction with Rollback

A money transfer example demonstrating transaction rollback on insufficient funds.

import { eq, sql } from 'drizzle-orm';

async function transferFunds(fromId: number, toId: number, amount: number) {
  await db.transaction(async (tx) => {
    const [from] = await tx.select().from(accounts).where(eq(accounts.userId, fromId));

    if (from.balance < amount) {
      tx.rollback(); // Rolls back all changes
    }

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.userId, fromId));

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.userId, toId));
  });
}

Transaction with Error Handling

async function safeTransfer(fromId: number, toId: number, amount: number) {
  try {
    const result = await db.transaction(async (tx) => {
      const [fromAccount] = await tx
        .select()
        .from(accounts)
        .where(eq(accounts.userId, fromId));

      if (!fromAccount || fromAccount.balance < amount) {
        tx.rollback();
        return { success: false, error: 'Insufficient funds' };
      }

      await tx.update(accounts)
        .set({ balance: sql`${accounts.balance} - ${amount}` })
        .where(eq(accounts.userId, fromId));

      await tx.update(accounts)
        .set({ balance: sql`${accounts.balance} + ${amount}` })
        .where(eq(accounts.userId, toId));

      return { success: true };
    });

    return result;
  } catch (error) {
    return { success: false, error: 'Transaction failed' };
  }
}

---

Example 4: Complex Query with Joins

Retrieving users with their posts using joins.

import { eq } from 'drizzle-orm';

const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postId: posts.id,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Result structure:
// [
//   { userId: 1, userName: 'John', postId: 1, postTitle: 'Hello' },
//   { userId: 1, userName: 'John', postId: 2, postTitle: 'World' },
//   { userId: 2, userName: 'Jane', postId: null, postTitle: null },
// ]

---

Example 5: Pagination Implementation

Implementing cursor-based pagination for large datasets.

import { gt, asc } from 'drizzle-orm';

async function getUsersPaginated(cursor?: number, limit = 10) {
  const query = db
    .select()
    .from(users)
    .orderBy(asc(users.id))
    .limit(limit + 1); // Get one extra to check if there's a next page

  if (cursor) {
    query.where(gt(users.id, cursor));
  }

  const results = await query;
  const hasNextPage = results.length > limit;
  const items = hasNextPage ? results.slice(0, -1) : results;
  const nextCursor = hasNextPage ? items[items.length - 1].id : null;

  return {
    items,
    nextCursor,
    hasNextPage,
  };
}

// Usage
const firstPage = await getUsersPaginated();
const secondPage = await getUsersPaginated(firstPage.nextCursor);

---

Example 6: Aggregation Query

Calculating user statistics with aggregations.

import { count, avg, sql, gt } from 'drizzle-orm';

const stats = await db
  .select({
    totalUsers: count(users.id),
    averageAge: avg(users.age),
    verifiedUsers: sql<number>`cast(count(case when ${users.verified} then 1 end) as int)`,
  })
  .from(users);

// Group by with having
const ageGroups = await db
  .select({
    age: users.age,
    count: sql<number>`cast(count(${users.id}) as int)`,
  })
  .from(users)
  .groupBy(users.age)
  .having(({ count }) => gt(count, 1));

---

Example 7: Soft Delete Pattern

Implementing soft delete to preserve data integrity.

import { isNull } from 'drizzle-orm';

// Schema with deletedAt
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  deletedAt: timestamp('deleted_at'),
});

// Always query non-deleted users
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

// Soft delete instead of hard delete
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, userId));

// Restore soft-deleted user
await db
  .update(users)
  .set({ deletedAt: null })
  .where(eq(users.id, userId));

---

Example 8: Full-Featured Repository Pattern

A complete repository class using Drizzle ORM.

import { eq, ilike, desc } from 'drizzle-orm';

class UserRepository {
  constructor(private db: typeof db) {}

  async create(data: typeof users.$inferInsert) {
    const [user] = await this.db.insert(users).values(data).returning();
    return user;
  }

  async findById(id: number) {
    const [user] = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id));
    return user;
  }

  async findByEmail(email: string) {
    const [user] = await this.db
      .select()
      .from(users)
      .where(eq(users.email, email));
    return user;
  }

  async search(query: string, limit = 10) {
    return this.db
      .select()
      .from(users)
      .where(ilike(users.name, `%${query}%`))
      .limit(limit);
  }

  async update(id: number, data: Partial<typeof users.$inferInsert>) {
    const [user] = await this.db
      .update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  async delete(id: number) {
    await this.db.delete(users).where(eq(users.id, id));
  }

  async list(options: { page?: number; pageSize?: number } = {}) {
    const { page = 1, pageSize = 10 } = options;
    return this.db
      .select()
      .from(users)
      .orderBy(desc(users.createdAt))
      .limit(pageSize)
      .offset((page - 1) * pageSize);
  }
}

Drizzle Kit Migrations - Complete Reference

Configuration (drizzle.config.ts)

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Configuration for Different Databases

PostgreSQL

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

MySQL

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'mysql',
  dbCredentials: {
    host: process.env.DB_HOST!,
    port: parseInt(process.env.DB_PORT!),
    user: process.env.DB_USER!,
    password: process.env.DB_PASSWORD!,
    database: process.env.DB_NAME!,
  },
});

SQLite

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: './local-db.sqlite',
  },
});

package.json Scripts

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:pull": "drizzle-kit pull",
    "db:studio": "drizzle-kit studio"
  }
}

CLI Commands

# Generate migration files from schema changes
npx drizzle-kit generate

# Apply pending migrations to database
npx drizzle-kit migrate

# Push schema directly to DB (development only - no migration files)
npx drizzle-kit push

# Pull schema from existing database (reverse engineer)
npx drizzle-kit pull

# Open Drizzle Studio (database GUI)
npx drizzle-kit studio

Programmatic Migration

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';

const db = drizzle(process.env.DATABASE_URL);

// Run migrations
await migrate(db, { migrationsFolder: './drizzle' });

Migration File Structure

drizzle/
├── 0001_create_users.sql
├── 0002_create_posts.sql
├── 0003_add_verified_column.sql
└── meta/
    └── 0001.json

Custom Migration SQL

-- 0001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 0002_add_verified_column.sql
ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT FALSE;

Migration Best Practices

1. Always review generated migrations before committing 2. Test migrations in development before running in production 3. Use `generate` + `migrate` for production workflow 4. Use `push` only for rapid prototyping/development 5. Back up production database before running migrations 6. Never modify existing migration files - create new ones 7. Use descriptive migration names for easier tracking

Drizzle ORM Patterns - Detailed Reference

This file contains detailed patterns for Drizzle ORM operations. For basic usage, see the main ../SKILL.md.

Table of Contents

• Schema Definition
• Relations
• CRUD Operations
• Query Operators
• Pagination
• Joins
• Aggregations
• Transactions
• Drizzle Kit Migrations
• Type Inference
• Common Patterns

---

Schema Definition

PostgreSQL Table

import { pgTable, serial, text, integer, boolean, timestamp, pgEnum } from 'drizzle-orm/pg-core';

// Enum definition
export const rolesEnum = pgEnum('roles', ['guest', 'user', 'admin']);

// Table with all column types
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: rolesEnum().default('user'),
  verified: boolean('verified').notNull().default(false),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

MySQL Table

import { mysqlTable, serial, text, int, tinyint, datetime } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  verified: tinyint('verified').notNull().default(0),
  createdAt: datetime('created_at').notNull().defaultNow(),
});

SQLite Table

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

Indexes and Constraints

import { uniqueIndex, index, primaryKey } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}, (table) => [
  uniqueIndex('slug_idx').on(table.slug),
  index('author_idx').on(table.authorId),
  index('created_idx').on(table.createdAt),
]);

Composite Primary Key

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (table) => [
  primaryKey({ columns: [table.userId, table.groupId] }),
]);

---

Relations

One-to-Many (v1 syntax)

import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  authorId: integer('author_id').references(() => users.id),
});

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

One-to-One

export const profiles = pgTable('profiles', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id).unique(),
  bio: text('bio'),
});

export const profilesRelations = relations(profiles, ({ one }) => ({
  user: one(users, {
    fields: [profiles.userId],
    references: [users.id],
  }),
}));

Many-to-Many (v2 syntax)

import { defineRelations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (t) => [primaryKey({ columns: [t.userId, t.groupId] })]);

export const relations = defineRelations({ users, groups, usersToGroups }, (r) => ({
  users: {
    groups: r.many.groups({
      from: r.users.id.through(r.usersToGroups.userId),
      to: r.groups.id.through(r.usersToGroups.groupId),
    }),
  },
  groups: {
    participants: r.many.users(),
  },
}));

Self-Referential Relation

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  invitedBy: integer('invited_by').references((): AnyPgColumn => users.id),
});

export const usersRelations = relations(users, ({ one }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
}));

---

CRUD Operations

Insert

import { eq } from 'drizzle-orm';

// Single insert
await db.insert(users).values({
  name: 'John',
  email: 'john@example.com',
});

// Multiple inserts
await db.insert(users).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
]);

// Returning inserted row
const [newUser] = await db.insert(users).values({
  name: 'John',
  email: 'john@example.com',
}).returning();

Select

// Select all
const allUsers = await db.select().from(users);

// Select specific columns
const result = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// Select with where
const user = await db.select().from(users).where(eq(users.id, 1));

// Select first match
const [user] = await db.select().from(users).where(eq(users.id, 1));

// $count shorthand
const count = await db.$count(users);
const activeCount = await db.$count(users, eq(users.verified, true));

Update

await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1));

// With returning
const [updatedUser] = await db.update(users)
  .set({ verified: true })
  .where(eq(users.email, 'john@example.com'))
  .returning();

Delete

await db.delete(users).where(eq(users.id, 1));

// With returning
const [deletedUser] = await db.delete(users)
  .where(eq(users.email, 'john@example.com'))
  .returning();

---

Query Operators

import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between, exists, notExists } from 'drizzle-orm';

// Comparison
eq(users.id, 1)
ne(users.name, 'John')
gt(users.age, 18)
gte(users.age, 18)
lt(users.age, 65)
lte(users.age, 65)

// String matching
like(users.name, '%John%')      // case-sensitive
ilike(users.name, '%john%')     // case-insensitive

// Null checks
isNull(users.deletedAt)
isNotNull(users.deletedAt)

// Array
inArray(users.id, [1, 2, 3])

// Range
between(users.createdAt, startDate, endDate)

// Combining conditions
and(
  gte(users.age, 18),
  eq(users.verified, true)
)

or(
  eq(users.role, 'admin'),
  eq(users.role, 'moderator')
)

---

Pagination

import { asc, desc } from 'drizzle-orm';

// Basic pagination
const page = 1;
const pageSize = 10;

const users = await db
  .select()
  .from(users)
  .orderBy(asc(users.id))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

// Cursor-based pagination (more efficient)
const lastId = 100;
const users = await db
  .select()
  .from(users)
  .where(gt(users.id, lastId))
  .orderBy(asc(users.id))
  .limit(10);

---

Joins

import { eq } from 'drizzle-orm';

// Left join
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Inner join
const result = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// Multiple joins
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .leftJoin(comments, eq(posts.id, comments.postId));

// Partial select with join
const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Self-join with alias
import { alias } from 'drizzle-orm';
const parent = alias(users, 'parent');
const result = await db
  .select()
  .from(users)
  .leftJoin(parent, eq(parent.id, users.parentId));

---

Aggregations

import { count, sum, avg, min, max, sql, gt } from 'drizzle-orm';

// Count all
const [{ value }] = await db.select({ value: count() }).from(users);

// Count with condition
const [{ value }] = await db
  .select({ value: count(users.id) })
  .from(users)
  .where(gt(users.age, 18));

// Sum, Avg
const [stats] = await db
  .select({
    totalAge: sum(users.age),
    avgAge: avg(users.age),
  })
  .from(users);

// Min, Max
const [extremes] = await db
  .select({
    oldest: min(users.age),
    youngest: max(users.age),
  })
  .from(users);

// Group by with having
const ageGroups = await db
  .select({
    age: users.age,
    count: sql<number>`cast(count(${users.id}) as int)`,
  })
  .from(users)
  .groupBy(users.age)
  .having(({ count }) => gt(count, 1));

---

Transactions

// Basic transaction
await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} + 100` })
    .where(eq(accounts.userId, 2));
});

// Transaction with rollback
await db.transaction(async (tx) => {
  const [account] = await tx.select()
    .from(accounts)
    .where(eq(accounts.userId, 1));

  if (account.balance < 100) {
    tx.rollback(); // Throws exception
  }

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));
});

// Transaction with return value
const newBalance = await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  const [account] = await tx.select()
    .from(accounts)
    .where(eq(accounts.userId, 1));

  return account.balance;
});

// Nested transactions (savepoints)
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John' });

  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ title: 'Hello', authorId: 1 });
  });
});

---

Drizzle Kit Migrations

Configuration (drizzle.config.ts)

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

package.json Scripts

{
  "scripts": {
    "generate": "drizzle-kit generate",
    "migrate": "drizzle-kit migrate",
    "push": "drizzle-kit push",
    "pull": "drizzle-kit pull"
  }
}

CLI Commands

# Generate migration files from schema
npx drizzle-kit generate

# Apply pending migrations
npx drizzle-kit migrate

# Push schema directly to DB (for development)
npx drizzle-kit push

# Pull schema from existing database
npx drizzle-kit pull

Programmatic Migration

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';

const db = drizzle(process.env.DATABASE_URL);
await migrate(db, { migrationsFolder: './drizzle' });

---

Type Inference

// Infer insert type
type NewUser = typeof users.$inferInsert;
// { id: number; name: string; email: string; ... }

// Infer select type
type User = typeof users.$inferSelect;
// { id: number; name: string; email: string; ... }

// Use in functions
async function createUser(data: typeof users.$inferInsert) {
  return db.insert(users).values(data).returning();
}

async function getUser(id: number): Promise<typeof users.$inferSelect> {
  const [user] = await db.select().from(users).where(eq(users.id, id));
  return user;
}

---

Common Patterns

Soft Delete

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  deletedAt: timestamp('deleted_at'),
});

// Query non-deleted only
const activeUsers = await db
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

// Soft delete
await db
  .update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, id));

Upsert

import { onConflict } from 'drizzle-orm';

await db
  .insert(users)
  .values({ id: 1, name: 'John', email: 'john@example.com' })
  .onConflict(onConflict(users.email).doUpdateSet({
    name: excluded.name,
  }));

Batch Operations

// Batch insert
await db.insert(users).values(batch).returning();

// Batch update
const updates = batch.map(item => ({
  id: item.id,
  name: item.name,
}));
await db.insert(users).values(updates).onConflictDoNothing();

Queries, Joins, and Aggregations - Complete Reference

CRUD Operations

Insert

// Single insert
await db.insert(users).values({
  name: 'John',
  email: 'john@example.com',
});

// Multiple inserts
await db.insert(users).values([
  { name: 'John', email: 'john@example.com' },
  { name: 'Jane', email: 'jane@example.com' },
]);

// Returning inserted row
const [newUser] = await db.insert(users).values({
  name: 'John',
  email: 'john@example.com',
}).returning();

Select

// Select all
const allUsers = await db.select().from(users);

// Select specific columns
const result = await db.select({
  id: users.id,
  name: users.name,
}).from(users);

// Select with where
const user = await db.select().from(users).where(eq(users.id, 1));

// Select first match
const [user] = await db.select().from(users).where(eq(users.id, 1));

// $count shorthand
const count = await db.$count(users);
const activeCount = await db.$count(users, eq(users.verified, true));

Update

await db.update(users)
  .set({ name: 'John Updated' })
  .where(eq(users.id, 1));

// With returning
const [updatedUser] = await db.update(users)
  .set({ verified: true })
  .where(eq(users.email, 'john@example.com'))
  .returning();

Delete

await db.delete(users).where(eq(users.id, 1));

// With returning
const [deletedUser] = await db.delete(users)
  .where(eq(users.email, 'john@example.com'))
  .returning();

Query Operators

import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between, exists, notExists } from 'drizzle-orm';

// Comparison
eq(users.id, 1)           // id = 1
ne(users.name, 'John')    // name != 'John'
gt(users.age, 18)         // age > 18
gte(users.age, 18)        // age >= 18
lt(users.age, 65)         // age < 65
lte(users.age, 65)        // age <= 65

// String matching
like(users.name, '%John%')   // case-sensitive
ilike(users.name, '%john%')  // case-insensitive

// Null checks
isNull(users.deletedAt)       // deleted_at IS NULL
isNotNull(users.deletedAt)    // deleted_at IS NOT NULL

// Array
inArray(users.id, [1, 2, 3])  // id IN (1, 2, 3)

// Range
between(users.createdAt, startDate, endDate)  // created_at BETWEEN start AND end

// Combining conditions
and(
  gte(users.age, 18),
  eq(users.verified, true)
)

or(
  eq(users.role, 'admin'),
  eq(users.role, 'moderator')
)

Pagination

import { asc, desc } from 'drizzle-orm';

// Basic pagination (offset-based)
const page = 1;
const pageSize = 10;

const users = await db
  .select()
  .from(users)
  .orderBy(asc(users.id))
  .limit(pageSize)
  .offset((page - 1) * pageSize);

// Cursor-based pagination (more efficient for large datasets)
const lastId = 100;
const users = await db
  .select()
  .from(users)
  .where(gt(users.id, lastId))
  .orderBy(asc(users.id))
  .limit(10);

Joins

import { eq, alias } from 'drizzle-orm';

// Left join
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Inner join
const result = await db
  .select()
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// Multiple joins
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .leftJoin(comments, eq(posts.id, comments.postId));

// Partial select with join
const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Self-join with alias
const parent = alias(users, 'parent');
const result = await db
  .select()
  .from(users)
  .leftJoin(parent, eq(parent.id, users.parentId));

Aggregations

import { count, sum, avg, min, max, sql, gt } from 'drizzle-orm';

// Count all
const [{ value }] = await db.select({ value: count() }).from(users);

// Count with condition
const [{ value }] = await db
  .select({ value: count(users.id) })
  .from(users)
  .where(gt(users.age, 18));

// Sum, Avg
const [stats] = await db
  .select({
    totalAge: sum(users.age),
    avgAge: avg(users.age),
  })
  .from(users);

// Min, Max
const [extremes] = await db
  .select({
    oldest: min(users.age),
    youngest: max(users.age),
  })
  .from(users);

// Group by with having
const ageGroups = await db
  .select({
    age: users.age,
    count: sql<number>`cast(count(${users.id}) as int)`,
  })
  .from(users)
  .groupBy(users.age)
  .having(({ count }) => gt(count, 1));

Relations - Complete Reference

One-to-Many (v1 syntax)

import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  authorId: integer('author_id').references(() => users.id),
});

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

One-to-One

export const profiles = pgTable('profiles', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id).unique(),
  bio: text('bio'),
});

export const profilesRelations = relations(profiles, ({ one }) => ({
  user: one(users, {
    fields: [profiles.userId],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ one }) => ({
  profile: one(profiles),
}));

Many-to-Many (v2 syntax with defineRelations)

import { defineRelations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (t) => [primaryKey({ columns: [t.userId, t.groupId] })]);

export const relations = defineRelations({ users, groups, usersToGroups }, (r) => ({
  users: {
    groups: r.many.groups({
      from: r.users.id.through(r.usersToGroups.userId),
      to: r.groups.id.through(r.usersToGroups.groupId),
    }),
  },
  groups: {
    participants: r.many.users(),
  },
}));

Many-to-Many (v1 syntax)

import { relations } from 'drizzle-orm';

export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
}));

Self-Referential Relation

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  invitedBy: integer('invited_by').references((): AnyPgColumn => users.id),
});

export const usersRelations = relations(users, ({ one, many }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
  invitedUsers: many(users),
}));

Querying with Relations

// Query with nested relations
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// Query with specific fields
const usersWithPostCount = await db.query.users.findMany({
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
      },
    },
  },
});

// Nested relations
const usersWithPostsAndComments = await db.query.users.findMany({
  with: {
    posts: {
      with: {
        comments: true,
      },
    },
  },
});

Schema Definition - Complete Reference

PostgreSQL Table

import { pgTable, serial, text, integer, boolean, timestamp, pgEnum } from 'drizzle-orm/pg-core';

// Enum definition
export const rolesEnum = pgEnum('roles', ['guest', 'user', 'admin']);

// Table with all column types
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: rolesEnum().default('user'),
  verified: boolean('verified').notNull().default(false),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

MySQL Table

import { mysqlTable, serial, text, int, tinyint, datetime } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  verified: tinyint('verified').notNull().default(0),
  createdAt: datetime('created_at').notNull().defaultNow(),
});

SQLite Table

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
});

Indexes and Constraints

import { uniqueIndex, index, primaryKey } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  authorId: integer('author_id').references(() => users.id),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}, (table) => [
  uniqueIndex('slug_idx').on(table.slug),
  index('author_idx').on(table.authorId),
  index('created_idx').on(table.createdAt),
]);

Composite Primary Key

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (table) => [
  primaryKey({ columns: [table.userId, table.groupId] }),
]);

Column Types Reference

PostgreSQL

• serial, bigserial - Auto-incrementing integers
• text, varchar(n) - Text columns
• integer, bigint, smallint - Integer types
• boolean - True/false
• timestamp, date, time - Date/time types
• numeric(p, s) - Precision numbers
• json, jsonb - JSON data
• uuid - UUID columns
• pgEnum - Custom enums

MySQL

• serial, bigserial - Auto-increment
• text, varchar(n) - Text
• int, bigint, tinyint - Integers
• tinyint(1) - Boolean
• datetime, date, time - Date/time
• decimal(p, s) - Precision numbers
• json - JSON data

SQLite

• integer - Auto-increment primary key
• text - Text
• integer - All integers
• No native boolean (use integer 0/1)
• No native date/time (store as text or integer)

Transactions - Complete Reference

Basic Transaction

await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} + 100` })
    .where(eq(accounts.userId, 2));
});

Transaction with Rollback

await db.transaction(async (tx) => {
  const [account] = await tx.select()
    .from(accounts)
    .where(eq(accounts.userId, 1));

  if (account.balance < 100) {
    tx.rollback(); // Throws exception and rolls back all changes
  }

  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));
});

Transaction with Return Value

const newBalance = await db.transaction(async (tx) => {
  await tx.update(accounts)
    .set({ balance: sql`${accounts.balance} - 100` })
    .where(eq(accounts.userId, 1));

  const [account] = await tx.select()
    .from(accounts)
    .where(eq(accounts.userId, 1));

  return account.balance;
});

Nested Transactions (Savepoints)

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'John' });

  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ title: 'Hello', authorId: 1 });
  });
});

Transfer Funds Example

async function transferFunds(fromId: number, toId: number, amount: number) {
  await db.transaction(async (tx) => {
    const [from] = await tx.select().from(accounts).where(eq(accounts.userId, fromId));

    if (from.balance < amount) {
      tx.rollback(); // Rolls back all changes
    }

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.userId, fromId));

    await tx.update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.userId, toId));
  });
}

Transaction Error Handling

try {
  await db.transaction(async (tx) => {
    // Transaction operations
    await tx.insert(users).values({ name: 'John' });
    // If any error occurs, automatic rollback
  });
} catch (error) {
  console.error('Transaction failed:', error);
}

Transaction Isolation Levels

await db.transaction(async (tx) => {
  // Operations
}, {
  isolationLevel: 'serializable', // or 'read committed', 'repeatable read'
});