Skip to content

sql.md

Latest commit

 

History

History
144 lines (103 loc) · 5.76 KB

sql.md

File metadata and controls

144 lines (103 loc) · 5.76 KB
Raw

SQL Queries

Run DuckDB SQL directly against documents stored in agent-fs — CSV, TSV, Parquet, Excel, JSON, NDJSON, SQLite databases, and DuckDB databases. Available as a CLI command (agent-fs sql), an API op (sql), and an MCP tool.

agent-fs sql "SELECT category, sum(amount) FROM '/finance/2026.csv' GROUP BY category"

Supported formats

Format Extensions Notes
CSV .csv, .csv.gz Delimiter, header, and types auto-detected
TSV .tsv, .tab, .tsv.gz Tab-delimited
Parquet .parquet
Excel .xlsx First sheet; header auto-detected
JSON .json, .json.gz Arrays of objects or single objects
NDJSON .ndjson, .jsonl (+ .gz) Newline-delimited JSON
SQLite .db, .sqlite, .sqlite3 All tables exposed — see below
DuckDB .duckdb Tables from the main schema

Referencing documents

There are two ways to make a document queryable:

1. Path literals (file formats)

Reference any stored CSV/TSV/Parquet/Excel/JSON/NDJSON document by its drive path as a quoted string, exactly like DuckDB's own file syntax:

agent-fs sql "SELECT * FROM '/data/sales.csv' WHERE amount > 100"
agent-fs sql "SELECT a.id FROM '/data/a.parquet' a JOIN '/data/b.ndjson' b USING (id)"

Only string literals that resolve to an existing document in the drive are bound — other strings are left untouched, so WHERE name = 'report.csv' works as expected.

2. Named table bindings

Bind documents to table names with --table (repeatable). Required for SQLite/DuckDB databases, and useful for readable multi-file queries:

agent-fs sql "SELECT s.name, t.tag FROM sales s JOIN tags t ON s.id = t.id" \
  -t sales=/data/sales.csv -t tags=/data/tags.parquet

SQLite and DuckDB databases expose every table under the binding name as a schema:

# /backups/app.db has tables `users` and `orders`
agent-fs sql "SELECT u.email, count(*) FROM app.users u JOIN app.orders o ON o.user_id = u.id GROUP BY 1" \
  -t app=/backups/app.db

Making any document SQL-able (format override)

If a document doesn't have a recognized extension, override the format with a :format suffix (CLI) or { path, format } (API/MCP):

agent-fs sql "SELECT sum(a) FROM logs" -t logs=/raw/data.txt:csv

Formats: csv, tsv, parquet, xlsx, json, ndjson, sqlite, duckdb.

CLI usage

agent-fs sql [query] [options]

  -t, --table <name=path[:format]>  Bind a document as a named table (repeatable)
  --max-rows <n>                    Max rows to return (default: 1000, max: 10000)
  --json                            Output the raw result object

The query can also be piped via stdin:

echo "SELECT count(*) FROM '/data/events.ndjson'" | agent-fs sql

Multi-statement queries are supported — the result of the last statement is returned:

agent-fs sql "CREATE TABLE big AS SELECT * FROM '/data/sales.csv' WHERE amount > 100; SELECT count(*) FROM big"

API usage

sql is a standard op on the dispatch endpoint:

curl -X POST http://localhost:7433/orgs/{orgId}/ops \
  -H "Authorization: Bearer <api-key>" \
  -H "Content-Type: application/json" \
  -d '{
    "op": "sql",
    "driveId": "<driveId>",
    "query": "SELECT s.name, t.tag FROM sales s JOIN tags t ON s.id = t.id",
    "tables": {
      "sales": "/data/sales.csv",
      "tags": { "path": "/data/tags.parquet", "format": "parquet" }
    },
    "maxRows": 1000
  }'

Response:

{
  "columns": [{ "name": "name", "type": "VARCHAR" }, { "name": "tag", "type": "VARCHAR" }],
  "rows": [{ "name": "alpha", "tag": "aa" }],
  "rowCount": 1,
  "truncated": false,
  "files": [
    { "table": "sales", "path": "/data/sales.csv", "format": "csv" },
    { "table": "tags", "path": "/data/tags.parquet", "format": "parquet" }
  ],
  "elapsedMs": 42
}

Value encoding: BIGINTs outside JavaScript's safe integer range and timestamps/dates/UUIDs are returned as strings; decimals as numbers; lists and structs as JSON arrays/objects; blobs as <blob N bytes> placeholders. The op requires the viewer role.

The same op is exposed as the sql MCP tool with identical arguments.

Web UI

The live UI has a SQL workbench at /sql/~/{orgId}/{driveId} — pick documents, write queries (Cmd+Enter to run), and view results as a table or quick chart. CSV/TSV/Parquet/JSON/NDJSON queries run client-side via DuckDB WASM; Excel/SQLite/DuckDB documents automatically run on the server. Open it from any queryable file via the Query button in the file detail view.

Sandboxing & limits

User SQL never touches the host. Referenced documents are downloaded and materialized into an in-memory DuckDB instance during setup; before any user SQL executes, external access is disabled, the local filesystem is turned off, and the configuration is locked. SQLite files are converted in an isolated bridge instance — the instance that runs your query never loads the sqlite extension, closing its direct-I/O escape hatch. Attempts to read host paths, fetch URLs, ATTACH, COPY ... TO, or change settings fail with a VALIDATION_ERROR.

Server-side knobs (environment variables on the daemon/server):

Variable Default Meaning
AGENT_FS_SQL_TIMEOUT_MS 30000 Query timeout (the query is interrupted)
AGENT_FS_SQL_MAX_FILE_BYTES 268435456 (256 MB) Per-document size cap
AGENT_FS_SQL_MEMORY_LIMIT 512MB DuckDB memory limit

maxRows caps the returned rows (default 1000, max 10000); truncated: true signals there were more.

Note: the first query that touches an .xlsx or SQLite document downloads the DuckDB excel/sqlite extension on the server (cached in ~/.duckdb afterwards), so the server needs outbound network access once.