database

Encore Database Operations

Instructions

Before making changes, confirm that the project uses Encore.ts.

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

This example assumes the APIError class is imported from the Encore API module.

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

For updates and deletes, verify that the caller is authorized to change the row, and scope the WHERE clause to rows the caller owns rather than matching on id alone.

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

Pass only constant SQL strings to the raw methods, and pass every user supplied value as a separate positional argument. Use raw methods only when a template literal cannot express the query, and take any table or column name from a fixed allowlist in code.

Database Sharing Across Services

Prefer calling the owning service's API over sharing a database. Use SQLDatabase.named() only when sharing is explicitly intended, keep that access to reads where possible, and keep schema ownership and migrations in the owning service.

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

Migration Safety

• Leave migrations that have already been applied unchanged; add a new migration instead
• Have the user review any migration that drops, alters or rewrites data, whether hand written or generated, before committing it or starting the app
• Prefer additive, backward compatible schema changes
• Back up data before destructive changes
• Migrations apply on startup, which also affects shared and production environments

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

db.connectionString contains database credentials. Keep it out of logs, output, source files, commits and config files such as drizzle.config.ts, and pass it to the ORM at runtime only.

Install the Drizzle packages at pinned versions and commit the lockfile.

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

Have the user review generated migrations before committing them or starting the app, since migrations apply on startup.

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 send interpolated values as bind parameters:

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

Bind parameters cover values only. Choose table names, column names, sort directions and other SQL fragments from a hardcoded allowlist in code.

Some examples above select all columns for brevity. In real code, select only the columns you need so sensitive columns stay out of API responses.

Guidelines

• Prefer template literals for queries (values are sent as bind parameters); when raw methods are needed, keep the SQL constant and pass values as positional arguments
• Specify types with generics: query<User>, queryRow<User>
• Migrations are applied on startup, so have the user review destructive migrations before starting the app
• Select only the columns you need instead of selecting all columns
• 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