Run in-process analytical SQL on ad-hoc data via mcp-server-duckdb (uvx)

Recipe: In-process analytical SQL via mcp-server-duckdb

Launch

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

Transport: stdio (NDJSON). Server: mcp-duckdb-server v1.27.2.

Tool inventory

Verified trace

1. Create table + insert data (id:3)

→ {"method":"tools/call","params":{"name":"query","arguments":{"query":"CREATE TABLE IF NOT EXISTS cities (name VARCHAR, country VARCHAR, population INTEGER); INSERT INTO cities VALUES ('Istanbul', 'Turkey', 15840900), ('Tokyo', 'Japan', 13960000), ('Berlin', 'Germany', 3645000), ('Paris', 'France', 2161000), ('London', 'UK', 8982000);"}}}
← {"content":[{"type":"text","text":"[(5,)]"}],"isError":false}

2. Analytical SELECT with computed columns (id:4)

→ {"method":"tools/call","params":{"name":"query","arguments":{"query":"SELECT name, country, population, ROUND(population / 1000000.0, 2) AS pop_millions FROM cities ORDER BY population DESC"}}}
← {"content":[{"type":"text","text":"[('Istanbul', 'Turkey', 15840900, 15.84), ('Tokyo', 'Japan', 13960000, 13.96), ('London', 'UK', 8982000, 8.98), ('Berlin', 'Germany', 3645000, 3.65), ('Paris', 'France', 2161000, 2.16)]"}],"isError":false}

3. Aggregation query (id:5)

→ {"method":"tools/call","params":{"name":"query","arguments":{"query":"SELECT COUNT(*) as total_cities, SUM(population) as total_pop, AVG(population) as avg_pop, MIN(population) as min_pop, MAX(population) as max_pop FROM cities"}}}
← {"content":[{"type":"text","text":"[(5, 44588900, 8917780.0, 2161000, 15840900)]"}],"isError":false}

Key differences from SQLite MCP

• DuckDB natively queries CSV files: SELECT * FROM read_csv_auto('data.csv')
• Parquet support: SELECT * FROM read_parquet('file.parquet')
• JSON file support: SELECT * FROM read_json_auto('data.json')
• Columnar storage optimized for OLAP — faster aggregations on large datasets
• Full SQL standard with window functions, CTEs, QUALIFY clause

Failure modes

• --db-path is required; omitting it causes the server to fail on startup
• Results are returned as Python-style tuple lists, not JSON objects — parse accordingly
• Multi-statement SQL (semicolon-separated) works but only the last statement's result is returned