DuckDB

This app installs the official DuckDB 1.5.4 command-line shell on the host and fronts it as typed methods. The bundle is the upstream DuckDB 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.

DuckDB is an in-process OLAP database — "SQLite for analytics." 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 .duckdb file like any other file. That posture is exactly right for an autonomous agent working in its own sandbox with no cloud account.

Why an agent wants this

- Zero provisioning. No initdb, no server lifecycle, no credentials, no "is the daemon up?" state. Run SQL against :memory: or a file and you're done. - Query files in place — the killer feature. Point SQL straight at CSV, Parquet, or JSON on disk with no load step: SELECT region, sum(amount) FROM '/data/*.parquet' GROUP BY region. The file *is* the table. - Fast analytics on a laptop. A columnar, vectorized engine: aggregations and joins over millions of rows run quickly in a single process, in the agent's own context. - Agent-friendly output. Get results as an aligned table, CSV, JSON, or Markdown — pick the shape that parses cleanly or drops straight into a report. - Full SQL. Window functions, CTEs, nested types (lists/structs/maps), COPY to/from Parquet/CSV, and a rich function library. - Self-contained + offline. One binary, no dependencies; the core CSV/Parquet/JSON readers are built in, so the common cases need no network and no extensions.

Methods

- duckdb.query — run SQL, get an aligned box table (default). In-memory or against a file. - duckdb.query_csv / duckdb.query_json / duckdb.query_markdown — same, as CSV / JSON / Markdown. - duckdb.file — execute a .sql script file (migrations, ETL, multi-statement setup). - duckdb.tables — list every table and view (SHOW ALL TABLES). - duckdb.schema — print the CREATE DDL for the database (.schema). - duckdb.exec — run the CLI with a verbatim argv (+ optional stdin) for any flag, output mode, or dot-command the curated methods don't cover (.mode, .import, .export, .read, -init, …). - duckdb.cli_help — the complete CLI help: every option and every dot-command, as clean text. - duckdb.version — the delivered DuckDB version. duckdb.help — the self-describing method list.

How to use it

1. Quick analysis (no setup): duckdb.query { "database": ":memory:", "sql": "SELECT count(*) FROM '/data/events.parquet'" }. 2. Persistent database: point database at a file path (/work/app.duckdb); it's created on first use and persists. The same file holds many tables/schemas. 3. Parse the output: use duckdb.query_json (array of row objects) or duckdb.query_csv. 4. Anything else: duckdb.exec { "args": [":memory:", "-cmd", ".import /data/in.csv t", "-c", "SELECT count(*) FROM t"] }.

Configuration

- database — :memory: (ephemeral, no provisioning) or an absolute path to a .duckdb/.db file (created on first use, persists, holds many tables). Independent of this, the SQL can read/write CSV, Parquet, and JSON files anywhere on disk. - Output mode — choose the method (query box / query_csv / query_json / query_markdown), or any other mode via duckdb.exec (-line, -html, -ascii, -jsonlines, …). - Extensions — Parquet/CSV/JSON readers are built in. Network-backed extensions (e.g. httpfs for S3/HTTP Parquet) are loadable via duckdb.exec (INSTALL httpfs; LOAD httpfs;) where the host allows egress; the local-only path needs neither. - Read-only — open a database without write access via duckdb.exec (-readonly).

Good to know

- Output returns verbatim where it's already clean; 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. Free and open source under the MIT License. - duckdb.help lists every method with its latency class — the self-describing discovery contract.

## DuckDB CLI help (duckdb.cli_help) ``` DuckDB CLI — command-line options and meta-commands ===================================================

DuckDB is an in-process analytical SQL database (think "SQLite for analytics"). The CLI runs SQL against an in-memory database (use ':memory:') or a database file, and can query CSV, Parquet, and JSON files directly with no import step, e.g. SELECT * FROM 'data/*.parquet' WHERE ... — the file IS the table.

USAGE: duckdb [OPTIONS] [DATABASE_FILE] [SQL]

DATABASE_FILE is a DuckDB database; it is created if it does not exist. Use ':memory:' for an ephemeral in-memory database.

------------------------------------------------------------------------------ COMMAND-LINE OPTIONS (duckdb -help) ------------------------------------------------------------------------------

OPTIONS: -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' -c COMMAND run "COMMAND" and exit -dark-mode use dark mode colors -echo print commands before execution -f FILENAME read/process named file and exit -init FILENAME read/process named file -header turn headers on -h show help message -help show help message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -jsonlines set output mode to 'jsonlines' -light-mode use light mode colors -line set output mode to 'line' -list set output mode to 'list' -markdown set output mode to 'markdown' -newline SEP set output row separator. Default: '\n' -no-init skip processing the init file -no-stdin exit after processing options instead of reading stdin -noheader turn headers off -nullvalue TEXT set text string for NULL values. Default 'NULL' -quote set output mode to 'quote' -readonly open the database read-only -s COMMAND run "COMMAND" and exit -safe enable safe-mode -separator SEP set output column separator. Default: '|' -storage-version VER database storage compatibility version to use. Default: 'v0.10.0' -table set output mode to 'table' -ui launches a web interface using the ui extension (configurable with .ui_command) -unredacted allow printing unredacted secrets -unsigned allow loading of unsigned extensions -version show DuckDB version

------------------------------------------------------------------------------ DOT-COMMANDS (meta-commands; usable inside the shell or via -cmd "...") ------------------------------------------------------------------------------ .bail on|off Stop after hitting an error. Default OFF .binary on|off Turn binary output on or off. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .columns Column-wise rendering of query results .decimal_sep SEP Sets the decimal separator used when rendering numbers. Only for duckbox mode. .databases List names and files of attached databases .dump ?TABLE? Render database content as SQL .display_colors [bold|underline] Display all terminal colors and their names .echo on|off Turn command echo on or off .edit Opens an external text editor to edit a query. .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .highlight on|off Toggle syntax highlighting in the shell on/off .highlight_colors OPTIONS Configure highlighting colors .highlight_errors on|off Turn highlighting of errors on or off .highlight_mode mixed|dark|light Toggle the highlight mode to dark or light mode .highlight_results on|off Turn highlighting of results on or off .import FILE TABLE Import data from FILE into TABLE .indexes ?TABLE? Show names of indexes .last Render the last result without truncating .large_number_rendering MODE Toggle readable rendering of large numbers (duckbox only) .log FILE|off Turn logging on or off. FILE can be stderr/stdout .maxrows COUNT Sets the maximum number of rows for display (default: 40). Only for duckbox mode. .maxwidth COUNT Sets the maximum width in characters. 0 defaults to terminal width. Only for duckbox mode. .mode MODE ?TABLE? Set output mode .multiline Sets the render mode to multi-line .nullvalue STRING Use STRING in place of NULL values .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .once ?FILE? Output for the next SQL command only to FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .pager OPTIONS Control pager usage for output .print STRING... Print literal STRING .progress_bar OPTIONS Configure the progress bar display .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILE Read input from FILE .read_line_version linenoise|fallback Sets the library used for processing interactive input .render_completion on|off Toggle displaying of completion prompts in the shell on/off .render_errors on|off Toggle rendering of errors in the shell on/off .rows Row-wise rendering of query results (default) .safe_mode Enable safe-mode .separator COL ?ROW? Change the column and row separators .schema ?PATTERN? Show the CREATE statements matching PATTERN .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .singleline Sets the render mode to single-line .startup_text none|version|all Start-up text to display. Set this as the first line in .duckdbrc .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .thousand_sep SEP Sets the thousand separator used when rendering numbers. Only for duckbox mode. .timer on|off Turn SQL timer on or off .ui_command [command] Set the UI command .version Show the version .width NUM1 NUM2 ... Set minimum column widths for columnar output

Run .help --all for extended information Run .help shortcuts for keyboard shortcuts ```