---
name: runlot-database
description: Use the PostgreSQL database of a Runlot project — env.db (exec, tryExec, session), @runlot/pg's Pool, ORMs (Prisma, Drizzle, Kysely, TypeORM, Sequelize, Knex), migrations in migrations/NNNN_*.sql, psql and runlot db commands, backups and point-in-time restore, and database errors such as 57014 (10-second statement limit), 53300, 57P03 and DatabaseStarting. Use when writing SQL or schema changes for a Runlot app, choosing a driver or ORM, running or debugging migrations, restoring data, or a Runlot database call fails.
---

# The Runlot database

Each project has one PostgreSQL-compatible database, created the first time the app uses one.
The worker reaches it as `env.db` — no connection string, no password, no pool to size. It runs
on the same machine as the app, so a call has no network round trip.

Docs: https://docs.runlot.io/docs/data/database/index.mdx

## Write queries

```ts
// Plain binding: rows back, throws on error
const rows = await env.db.exec("select id, title from posts where owner_id = $1", [user.id]);

// Need the SQLSTATE? tryExec returns it instead of throwing
const r = await env.db.tryExec("insert into users (email) values ($1)", [email]);
if (!r.ok && r.error.code === "23505") return new Response("Already registered", { status: 409 });
```

Or the node-postgres API, which also gives `err.code` and `Date` objects:

```ts
import { Pool } from "@runlot/pg";
const pool = new Pool({ db: env.db });        // `db` is the only option that matters
const { rows } = await pool.query("select * from posts where id = $1", [id]);
```

`import { Pool } from "pg"` resolves to `@runlot/pg` in a Runlot build. Each `pool.query` is its
own session; use `pool.connect()` (or `env.db.session()`) for a transaction. ORM setup:
[references/drivers-and-orms.md](references/drivers-and-orms.md). Value mapping and sessions:
[references/env-db.md](references/env-db.md).

## Change the schema

```text
migrations/
  0001_init.sql
  0002_posts.sql
```

`runlot deploy` applies new files in order, each in one transaction, and records them in
`schema_migrations`. `runlot db migrate` applies them without a deploy. Never edit an applied
file — add the next number. Details: [references/migrations.md](references/migrations.md).

## Look at the data

```bash
runlot db execute -c "select count(*) from posts"
runlot db shell                      # psql; arguments after -- go to psql
runlot db connect                    # host, user, password for another client
```

With the MCP server, `runlot_pg_execute` runs SQL read-only unless the call passes `allow_write`.

## Limits that shape the code

| Limit | Value | Consequence |
|---|---|---|
| One statement | 10 s, cannot be raised | `57014`; batch by key range, aggregate then join |
| Waiting for a connection | 10 s | `53300`; too many concurrent calls or long transactions |
| Size | 500 MiB Free, 8 GiB Pro | Notified at 90 %; budget 10–15× the raw data for indexes |
| Engine | One statement at a time per project | A slow query delays every other request |

Errors and their fixes: [references/errors.md](references/errors.md).

## Backups

Automatic, about once a minute while the database is in use; any commit in the last 24 hours
is a restore point. `runlot db backups` lists them; `runlot db restore <epoch>/<seq>` (or
`latest`, `txn:<id>`, a UTC time) restores after taking a backup of the current state. See
[references/backups.md](references/backups.md).

## Do

- Parameterise every value (`$1`, `$2`); never build SQL by concatenation.
- Treat `int8`, `numeric` and timestamps from `env.db.exec` as strings (they are never rounded).
- Keep transactions short and inside one session; commit explicitly — `close()` rolls back.
- Put files and images in `env.storage`, not in `bytea` columns.
- Read `runlot health` (`db_admission`, `db_size`) when the database is slow or growing.

## Don't

- Don't open a TCP connection with a URL from `runlot db connect` inside the worker; use `env.db`.
- Don't run `prisma migrate deploy` or `drizzle-kit push` against production; generate SQL into `migrations/`.
- Don't rely on `SET statement_timeout` to raise the 10-second ceiling (it can only lower it).
- Don't use `--db shared` previews to test migrations.
- Don't bind more than two other databases in `databases`, and don't expect a preview to get
  them (previews skip bound databases). Databases belong to the organization: `runlot db list`
  shows them, `runlot db create <name>` makes one that no app is named after (Pro), and a
  project reads another with `"databases": [{"binding": "shop", "database": "shop"}]` in its
  runlot.json — the deploy writes the binding. Deleting a project keeps its database.