State and SQL

/data is a bind mount that outlives the container, so a SQLite database there survives deploys, sleeps and wakes. Bun ships the driver: nothing to install and nothing to provision. This is the whole setup:

import { Database } from "bun:sqlite";

const db = new Database(process.env.DATA_DIR + "/app.db");
db.run("create table if not exists todo(id integer primary key, txt text)");

Name it with a SQLite extension: .db, .sqlite or .sqlite3. Four things key off that: snapshots take SQLite's online backup, replication finds it, fork carries it, and sql can read it. Under any other name it is copied as a plain file that a live writer may be mid-transaction in, which looks fine until the first restore.

sql(app: "hn-digest")                                → tables and their CREATE statements
sql(app: "hn-digest", sql: "select count(*) from item")

sql reads that file from the host side of the mount, so it answers for a sleeping or crashed app without waking it, which is exactly when the data is most worth reading. Rows come back as rows with a count. The response is capped at 256 KB with a truncated flag and there is no pagination, so a limit in the statement is the caller's job. Pass file when an app keeps more than one database; if it does and none is named, the error lists them.

The database is made by your code, so a brand-new app has none until it has booted once, and sql says so rather than inventing one, because a database that springs into existence makes a typo in file look like it worked. To put a schema in first, ask for it outright with create and a file:

sql(app: "hn-digest", file: "app.db", create: true,
    sql: "create table item(id integer primary key, title text)")

After that first call it is an ordinary database that discovery finds on its own. Writes and migrations are always allowed and the only undo is a snapshot rollback, which is an operator action rather than a tool, so fork first when the migration is the thing you are unsure about. The database belongs to the app: destroyed with it, and a fork gets its own copy rather than a share.

Already have a database elsewhere? Set its connection string as a secret and sql answers for that too: Neon, Supabase, RDS, anything reachable over the internet that speaks Postgres. The secret's name becomes the database's name, and DATABASE_URL is found without being asked for.

set_secret(app: "hn-digest", key: "DATABASE_URL", value: "postgres://…")
sql(app: "hn-digest", file: "DATABASE_URL", sql: "select count(*) from item")

An app with both keeps both: the reply lists them under files and the console's SQL tab shows a picker. Three things differ. The connection is made from our box, so private, loopback and link-local addresses are refused and TLS is required whether or not the URL asks for it. That is the same reason a tunnel or a VPC-only endpoint will not work. Results cap at 1,000 rows as well as 256 KB. And there is no snapshot behind a database we do not host, so the fork-first advice above is not available: rolling back that migration is your provider's job, not ours.

Snapshots use SQLite's online backup, so they are consistent against a live writer, which is what makes it possible to clone an app that is serving traffic. They are taken on a timer, on sleep, and before every fork and promote. Durable storage adds continuous off-site replication within a minute, but only on a box that has off-site storage configured; where none is, the setting records the choice and replicates nothing, and the API says so.