drizzle

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Drizzle ORM Schema Style Guide

Drizzle ORM 架构风格指南

Configuration

配置

Config:
```
drizzle.config.ts
```
Schemas:
```
src/database/schemas/
```
Migrations:
```
src/database/migrations/
```
Dialect:
```
postgresql
```
with
```
strict: true
```

配置文件：
```
drizzle.config.ts
```
架构文件：
```
src/database/schemas/
```
迁移文件：
```
src/database/migrations/
```
数据库方言：启用
```
strict: true
```
的
```
postgresql
```

Helper Functions

辅助函数

Location:

src/database/schemas/_helpers.ts

```
timestamptz(name)
```
: Timestamp with timezone
```
createdAt()
```
,
```
updatedAt()
```
,
```
accessedAt()
```
: Standard timestamp columns
```
timestamps
```
: Object with all three for easy spread

位置：

src/database/schemas/_helpers.ts

```
timestamptz(name)
```
：带时区的时间戳
```
createdAt()
```
、
```
updatedAt()
```
、
```
accessedAt()
```
：标准时间戳列
```
timestamps
```
：包含上述三个函数的对象，支持展开语法

Naming Conventions

命名规范

Tables: Plural snake_case (
```
users
```
,
```
session_groups
```
)
Columns: snake_case (
```
user_id
```
,
```
created_at
```
)

表名：复数蛇形命名法（
```
users
```
、
```
session_groups
```
）
列名：蛇形命名法（
```
user_id
```
、
```
created_at
```
）

Column Definitions

列定义

Primary Keys

主键

typescript

id: text('id')
  .primaryKey()
  .$defaultFn(() => idGenerator('agents'))
  .notNull(),

ID prefixes make entity types distinguishable. For internal tables, use

uuid

typescript

id: text('id')
  .primaryKey()
  .$defaultFn(() => idGenerator('agents'))
  .notNull(),

ID前缀可区分实体类型。对于内部表，使用

uuid

。

Foreign Keys

外键

typescript

userId: text('user_id')
  .references(() => users.id, { onDelete: 'cascade' })
  .notNull(),

typescript

userId: text('user_id')
  .references(() => users.id, { onDelete: 'cascade' })
  .notNull(),

Timestamps

时间戳

typescript

...timestamps,  // Spread from _helpers.ts

typescript

...timestamps,  // Spread from _helpers.ts

Indexes

索引

typescript

// Return array (object style deprecated)
(t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],

typescript

// Return array (object style deprecated)
(t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],

Type Inference

类型推断

typescript

export const insertAgentSchema = createInsertSchema(agents);
export type NewAgent = typeof agents.$inferInsert;
export type AgentItem = typeof agents.$inferSelect;

typescript

export const insertAgentSchema = createInsertSchema(agents);
export type NewAgent = typeof agents.$inferInsert;
export type AgentItem = typeof agents.$inferSelect;

Example Pattern

示例模式

typescript

export const agents = pgTable(
  'agents',
  {
    id: text('id').primaryKey().$defaultFn(() => idGenerator('agents')).notNull(),
    slug: varchar('slug', { length: 100 }).$defaultFn(() => randomSlug(4)).unique(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    clientId: text('client_id'),
    chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(),
    ...timestamps,
  },
  (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],
);

typescript

export const agents = pgTable(
  'agents',
  {
    id: text('id').primaryKey().$defaultFn(() => idGenerator('agents')).notNull(),
    slug: varchar('slug', { length: 100 }).$defaultFn(() => randomSlug(4)).unique(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    clientId: text('client_id'),
    chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(),
    ...timestamps,
  },
  (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],
);

Common Patterns

常见模式

Junction Tables (Many-to-Many)

关联表（多对多）

typescript

export const agentsKnowledgeBases = pgTable(
  'agents_knowledge_bases',
  {
    agentId: text('agent_id').references(() => agents.id, { onDelete: 'cascade' }).notNull(),
    knowledgeBaseId: text('knowledge_base_id').references(() => knowledgeBases.id, { onDelete: 'cascade' }).notNull(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    enabled: boolean('enabled').default(true),
    ...timestamps,
  },
  (t) => [primaryKey({ columns: [t.agentId, t.knowledgeBaseId] })],
);

typescript

export const agentsKnowledgeBases = pgTable(
  'agents_knowledge_bases',
  {
    agentId: text('agent_id').references(() => agents.id, { onDelete: 'cascade' }).notNull(),
    knowledgeBaseId: text('knowledge_base_id').references(() => knowledgeBases.id, { onDelete: 'cascade' }).notNull(),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
    enabled: boolean('enabled').default(true),
    ...timestamps,
  },
  (t) => [primaryKey({ columns: [t.agentId, t.knowledgeBaseId] })],
);

Database Migrations

数据库迁移

See

references/db-migrations.md

for detailed migration guide.

bash

undefined

详细迁移指南请参考

references/db-migrations.md

。

bash

undefined

Generate migrations

生成迁移文件

bun run db:generate

After modifying SQL (e.g., adding IF NOT EXISTS)

修改SQL后（例如添加IF NOT EXISTS）

bun run db:generate:client

undefined

bun run db:generate:client

undefined

Migration Best Practices

迁移最佳实践

sql

-- ✅ Idempotent operations
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
DROP TABLE IF EXISTS "old_table";
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");

-- ❌ Non-idempotent
ALTER TABLE "users" ADD COLUMN "avatar" text;

Rename migration files meaningfully:

0046_meaningless.sql

→

0046_user_add_avatar.sql

sql

-- ✅ 幂等操作
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatar" text;
DROP TABLE IF EXISTS "old_table";
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");

-- ❌ 非幂等操作
ALTER TABLE "users" ADD COLUMN "avatar" text;

为迁移文件赋予有意义的名称：

0046_meaningless.sql

→

0046_user_add_avatar.sql