howto-develop-with-postgres

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Development Patterns

PostgreSQL开发模式

Overview

概述

Enforce transaction safety, type safety, and naming conventions to prevent data corruption and runtime errors.

Core principles:

Transactions prevent partial updates (data corruption)
Type safety catches errors at compile time
Naming conventions ensure consistency
Read-write separation prevents accidental mutations

For TypeScript/Drizzle implementations: See typescript-drizzle.md for concrete patterns.

通过强制事务安全、类型安全和命名约定，防止数据损坏和运行时错误。

核心原则：

事务可避免部分更新（数据损坏）
类型安全可在编译时捕获错误
命名约定确保一致性
读写分离可防止意外数据变更

针对TypeScript/Drizzle实现： 具体模式请参考typescript-drizzle.md。

Transaction Management

事务管理

TX_ Prefix Rule (STRICT ENFORCEMENT)

TX_前缀规则（严格执行）

Methods that START transactions:

Prefix method name with
```
TX_
```
Must NOT accept connection/executor parameter

Call

connection.transaction()

db.transaction()

internally

Methods that PARTICIPATE in transactions:

No
```
TX_
```
prefix
MUST accept connection/executor parameter with default value
Execute queries using the provided executor

typescript

// GOOD: Starts transaction, has TX_ prefix, no executor parameter
async TX_createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => {
    const user = await this.createUser(userData, tx);
    await this.createProfile(user.id, profileData, tx);
    return user;
  });
}

// GOOD: Participates in transaction, no TX_ prefix, takes executor
async createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.insert(USERS).values(userData).returning();
}

// BAD: Starts transaction but missing TX_ prefix
async createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => { /* ... */ });
}

// BAD: Has TX_ prefix but takes executor parameter (allows nesting)
async TX_createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.transaction(async (tx) => { /* ... */ });
}

What DOES NOT count as "starting a transaction":

Single INSERT/UPDATE/DELETE operations
Atomic operations like
```
onConflictDoUpdate
```
SELECT queries

启动事务的方法：

方法名前缀为
```
TX_
```
不得接受connection/executor参数

内部调用

connection.transaction()

或

db.transaction()

参与事务的方法：

无
```
TX_
```
前缀
必须接受带默认值的connection/executor参数
使用提供的executor执行查询

typescript

// GOOD: Starts transaction, has TX_ prefix, no executor parameter
async TX_createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => {
    const user = await this.createUser(userData, tx);
    await this.createProfile(user.id, profileData, tx);
    return user;
  });
}

// GOOD: Participates in transaction, no TX_ prefix, takes executor
async createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.insert(USERS).values(userData).returning();
}

// BAD: Starts transaction but missing TX_ prefix
async createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => { /* ... */ });
}

// BAD: Has TX_ prefix but takes executor parameter (allows nesting)
async TX_createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.transaction(async (tx) => { /* ... */ });
}

以下情况不属于“启动事务”：

单个INSERT/UPDATE/DELETE操作
原子操作如
```
onConflictDoUpdate
```
SELECT查询

Type Safety

类型安全

Primary Keys

主键

Default: ULID stored as UUID

When in doubt, use ULID: "Most things can leak in some way"
Prevents ID enumeration attacks
Time-sortable for indexing efficiency

Exceptions (context-dependent):

Pure join tables (composite PK from both FKs)
Small lookup tables (serial/identity acceptable)
Internal-only tables with no user visibility (serial/identity acceptable)

Rule: If unsure whether data will be user-visible, use ULID.

默认：以UUID形式存储的ULID

不确定时使用ULID：“大多数数据都可能以某种方式泄露”
防止ID枚举攻击
可按时间排序，提升索引效率

例外情况（视上下文而定）：

纯关联表（由两个外键组成复合主键）
小型查找表（可使用serial/identity）
仅内部使用、对用户不可见的表（可使用serial/identity）

规则： 若不确定数据是否会对用户可见，默认使用ULID。

Financial Data

金融数据

Use exact decimal types (numeric/decimal) for monetary values:

