botdb

docs / reference

SQL support and table rules

The SQL dialect botdb accepts, which statements and schema features are allowed, and why.

botdb speaks SQLite’s dialect. Each database is a regular SQLite database on every device, plus a change log that records writes as logical row and column changes. Statements are checked by an authorizer before they run, so unsupported SQL is rejected without side effects.

Reads

Everything SQLite supports in a SELECT works: joins, CTEs (including recursive ones), window functions, aggregates, JSON functions, views. Informational pragmas work too, such as PRAGMA table_info(watched), index_list, foreign_key_list, integrity_check or user_version.

With --readonly, the MCP query tool, or a database shared with you read-only, only statements that don’t modify anything are accepted (SELECT, WITH, informational pragmas).

Writes

A script with several statements runs atomically: all or nothing. The output shows the last statement that returned rows, or the total number of changed rows.

Pass values as parameters, never by concatenating strings:

botdb sql "INSERT INTO watched (title, rating) VALUES (?, ?)" -p "Past Lives" -p 4
botdb sql "UPDATE watched SET rating = :rating WHERE title = :title" \
  --params '{"title": "Past Lives", "rating": 5}'
botdb sql "INSERT INTO watched (title, rating) VALUES (?, ?)" --params '["Aftersun", 5]'

-p values that are JSON numbers, true, false or null are bound with that type; anything else is text. --params takes a JSON array (for ?) or object (for :name). Parameters only work with a single statement.

Schema changes

Schema changes are ordinary SQL and sync to every device:

Rules for synced tables

Devices edit offline and merge later, column by column. Table definitions that can’t merge safely are rejected with an error code and a hint:

Rule Code Why Do this instead
Every table needs an explicit PRIMARY KEY missing_primary_key Rows are identified across devices by primary key. id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16))))
No INTEGER PRIMARY KEY integer_primary_key Auto-assigned rowids collide when two devices insert offline. Random text ids as above, or INT PRIMARY KEY if you always supply the id.
No BLOB primary keys blob_primary_key Keys are encoded as JSON in the change log. TEXT or INT keys.
NOT NULL columns need a DEFAULT not_null_without_default Columns merge independently, so a row can exist before a value for every column arrives. title TEXT NOT NULL DEFAULT ''
No UNIQUE constraints unique_constraint Two devices can each insert a valid row that together violate the constraint. Make the unique value the primary key, or check before inserting.
No unique indexes unique_index Same as above. A regular index, or make the value the primary key.
No CHECK constraints check_constraint Merging individually valid values can produce a row that fails the check. Validate before writing.
No generated columns generated_column Their values are computed locally, not replicated. Compute in queries or views.

The INTEGER PRIMARY KEY rule applies to a single-column INTEGER key on a regular (rowid) table.

Foreign keys can be declared but aren’t enforced: a row can arrive before the row it references, or outlive it after a concurrent delete.

Statements that aren’t supported

These are rejected for every table, synced or local, with code unsupported_sql:

Not supported Why or what to do instead
Triggers botdb uses its own triggers to capture changes.
Transaction statements (BEGIN, COMMIT, SAVEPOINT, …) Each command already runs atomically: send all statements in one script.
Temporary tables, indexes and views Use a regular table whose name starts with _.
Virtual tables, ATTACH, DETACH Not supported.
Pragmas that change settings Only informational pragmas are allowed.
load_extension Not allowed.

Names starting with _botdb or sqlite_ are reserved.

Local-only tables

Tables whose name starts with _ are local to the device. They never sync and skip the table rules above (no primary key needed, UNIQUE and CHECK allowed). Use them for caches, scratch data and intermediate results.

botdb sql "CREATE TABLE _search_cache (q TEXT UNIQUE, result TEXT, at TEXT)"

Renaming a local table to a synced name validates it and starts syncing its existing rows. Renaming a synced table to a name starting with _ removes it from sync: other devices drop it.

A good default table

CREATE TABLE watched (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
  title TEXT NOT NULL DEFAULT '',
  rating INTEGER,
  watched_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX watched_by_date ON watched (watched_at);

When the same entity might be added from two places, use a natural key instead of a random one, so the rows merge rather than duplicate:

CREATE TABLE movies (
  imdb_id TEXT PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  year INT
);

Errors

Errors are JSON on stderr when piped, with exit status 1:

{"error":{"code":"not_null_without_default","message":"NOT NULL column `watched.title` needs a DEFAULT","hint":"add a default, e.g. `title TEXT NOT NULL DEFAULT ''`"}}

Other common codes: sql_error (SQLite errors such as no such table, with a hint where possible), read_only, missing_params, param_count, params_multi_statement, no_such_table. Agents should read the hint and retry with the corrected statement.

← CLI referenceConflict resolution →