DuckDB MCP Server: The Hard Way

Build an MCP server that lets an AI agent run SQL over your local CSV, Parquet and JSON files, and make it safe enough that you would actually point it at a folder. Start from duckdb.connect() and end with a sandbox you have tried to break on purpose.

At the end you will have built duckdb-mcp yourself, in about 250 lines, and you will understand the two things most "SQL for your agent" tutorials skip: what a database engine will do if you let it, and how to shape results for a reader with a small context window.

Why This Exists

The usual demo hands the model a run_sql(query) tool and a database connection. That is fine until you remember what a SQL engine can do:

• DuckDB reads any file the process can see: read_csv('C:/Users/you/.ssh/id_rsa').

• It reads URLs, installs extensions from the internet, attaches other databases.

• It writes files: COPY ... TO, EXPORT DATABASE, and, surprisingly, EXPLAIN ANALYZE COPY ..., which looks like a harmless "explain" and is not.

• A SELECT * on a 10 GB file will happily try to answer.

A useful data tool for an agent is mostly about saying no in the right places, and saying it in words the agent can act on.

Related MCP server: mcp-duckdb-analyst

Who This Is For

• Developers who have built a "hello world" MCP server and want a real one

• Anyone who wants an agent to analyse local data without handing over the disk

• Anyone curious how much DuckDB can do with nothing installed but pip install duckdb

Prerequisites: Python and some SQL. No DuckDB knowledge needed.

pip install -r requirements.txt      # or: uv pip install -r requirements.txt
python verify.py

Modules

Each module contains:

• README.md: the concepts and the design decisions

• main.py: a working, commented implementation of that stage. It asserts every claim the README makes, and prints OK only if they all hold

• break-it.md: challenges that break your own code, with hints

How To Use This Course

Option A: Build along. Read the modules in order, run each main.py, then do the break-it challenges.

Option B: Reference. Each main.py stands alone (Module 07 loads Module 06's file by path, on purpose, because it tests that server).

Option C: Use the finished tool. pip install duckdb-mcp is the full version of what Module 06 assembles: more tools, glob and multi-folder support, profile and explain tools.

Every Module Is Verified

python verify.py          # all seven
python verify.py 03 06    # just these

CI runs it on Linux and Windows on every push and weekly, so the code in this course can't silently stop working when DuckDB or mcp changes.

Architecture Overview

 AI agent (Claude Code, Claude Desktop, ...)
        |  MCP over stdio: JSON-RPC tool calls                  <- Module 04
        v
 +-------------------------------------------------------+
 | MCP server: tools, docstrings, ToolError messages     |       <- Module 04
 |   gate    one SELECT-type statement, EXPLAIN checked  |       <- Module 03
 |   names   'orders.csv' -> absolute path (if it exists)|       <- Module 02
 |   shape   fetchmany(cap + 1), notice, md/csv/json     |       <- Module 05
 |   limits  timeout, interrupt()                        |       <- Module 06
 +-------------------------------------------------------+
        |  SQL
        v
 DuckDB (in memory)  allowed_directories + external access off + locked   <- Module 06
        |
        v
 your data folder only: csv, tsv, parquet, json, ndjson

Key Design Principles

1. Two layers, because each has a hole the other covers. DuckDB's sandbox stops reads outside the folder but allows writes inside it. The statement gate stops writes but knows nothing about paths. Neither alone is enough.

2. Let the parser parse. sql.lower().startswith("select") is fooled by comments and by SELECT 1; DROP TABLE t. extract_statements() is not.

3. Never compute what you will not show. Fetch cap + 1 rows. The extra row tells you the result was cut, with no COUNT(*) over a billion rows.

4. Errors are instructions. "Permission denied" is a dead end. "File access is limited to C:/data. Call list_files" is the next step.

5. Test the tests. A security suite that has never failed has not proven anything. Module 07 removes the sandbox and checks that the suite notices.

Tech Stack

• Language: Python 3.10+

• Engine: DuckDB (in-memory, no extensions)

• Protocol: MCP over stdio

• Dependencies: mcp, duckdb, anyio

License

MIT. Use it, modify it, ship it.

Built by 0xmortuex