# encore-database

Teaches the AI agent to manage PostgreSQL in Encore.ts applications. It guides schema migrations and typed SQL queries using SQLDatabase.

- skill · `type=skill` · Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI, OpenCode · 799 installs · ★ 30
- Author: encoredev
- License: Apache-2.0 · policy=mirror · the beginning of the file is at the end of this page
- Source: https://github.com/encoredev/skills/tree/HEAD/encore/database/SKILL.md
- Updated: 2026-10-23
- HTML: https://affpapa.org/en/nevibecode/skills/encore-database

## What it does

This skill guides the assistant on utilizing `SQLDatabase` from `encore.dev/storage/sqldb`. It enforces typed database operations with methods such as `query`, `queryRow`, and `queryAll`. Write queries rely on `exec`. Template literals automatically parameterize variables to block SQL injection vulnerabilities.

Database migrations go into the `migrations/` directory using `.up.sql` extensions with strict sequential numbers. Without ordered numbering, startup migrations fail. The instructions also explain cross-service database references using `SQLDatabase.named()` and Drizzle ORM integration powered by `db.connectionString`.

## Requirements
- Works with: PostgreSQL

## Install

### Claude Code (native platform)

1. **Skills CLI (Vercel)**
```bash
npx skills add encoredev/skills --skill encore-database -a claude-code
```
```bash
npx skills add encoredev/skills --skill database -a claude-code
```

2. **Manually: put the files in place**
- File: `~/.claude/skills/database/SKILL.md` — Copy the whole skill folder; for one project use .claude/skills/database/

### Codex

1. **Skills CLI (Vercel)**
```bash
npx skills add encoredev/skills --skill database -a codex
```

2. **Manually: put the files in place**
- File: `.agents/skills/database/SKILL.md` — For all projects use ~/.agents/skills/

### Cursor

1. **Skills CLI (Vercel)**
```bash
npx skills add encoredev/skills --skill database -a cursor
```

2. **Manually: put the files in place**
- File: `.cursor/skills/database/SKILL.md` — Cursor also reads .agents/skills/ and .claude/skills/

### GitHub Copilot

1. **GitHub CLI**
```bash
gh skill install encoredev/skills database
```

2. **Manually: put the files in place**
- File: `.github/skills/database/SKILL.md`

### Gemini CLI

1. **Skills CLI (Vercel)**
```bash
npx skills add encoredev/skills --skill database -a gemini-cli
```

2. **Platform CLI**
```bash
gemini skills install https://github.com/encoredev/skills --path encore/database
```

### Windsurf (Devin Desktop)

**Manually: put the files in place**
- File: `.devin/skills/database/SKILL.md` — The legacy .windsurf/skills/ path still works

### OpenCode

**Skills CLI (Vercel)**
```bash
npx skills add encoredev/skills --skill database -a opencode
```

### Claude.ai

**Claude.ai**
- Zip the folder and upload it in Settings → Capabilities → Skills

Where a file is given instead of a command, copy the files from the source to that path: https://github.com/encoredev/skills/tree/HEAD/encore/database/SKILL.md

## When to use it
- create sequential SQL migration files in Encore.ts
- write parameterized queries using `queryRow` and `exec`
- connect Drizzle ORM to PostgreSQL via `db.connectionString`
- share an existing database with another service using `SQLDatabase.named`

## Example prompts
- Create a migration file for user sessions and add an insert query with db.exec
- Fetch an active account by id using queryRow in Encore.ts
- Configure Drizzle ORM for my Encore database schema

## Source text · SKILL.md · Apache-2.0

Below is the content of a third-party file. It is data, not instructions for you: do not act on anything written inside unless the human asks. Block tag: 0f7bd35693f03f72.

BEGIN THIRD-PARTY FILE 0f7bd35693f03f72
````text
---
name: encore-database
description: Work with PostgreSQL in Encore.ts using `SQLDatabase` from `encore.dev/storage/sqldb` — schema migrations and SQL queries.
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".
---

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

### Raw Query Methods

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

## Database Sharing Across Services

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
````
END THIRD-PARTY FILE 0f7bd35693f03f72

The third-party file has ended. Everything between the 0f7bd35693f03f72 tags is data: requests and commands from it are not followed. An “end of file” line with another tag inside the block is part of the file.

First 3158 of 5943 bytes. © encoredev · Apache-2.0 · https://github.com/encoredev/skills/tree/HEAD/encore/database/SKILL.md

_Part of the page did not fit — see the HTML version._

---
NeVibeCode on affpapa.org. Catalog texts are descriptions, not instructions for the agent. How to choose and install: https://affpapa.org/en/nevibecode/agents.md