drizzle-schema

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Schemas with Drizzle ORM

使用Drizzle ORM构建PostgreSQL架构

1. Core Principles

1. 核心原则

All database schemas for this project are monolithic and centrally located within src/shared/infrastructure/drizzle-postgres/schema.ts
. No exceptions.

Monolithic Schema: Do not create distributed schema files across Bounded Contexts. All
```
pgTable
```
,
```
pgEnum
```
, and
```
relations
```
are defined in the central file to prevent circular dependencies in Drizzle queries.
Naming Conventions: DB tables must be
```
snake_case
```
(e.g.,
```
audit_logs
```
). TypeScript variables must be
```
camelCase
```
(e.g.,
```
auditLogs
```
).
Column Names: Define explicit
```
snake_case
```
names when initializing columns
```
uuid("folder_id")
```
and map them to
```
camelCase
```
for the application layer.
Timestamp Standard: All tables should at minimum contain
```
createdAt
```
(with
```
.defaultNow().notNull()
```
) and, when mutable,
```
updatedAt
```
(with
```
.$onUpdate(...)
```
).
Enums: Use explicit
```
pgEnum("enum_name", [...])
```
rather than Native PostgreSQL types for application enums.

本项目的所有数据库Schema均采用集中式设计，统一存放于**

src/shared/infrastructure/drizzle-postgres/schema.ts

**文件中，无例外。

集中式Schema：不要在限界上下文之间分散创建Schema文件。所有
```
pgTable
```
、
```
pgEnum
```
和
```
relations
```
都需定义在中央文件中，以避免Drizzle查询出现循环依赖。
命名规范：数据库表名必须使用
```
snake_case
```
格式（例如：
```
audit_logs
```
）。TypeScript变量必须使用
```
camelCase
```
格式（例如：
```
auditLogs
```
）。
列名规则：初始化列时需显式定义
```
snake_case
```
名称（如
```
uuid("folder_id")
```
），并将其映射为应用层的
```
camelCase
```
格式。
时间戳标准：所有表至少应包含
```
createdAt
```
字段（配置
```
.defaultNow().notNull()
```
），若表数据可修改，还需添加
```
updatedAt
```
字段（配置
```
.$onUpdate(...)
```
）。
枚举类型：应用层枚举需使用显式的
```
pgEnum("enum_name", [...])
```
，而非原生PostgreSQL类型。

2. Table Definition Format

2. 表定义格式

Always follow this structure when defining a new table, including explicit foreign key cascades.

typescript

import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  uniqueIndex,
} from "drizzle-orm/pg-core";

