Databases

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#

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

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)")

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

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

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.

region= keeps its data in a region, 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.

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)

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, which is what it bills: 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 (an integer primary key is the row's own id, not an index).

query and execute each take one statement; send several in a 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.

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

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:

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"]),
])

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

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

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:

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

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.

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

See the schema#

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

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:

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()

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).

db = noclick.Database.from_name("booking-db")
preview = db.branch("booking-db-preview").wait()
preview.migrate("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):

db = noclick.Database.from_name("booking-db")
bigger = db.branch("booking-pg", engine="postgres").wait()
print(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.

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

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)",
])

Embed with AI.embed and write the vectors as lists; cast(? as halfvec) turns a list into a vector:

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)
])

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

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"])

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:

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"])

• 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 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:

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"])

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:

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())

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.

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.

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, 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.

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:

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"],
)

The agent writes plain SQL with parameters, so it can filter, join and search by keyword; on Postgres, noclick.embed lets it search by meaning too. Its calls follow your approval rules 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 and a hosted MCP server.

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:

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")],
)

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:

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],
    )

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.

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

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:

bundle = db.export().wait()
print(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 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, its data kept (and its storage billed) until it's purged; its connection URLs stop working at once. A database with branches raises noclick.Conflict naming them until you delete them, and so does one an app still binds or an agent has in its tools, until you unbind it or take it out of the agent's tools.

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

Limits and billing#

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), $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.

Errors#

Every method is in the Database reference: query, execute, migrate, branch and the rest. For files rather than rows, use a bucket.