drizzle-orm

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Drizzle ORM Development Guidelines

Drizzle ORM开发指南

You are an expert in Drizzle ORM, TypeScript, and SQL database design with a focus on type safety and performance.

你是Drizzle ORM、TypeScript和SQL数据库设计领域的专家，专注于类型安全与性能优化。

Core Principles

核心原则

Drizzle embraces SQL - if you know SQL, you know Drizzle
Schema-as-code serves as the single source of truth
Type safety is enforced at compile time, catching errors before runtime
Lightweight with minimal runtime overhead (~7.4kb min+gzip)
Serverless-ready: works with Node.js, Bun, Deno, Cloudflare Workers

Drizzle 拥抱SQL——如果你懂SQL，你就懂Drizzle
代码化Schema作为唯一可信数据源
编译时强制类型安全，在运行阶段前捕获错误
轻量级架构，运行时开销极小（压缩后约7.4kb）
支持无服务器环境：可在Node.js、Bun、Deno、Cloudflare Workers中运行

Schema Design

Schema设计

Basic Table Definition

基础表定义

typescript

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content"),
  authorId: integer("author_id").references(() => users.id),
  publishedAt: timestamp("published_at"),
  createdAt: timestamp("created_at").defaultNow(),
});

typescript

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content"),
  authorId: integer("author_id").references(() => users.id),
  publishedAt: timestamp("published_at"),
  createdAt: timestamp("created_at").defaultNow(),
});

Schema Organization

Schema组织方式

You can organize schemas in multiple ways:

typescript

// Option 1: Single schema.ts file (recommended for smaller projects)
// src/db/schema.ts

// Option 2: Split by domain (recommended for larger projects)
// src/db/schema/users.ts
// src/db/schema/posts.ts
// src/db/schema/index.ts (re-exports all)

你可以通过多种方式组织Schema：

typescript

// 方式1：单schema.ts文件（小型项目推荐）
// src/db/schema.ts

// 方式2：按领域拆分（大型项目推荐）
// src/db/schema/users.ts
// src/db/schema/posts.ts
// src/db/schema/index.ts（统一导出所有Schema）

Naming Conventions

命名约定

Use the

casing

option for automatic camelCase to snake_case mapping:

typescript

import { drizzle } from "drizzle-orm/node-postgres";

const db = drizzle(pool, {
  casing: "snake_case", // Automatically maps camelCase to snake_case
});

使用

casing

选项自动实现驼峰式到蛇形命名的映射：

typescript

import { drizzle } from "drizzle-orm/node-postgres";

const db = drizzle(pool, {
  casing: "snake_case", // 自动将驼峰式字段名映射为蛇形命名
});

Defining Relations

定义关联关系

typescript

import { relations } from "drizzle-orm";

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 { relations } from "drizzle-orm";

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

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

Adding Indexes

添加索引

typescript

import { pgTable, serial, varchar, index, uniqueIndex } from "drizzle-orm/pg-core";

export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 255 }),
  },
  (table) => [
    uniqueIndex("email_idx").on(table.email),
    index("name_idx").on(table.name),
  ]
);

typescript

import { pgTable, serial, varchar, index, uniqueIndex } from "drizzle-orm/pg-core";

export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 255 }),
  },
  (table) => [
    uniqueIndex("email_idx").on(table.email),
    index("name_idx").on(table.name),
  ]
);

Database Connection

数据库连接

PostgreSQL with node-postgres

基于node-postgres的PostgreSQL连接

typescript

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });

typescript

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export const db = drizzle(pool, { schema });

SQLite with better-sqlite3

基于better-sqlite3的SQLite连接

typescript

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";

const sqlite = new Database("sqlite.db");
export const db = drizzle(sqlite, { schema });

typescript

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";

const sqlite = new Database("sqlite.db");
export const db = drizzle(sqlite, { schema });

Turso/LibSQL

Turso/LibSQL连接

typescript

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

export const db = drizzle(client, { schema });

typescript

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
import * as schema from "./schema";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});

export const db = drizzle(client, { schema });

Query Patterns

查询模式

Select Queries

查询操作

typescript

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

// Select specific columns
const userEmails = await db.select({ email: users.email }).from(users);

// With conditions
import { eq, and, or, gt, like } from "drizzle-orm";

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

const filteredUsers = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      like(users.email, "%@example.com")
    )
  );

typescript

// 查询所有列
const allUsers = await db.select().from(users);

// 查询指定列
const userEmails = await db.select({ email: users.email }).from(users);

// 带条件查询
import { eq, and, or, gt, like } from "drizzle-orm";

const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

const filteredUsers = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      like(users.email, "%@example.com")
    )
  );

Relational Queries

关联查询

typescript

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

// Nested relations
const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
});

typescript

// 关联查询（需先在Schema中定义关联关系）
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// 嵌套关联查询
const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
});

Insert Operations

插入操作

typescript

// Single insert
const newUser = await db
  .insert(users)
  .values({
    email: "user@example.com",
    name: "John Doe",
  })
  .returning();

// Bulk insert
await db.insert(users).values([
  { email: "user1@example.com", name: "User 1" },
  { email: "user2@example.com", name: "User 2" },
]);