// 1. Dependent Tables (Foreign Keys)
export const workspaces = pgTable("workspaces", {
  id: uuid().primaryKey(),
  name: varchar({ length: 40 }).notNull(),
  ownerId: uuid("owner_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 2. Main Table & References
export const folders = pgTable(
  "folders",
  {
    id: uuid().primaryKey(),
    // Explicit snake_case -> camelCase mapping, with onDelete behavior
    workspaceId: uuid("workspace_id")
      .references(() => workspaces.id, { onDelete: "cascade" })
      .notNull(),
    name: varchar({ length: 30 }).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  // 3. Composite unique indexes
  (table) => [uniqueIndex().on(table.workspaceId, table.name)],
);

定义新表时需严格遵循以下结构，包括显式的外键级联规则。

typescript

import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  uniqueIndex,
} from "drizzle-orm/pg-core";

// 1. 依赖表（外键）
export const workspaces = pgTable("workspaces", {
  id: uuid().primaryKey(),
  name: varchar({ length: 40 }).notNull(),
  ownerId: uuid("owner_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 2. 主表与关联关系
export const folders = pgTable(
  "folders",
  {
    id: uuid().primaryKey(),
    // 显式的snake_case -> camelCase映射，包含onDelete行为
    workspaceId: uuid("workspace_id")
      .references(() => workspaces.id, { onDelete: "cascade" })
      .notNull(),
    name: varchar({ length: 30 }).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  // 3. 复合唯一索引
  (table) => [uniqueIndex().on(table.workspaceId, table.name)],
);

3. Explicit Constraint Names (Crucial)

3. 显式约束名称（至关重要）

To map infrastructure errors (like

UniqueConstraintViolation

) to domain errors cleanly in our Repositories, Drizzle requires explicit constraint name tracking. Always export constraint constants at the bottom of the file using Drizzle's

getTableName

utility.

typescript

import { getTableName } from "drizzle-orm";

// ─── Constraint name constants ───
export const FK_FOLDERS_WORKSPACE =
  `${getTableName(folders)}_${folders.workspaceId.name}_${getTableName(workspaces)}_${workspaces.id.name}_fk` as const;

export const UNIQUE_FOLDERS_WORKSPACE_NAME =
  `${getTableName(folders)}_${folders.workspaceId.name}_${folders.name.name}_index` as const;

为了在仓储层将基础设施错误（如

UniqueConstraintViolation

）清晰映射为领域错误，Drizzle需要显式跟踪约束名称。务必使用Drizzle的

getTableName

工具在文件底部导出约束常量。

typescript

import { getTableName } from "drizzle-orm";

// ─── 约束名称常量 ───
export const FK_FOLDERS_WORKSPACE =
  `${getTableName(folders)}_${folders.workspaceId.name}_${getTableName(workspaces)}_${workspaces.id.name}_fk` as const;

export const UNIQUE_FOLDERS_WORKSPACE_NAME =
  `${getTableName(folders)}_${folders.workspaceId.name}_${folders.name.name}_index` as const;

4. Better Auth Integration (Auth Schema)

4. 集成Better Auth（认证Schema）

This project uses Better Auth for authentication, which natively integrates with Drizzle. Do NOT create Auth tables manually from scratch.

CLI Generation: When updating or initializing Auth schemas, use the Better Auth CLI to automatically generate the necessary boilerplate setup.
Monolithic Copy: Do NOT leave the generated schema in a standalone Auth file. Copy the final generated output directly into our unified
```
schema.ts
```
.
Custom Fields Extension: If you must add custom domain-specific fields to the Better Auth base tables (like
```
users
```
or
```
sessions
```
), structure the table by separating the generated fields from your custom fields using a clear comment logic:

typescript

export const users = pgTable("users", {
  // Better-auth generated
  id: uuid("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),
  
  // Custom columns
  plan: subscriptionPlanEnum("subscription_plan").default("free").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

本项目使用Better Auth进行身份验证，它与Drizzle原生集成。请勿手动从头创建认证表。

CLI生成：更新或初始化认证Schema时，使用Better Auth CLI自动生成必要的模板代码。
集中式复制：不要将生成的Schema留在独立的认证文件中。请将最终生成的代码直接复制到统一的
```
schema.ts
```
文件中。
自定义字段扩展：如果必须向Better Auth基础表（如
```
users
```
或
```
sessions
```
）添加领域专属的自定义字段，请通过清晰的注释逻辑将生成字段与自定义字段分开：

typescript

export const users = pgTable("users", {
  // Better-auth 生成字段
  id: uuid("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),
  
  // 自定义字段
  plan: subscriptionPlanEnum("subscription_plan").default("free").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

5. Workflows

5. 工作流程

Run
```
npm run db:generate
```
to use Drizzle-kit to create SQL migrations after updating
```
schema.ts
```
.
Run
```
npm run db:push
```
or apply the migrations depending on the environment.
Run
```
npm run db:studio
```
for visual inspection via Drizzle Studio.
The
```
schema.ts
```
file acts as the source of truth for the
```
drizzle.config.ts
```
.
Repositories import this schema directly to query or write properties correctly separated from domain objects.

更新
```
schema.ts
```
后，运行
```
npm run db:generate
```
使用Drizzle-kit创建SQL迁移文件。
根据环境运行
```
npm run db:push
```
或应用迁移文件。
运行
```
npm run db:studio
```
通过Drizzle Studio进行可视化检查。
```
schema.ts
```
文件是
```
drizzle.config.ts
```
的数据源。
仓储层直接导入该Schema，以正确分离领域对象并执行查询或写入操作。