botdb

docs / start

Quickstart

Create a vault and a database, define a table, write and query rows, and undo changes.

This walks through the movies example on a single machine. Multiple machines continues from here.

1. Create a vault

botdb login

botdb login first asks where your databases should sync by default:

Where should your databases sync and back up by default?
  1) botdb cloud, hosted and end-to-end encrypted (https://botdb.dev)
  2) iCloud Drive (~/Library/Mobile Documents/com~apple~CloudDocs/botdb)
  u) your own relay URL (http(s)://...) or another folder path
  n) nowhere for now: databases stay on this device
Choice [1]:

botdb cloud is the default: botdb creates a free account for your vault on the spot, with nothing to sign up for. The other numbered entries are cloud folders botdb detected (iCloud Drive, Dropbox, Google Drive, OneDrive). Enter your own relay or another folder with u, or choose n and add a remote later with botdb remote add --default cloud. Skip the prompt with --remote <url|path|cloud> or --no-remote. Without a terminal (an agent running it), botdb login uses botdb cloud.

Then it creates your vault and shows its 24-word recovery phrase. Write it down somewhere safe. It’s the only way to restore your databases on a new machine, anyone who has it can read them, and nobody can recover it for you.

Check that you wrote the phrase down correctly:

botdb vault verify

2. Create a database

botdb create movies

The first database you create becomes the default for commands that take -d. Switch with botdb use <name>.

3. Create a table

botdb sql "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'))
)"

Synced tables need an explicit primary key, can’t use INTEGER PRIMARY KEY, and need a DEFAULT on NOT NULL columns. botdb rejects anything that can’t merge safely and prints a hint. See SQL support and table rules.

4. Write and read

botdb sql "INSERT INTO watched (title, rating) VALUES (?, ?)" -p "Dune: Part Two" -p 5
botdb sql "INSERT INTO watched (title, rating) VALUES (:title, :rating)" \
  --params '{"title": "Perfect Days", "rating": 4}'

botdb sql "SELECT title, rating FROM watched ORDER BY watched_at DESC"
botdb schema        # tables, columns, row counts, indexes and views

Always pass values with -p (positional ?) or --params rather than building SQL strings. -p 5 is bound as a number; -p "Dune: Part Two" as text.

5. Output formats

In a terminal, results print as a table. When stdout is piped (as it is for agents and scripts), botdb prints JSON:

botdb sql "SELECT title, rating FROM watched" | cat
# [{"title":"Dune: Part Two","rating":5},{"title":"Perfect Days","rating":4}]

botdb sql "UPDATE watched SET rating = 5 WHERE title = 'Perfect Days'" | cat
# {"ok":true,"changes":1}

botdb sql -F csv "SELECT * FROM watched" > watched.csv

6. History and undo

Every change is recorded:

botdb history watched --limit 20       # recent changes to the table, newest first
botdb sql "DELETE FROM watched"        # oops
botdb revert --at 5m                   # restore the database as of 5 minutes ago

A revert writes new changes that restore the old state, so it syncs to every device and shows up in history like any other write.

7. Sync

Sync is automatic: botdb pulls before reads when its copy is more than 10 seconds old, and pushes in the background after writes. To check or force it:

botdb status      # pending changes, last pull and push, errors, newest local snapshot
botdb sync        # sync the vault and every database now

Next

← InstallationMultiple machines →