encore-database by encoredev/skills
npx skills add https://github.com/encoredev/skills --skill encore-databaseimport { SQLDatabase } from "encore.dev/storage/sqldb";
const db = new SQLDatabase("mydb", {
migrations: "./migrations",
});
Encore 提供了多种查询方法:
query - 多行查询返回一个用于遍历多行的异步迭代器:
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);
}
queryAll - 所有行作为数组返回将所有行作为数组返回(是 query 的便捷包装):
const users = await db.queryAll<User>`
SELECT id, email, name FROM users WHERE active = true
`;
// users 是 User[] 类型
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
queryRow - 单行查询返回一行数据或 null:
const user = await db.queryRow<User>`
SELECT id, email, name FROM users WHERE id = ${userId}
`;
if (!user) {
throw APIError.notFound("user not found");
}
exec - 无返回值用于 INSERT、UPDATE、DELETE 操作:
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}
`;
使用带有位置参数($1、$2 等)的原始 SQL 字符串,而不是模板字面量:
// 原始查询返回多行
const rows = await db.rawQuery<User>("SELECT * FROM users WHERE active = $1", true);
// 原始查询返回单行
const user = await db.rawQueryRow<User>("SELECT * FROM users WHERE id = $1", userId);
// 原始查询将所有行作为数组返回
const users = await db.rawQueryAll<User>("SELECT * FROM users WHERE role = $1", "admin");
// 用于 INSERT/UPDATE/DELETE 的原始 exec
await db.rawExec("INSERT INTO users (id, email) VALUES ($1, $2)", id, email);
使用 SQLDatabase.named() 引用由其他服务拥有的数据库:
import { SQLDatabase } from "encore.dev/storage/sqldb";
// 在拥有数据库的服务中
const db = new SQLDatabase("shared-db", {
migrations: "./migrations",
});
// 在另一个需要访问权限的服务中
const sharedDb = SQLDatabase.named("shared-db");
// 现在可以查询共享数据库
const user = await sharedDb.queryRow<User>`SELECT * FROM users WHERE id = ${id}`;
service/
└── migrations/
├── 001_create_users.up.sql
├── 002_add_posts.up.sql
└── 003_add_indexes.up.sql
.up.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);
// db.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
const db = new SQLDatabase("mydb", {
migrations: {
path: "migrations",
source: "drizzle",
},
});
export const orm = drizzle(db.connectionString);
// 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.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
out: "migrations",
schema: "schema.ts",
dialect: "postgresql",
});
生成迁移:drizzle-kit generate
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));
Encore 的模板字面量会自动转义值:
// 安全 - 值被参数化
const email = "user@example.com";
await db.queryRow`SELECT * FROM users WHERE email = ${email}`;
// 错误 - 存在 SQL 注入风险
await db.queryRow(`SELECT * FROM users WHERE email = '${email}'`);
query<User>、queryRow<User>queryRowquery 处理多行数据每周安装量
175
代码仓库
GitHub 星标数
20
首次出现
2026年1月21日
安全审计
安装于
codex138
opencode133
gemini-cli131
claude-code117
github-copilot115
cursor104
import { SQLDatabase } from "encore.dev/storage/sqldb";
const db = new SQLDatabase("mydb", {
migrations: "./migrations",
});
Encore provides several query methods:
query - Multiple RowsReturns an async iterator for multiple rows:
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);
}
queryAll - All Rows as ArrayReturns all rows as an array (convenience wrapper around query):
const users = await db.queryAll<User>`
SELECT id, email, name FROM users WHERE active = true
`;
// users is User[]
queryRow - Single RowReturns one row or null:
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 ValueFor INSERT, UPDATE, DELETE operations:
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}
`;
Use raw SQL strings with positional parameters ($1, $2, etc.) instead of template literals:
// Raw query returning multiple rows
const rows = await db.rawQuery<User>("SELECT * FROM users WHERE active = $1", true);
// Raw query returning single row
const user = await db.rawQueryRow<User>("SELECT * FROM users WHERE id = $1", userId);
// Raw query returning all rows as array
const users = await db.rawQueryAll<User>("SELECT * FROM users WHERE role = $1", "admin");
// Raw exec for INSERT/UPDATE/DELETE
await db.rawExec("INSERT INTO users (id, email) VALUES ($1, $2)", id, email);
Reference a database owned by another service using SQLDatabase.named():
import { SQLDatabase } from "encore.dev/storage/sqldb";
// In the service that owns the database
const db = new SQLDatabase("shared-db", {
migrations: "./migrations",
});
// In another service that needs access
const sharedDb = SQLDatabase.named("shared-db");
// Now you can query the shared database
const user = await sharedDb.queryRow<User>`SELECT * FROM users WHERE id = ${id}`;
service/
└── migrations/
├── 001_create_users.up.sql
├── 002_add_posts.up.sql
└── 003_add_indexes.up.sql
.up.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);
// db.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { drizzle } from "drizzle-orm/node-postgres";
const db = new SQLDatabase("mydb", {
migrations: {
path: "migrations",
source: "drizzle",
},
});
export const orm = drizzle(db.connectionString);
// 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.ts
import { defineConfig } from "drizzle-kit";
export default defineConfig({
out: "migrations",
schema: "schema.ts",
dialect: "postgresql",
});
Generate migrations: drizzle-kit generate
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));
Encore's template literals automatically escape values:
// 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}'`);
query<User>, queryRow<User>queryRow when expecting 0 or 1 resultquery with async iteration for multiple rowsWeekly Installs
175
Repository
GitHub Stars
20
First Seen
Jan 21, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex138
opencode133
gemini-cli131
claude-code117
github-copilot115
cursor104
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
116,600 周安装