# Database

> The database inside the runtime: schema from files, queries from functions.

[HTML version](https://snoozestack.com/docs/database/) · [Docs index](https://snoozestack.com/llms.txt)

Every project's database lives *inside* its [runtime](https://snoozestack.com/docs/runtime.md): a database whose shape comes from your committed [schema](https://snoozestack.com/docs/schema.md), whose only reader and writer is a [function](https://snoozestack.com/docs/functions.md) holding the `db` capability, and whose state — local, per preview, and live — is isolated per namespace. There is no database server to connect to and no client-facing query API to secure.

**Why it lives in the runtime**

The queries your functions run are in-process reads against local state — no connection pool, no network hop, no N+1 latency cliff. That is not a toy configuration: you get ordinary SQL and real transactional semantics without the operational surface of a database server, and hosted state is continuously replicated to durable storage rather than living on one disk.

It also keeps the whole backend portable. Your data sits under `.snoozestack/` next to your storage and signals — one directory that runs identically on your machine, in a container, and hosted, and one directory to copy when you want your data somewhere else. It is a genuine exit, not an export in someone's bespoke shape.

### Reading and writing it

A function granted the `db` capability gets one method — a parameterized, single-statement query:

```ts title="snoozestack/functions/notes/index.ts"
export default async function handler(req: Request, capabilities) {
  const user = capabilities.auth.user;
  if (!user) return new Response("unauthorized", { status: 401 });

  const { rows, columns } = capabilities.db.query(
    "select id, body, created_at from notes where user_id = ? order by created_at desc",
    [user.id],
  );
  return Response.json({ columns, rows });
}
```

Writes return `{ changes, lastInsertRowid }` instead of rows. One statement per call is enforced — a second `;`-separated statement is an error, not a silent no-op — which is what makes string-building attacks against this surface boring. Always pass values as `params`, never interpolated into the SQL.

### Looking at the rows yourself

While you're building, you usually just want to see what a function actually wrote. The console shows it at **Database → Data**, and the CLI prints the same rows:

```bash title="reading rows"
snoozestack data tables                  # every table, with row and column counts
snoozestack data rows notes              # one page of rows (100), tab-separated
snoozestack data rows notes --all        # every page
snoozestack data rows notes --json | jq '.rows[0]'
snoozestack data rows notes --preview ada    # that preview's own database
```

This reads over the [management API](https://snoozestack.com/docs/management-api.md), so it uses a management credential — your own login, or a revocable project token from `snoozestack projects tokens create` for CI. Deliberately *not* the project's anon or service key: those are app credentials, and the project's own API has no table endpoint for them to unlock. A client reaches data only through a [function](https://snoozestack.com/docs/functions.md) you committed, which is what makes a table holding third-party access tokens unreadable to anyone holding a key — see [Permissions](https://snoozestack.com/docs/permissions.md). Reads made with a project token are recorded in the project's [audit history](https://snoozestack.com/docs/portal-tour.md).

| Surface | Availability |
| --- | --- |
| Portal UI | Read-only **Database → Schema** (tables, columns, indexes) and **Database → Data** (browse rows) — in both the hosted console and the local dashboard |
| CLI | `snoozestack data tables` / `snoozestack data rows <table>` to read rows; `snoozestack schema build` for the schema, which is a committed file — see [Schema](https://snoozestack.com/docs/schema.md) |
| SDK / HTTP | No direct database client, by design — apps call a [function](https://snoozestack.com/docs/functions.md) with `functions.invoke()` |

### Where the data lives

- **Locally**: `.snoozestack/dev.db`, built from your schema on first run and kept in sync as you edit it. `snoozestack dev reset` throws it away and rebuilds (re-running your committed `seed.mjs`, if any).
- **Hosted**: inside the project's runtime container, on a volume replicated continuously to durable storage — restore and host-failure recovery are platform responsibilities, not yours.
- **Per namespace**: live (`default`) and each named preview hold separate databases, so a preview publish can be exercised against its own data.

### Your schema isn't locked in

The `defineTable()` vocabulary is deliberately portable: every type has a defined mapping in more than one SQL engine, so the schema describes your data rather than an engine. If your product ever outgrows what the runtime provides, the schema — and the ordinary SQL your functions already write — travels with it.