# Databases

> A SQL database for an app or an agent. SQLite by default, Postgres when you outgrow it, with migrations, restores and branches.

A **database** is a SQL database NoClick runs for you: a SQLite database by default, which costs almost nothing while it's idle, or Postgres when you need its features or its size. An app keeps its data in one, and an agent can keep its own state in one the same way. Your code talks to it over the API, so there's no connection pool to manage and nothing to reach from inside a private network.

## Create a database

Name it in your code: `from_name` with `create_if_missing=True` creates the database the first time it's used, and finds it every time after.

```python
import noclick

db = noclick.Database.from_name("orders", create_if_missing=True)
db.execute("create table if not exists orders (id text primary key, item text, qty integer)")
```

```ts
import { Database } from "noclick";

const db = Database.fromName("orders", { createIfMissing: true });
await db.execute("create table if not exists orders (id text primary key, item text, qty integer)");
```

`create` makes one outright, and raises `noclick.Conflict` if the name is taken (its `existing_id` names the one that's there):

```python
analytics = noclick.Database.create("analytics", engine="postgres")
```

```ts
import { Database } from "noclick";

const analytics = await Database.create("analytics", { engine: "postgres" });
```

| Engine | Good for | Size |
| --- | --- | --- |
| `"sqlite"` (default) | an app's or an agent's own data; one database per user | up to 10 GB |
| `"postgres"` | large data, many writers, [search by meaning](https://www.noclick.com/docs/guide/databases.md#search-by-meaning), tools that connect over the wire | no fixed limit |

A name is unique for each user, and for your account. Created inside `as_user`, a database belongs to that user and bills to it, which is how you give each of your users a database of their own. See [Users](https://www.noclick.com/docs/guide/users.md).

`region=` keeps its data in a [region](https://www.noclick.com/docs/guide/regions.md), for good (your account's default, else `us-east`); a branch is in its source's. Put a database beside the agent or app that uses it: `region=agent.region`. A Postgres database runs in its region's data centers. A SQLite database in `eu` keeps its data in the European Union; in `us-east` or `us-west`, in the United States, in the east or the west as capacity allows; in `ap`, in Asia-Pacific as capacity allows.

## Read with query

`query` runs a read and gives you the rows as dicts. Pass values as parameters, never inside the SQL string: `?` placeholders take a list, `:name` placeholders take a dict, on either engine.

```python
import noclick

db = noclick.Database.from_name("orders")
for row in db.query("select id, item, qty from orders where qty > ?", [2]):
    print(row["id"], row["item"], row["qty"])

result = db.query("select count(*) as n from orders where item = :item", {"item": "bun"})
print(result.rows[0]["n"], result.rows_read, result.duration_ms)
```

```ts
import { Database } from "noclick";

const db = Database.fromName("orders");
for (const row of await db.query("select id, item, qty from orders where qty > ?", [2])) {
  console.log(row.id, row.item, row.qty);
}

const result = await db.query("select count(*) as n from orders where item = :item", { item: "bun" });
console.log(result.rows[0].n, result.rowsRead, result.durationMs);
```

A `QueryResult` has the `rows`, the `columns` in order, and what the statement touched (`rows_read`, `rows_written`). Iterating it yields the rows. A `datetime` parameter is sent as an ISO timestamp, and an integer parameter is exact across the whole 64-bit range on both engines. On SQLite the counts are the host's own, and `rows_written` is what a write is [billed](https://www.noclick.com/docs/guide/databases.md#limits-and-billing) on: a row written counts once for its table and once for each index the write changes, so inserting one row into a table keyed by a `text primary key` writes 2, and so does an `update` of a column you indexed. An `integer primary key` is the row's own id, not an index, so an insert there writes 1, as it does into a table declared `without rowid`, which keeps its rows in its primary key's index; an `insert or ignore` that was ignored writes 0.

`query` and `execute` each take one statement; send several in a [batch](https://www.noclick.com/docs/guide/databases.md#write-with-execute-and-batch). On Postgres, `?` is also a JSON operator, so it's a placeholder only when `params` is a list: with a list, write `jsonb_exists(doc, 'key')` instead of `doc ? 'key'`.

Values come back as JSON: a BLOB (`bytea` on Postgres) as base64 text, a timestamp in ISO 8601. On SQLite an integer past 2^53 comes back rounded, so select `cast(id as text)` to keep it exact. On Postgres a `numeric` comes back as a number when one holds it exactly, else as text, and a vector as its text (`[0.1,0.2,0.3]`).

`query` runs read-only: a statement that would change anything raises `noclick.InvalidRequest`, so it's safe to hand a read to an agent or a dashboard. A result is at most 10 MB, and a statement stops after 30 seconds; page through big tables with `limit` and a `where` on the last key you saw.

## Write with execute and batch

`execute` runs one statement that writes: an `insert`, `update`, `delete`, or a change to the schema.

```python
done = db.execute("insert into orders (id, item, qty) values (?, ?, ?)", ["o1", "bun", 3])
print(done.rows_written, done.last_row_id)
```

```ts
const done = await db.execute("insert into orders (id, item, qty) values (?, ?, ?)", ["o1", "bun", 3]);
console.log(done.rowsWritten, done.lastRowId);
```

`last_row_id` is the rowid an insert made on SQLite; on Postgres, add `returning id` and read it from `done.rows`.

A list passed as a parameter binds, on Postgres, as an array (`numeric[]` for numbers, `text[]` for text), and on SQLite as its JSON text.

`batch` runs up to 100 statements in one transaction, so they all apply or none do:

```python
db = noclick.Database.from_name("booking-db")
moved, freed = db.batch([
    ("update bookings set slot = ? where id = ?", ["18:30", "b_41"]),
    ("update slots set taken = 0 where time = ?", ["18:00"]),
])
```

```ts
import { Database } from "noclick";

const db = Database.fromName("booking-db");
const [moved, freed] = await db.batch([
  ["update bookings set slot = ? where id = ?", ["18:30", "b_41"]],
  ["update slots set taken = 0 where time = ?", ["18:00"]],
]);
```

Over HTTP, `query`, `execute` and `batch` are one call, `POST /v1/databases/{id}/query`: `{"sql", "params"}` runs one statement, `{"statements": [{"sql", "params"}, ...]}` runs a batch (a `batch_result`, whose `results` hold one per statement), and `"read_only": true` refuses a statement that writes, as `query` does. Through the [MCP server](https://www.noclick.com/docs/guide/mcp.md) it's `api_call` with the operation `databases_query` and the same arguments, plus `database_id`.

### When the database doesn't answer

Now and then a database doesn't answer a request: a connection drops, or its host is briefly busy. NoClick sends it again for you, trying up to 3 times, when that can't apply it twice: a read always, a SQLite write too (each carries a key the database keeps for 10 minutes, so a write sent again runs once and the repeat gets the first run's answer), and a Postgres write only when the connection couldn't be made, so nothing was sent.

When every try fails, the call raises `noclick.UpstreamError` (HTTP 503), which says what happened:

| Field | What it is |
| --- | --- |
| `applied` | `False` when nothing was changed: a read, or a write the database never ran. The SDK has already sent it again (the same `Idempotency-Key` runs it again, rather than answering the failure), and you can too. `None` when NoClick can't tell whether the write ran, and the message says so. `True`, rarely, when it ran and its answer was lost (a result over 4 KB, too large to keep for a repeat): read what it wrote rather than sending it again. |
| `upstream_attempts` | How many times NoClick sent it to the database. |
| `attempts` | How many times the SDK sent the call, its own retries included. |

So the write you have to think about is one that may have run. Write it so a second run changes nothing, and retrying it is safe whatever `applied` says: give each row a key your code chooses and insert with `on conflict do nothing`, and set values (`set qty = ?`) rather than add to them (`set qty = qty + 1`).

```python
import noclick

db = noclick.Database.from_name("inbox")
save = ("insert into messages (id, body) values (?, ?) on conflict (id) do nothing", [message_id, body])
try:
    db.execute(*save)
except noclick.UpstreamError:
    # Safe whether or not it ran (the error's applied): a second run of this insert changes nothing.
    db.execute(*save)
```

```ts
import { Database, UpstreamError } from "noclick";

const db = Database.fromName("inbox");
const save = () => db.execute("insert into messages (id, body) values (?, ?) on conflict (id) do nothing", [messageId, body]);
try {
  await save();
} catch (err) {
  if (!(err instanceof UpstreamError)) throw err;
  await save(); // safe whether or not it ran (err.applied): a second run of this insert changes nothing
}
```

A write that isn't safe to run twice (a counter, a transfer between two rows) is one to send again only when `applied` is `False`; when it's `None`, read the rows it would have changed first.

## Migrate the schema

Keep your schema in numbered `.sql` files next to your code and run `migrate` on every deploy. It applies only the ones the database hasn't run yet, in name order:

```
migrations/
  0001_init.sql
  0002_add_slots.sql
```

```python
db = noclick.Database.from_name("booking-db", create_if_missing=True)
result = db.migrate("migrations/")
print(result.applied)   # ['0002_add_slots.sql'], or [] when there was nothing new
```

```ts
import { readdir, readFile } from "node:fs/promises";
import { Database } from "noclick";

// The TypeScript client takes the migrations themselves, by name: read the folder first.
async function readMigrations(dir: string): Promise<Record<string, string>> {
  const names = (await readdir(dir)).filter((name) => name.endsWith(".sql"));
  return Object.fromEntries(await Promise.all(names.map(async (name) => [name, await readFile(`${dir}/${name}`, "utf8")])));
}

const db = Database.fromName("booking-db", { createIfMissing: true });
const result = await db.migrate(await readMigrations("migrations"));
console.log(result.applied);   // ['0002_add_slots.sql'], or [] when there was nothing new
```

The files are read on your machine and sent together. A dict works the same way (`{"0001_init.sql": "create table ..."}`), for code that builds its schema in memory. The new migrations run in one transaction: if one fails, none of them apply and `noclick.InvalidRequest` names the one that failed. Each applied migration is remembered with a checksum of its SQL, so editing one that already ran raises `noclick.Conflict`; add a new file instead.

`dry_run=True` lists what would run (in `pending`) and changes nothing. `migrations()` lists what has run:

```python
for migration in db.migrations():
    print(migration.name, migration.applied_at)
```

```ts
for (const migration of await db.migrations()) {
  console.log(migration.name, migration.appliedAt);
}
```

### Seed a preview

A `seed.sql` beside the migrations directory (`db/seed.sql` beside `db/migrations/`, as Supabase lays them out) is sent with them, or name one with `seed=`. It runs once, after the migrations and in their transaction, on an app preview's database that holds none of production's data, so the preview has rows to show; it shows in `applied` and the ledger as `(seed)`. Anywhere else, production included, it's ignored. See [A preview's own data](https://www.noclick.com/docs/guide/apps.md#a-preview-s-own-data).

```python
db.migrate("db/migrations/", seed="db/demo.sql")
```

```ts
import { readFile } from "node:fs/promises";

await db.migrate(await readMigrations("db/migrations"), { seed: await readFile("db/demo.sql", "utf8") });
```

## See the schema

```python
for table in db.schema():
    print(table.name, table.row_count, [column.name for column in table.columns])
```

```ts
for (const table of await db.schema()) {
  console.log(table.name, table.rowCount, table.columns.map((column) => column.name));
}
```

Each `Table` has its `columns` in order (with `type`, `nullable`, `primary_key` and `default`) and a `row_count`, which is exact on SQLite and the planner's estimate on Postgres: `None` until Postgres has one, which it makes once the table is analyzed (by autovacuum after enough writes, or `analyze`).

## Restore to an earlier time

A SQLite database keeps every change for 30 days, a Postgres database for 7. `restore` puts the database back as it was at a moment in that window, in place: whatever was written since is gone. It's an [operation](https://www.noclick.com/docs/guide/operations.md):

```python
from datetime import datetime, timedelta, timezone

import noclick

db = noclick.Database.from_name("booking-db")
before = datetime.now(timezone.utc) - timedelta(hours=2)
restored = db.restore(at=before).wait()
```

```ts
import { Database } from "noclick";

const db = Database.fromName("booking-db");
const before = new Date(Date.now() - 2 * 60 * 60 * 1000);
const restored = await (await db.restore({ at: before })).wait();
```

While it runs, the database's `status` is `restoring` and queries raise `noclick.Conflict`. To look at the past without losing the present, branch at that time instead.

A new SQLite database has no history for its first minute or two: a restore, a branch `at` a moment or undoing a migration then fails with `noclick.Conflict`, and works a couple of minutes later.

## Branch a copy

`branch` copies the database into a new one: as it is now, or as it was `at` a moment in its history (30 days on SQLite, 7 on Postgres). Use a branch to try a migration before it reaches production, or to recover a table someone dropped. An app's previews get their own copies without this (see [A preview's own data](https://www.noclick.com/docs/guide/apps.md#a-preview-s-own-data)).

```python
db = noclick.Database.from_name("booking-db")
preview = db.branch("booking-db-preview").wait()
preview.migrate("migrations/")
```

```ts
import { Database } from "noclick";

const db = Database.fromName("booking-db");
const preview = await (await db.branch("booking-db-preview")).wait();
await preview.migrate(await readMigrations("migrations"));
```

A branch is a database of its own, for the same user: `parent_id` names where it came from, `db.branches()` lists them, and deleting a branch never touches its source. A database that has branches can't be deleted until they are.

A Postgres branch shares its source's storage until either changes, so it's made in moments whatever the size. A database and its branches (a preview's copies among them) include 25 together; each branch past that costs $1.50 a month, by the hour it exists, charged to the database they all came from. A SQLite database is copied, and while that runs its `status` is `branching` and statements raise `noclick.Conflict`.

## Move to Postgres

A SQLite database stops growing at 10 GB: a write that would pass it raises `noclick.QuotaExceeded`, whose next step is to branch into Postgres. Branch with `engine="postgres"`, then point your app at the new database once it's ready (invite-only beta: without access, this branch answers `noclick.NotAvailable`):

```python
db = noclick.Database.from_name("booking-db")
bigger = db.branch("booking-pg", engine="postgres").wait()
print(bigger.engine)   # postgres
```

```ts
import { Database } from "noclick";

const db = Database.fromName("booking-db");
const bigger = await (await db.branch("booking-pg", { engine: "postgres" })).wait();
console.log(bigger.engine);   // postgres
```

The copy is a snapshot: writes to the SQLite database after the branch started aren't in it, so pause writes (or branch again) before you switch.

## Search by meaning

A Postgres database also searches by meaning: every one has the `vector` extension (pgvector) turned on, so each row's embedding sits in a column beside it, and one query can filter, join and rank. That covers a help center, each customer's documents or an agent's notes, into the millions of rows, with no separate search service to keep in step. A SQLite database has no vector search: create one with `engine="postgres"`, or [branch into Postgres](https://www.noclick.com/docs/guide/databases.md#move-to-postgres).

Store the embedding next to the text, with a keyword column for hybrid search, and index both:

```python
import noclick

kb = noclick.Database.from_name("help-center", create_if_missing=True, engine="postgres")
kb.batch([
    """create table if not exists articles (
        id text primary key,
        user_id text,
        title text not null,
        body text not null,
        embedding halfvec(1536),
        search tsvector generated always as (to_tsvector('english', title || ' ' || body)) stored
    )""",
    "create index if not exists articles_embedding on articles using hnsw (embedding halfvec_cosine_ops)",
    "create index if not exists articles_search on articles using gin (search)",
    "create index if not exists articles_user_id on articles (user_id)",
])
```

```ts
import { Database } from "noclick";

const kb = Database.fromName("help-center", { createIfMissing: true, engine: "postgres" });
await kb.batch([
  `create table if not exists articles (
    id text primary key,
    user_id text,
    title text not null,
    body text not null,
    embedding halfvec(1536),
    search tsvector generated always as (to_tsvector('english', title || ' ' || body)) stored
  )`,
  "create index if not exists articles_embedding on articles using hnsw (embedding halfvec_cosine_ops)",
  "create index if not exists articles_search on articles using gin (search)",
  "create index if not exists articles_user_id on articles (user_id)",
]);
```

Embed with [`AI.embed`](https://www.noclick.com/docs/guide/ai.md#embeddings) and write the vectors as lists; `cast(? as halfvec)` turns a list into a vector:

```python
import noclick

MODEL = "openrouter/openai/text-embedding-3-small"
articles = [
    ("refunds", "Refunds", "Refunds reach your card in 5 to 10 days."),
    ("shipping", "Shipping", "We ship worldwide within 3 days."),
]
kb = noclick.Database.from_name("help-center")
vectors = noclick.AI.embed([f"{title}\n{body}" for _, title, body in articles], model=MODEL).vectors
kb.batch([
    (
        "insert into articles (id, title, body, embedding) values (?, ?, ?, cast(? as halfvec)) "
        "on conflict (id) do update set title = excluded.title, body = excluded.body, embedding = excluded.embedding",
        [article_id, title, body, vector],
    )
    for (article_id, title, body), vector in zip(articles, vectors)
])
```

```ts
import { AI, Database } from "noclick";

const MODEL = "openrouter/openai/text-embedding-3-small";
const articles = [
  ["refunds", "Refunds", "Refunds reach your card in 5 to 10 days."],
  ["shipping", "Shipping", "We ship worldwide within 3 days."],
];
const kb = Database.fromName("help-center");
const { vectors } = await AI.embed(articles.map(([, title, body]) => `${title}\n${body}`), { model: MODEL });
await kb.batch(articles.map(([articleId, title, body], i) => [
  "insert into articles (id, title, body, embedding) values (?, ?, ?, cast(? as halfvec)) " +
    "on conflict (id) do update set title = excluded.title, body = excluded.body, embedding = excluded.embedding",
  [articleId, title, body, vectors[i]],
] as const));
```

Search by ordering on the distance (`<=>` is cosine distance, smaller is closer). The `order by ... limit` shape is what lets the index answer:

```python
import noclick

MODEL = "openrouter/openai/text-embedding-3-small"
kb = noclick.Database.from_name("help-center")
[vector] = noclick.AI.embed("how long do refunds take", model=MODEL).vectors
for row in kb.query(
    "select id, title from articles order by embedding <=> cast(:v as halfvec) limit 5",
    {"v": vector},
):
    print(row["id"], row["title"])
```

```ts
import { AI, Database } from "noclick";

const MODEL = "openrouter/openai/text-embedding-3-small";
const kb = Database.fromName("help-center");
const [vector] = (await AI.embed("how long do refunds take", { model: MODEL })).vectors;
for (const row of await kb.query(
  "select id, title from articles order by embedding <=> cast(:v as halfvec) limit 5",
  { v: vector },
)) {
  console.log(row.id, row.title);
}
```

### Embed inside SQL

Every NoClick Postgres database has one function of NoClick's own, `noclick.embed(input text, model text default null) returns halfvec`: it turns text into a vector inside the statement, with the same models as `AI.embed` (NoClick's default embedding model when `model` is null). So one statement searches by meaning, with no vector to make first:

```python
import noclick

kb = noclick.Database.from_name("help-center")
for row in kb.query(
    "select id, title from articles "
    "order by embedding <=> noclick.embed(:q, 'openrouter/openai/text-embedding-3-small') limit 5",
    {"q": "how long do refunds take"},
):
    print(row["id"], row["title"])
```

```ts
import { Database } from "noclick";

const kb = Database.fromName("help-center");
for (const row of await kb.query(
  "select id, title from articles " +
    "order by embedding <=> noclick.embed(:q, 'openrouter/openai/text-embedding-3-small') limit 5",
  { q: "how long do refunds take" },
)) {
  console.log(row.id, row.title);
}
```

- It takes its text as a parameter (`:q`, `?`) or a quoted string, and its model as a parameter, a quoted string or null. It can't embed a column (`noclick.embed(body)`): embed the rows with `AI.embed` in your code and store the vectors, as above.
- NoClick embeds the text before the statement runs, so it works in `query`, `execute`, `batch`, an agent's tools and an app's binding, and a read-only `query` can search by meaning on its own. Over a [connection URL](https://www.noclick.com/docs/guide/databases.md#connect-other-tools) the function is there but answers with an error saying so: embed in your code and bind the vector there.
- Each distinct text is embedded once per request, however many rows it's compared with; a request may embed up to 100 distinct texts, and one that tries more fails with `noclick.QuotaExceeded`.
- It's billed as AI (`AI.embed`'s price for that model) to the user the database belongs to, and counts toward their spend cap.
- Name the model the column was embedded with: vectors from different models can't be compared.

It doesn't exist on SQLite, which has no vector search.

Hybrid search finds what meaning alone misses, like an error code or a product name. Rank by both and fuse the two rankings (reciprocal rank fusion), in one query:

```python
import noclick

MODEL = "openrouter/openai/text-embedding-3-small"
HYBRID = """
with semantic as (
    select id, row_number() over (order by embedding <=> cast(:v as halfvec)) as rank
    from articles
    where user_id is null or user_id = :user_id
    order by embedding <=> cast(:v as halfvec)
    limit 20
), keyword as (
    select id, row_number() over (order by ts_rank_cd(search, q) desc) as rank
    from articles, websearch_to_tsquery('english', :text) q
    where search @@ q and (user_id is null or user_id = :user_id)
    order by ts_rank_cd(search, q) desc
    limit 20
)
select a.id, a.title, coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) as score
from semantic s full join keyword k using (id) join articles a using (id)
order by score desc
limit 5
"""

kb = noclick.Database.from_name("help-center")
text = "refund to my card"
[vector] = noclick.AI.embed(text, model=MODEL).vectors
for row in kb.query(HYBRID, {"v": vector, "text": text, "user_id": "acme"}):
    print(row["title"], row["score"])
```

```ts
import { AI, Database } from "noclick";

const MODEL = "openrouter/openai/text-embedding-3-small";
const HYBRID = `
with semantic as (
    select id, row_number() over (order by embedding <=> cast(:v as halfvec)) as rank
    from articles
    where user_id is null or user_id = :user_id
    order by embedding <=> cast(:v as halfvec)
    limit 20
), keyword as (
    select id, row_number() over (order by ts_rank_cd(search, q) desc) as rank
    from articles, websearch_to_tsquery('english', :text) q
    where search @@ q and (user_id is null or user_id = :user_id)
    order by ts_rank_cd(search, q) desc
    limit 20
)
select a.id, a.title, coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) as score
from semantic s full join keyword k using (id) join articles a using (id)
order by score desc
limit 5
`;

const kb = Database.fromName("help-center");
const text = "refund to my card";
const [vector] = (await AI.embed(text, { model: MODEL })).vectors;
for (const row of await kb.query(HYBRID, { v: vector, text, user_id: "acme" })) {
  console.log(row.title, row.score);
}
```

A few things keep it fast and right:

- **Use `halfvec`.** It stores each number in 2 bytes, half the memory of `vector`, with little effect on search quality for most embedding models, and indexes up to 4,000 dimensions (`vector` indexes 2,000). A 3,072-dimension model needs `halfvec`, or a shorter vector through `dimensions=` on `AI.embed`.
- **One model per column.** Vectors from different models can't be compared: keep the model beside the table, and re-embed every row when you change it.
- **Filters return full results.** Every NoClick Postgres database sets `hnsw.iterative_scan = relaxed_order`, so a search with a `where` still returns `limit` rows instead of fewer; they come back in very nearly the right order, so sort them again in an outer query when exact order matters.
- **Rows per user.** With an index on `user_id`, a user with up to about 20,000 rows is searched exactly, which Postgres chooses on its own, in tens of milliseconds. To keep users apart entirely, give each one a database of its own, created inside `as_user`.
- **Big loads.** Load first and create the vector index afterwards: it builds much faster, and building takes about 4 GB of memory per million 1,536-dimension rows.
- **Rows that change often** (a view count, a status) belong in another table: every update to a row re-writes it into the vector index.
- **Keyword ranking** with `ts_rank_cd` counts matches in each row; it isn't BM25, which also weighs how rare a word is. Fused with the vector ranking, it's enough for most search.

A small knowledge base, up to about 200,000 tokens, doesn't need search at all: put it in the agent's instructions or the prompt.

## Connect other tools

A Postgres database also speaks the wire protocol, for `psql`, an ORM, or a BI tool. `connection_url` gives a URL whose password stops working after `ttl_s` seconds, and `read_only=True` connects as a role that can only read:

```python
import psycopg

import noclick

db = noclick.Database.from_name("analytics")
with psycopg.connect(db.connection_url(ttl_s=600, read_only=True)) as conn:
    print(conn.execute("select count(*) from events").fetchone())
```

```ts
import pg from "pg";
import { Database } from "noclick";

const db = Database.fromName("analytics");
const conn = new pg.Client({ connectionString: await db.connectionUrl({ ttlS: 600, readOnly: true }) });
await conn.connect();
try {
  console.log((await conn.query("select count(*) from events")).rows[0]);
} finally {
  await conn.end();
}
```

The URL carries its credentials, so treat it as a secret. When it expires, its login stops working and any session still open on it ends. A Postgres database idle for a few minutes wakes on its next query, which takes a moment longer. A SQLite database has no wire protocol and raises `noclick.NotAvailable`: use `query` and `execute`.

An app running in a container that binds the database gets a URL of its own with each release, as `DATABASE_URL`, which lasts as long as the release does: see [A container's DATABASE_URL](https://www.noclick.com/docs/guide/apps.md#a-container-s-database-url). Never save one of these URLs as an app's setting or secret: it's refused.

## Use it from an app or an agent

An app reaches its database through a binding: `app.bind("DB", db)` makes it available to the deployed code as `DB`, and `used_by` on the database lists the apps that bind it; the app's code can also find or make it by name with `from_name`. See [Apps](https://www.noclick.com/docs/guide/apps.md).

A preview's code gets its own copy at its first write and reads production's until then: on Postgres a copy-on-write branch of the data, on SQLite production's schema and migrations without its rows, filled by the [seed](https://www.noclick.com/docs/guide/databases.md#seed-a-preview), or with production's rows too when the code asks with `from_name(..., branch_data="copy")` (up to 200,000 rows and 16 MB, read at one moment without stopping production). Its migrations change its copy; production's change when production's code runs them, after you promote. See [A preview's own data](https://www.noclick.com/docs/guide/apps.md#a-preview-s-own-data).

An agent works on a database with the same standard operations. Put the database in the agent's `tools` and the model gets three tools named after it: `booking-db__query` (a read-only statement), `booking-db__execute` (a statement that writes) and `booking-db__schema` (its tables and columns). `only(...)` gives it some of them:

```python
import noclick

bookings = noclick.Database.from_name("booking-db")
concierge = noclick.Agent.from_name(
    "concierge",
    create_if_missing=True,
    instructions="You book tables. Check the bookings before you promise one.",
    tools=[bookings.only("query", "schema"), "search"],
)
```

```ts
import { Agent, Database } from "noclick";

const bookings = Database.fromName("booking-db");
const concierge = Agent.fromName("concierge", {
  createIfMissing: true,
  instructions: "You book tables. Check the bookings before you promise one.",
  tools: [bookings.only("query", "schema"), "search"],
});
```

The agent writes plain SQL with parameters, so it can filter, join and search by keyword; on Postgres, [`noclick.embed`](https://www.noclick.com/docs/guide/databases.md#embed-inside-sql) lets it search by meaning too. Its calls follow your [approval rules](https://www.noclick.com/docs/guide/policy.md) like any tool's, under the names `database.query`, `database.execute` and `database.schema`: `require_approval=["database.execute"]` makes every write wait for a person. The same refs work in a [toolkit](https://www.noclick.com/docs/guide/toolkits.md) and a [hosted MCP server](https://www.noclick.com/docs/guide/mcp-servers.md).

An agent that should only save what it finds needs no SQL at all. `only("upsert")` is a tool that writes one row of one table, inserting it or updating the row with the same primary key; its parameters are that table's columns, so the model fills in a form. Bind its table with `bind(...)`, and bind a column too to stamp every row with it; bound parameters leave the tool's schema, as for a [connection's operations](https://www.noclick.com/docs/guide/connections.md#fix-arguments-the-model-mustn-t-choose):

```python
import noclick

leads = noclick.Database.from_name("leads")
researcher = noclick.Agent.from_name(
    "researcher",
    create_if_missing=True,
    instructions="You research each company you're given and save what you find.",
    tools=["exa.search", leads.only("upsert").bind(table="enriched")],
)
```

```ts
import { Agent, Database } from "noclick";

const leads = Database.fromName("leads");
const researcher = Agent.fromName("researcher", {
  createIfMissing: true,
  instructions: "You research each company you're given and save what you find.",
  tools: ["exa.search", leads.only("upsert").bind({ table: "enriched" })],
});
```

Its calls are `database.upsert` to your approval rules. `db.only("query").bind(...)` pins a parameter of the other operations the same way.

An agent can use its own user's databases and your account's, never another user's. Give each user's agent a database of its own by creating it inside `as_user`:

```python
import noclick

with noclick.as_user("user_42"):
    state = noclick.Database.from_name("assistant-state", create_if_missing=True)
    state.execute("create table if not exists notes (id integer primary key, body text, created_at text)")
    assistant = noclick.Agent.from_name(
        "assistant", create_if_missing=True, instructions="You keep notes for the user.", tools=[state],
    )
```

```ts
import { Agent, Database, asUser } from "noclick";

await asUser("user_42", async () => {
  const state = Database.fromName("assistant-state", { createIfMissing: true });
  await state.execute("create table if not exists notes (id integer primary key, body text, created_at text)");
  const assistant = Agent.fromName("assistant", {
    createIfMissing: true, instructions: "You keep notes for the user.", tools: [state],
  });
});
```

## An automation's tables

Each automation in NoClick that keeps tables (its interface's tables and its Table nodes) keeps them in a SQLite database of its own, listed with yours: its `workflow` names the automation, and each table is a SQL table with `id`, `row_index`, `data` (the row, as JSON) and `created_at`. Read it as you would any database, with `schema`, `query`, a branch or an export. Only the automation writes to it, so its tables keep their order and their counts: `execute`, `batch`, `migrate` and `restore` raise `noclick.Conflict`, an agent given it as a tool can't write to it, and it's deleted with its automation.

```python
for db in noclick.Database.list():
    if db.workflow:
        print(db.workflow["name"], [table.name for table in db.schema()])
```

```ts
import { Database } from "noclick";

for (const db of await Database.list()) {
  if (db.workflow) {
    console.log(db.workflow.name, (await db.schema()).map((table) => table.name));
  }
}
```

## Export and delete

`export` dumps the schema and data as SQL that loads into a database of the same engine: `databases/<name>.sql`, which `sqlite3 booking.db < databases/booking-db.sql` loads, or for Postgres a `pg_dump` script for `psql` (NoClick's own `noclick` schema, owners and grants left out). It's an operation that gives an [export bundle](https://www.noclick.com/docs/guide/export.md):

```python
bundle = db.export().wait()
print(bundle.url)   # a zip holding the dump; the link expires
```

```ts
const bundle = await (await db.export()).wait();
console.log(bundle.url);   // a zip holding the dump; the link expires
```

A SQLite dump reads the database a page at a time, so for an exact moment, [branch it](https://www.noclick.com/docs/guide/databases.md#branch-a-copy) at that moment and export the branch. Exporting a Postgres database wakes it, and its compute is billed as any use is.

`delete` moves the database to the [trash](https://www.noclick.com/docs/guide/undo.md), its data kept (and its storage billed) until it's purged; its connection URLs stop working at once, its apps' container releases' own included. A database with branches raises `noclick.Conflict` naming them until you delete them, and so does one an agent has in its `tools`, until you take it out of the agent's tools. One an app binds goes to the trash too, since restoring it undoes the delete: the app keeps its binding, and while the database is in the trash the app's code gets `noclick.NotFound` for it and its container releases have no `DATABASE_URL`. Restore it and the app reaches it again (a container release gets a new `DATABASE_URL` when the app next deploys); once it's purged, the app's binding names nothing. Its delete's preview (`POST /v1/delete_previews`) names each app that binds it and what it loses before anything happens.

```python
noclick.Database.from_name("booking-db-preview").delete()
```

```ts
import { Database } from "noclick";

await Database.fromName("booking-db-preview").delete();
```

## Limits and billing

| Limit | Value |
| --- | --- |
| SQLite size | 10 GB, then branch into Postgres |
| One result | 10 MB |
| One statement | 30 seconds |
| One batch | 100 statements |
| Point-in-time history | 30 days (SQLite), 7 days (Postgres) |
| A SQLite statement | 100 KB, with up to 100 parameters |
| A SQLite table | 100 columns; a row, a string or a BLOB up to 2 MB |
| SQLite throughput | one statement at a time per database, about 1,000 a second |
| Connection URL lifetime | 60 seconds to 24 hours (a container app's release's own: as long as the release) |
| Vector search | Postgres only; an index takes up to 2,000 dimensions (`vector`) or 4,000 (`halfvec`) |
| `noclick.embed` in one request | 100 distinct texts |
| A preview's copy of SQLite rows (`branch_data="copy"`) | 200,000 rows and 16 MB |

A SQLite database is billed for what it stores, $0.20 per GB-month, and for the rows its statements read and write (`rows_read`, `rows_written` on each result, a write to an index counted as a row: see [Read with query](https://www.noclick.com/docs/guide/databases.md#read-with-query)), $0.001 per million read and $1.00 per million written. A Postgres database is billed for its compute time while it's awake, $0.222 an hour per compute unit (about 4 GB of memory with its CPU; a database scales between a quarter of a unit and two as its load needs), for what it stores, $0.35 per GB-month, for the history it keeps for restores, $0.20 per GB-month, and for each branch past the 25 it and its branches include, $1.50 per branch-month; its `rows_read` and `rows_written` are the rows a statement returned and changed, and aren't billed. An idle database, on either engine, costs only its storage (and on Postgres any branches past the 25). Spend counts toward the cap of the user the database belongs to. SQLite refuses virtual tables, so every database can always leave as plain SQL.

Each `execute` or `batch` on SQLite also stores its [retry key](https://www.noclick.com/docs/guide/databases.md#when-the-database-doesn-t-answer) in the database, with its results when they come to 4 KB or less: about 164 bytes when it returns no rows, up to about 5 KB when it returns some. This isn't in `rows_written`, so writing it isn't billed, but it is in `size_bytes`, which storage is billed on. A key stays at least 10 minutes and is removed by a later write, so a database writing 10 times a second carries about 1 MB of them ($0.0002 a month), and one that stops writing keeps its last ones until it next writes.

## Errors

| Error | When |
| --- | --- |
| `InvalidRequest` | the SQL doesn't parse or fails (the engine's own words); `sql` holds more than one statement; `query` got a statement that writes; a migration failed |
| `Conflict` | the name is taken; a migration that already ran was edited; the database is restoring or branching; deleting one with branches or that an agent has in its tools; a SQLite database in its first minute or two has no history to restore or branch from yet |
| `QuotaExceeded` | a SQLite database would pass 10 GB; a request embeds more than 100 texts; a preview asked for the rows of a SQLite database past 200,000 rows or 16 MB |
| `NotAvailable` | `connection_url` on a SQLite database; databases aren't enabled on this deployment |
| `NotFound` | no database by that name or id, for the user the call acts for |
| `UpstreamError` | the database didn't answer after NoClick sent the request again; `applied` says whether anything changed (see [When the database doesn't answer](https://www.noclick.com/docs/guide/databases.md#when-the-database-doesn-t-answer)) |

Every method is in the [Database reference](https://www.noclick.com/docs/reference/python/Database.md): [`query`](https://www.noclick.com/docs/reference/python/Database.md#Database.query), [`execute`](https://www.noclick.com/docs/reference/python/Database.md#Database.execute), [`migrate`](https://www.noclick.com/docs/reference/python/Database.md#Database.migrate), [`branch`](https://www.noclick.com/docs/reference/python/Database.md#Database.branch) and the rest. For files rather than rows, use a [bucket](https://www.noclick.com/docs/guide/storage.md).