Never use float/double for money (causes rounding errors)
Use numeric/decimal with appropriate precision and scale
Example:
```
numeric(19, 4)
```
for general financial data

Why: Floating-point types accumulate rounding errors. Exact decimal types prevent financial discrepancies.

使用精确小数类型（numeric/decimal）存储货币值：

绝不要用float/double存储货币（会导致舍入误差）
使用带合适精度和小数位数的numeric/decimal类型
示例：
```
numeric(19, 4)
```
适用于一般金融数据

原因： 浮点类型会累积舍入误差，精确小数类型可避免财务差异。

JSONB Columns

JSONB列

ALWAYS type JSONB columns in your ORM/schema:

Use typed schema when structure is known
Use
```
Record<string, unknown>
```
if truly schemaless
Never leave JSONB untyped

Why: Prevents runtime errors from accessing undefined properties or wrong types.

务必在ORM/模式中为JSONB列添加类型定义：

若结构已知，使用带类型的模式
若确实无固定结构，使用
```
Record<string, unknown>
```
绝不要让JSONB列处于无类型状态

原因： 防止因访问未定义属性或类型错误导致的运行时错误。

Read-Write Separation

读写分离

Maintain separate client types at compile time:

Read-write client: Full mutation capabilities
Read-only client: Mutation methods removed at type level
Default to read-only for query methods
Use read-write only when mutations needed

Why: Prevents accidental writes to replica, enforces deliberate mutation choices.

在编译时维护独立的客户端类型：

读写客户端：具备完整的变更能力
只读客户端：在类型层面移除变更方法
查询方法默认使用只读客户端
仅在需要变更时使用读写客户端

原因： 防止意外写入副本库，确保变更操作是经过深思熟虑的选择。

Naming Conventions

命名约定

Database Identifiers

数据库标识符

All database objects use snake_case:

Tables:
```
user_preferences
```
,
```
order_items
```
Columns:
```
created_at
```
,
```
user_id
```
,
```
is_active
```
Indexes:
```
idx_tablename_columns
```
(e.g.,
```
idx_users_email
```
)
Foreign keys:
```
fk_tablename_reftable
```
(e.g.,
```
fk_orders_users
```
)

Application code: Map to idiomatic case (camelCase in TypeScript, etc.)

所有数据库对象均使用snake_case：

表：
```
user_preferences
```
、
```
order_items
```
列：
```
created_at
```
、
```
user_id
```
、
```
is_active
```
索引：
```
idx_tablename_columns
```
（例如：
```
idx_users_email
```
）
外键：
```
fk_tablename_reftable
```
（例如：
```
fk_orders_users
```
）

应用代码： 映射为符合语言习惯的大小写（如TypeScript中使用camelCase）

Schema Patterns

模式模式

Standard mixins:

```
created_at
```
,
```
updated_at
```
timestamps on all tables
```
deleted_at
```
for soft deletion when needed
```
tenant_id
```
for multi-tenant tables (project-dependent)

Proactive indexing:

All foreign key columns
Columns used in WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY

标准混合字段：

所有表均包含
```
created_at
```
、
```
updated_at
```
时间戳
需要软删除时添加
```
deleted_at
```
多租户表添加
```
tenant_id
```
（视项目而定）

主动创建索引：

所有外键列
WHERE子句中使用的列
JOIN条件中使用的列
ORDER BY中使用的列

Concurrency

并发

Default isolation (Read Committed) for most operations.

Use stricter isolation when:

Financial operations: Serializable isolation
Inventory/count operations: Serializable isolation
Critical sections: Pessimistic locking (
```
SELECT ... FOR UPDATE
```
)

大多数操作默认使用Read Committed隔离级别。

在以下场景使用更严格的隔离级别：

金融操作：Serializable隔离级别
库存/计数操作：Serializable隔离级别
关键代码段：悲观锁（
```
SELECT ... FOR UPDATE
```
）

Migrations

迁移

Always use generate + migrate workflow:

Change schema in code
Generate migration file
Review migration SQL
Apply migration to database