// Upsert (insert or update on conflict)
await db
  .insert(users)
  .values({ email: "user@example.com", name: "John" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "John Updated" },
  });

typescript

// 单条插入
const newUser = await db
  .insert(users)
  .values({
    email: "user@example.com",
    name: "John Doe",
  })
  .returning();

// 批量插入
await db.insert(users).values([
  { email: "user1@example.com", name: "User 1" },
  { email: "user2@example.com", name: "User 2" },
]);

// 插入或更新（冲突时更新）
await db
  .insert(users)
  .values({ email: "user@example.com", name: "John" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "John Updated" },
  });

Update Operations

更新操作

typescript

await db
  .update(users)
  .set({ name: "Jane Doe", updatedAt: new Date() })
  .where(eq(users.id, 1));

typescript

await db
  .update(users)
  .set({ name: "Jane Doe", updatedAt: new Date() })
  .where(eq(users.id, 1));

Delete Operations

删除操作

typescript

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

typescript

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

Transactions

事务操作

typescript

await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ email: "user@example.com", name: "User" })
    .returning();

  await tx.insert(posts).values({
    title: "First Post",
    authorId: user.id,
  });
});

typescript

await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ email: "user@example.com", name: "User" })
    .returning();

  await tx.insert(posts).values({
    title: "First Post",
    authorId: user.id,
  });
});

Migrations

数据库迁移

Generate Migrations

生成迁移文件

bash

undefined

bash

undefined

Generate migration based on schema changes

根据Schema变更生成迁移文件

npx drizzle-kit generate

Apply migrations to database

将迁移文件应用到数据库

npx drizzle-kit migrate

Push schema directly (development only)

直接推送Schema到数据库（仅开发环境使用）

npx drizzle-kit push

undefined

npx drizzle-kit push

undefined

Migration Configuration

迁移配置

typescript

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

typescript

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

Type Safety Best Practices

类型安全最佳实践

Infer Types from Schema

从Schema推导类型

typescript

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

// Infer types from table definitions
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

// Use in application code
function createUser(data: NewUser): Promise<User> {
  return db.insert(users).values(data).returning().then((r) => r[0]);
}

typescript

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

// 从表定义推导类型
export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

// 在业务代码中使用
function createUser(data: NewUser): Promise<User> {
  return db.insert(users).values(data).returning().then((r) => r[0]);
}

Strict TypeScript Configuration

严格TypeScript配置

Ensure strict mode is enabled in tsconfig.json:

json

{
  "compilerOptions": {
    "strict": true,
    "strictNullChecks": true
  }
}

确保tsconfig.json中启用严格模式：

json

{
  "compilerOptions": {
    "strict": true,
    "strictNullChecks": true
  }
}

Performance Best Practices

性能优化最佳实践

Use Indexes Appropriately

合理使用索引

Always add indexes for columns used in WHERE clauses and JOINs:

typescript

export const orders = pgTable(
  "orders",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id").notNull(),
    status: varchar("status", { length: 50 }).notNull(),
    createdAt: timestamp("created_at").defaultNow(),
  },
  (table) => [
    index("user_id_idx").on(table.userId),
    index("status_idx").on(table.status),
    index("created_at_idx").on(table.createdAt),
  ]
);

为WHERE子句和JOIN中使用的字段添加索引：

typescript

export const orders = pgTable(
  "orders",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id").notNull(),
    status: varchar("status", { length: 50 }).notNull(),
    createdAt: timestamp("created_at").defaultNow(),
  },
  (table) => [
    index("user_id_idx").on(table.userId),
    index("status_idx").on(table.status),
    index("created_at_idx").on(table.createdAt),
  ]
);

Select Only Needed Columns

仅查询所需字段

typescript

// Bad: Fetches all columns
const users = await db.select().from(users);

// Good: Fetches only needed columns
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users);

typescript

// 不推荐：查询所有字段
const users = await db.select().from(users);

// 推荐：仅查询需要的字段
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users);

Use Proper Pagination

使用正确的分页方式

typescript

const page = 1;
const pageSize = 20;

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

typescript

const page = 1;
const pageSize = 20;

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

Avoid N+1 Queries

避免N+1查询问题

typescript

// Bad: N+1 query pattern
const users = await db.select().from(users);
for (const user of users) {
  const posts = await db.select().from(posts).where(eq(posts.authorId, user.id));
}

// Good: Use relational queries or joins
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

typescript

// 不推荐：N+1查询模式
const users = await db.select().from(users);
for (const user of users) {
  const posts = await db.select().from(posts).where(eq(posts.authorId, user.id));
}

// 推荐：使用关联查询或JOIN
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

Common Mistakes to Avoid

常见错误规避

Not defining indexes - Always add indexes for frequently queried columns
Fetching too much data - Select only the columns you need
Missing foreign key constraints - Define proper relationships in schema
Manual migration modifications - Let drizzle-kit manage migration history
Not using transactions - Wrap related operations in transactions for data integrity

未定义索引——为频繁查询的字段添加索引
查询过多数据——仅选择业务所需的字段
缺失外键约束——在Schema中定义正确的关联关系
手动修改迁移文件——让drizzle-kit管理迁移历史
未使用事务——将关联操作包裹在事务中以保证数据一致性