Run analytical SQL on local CSV/Parquet/JSON files via mcp-server-duckdb (uvx)

Recipe: Analytical SQL on local CSV files via mcp-server-duckdb

Surface

• Package: mcp-server-duckdb (Python, uvx-ready)
• Launch: uvx mcp-server-duckdb --db-path /tmp/analysis.duckdb
• Transport: stdio, NDJSON framing (NOT Content-Length)
• Auth: none
• Tools: 1 — query (param: query: string)

What it does

DuckDB runs analytical SQL in-process — no external database server needed. The single query tool accepts any DuckDB SQL, including read_csv_auto('/path/to/file.csv') which auto-detects column types from CSV headers. You can also read Parquet and JSON files the same way.

Gotchas

1. NDJSON framing required — the server does NOT use Content-Length: headers. Send one JSON-RPC message per line, read one per line.
2. The tool parameter is `query`, not `sql` — the inputSchema names it query, so arguments: { query: "SELECT ..." }. Using { sql: "..." } returns a validation error.
3. Results are Python tuple strings — results come back as "[(val1, val2), ...]" format, not structured JSON arrays. Parse accordingly.
4. The `--db-path` flag is required — it creates (or opens) a DuckDB file. Use a temp path for throwaway analysis.

Verified trace (3 queries, all successful)

Query 1 — CSV ingest (87ms):

→ tools/call { name: "query", arguments: { query: "CREATE TABLE sales AS SELECT * FROM read_csv_auto('/tmp/pathfinder-duckdb/sales.csv')" } }
← { content: [{ type: "text", text: "[(12,)]" }], isError: false }

(12 rows ingested from a 12-row CSV with columns: product, region, quarter, revenue, units)

Query 2 — GROUP BY aggregation (40ms):

→ tools/call { name: "query", arguments: { query: "SELECT product, SUM(revenue) as total_revenue, SUM(units) as total_units, ROUND(SUM(revenue)::DOUBLE/SUM(units), 2) as avg_price FROM sales GROUP BY product ORDER BY total_revenue DESC" } }
← { content: [{ type: "text", text: "[('Widget B', 85900, 2149, 39.97), ('Widget A', 72000, 1440, 50.0)]" }], isError: false }

Query 3 — Window function LAG (22ms):

→ tools/call { name: "query", arguments: { query: "SELECT product, quarter, SUM(revenue) as qtr_revenue, SUM(revenue) - LAG(SUM(revenue)) OVER (PARTITION BY product ORDER BY quarter) as revenue_change FROM sales GROUP BY product, quarter ORDER BY product, quarter" } }
← { content: [{ type: "text", text: "[('Widget A', 'Q1', 22300, None), ('Widget A', 'Q2', 24300, 2000), ('Widget A', 'Q3', 25400, 1100), ('Widget B', 'Q1', 26600, None), ('Widget B', 'Q2', 28800, 2200), ('Widget B', 'Q3', 30500, 1700)]" }], isError: false }

Latency

• CSV ingest: 87ms
• Aggregate query: 40ms
• Window function: 22ms
• All sub-100ms for a 12-row dataset. DuckDB is columnar and vectorized — expect good performance even on larger files.