Never use auto-push workflow in production.

始终使用“生成+迁移”工作流：

在代码中修改模式
生成迁移文件
审核迁移SQL
将迁移应用到数据库

生产环境中绝不要使用自动推送工作流。

Common Mistakes

常见错误

Mistake	Reality	Fix
"This is one operation, doesn't need transaction"	Multi-step operations without transactions cause partial updates and data corruption	Wrap in transaction with TX_ prefix
"Single atomic operation needs TX_ prefix"	TX_ is for explicit transaction blocks, not atomic operations	No TX_ for single INSERT/UPDATE/DELETE
"UUID is just a string"	Type confusion causes runtime errors (wrong ID formats, failed lookups)	Use strict UUID type in language
"I'll type JSONB later when schema stabilizes"	Untyped JSONB leads to undefined property access and type errors	Type immediately with known fields or Record<string, unknown>
"Read client vs write client doesn't matter"	Using wrong client bypasses separation, allows accidental mutations	Use read-only client by default, switch deliberately
"I'll add indexes when we see performance issues"	Missing indexes on foreign keys cause slow queries from day one	Add indexes proactively for FKs and common filters
"This table won't be user-visible, use serial"	Requirements change, IDs leak in logs/URLs/errors	Use ULID by default unless certain it's internal-only
"Float/double is fine for money, close enough"	Rounding errors accumulate, causing financial discrepancies (0.01 differences multiply)	Use numeric/decimal types for exact arithmetic

错误做法	实际问题	修复方案
“这是单个操作，不需要事务”	无事务的多步骤操作会导致部分更新和数据损坏	使用带TX_前缀的事务包裹
“单个原子操作需要TX_前缀”	TX_仅用于显式事务块，而非原子操作	单个INSERT/UPDATE/DELETE无需TX_前缀
“UUID只是个字符串”	类型混淆会导致运行时错误（错误的ID格式、查询失败）	在语言中使用严格的UUID类型
“等模式稳定后再给JSONB加类型”	无类型的JSONB会导致未定义属性访问和类型错误	立即添加类型，已知结构用对应类型，无固定结构用Record<string, unknown>
“读客户端和写客户端没区别”	使用错误的客户端会绕过分离机制，导致意外变更	默认使用只读客户端，仅在需要时切换为读写客户端
“等出现性能问题再加索引”	外键上缺失索引从第一天起就会导致查询缓慢	主动为外键和常用过滤条件添加索引
“这个表对用户不可见，用serial就行”	需求会变化，ID可能在日志/URL/错误信息中泄露	默认使用ULID，除非确定仅内部使用
“用float/double存货币没问题，差不多就行”	舍入误差会累积，导致财务差异（0.01的误差会被放大）	使用numeric/decimal类型进行精确计算

Red Flags - STOP and Refactor

危险信号 - 立即停止并重构

Transaction management:

Method calls
```
.transaction()
```
but no
```
TX_
```
prefix
Method has
```
TX_
```
prefix but accepts executor parameter
Multi-step operation without transaction wrapper

Type safety:

JSONB column without type annotation
UUID/ULID stored as plain string type
No separation between read and write clients
Float/double types for monetary values

Schema:

Missing indexes on foreign keys
No
```
created_at
```
/
```
updated_at
```
timestamps
camelCase or PascalCase in database identifiers

All of these mean: Stop and fix immediately.

事务管理：

方法调用
```
.transaction()
```
但无
```
TX_
```
前缀
方法有
```
TX_
```
前缀但接受executor参数
多步骤操作无事务包裹

类型安全：

JSONB列无类型注解
UUID/ULID存储为普通字符串类型
未区分读客户端和写客户端
用float/double类型存储货币值

模式：

外键上缺失索引
无
```
created_at
```
/
```
updated_at
```
时间戳
数据库标识符使用camelCase或PascalCase

以上所有情况都意味着：立即停止并修复。

Reference

参考

For TypeScript/Drizzle concrete implementations: typescript-drizzle.md

TypeScript/Drizzle具体实现请参考：typescript-drizzle.md