Agent X-Ray

Notes Skills About

Skills/

正在取…

INSTALL

FILES

本页目录

苏ICP备2025204887号-2

Skills/开发框架/encore-database

encore-database精选

Encore.ts 的 Postgres:SQLDatabase、迁移目录与 SQL 查询,含 Drizzle / Prisma 接法

2 个文件 · 16.3 KB · 更新于 2026-09-03 · 出处 encoredev/skills ↗

GitHub ↗下载 zip · 6 KB

INSTALL

npx skills add encoredev/skills --skill encore-database

copy

装进 .claude/skills/encore-database/;Codex 读 .agents/skills/,同一目录结构。

FILES

▾encore-database/

SKILL.md5.8 KB

LICENSE10.5 KB

本页目录

Instructions Query Methods Database Sharing Across Services Migrations Drizzle ORM Integration SQL Injection Protection Guidelines

SKILL.mdmarkdown · 5.8 KB · 251 行

copy

nameencore-databasedescriptionWork with PostgreSQL in Encore.ts using `SQLDatabase` from `encore.dev/storage/sqldb` — schema migrations and SQL queries.when_to_useUser wants to add a database table, write a migration, run a SQL query, insert/update/delete rows, set up Drizzle or Prisma against an Encore database, or design a relational schema. Covers `new SQLDatabase(...)`, `db.query`, `db.queryRow`, `db.exec`, the `migrations/` directory, `*.up.sql` files, sequential migration numbering, and ORM integration via `db.connectionString`. Trigger phrases: "Postgres table", "user_sessions table", "SQL", "migration", "queryRow", "INSERT", "SELECT", "schema", "Drizzle", "Prisma".

Encore Database Operations

Instructions

Database Setup

typescript

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

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

Query Methods

Encore provides several query methods:

query - Multiple Rows

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);
}

queryAll - All Rows as Array

Returns all rows as an array (convenience wrapper around query):

typescript

const users = await db.queryAll<User>`
  SELECT id, email, name FROM users WHERE active = true
`;
// users is User[]

queryRow - Single Row

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");
}

exec - No Return Value

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}
`;

Use raw SQL strings with positional parameters ($1, $2, etc.) instead of template literals:

typescript

// 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():

typescript

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}`;

text

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

Start with a number (001, 002, etc.)
Followed by underscore and description
End with .up.sql
Numbers must be sequential

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

typescript

// 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);

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

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

export default defineConfig({
  out: "migrations",
  schema: "schema.ts",
  dialect: "postgresql",
});

Generate migrations: drizzle-kit generate

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));

SQL Injection Protection

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}'`);

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

Encore Database Operations

Instructions

Database Setup

Query Methods

query - Multiple Rows

queryAll - All Rows as Array

queryRow - Single Row

exec - No Return Value

Raw Query Methods

Migrations

File Structure

Naming Convention

Example Migration

Drizzle ORM Integration

Setup

Schema

Drizzle Config

Using Drizzle

SQL Injection Protection

Guidelines