第5章：数据库 —— SQLDatabase、迁移、查询与 ORM 集成
约 5 分钟 · 更新于 2026-09-02
在 Encore 里"要一个数据库"是两行代码的事：一个声明 + 一个迁移目录。本章覆盖查询方法全家族、事务、迁移规则、跨服务共享、CLI 工具，以及 knex / Drizzle / Prisma 三种 ORM 集成路线。
一、声明数据库
typescript
import { SQLDatabase } from "encore.dev/storage/sqldb";
const db = new SQLDatabase("order", {
migrations: "./migrations",
});
发生了什么：
- 本地：encore run 自动用 Docker 拉起 Postgres（encoredotdev/postgres 镜像，内置 pgvector 与 PostGIS 扩展），建库并执行迁移；
- 云上：映射到 AWS RDS / GCP Cloud SQL（Encore Cloud 自动开通）或你在 infra-config.json 里指定的实例（自托管，第 16 章）；
- 数据库名在应用内全局唯一；一个服务可以声明多个数据库，通常一服务一库。
二、迁移 (Migrations)
迁移文件放在声明时指定的目录，纯 SQL：
text
order/
└── migrations/
├── 001_create_orders.up.sql
├── 002_add_passengers.up.sql
└── 003_add_indexes.up.sql
命名规则（违反即报错）：
- 数字开头（1_、001_ 均可），必须严格递增；
- 下划线后接描述；
- 以 .up.sql 结尾。
sql
-- order/migrations/001_create_orders.up.sql
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_no TEXT UNIQUE NOT NULL,
flight_no TEXT NOT NULL,
amount_cents BIGINT NOT NULL,
status TEXT NOT NULL DEFAULT 'CREATED',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_orders_status ON orders(status);
迁移在应用启动时自动执行（本地与云上一致）。迁移是 schema 的唯一事实源——这对订单、支付这类审计敏感领域是重要性质：schema 变更全部进代码评审与版本历史。
三、查询方法全家族
模板字符串形式（推荐，自动参数化防注入）：
typescript
// query —— 多行，返回异步迭代器
const rows = await db.query<{ orderNo: string; amountCents: number }>`
SELECT order_no, amount_cents FROM orders WHERE status = ${status}
`;
for await (const row of rows) {
// 逐行流式处理，适合大结果集
}
// queryAll —— 多行，一次取回数组
const orders = await db.queryAll<Order>`
SELECT * FROM orders WHERE status = ${status}
`;
// queryRow —— 单行或 null
const order = await db.queryRow<Order>`
SELECT * FROM orders WHERE order_no = ${orderNo}
`;
if (!order) throw APIError.notFound("order not found");
// exec —— 不返回行（INSERT / UPDATE / DELETE / DDL）
await db.exec`
UPDATE orders SET status = ${"PAID"} WHERE order_no = ${orderNo}
`;
raw 形式（SQL 需要动态拼装时用，参数走 $1 占位）：
typescript
const rows = await db.rawQuery<Order>("SELECT * FROM orders WHERE status = $1", status);
const row = await db.rawQueryRow<Order>("SELECT * FROM orders WHERE id = $1", id);
const all = await db.rawQueryAll<Order>("SELECT * FROM orders WHERE status = $1", status);
await db.rawExec("INSERT INTO audit_log (msg) VALUES ($1)", msg);
防注入的边界
db.query`...${v}...` 的插值编译为参数化查询，安全；db.rawQuery("..." + v) 字符串拼接危险。动态拼 SQL（如多条件搜索）时把用户值全部走 $n 参数、只拼接白名单内的 SQL 片段。本机 B2B 项目把全仓唯一的 rawQuery 使用点集中在一个文件（list-query.ts）便于审计，值得效仿。
类型标注建议
query<T> 的泛型是你对结果行的断言，编译器无法核对 SQL 与 T 是否一致。列名经 AS 改名、可空列、聚合列都容易写错——用集成测试盯住（第 15 章），或上 ORM 获得端到端类型。
四、事务
typescript
// AsyncDisposable 风格：离开作用域自动回滚未提交事务
await using tx = await db.begin();
await tx.exec`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`;
await tx.exec`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`;
await tx.commit(); // 不 commit 则自动回滚
await using 是 TypeScript 5.2+ 的显式资源管理语法：异常路径不需要手写 rollback，作用域结束未提交即回滚。
五、跨服务共享数据库
默认一服务一库是推荐架构（服务边界=数据边界）。确需共享时用 SQLDatabase.named 引用已存在的库：
typescript
// 拥有方（order 服务）
const db = new SQLDatabase("order", { migrations: "./migrations" });
// 引用方（report 服务）—— 不带迁移，只读引用
const orderDb = SQLDatabase.named("order");
const row = await orderDb.queryRow`SELECT count(*) AS n FROM orders`;
静态分析器会把这条依赖画进架构图。注意：共享库让两个服务在 schema 上耦合，报表/后台类只读场景可以接受，写路径共享是拆分失败的信号（见第 6 章决策表）。
六、CLI 数据库工具
bash
encore db shell order # 打开 psql（默认只读；--write --admin --superuser 提权）
encore db conn-uri order # 输出连接串（喂给 GUI 客户端 / 脚本）
encore db proxy [--env=name] # 本地代理到指定环境的数据库
encore db reset order # 重置该服务数据库（重跑全部迁移）
--env 参数让这套命令同样适用于云上环境（如 encore db shell order --env=staging），权限受 Encore Cloud 的 IAM 管控。
七、ORM 集成
Encore 的数据库是标准 Postgres，任何"支持标准 SQL 驱动 + 迁移产物是纯 SQL"的 ORM 都能接。桥梁是 db.connectionString。
路线 A：knex / kysely 等查询构造器
typescript
import { SQLDatabase } from "encore.dev/storage/sqldb";
import knex from "knex";
const SiteDB = new SQLDatabase("site", { migrations: "./migrations" });
const orm = knex({
client: "pg",
connection: SiteDB.connectionString,
});
const Sites = () => orm<Site>("site");
// 用法：await Sites().where("id", id).first();
迁移仍由 Encore 管（手写 SQL），knex 只负责查询——第 14 章实战二采用这条路线。
路线 B：Drizzle（迁移也交给 ORM 生成）
typescript
// database.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
const db = new SQLDatabase("app", {
migrations: { path: "migrations", source: "drizzle" }, // 声明迁移来源为 drizzle
});
export const orm = drizzle(db.connectionString);
typescript
// schema.ts
import * as p from "drizzle-orm/pg-core";
export const users = p.pgTable("users", {
id: p.uuid().primaryKey().defaultRandom(),
email: p.text().unique().notNull(),
name: p.text().notNull(),
});
typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
out: "migrations",
schema: "schema.ts",
dialect: "postgresql",
});
drizzle-kit generate 生成 SQL 迁移文件 → Encore 启动时自动执行。查询获得端到端类型：
typescript
import { eq } from "drizzle-orm";
const user = await orm.select().from(users).where(eq(users.id, id));
await orm.insert(users).values({ email, name });
路线 C：Prisma
同理：Prisma 的迁移产物是纯 SQL，connectionString 接上即可。三条路线选型：
| 场景 | 建议 |
|---|
| SQL 熟练、想要最少抽象 | 原生模板字符串 |
| 复杂动态查询多 | knex / kysely |
| 想要 schema→类型全链路 | Drizzle（与 Encore 集成最深，迁移源可声明） |
八、常见误区
- 迁移编号跳号或修改已执行过的迁移文件——前者报错，后者导致环境间 schema 漂移；改 schema 永远新增迁移。
- 在 handler 里 new SQLDatabase——原语必须包级声明。
- 用字符串拼接构造 SQL——注入风险；动态 SQL 走 rawQuery + $n 参数。
- query 结果忘了 for await（当成数组用）——它是异步迭代器，要数组用 queryAll。
- 事务用 db.begin() 后在事务内误用 db.exec（而不是 tx.exec）——绕过了事务。
- 把泛型 query<T> 当成类型安全保证——它只是断言，需测试或 ORM 兜底。
- 图方便到处 SQLDatabase.named 共享库——数据边界溶解，拆服务白拆。
九、实践练习
- 建 order 服务与订单表（含 order_no 唯一约束、status 默认值、created_at），写入/查询/更新三个端点全部走模板字符串。
- 故意插入重复 order_no，观察 Postgres 唯一约束错误如何浮出，为它包一层 APIError.alreadyExists。
- 写一个转账式双写事务（扣减库存 + 创建订单），在两写之间抛异常，用 encore db shell 验证自动回滚。
- 新增迁移 002_add_contact.up.sql 给订单表加联系人列，观察 encore run 自动执行；再试着把 002 改名 004，看报错。
- 用 Drizzle 重做练习 1，对比模板字符串与 ORM 的类型体验。
十、总结
- 数据库 = new SQLDatabase(名, {migrations}) + SQL 迁移目录；本地 Docker 自动开通、启动自动迁移。
- 查询四方法（query/queryAll/queryRow/exec）+ raw 四兄弟；模板字符串自动参数化防注入。
- 事务用 await using tx = await db.begin()，未提交自动回滚。
- 跨服务共享用 SQLDatabase.named，但一服务一库是默认正道。
- CLI 四件套：shell / conn-uri / proxy / reset，均支持 --env。
- ORM 三路线：模板字符串、knex、Drizzle/Prisma；connectionString 是通用接口。
请继续阅读：第6章:服务架构。
原始资料引用