Back to Details

encore-database

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Encore Database Operations

Encore 数据库操作

Instructions

操作说明

Database Setup

数据库设置

typescript
import { SQLDatabase } from "encore.dev/storage/sqldb";

const db = new SQLDatabase("mydb", {
  migrations: "./migrations",
});
typescript
import { SQLDatabase } from "encore.dev/storage/sqldb";

const db = new SQLDatabase("mydb", {
  migrations: "./migrations",
});

Query Methods

查询方法

Encore provides three main query methods:
Encore 提供三种主要查询方法：

query
- Multiple Rows

query
- 多行查询

Returns an async iterator for multiple rows:
typescript
interface User {
  id: string;
  email: string;
  name: string;
}

const rows = await db.query<User>`
  SELECT id, email, name FROM users WHERE active = true
`;

const users: User[] = [];
for await (const row of rows) {
  users.push(row);
}
返回用于多行数据的异步迭代器：
typescript
interface User {
  id: string;
  email: string;
  name: string;
}

const rows = await db.query<User>`
  SELECT id, email, name FROM users WHERE active = true
`;

const users: User[] = [];
for await (const row of rows) {
  users.push(row);
}

queryRow
- Single Row

queryRow
- 单行查询

Returns one row or null:
typescript
const user = await db.queryRow<User>`
  SELECT id, email, name FROM users WHERE id = ${userId}
`;

if (!user) {
  throw APIError.notFound("user not found");
}
返回一行数据或 null：
typescript
const user = await db.queryRow<User>`
  SELECT id, email, name FROM users WHERE id = ${userId}
`;

if (!user) {
  throw APIError.notFound("user not found");
}

exec
- No Return Value

exec
- 无返回值

For INSERT, UPDATE, DELETE operations:
typescript
await db.exec`
  INSERT INTO users (id, email, name)
  VALUES (${id}, ${email}, ${name})
`;

await db.exec`
  UPDATE users SET name = ${newName} WHERE id = ${id}
`;

await db.exec`
  DELETE FROM users WHERE id = ${id}
`;
适用于 INSERT、UPDATE、DELETE 操作：
typescript
await db.exec`
  INSERT INTO users (id, email, name)
  VALUES (${id}, ${email}, ${name})
`;

await db.exec`
  UPDATE users SET name = ${newName} WHERE id = ${id}
`;

await db.exec`
  DELETE FROM users WHERE id = ${id}
`;

Migrations

数据库迁移

File Structure

文件结构

service/
└── migrations/
    ├── 001_create_users.up.sql
    ├── 002_add_posts.up.sql
    └── 003_add_indexes.up.sql
service/
└── migrations/
    ├── 001_create_users.up.sql
    ├── 002_add_posts.up.sql
    └── 003_add_indexes.up.sql

Naming Convention

命名规范

  • Start with a number (001, 002, etc.)
  • Followed by underscore and description
  • End with 
    .up.sql
  • Numbers must be sequential
  • 以数字开头（001、002 等）
  • 后跟下划线和描述信息
  • 以 
    .up.sql
    结尾
  • 数字必须按顺序排列

Example Migration

迁移示例

sql
-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
sql
-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Drizzle ORM Integration

Drizzle ORM 集成

Setup

设置

typescript
// db.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

const db = new SQLDatabase("mydb", {
  migrations: {
    path: "migrations",
    source: "drizzle",
  },
});

export const orm = drizzle(db.connectionString, { schema });
typescript
// db.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

const db = new SQLDatabase("mydb", {
  migrations: {
    path: "migrations",
    source: "drizzle",
  },
});

export const orm = drizzle(db.connectionString, { schema });

Schema

模式

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(),
  createdAt: p.timestamp().defaultNow(),
});
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(),
  createdAt: p.timestamp().defaultNow(),
});

Drizzle Config

Drizzle 配置

typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "migrations",
  schema: "schema.ts",
  dialect: "postgresql",
});
Generate migrations: 
drizzle-kit generate
typescript
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "migrations",
  schema: "schema.ts",
  dialect: "postgresql",
});
生成迁移文件：
drizzle-kit generate

Using Drizzle

使用 Drizzle

typescript
import { orm } from "./db";
import { users } from "./schema";
import { eq } from "drizzle-orm";

// Select
const allUsers = await orm.select().from(users);
const user = await orm.select().from(users).where(eq(users.id, id));

// Insert
await orm.insert(users).values({ email, name });

// Update
await orm.update(users).set({ name }).where(eq(users.id, id));

// Delete
await orm.delete(users).where(eq(users.id, id));
typescript
import { orm } from "./db";
import { users } from "./schema";
import { eq } from "drizzle-orm";

// 查询
const allUsers = await orm.select().from(users);
const user = await orm.select().from(users).where(eq(users.id, id));

// 插入
await orm.insert(users).values({ email, name });

// 更新
await orm.update(users).set({ name }).where(eq(users.id, id));

// 删除
await orm.delete(users).where(eq(users.id, id));

SQL Injection Protection

SQL 注入防护

Encore's template literals automatically escape values:
typescript
// SAFE - values are parameterized
const email = "user@example.com";
await db.queryRow`SELECT * FROM users WHERE email = ${email}`;

// WRONG - SQL injection risk
await db.queryRow(`SELECT * FROM users WHERE email = '${email}'`);
Encore 的模板字面量会自动对值进行转义：
typescript
// 安全 - 值已参数化
const email = "user@example.com";
await db.queryRow`SELECT * FROM users WHERE email = ${email}`;

// 错误 - 存在 SQL 注入风险
await db.queryRow(`SELECT * FROM users WHERE email = '${email}'`);

Guidelines

注意事项

  • Always use template literals for queries (automatic escaping)
  • Specify types with generics: 
    query<User>
    , 
    queryRow<User>
  • Migrations are applied automatically on startup
  • Use 
    queryRow
    when expecting 0 or 1 result
  • Use 
    query
    with async iteration for multiple rows
  • Database names should be lowercase, descriptive
  • Each service typically has its own database
  • 始终使用模板字面量编写查询（自动转义）
  • 使用泛型指定类型：
    query<User>
    、
    queryRow<User>
  • 迁移会在启动时自动应用
  • 当预期结果为 0 或 1 条时使用 
    queryRow
  • 对多行数据使用 
    query
    配合异步迭代
  • 数据库名称应使用小写且具有描述性
  • 每个服务通常拥有自己的数据库