Back to Details

drizzle-orm-patterns

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Drizzle ORM Patterns

Drizzle ORM 实践方案

Overview

概述

Expert guide for building type-safe database applications with Drizzle ORM. Covers schema definition, relations, queries, transactions, and migrations for all supported databases.
这是使用Drizzle ORM构建类型安全数据库应用的权威指南，涵盖所有支持数据库的schema定义、关联关系、查询、事务和迁移。

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
  • 定义包含表、列和约束的数据库schema
  • 创建表之间的关联关系（一对一、一对多、多对多）
  • 编写类型安全的CRUD查询
  • 实现复杂的连接和聚合操作
  • 管理带回滚功能的数据库事务
  • 使用Drizzle Kit配置迁移
  • 操作PostgreSQL、MySQL、SQLite、MSSQL或CockroachDB数据库

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()
    for complex relationships
  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
  1. 选择数据库方言 - 选择PostgreSQL、MySQL、SQLite、MSSQL或CockroachDB
  2. 定义schema - 使用对应的表函数（pgTable、mysqlTable等）
  3. 配置关联关系 - 使用
    relations()
    或
    defineRelations()
    定义复杂关联
  4. 初始化数据库客户端 - 使用正确的凭据创建Drizzle客户端
  5. 编写查询 - 使用查询构建器实现类型安全的CRUD操作
  6. 处理事务 - 必要时将多步操作包裹在事务中
  7. 配置迁移 - 使用Drizzle Kit管理schema

Examples

示例

Example 1: Create a Complete Schema with Relations

示例1：创建包含关联关系的完整Schema

typescript
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] }),
}));
typescript
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] }),
}));

Example 2: CRUD Operations

示例2：CRUD操作

typescript
import { eq } from 'drizzle-orm';

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

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

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

// Delete
await db.delete(users).where(eq(users.id, 1));
typescript
import { eq } from 'drizzle-orm';

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

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

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

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

Example 3: Transaction with Rollback

示例3：带回滚的事务

typescript
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));
  });
}
typescript
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));
  });
}

Schema Definition

Schema定义

PostgreSQL Table

PostgreSQL表

typescript
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(),
});
typescript
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

MySQL表

typescript
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(),
});
typescript
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

SQLite表

typescript
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(),
});
typescript
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

索引和约束

typescript
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),
]);
typescript
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

复合主键

typescript
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] }),
]);
typescript
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)

一对多（v1语法）

typescript
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],
  }),
}));
typescript
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

一对一

typescript
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],
  }),
}));
typescript
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)

多对多（v2语法）

typescript
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(),
  },
}));
typescript
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

自引用关联

typescript
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],
  }),
}));
typescript
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

CRUD操作

Insert

插入

typescript
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();
typescript
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

查询

typescript
// 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));
typescript
// 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

更新

typescript
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();
typescript
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

删除

typescript
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();
typescript
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

查询操作符

typescript
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')
)
typescript
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

分页

typescript
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);
typescript
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

连接查询

typescript
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));
typescript
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

聚合查询

typescript
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));
typescript
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

事务

typescript
// 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 });
  });
});
typescript
// 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

Drizzle Kit迁移

Configuration (drizzle.config.ts)

配置文件（drizzle.config.ts）

typescript
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});
typescript
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

package.json脚本

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

CLI Commands

CLI命令

bash
undefined
bash
undefined

Generate migration files from schema

Generate migration files from schema

npx drizzle-kit generate
npx drizzle-kit generate

Apply pending migrations

Apply pending migrations

npx drizzle-kit migrate
npx drizzle-kit migrate

Push schema directly to DB (for development)

Push schema directly to DB (for development)

npx drizzle-kit push
npx drizzle-kit push

Pull schema from existing database

Pull schema from existing database

npx drizzle-kit pull
undefined
npx drizzle-kit pull
undefined

Programmatic Migration

程序化迁移

typescript
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' });
typescript
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

类型推断

typescript
// 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;
}
typescript
// 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

软删除

typescript
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));
typescript
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

插入或更新（Upsert）

typescript
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,
  }));
typescript
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

批量操作

typescript
// 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();
typescript
// 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();

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
  1. 类型安全：始终使用TypeScript并利用
    $inferInsert
    / 
    $inferSelect
  2. 关联关系：使用relations() API定义关联以支持嵌套查询
  3. 事务：对必须同时成功的多步操作使用事务
  4. 迁移：生产环境使用
    generate
    + 
    migrate
    ，开发环境使用
    push
  5. 索引：在频繁查询的列和外键上添加索引
  6. 软删除：可能的话使用
    deletedAt
    时间戳替代硬删除
  7. 分页：对大型数据集使用基于游标的分页
  8. 查询优化：使用
    .limit()
    和
    .where()
    仅获取所需数据

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
  • Type Inference: Use 
    InferSelectModel
    and 
    InferInsertModel
    from 
    drizzle-orm
    for newer type-safe patterns
  • 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
  • Soft Delete Queries: Remember to always filter 
    deletedAt IS NULL
    in queries
  • 外键约束：始终使用箭头函数
    () => table.column
    定义引用，避免循环依赖问题
  • 事务回滚：调用
    tx.rollback()
    会抛出异常 - 必要时使用try/catch
  • 返回子句：并非所有数据库都支持
    .returning()
    - 请检查方言兼容性
  • 类型推断：对于更新的类型安全模式，使用
    drizzle-orm
    中的
    InferSelectModel
    和
    InferInsertModel
  • 批量操作：大型批量插入可能会触发数据库限制 - 拆分为较小的批次
  • 生产环境迁移：应用到生产环境前务必在预发布环境测试迁移
  • 软删除查询：查询时请务必过滤
    deletedAt IS NULL