Index of every page on this site: llms.txt
All skills
encoredev avatar

/database

@e86a3ab official
by Encoreencoredev/skills30 stars
6
Run it yourself

Work with PostgreSQL in Encore.ts using `SQLDatabase` from `encore.dev/storage/sqldb` — schema migrations and SQL queries.

  • 1 file
  • 5.8 KB
  • Updated 6 months ago
  • GitHub
Use this Skill: https://skilld.dev/gh/encoredev/skills/database

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈34 tokens for metadata: the name and description. ≈1.3k when used: this file.

Description uses 8.2% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈659 characters, or 8.2%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

Encore Database Operations

Instructions

Database Setup

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:

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

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:

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:

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

Raw Query Methods

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

Database Sharing Across Services

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

Migrations

File Structure

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

Example Migration

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

Setup

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

// 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.config.ts
import { defineConfig } from "drizzle-kit";

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

Generate migrations: drizzle-kit generate

Using Drizzle

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:

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

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

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at e86a3ab. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub 4 weeks ago.

Activeupdated 6 months ago
Other metadata
when_to_use
User 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".
  • TypeScript
  • postgres
  • encore
  • sqldb
  • migrations
  • drizzle
  • prisma
  • orm
  • schema

README badge

README badge for encoredev/skills/database

Works with PostgreSQL in Encore.ts using the `SQLDatabase` API — supports schema migrations, typed SQL queries (`query`, `queryRow`, `exec`), and Drizzle or Prisma ORM integration. Covers migration file structure, shared databases across services, and parameterized query safety.

Generated from the current SKILL.md.

Does this skill work with databases other than PostgreSQL?
No. This skill covers PostgreSQL only via Encore's `SQLDatabase` API. MySQL and other databases are not supported.
Can I share a database between multiple Encore services?
Yes. Use `SQLDatabase.named()` in a service to reference a database owned by another service, then query it with the same methods as a local database.
Do I need to write raw SQL, or can I use an ORM?
Both work. The skill covers raw template-literal queries, raw SQL with positional parameters, and Drizzle ORM integration via `db.connectionString`.
How do migrations work in Encore?
Place numbered `.up.sql` files in a `migrations/` directory (e.g., `001_create_users.up.sql`). Encore applies them sequentially on startup. Drizzle ORM can generate these files with `drizzle-kit generate`.
Are SQL queries automatically protected against injection?
Yes, when using template literals like ``db.queryRow`SELECT * FROM users WHERE id = ${userId}` ``. Values are parameterized automatically. String interpolation without template literals is unsafe.

Generated from the current SKILL.md. These answers refresh after source changes.