> ## Documentation Index
> Fetch the complete documentation index at: https://vetta.sh/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# database

> A fullstack app's managed database — client.database.

Every `fullstack` [app](/docs/sdk/apps) has a managed Postgres database, and `client.database` reaches it
without a connection string: SQL, the table catalogue, a table-level REST facade, and migrations.
Four methods and five verbs on `from(table)`. API detail: [Database](/docs/api/database).

Every method takes an optional `{ app }` — an `app_` id or an app name. **Omit it when the
organization has one `fullstack` app** and that app is used. With several, the call fails with
`ambiguous_app` and the message lists the candidates; with none, `not_found`.

```ts theme={"system"}
import { createClient } from "@usenaive-sdk/vetta";
const client = createClient({ apiKey: process.env.VETTA_API_KEY! });

await client.database.migrate("0001_todos", "create table todos (id serial primary key, title text, done bool default false)");
await client.database.from("todos").insert([{ title: "ship it" }]);
const open = await client.database.from("todos").select("id,title", { done: false });
```

## query

```ts theme={"system"}
client.database.query(sql: string, options?: { app?: string }): Promise<{ object: "app_db_result"; rows: unknown[]; row_count: number }>
```

`POST /v1/apps/{app}/db/query`. Arbitrary SQL, rows back as JSON.

## tables

```ts theme={"system"}
client.database.tables(options?: { app?: string }): Promise<DbTable[]>
```

`GET /v1/apps/{app}/db/tables`. Each `DbTable` is `{ object: "db_table", name, schema, rows, size_bytes }`; `rows` and `size_bytes` are estimates and may be `null`.

## from

```ts theme={"system"}
client.database.from(table: string, options?: { app?: string }): {
  select(columns?: string, filter?: DbFilter, page?: { order?: string; limit?: number; offset?: number }): Promise<unknown[]>;
  insert(rows: Record<string, unknown>[]): Promise<unknown[]>;
  upsert(rows: Record<string, unknown>[], options?: { onConflict?: string }): Promise<unknown[]>;
  update(patch: Record<string, unknown>, filter: DbFilter): Promise<unknown[]>;
  delete(filter: DbFilter): Promise<unknown[]>;
}
```

The verbs over `/v1/apps/{app}/db/rest/{table}`: `GET`, `POST`, `PATCH`, `DELETE`. Writes ask for
the affected rows back (`Prefer: return=representation`), so each promise resolves to the rows the
database saw — `insert` to what it inserted, `update` and `delete` to what they touched. `upsert` is
a `POST` that merges duplicates instead of refusing them (`Prefer: resolution=merge-duplicates`): on
the primary key by default, or on the unique columns `onConflict` names (`"email"`, `"org_id,slug"`
→ `?on_conflict=`). The REST layer's own `PUT` (one row, every key column repeated in the filter)
and `HEAD` (a count with no body) have no SDK verb: `upsert` covers what `PUT` does, a count is
`database.query("select count(*) from …")`, and a client that already speaks that dialect calls the
[REST route](/docs/api/database) directly.

A `DbFilter` is `column → value`. A bare string, number or boolean means equality; `null` means
`is.null`; an array means `in.(…)`; and a string that begins with an operator is passed through as
is — `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `like`, `ilike`, `is`, `in`, and `not.` in front of any
of them. `select`'s third argument orders and pages: `order` is `column`, `column.desc`, or
`column.asc.nullslast`; `limit` and `offset` are row counts.

```ts theme={"system"}
await client.database.from("todos").select("*", { done: false });                 // done=eq.false
await client.database.from("todos").select("*", {}, { order: "id.desc", limit: 5 }); // order=id.desc&limit=5
await client.database.from("todos").select("id", { id: [1, 2, 3] });              // id=in.(1,2,3)
await client.database.from("todos").update({ done: true }, { id: 7 });            // id=eq.7
await client.database.from("todos").delete({ created_at: "lt.2026-01-01" });      // passed through
await client.database.from("todos").select("*", { owner: null });                 // owner=is.null
```

<Warning>
  `from()` runs as the database's service role: the row-level security policies your app enforces for
  its own users do not apply. Every call is recorded in the audit log with its method and path.
</Warning>

## migrate

```ts theme={"system"}
client.database.migrate(name: string, sql: string, options?: { app?: string }): Promise<DbMigration>
```

`POST /v1/apps/{app}/db/migrations`, with the idempotency key the client mints for every write. A
migration runs **once per name**: a repeat with the same SQL resolves to the existing row without
running anything, and a repeat with different SQL under the same name rejects with `version_conflict`.

## migrations

```ts theme={"system"}
client.database.migrations(options?: { app?: string }): Promise<DbMigration[]>
```

`GET /v1/apps/{app}/db/migrations`. Each `DbMigration` is `{ id, object: "db_migration", name, sha256, applied_at, statements }`, in the order applied.