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

Recipe: Analytical SQL on CSV files via mcp-server-duckdb (uvx)

Server: mcp-server-duckdb v1.27.2 via uvx Transport: stdio (newline-delimited JSON-RPC) Auth: none — zero config, no API key Tools: 1 — query (execute any SQL)

Launch

uvx mcp-server-duckdb --db-path /tmp/analytics.duckdb

Key capability

DuckDB can query CSV, Parquet, and JSON files directly with read_csv_auto(), read_parquet(), read_json_auto() — no import step, no running database server. Supports full analytical SQL: aggregations, window functions (RANK, ROW_NUMBER), CTEs, JOINs across files.

Trace 1 — Aggregation with GROUP BY

Query: SELECT product, SUM(units) AS total_units, SUM(revenue) AS total_revenue, ROUND(SUM(revenue)/SUM(units), 2) AS avg_price_per_unit FROM read_csv_auto('./pathfinder_test_sales.csv') GROUP BY product ORDER BY total_revenue DESC

Result: [('Widget B', 600, 24000.0, 40.0), ('Widget A', 615, 18450.0, 30.0), ('Widget C', 295, 8850.0, 30.0)]

Trace 2 — Window function (RANK)

Query: SELECT product, region, revenue, RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region FROM read_csv_auto('./pathfinder_test_sales.csv') ORDER BY region, rank_in_region

Result: [('Widget B', 'East', 7200.0, 1), ('Widget A', 'East', 3900.0, 2), ('Widget C', 'East', 2850.0, 3), ('Widget B', 'North', 8800.0, 1), ('Widget A', 'North', 4500.0, 2), ('Widget C', 'North', 2700.0, 3), ('Widget B', 'South', 8000.0, 1), ('Widget A', 'South', 5250.0, 2), ('Widget C', 'South', 3300.0, 3), ('Widget A', 'West', 4800.0, 1)]

Notes

• Cold start ~3s (uvx install + server init), then sub-second queries
• --db-path is required but can point to a new file (DuckDB creates it)
• Tuple-format results — parse with your language's JSON/tuple parser
• No schema discovery tool built-in, but DESCRIBE SELECT * FROM read_csv_auto('file.csv') works