SQLite

This app installs the official SQLite 3.45.2 command-line shell (sqlite3) on the host and fronts it as typed methods. The bundle is the upstream sqlite3 CLI binary (sha-pinned per OS/arch, fetched from the Pilot artifact registry at install) plus a tiny wrapper that serves a clean, complete --help.

SQLite is a zero-server, single-file, transactional (OLTP) SQL engine — the most widely deployed database in the world. There is no server, no daemon, no port, no auth, and nothing to provision: an agent opens an in-memory database (:memory:) or a single .db file like any other file. It is the durable, on-disk complement to DuckDB's in-memory analytics (io.pilot.duckdb) — real transactional SQL and durable agent memory, locally, with no cloud account.

Why an agent wants this

- Zero provisioning. No server lifecycle, no credentials, no "is the daemon up?" state. Open :memory: or a file and run SQL. - Durable, transactional storage — the killer feature. A single .db file *is* the database: ACID transactions, real constraints, indexes, triggers, and full-text search, all persisted to one file the agent can copy, back up, or hand off. The right home for durable agent memory and structured state. - Universal + rock-solid. SQLite is public-domain, exhaustively tested, and backward-compatible — the format your data will still open in decades from now. - Agent-friendly output. sqlite.query returns rows as JSON; other shapes (CSV, table, Markdown, line, HTML) are one sqlite.exec away. - Full SQL. Window functions, CTEs, JSON1, generated columns, upserts, FTS5, and a rich function library. - Self-contained + offline. One binary, no dependencies, no network.

Methods

- sqlite.query — run SQL, get rows back as a JSON array. In-memory or against a file. - sqlite.script — run a multi-statement SQL script (DDL + DML + a trailing SELECT) in one call. - sqlite.schema — print the CREATE DDL for every object in the database (.schema). - sqlite.tables — list the tables in the database (.tables). - sqlite.exec — run the CLI with a verbatim argv (+ optional stdin) for any flag, output mode, or dot-command the curated methods don't cover (.dump, .import, .backup, .clone, .read, -csv, …). - sqlite.cli_help — the complete CLI help: every option and every dot-command, as clean text. - sqlite.version — the delivered SQLite version. sqlite.help — the self-describing method list.

How to use it

1. Quick query (no setup): sqlite.query { "database": ":memory:", "sql": "SELECT 42 AS answer" }. 2. Persistent database: point database at a file path (/work/app.db); it's created on first use and persists. The same file holds many tables, indexes, and views. 3. Set up a schema: sqlite.script { "database": "/work/app.db", "sql": "CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT); INSERT INTO notes(body) VALUES ('hello');" }. 4. Anything else: sqlite.exec { "args": ["/work/app.db", "-cmd", ".mode csv", "-cmd", ".import /data/in.csv t", "SELECT count(*) FROM t"] }.

Configuration

- database — :memory: (ephemeral, no provisioning) or an absolute path to a .db/.sqlite file (created on first use, persists, holds many tables). - Output mode — sqlite.query returns JSON; choose any other mode via sqlite.exec (-csv, -table, -markdown, -line, -html, -box, -ascii). - Read-only — open a database without write access via sqlite.exec (-readonly).

Good to know

- On a non-zero exit (SQL error) the reply is {stdout, stderr, exit} so the caller sees everything the CLI produced. - Runs on macOS and Linux (arm64 + amd64); the binary is fetched from the Pilot artifact registry and sha-pinned on install. SQLite is in the public domain (the "blessing" license). - sqlite.help lists every method with its latency class — the self-describing discovery contract.

## SQLite CLI help (sqlite.cli_help) ``` SQLite CLI (sqlite3) — command-line options and meta-commands =============================================================

SQLite is a zero-server, single-file, transactional SQL database engine. The sqlite3 CLI runs SQL against a database file (created if it does not exist) or an in-memory database (use ':memory:' or omit the filename).

USAGE: sqlite3 [OPTIONS] [FILENAME] [SQL]

FILENAME is an SQLite database file; ':memory:' is an ephemeral in-memory DB. A trailing SQL string is executed (may contain multiple ;-separated statements).

------------------------------------------------------------------------------ COMMAND-LINE OPTIONS (sqlite3 -help) ------------------------------------------------------------------------------ -- treat no subsequent arguments as options -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -deserialize open the database using sqlite3_deserialize() -echo print inputs before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory -markdown set output mode to 'markdown' -maxsize N maximum size for a --deserialize database -memtrace trace all memory allocations and deallocations -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -nonce STRING set the safe-mode escape nonce -nullvalue TEXT set text string for NULL values. Default '' -pagecache SIZE N use N slots of SZ bytes each for page cache memory -pcachetrace trace all page cache operations -quote set output mode to 'quote' -readonly open the database read-only -safe enable safe-mode -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -tabs set output mode to 'tabs' -unsafe-testing allow unsafe commands and modes for testing -version show SQLite version -vfs NAME use NAME as the default VFS

------------------------------------------------------------------------------ DOT-COMMANDS (meta-commands; usable inside the shell or via -cmd "...") ------------------------------------------------------------------------------ .auth ON|OFF Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .check GLOB Fail if output since .testcase does not match .clone NEWDB Clone data into NEWDB from the existing database .connection [close] [#] Open or close an auxiliary database connection .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dump ?OBJECTS? Render database content as SQL .echo on|off Turn command echo on or off .eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .filectrl CMD ... Run various sqlite3_file_control() operations .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .import FILE TABLE Import data from FILE into TABLE .indexes ?TABLE? Show names of indexes .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT .lint OPTIONS Report potential schema issues. .load FILE ?ENTRY? Load an extension library .log FILE|on|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?OPTIONS? Set output mode .nonce STRING Suspend safe mode for one command if nonce matches .nullvalue STRING Use STRING in place of NULL values .once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .parameter CMD ... Manage SQL parameter bindings .print STRING... Print literal STRING .progress N Invoke progress handler after every N opcodes .prompt MAIN CONTINUE Replace the standard prompts .quit Stop interpreting input stream, exit if primary. .read FILE Read input from FILE or command output .restore ?DB? FILE Restore content of DB (default "main") from FILE .save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) .scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERN .separator COL ?ROW? Change the column and row separators .sha3sum ... Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .stats ?ARG? Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace ?OPTIONS? Output each SQL statement as it is run .version Show source, library and compiler versions .vfsinfo ?AUX? Information about the top-level VFS .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set minimum column widths for columnar output

```