DuckDB MCP lets an assistant inspect tables and run SQL against a local database. Use it for repeated data exploration and report reconciliation. If you already know the one SQL query you need, DuckDB's Python API or CLI is usually enough.
This guide uses MotherDuck's open-source local server with a .duckdb file. It also covers a Windows failure that can look like missing tools: the server starts in a Python test, but Codex loses its initialization connection until Python output is set to UTF-8.
Build a small database first
With uv and Codex CLI installed, open PowerShell in an empty directory:
uv venv --python 3.12 .venv
uv pip install --python .venv\Scripts\python.exe mcp-server-motherduck==1.1.1
Save this as create_sales.py and run it once in that new directory:
import duckdb
with duckdb.connect('sales.duckdb') as db:
db.execute('CREATE TABLE orders(region VARCHAR, amount INTEGER)')
db.execute("INSERT INTO orders VALUES ('Taipei',120),('Taipei',80),('Kaohsiung',50)")
.venv\Scripts\python.exe .\create_sales.py
$server = (Resolve-Path .venv\Scripts\mcp-server-motherduck.exe).Path
$db = (Resolve-Path .\sales.duckdb).Path
Let the creation process exit before starting MCP, so it no longer holds a write connection. This local file connection needs no MotherDuck cloud account.
Connect the clients
In the same PowerShell session:
codex mcp add duckdb --env PYTHONIOENCODING=utf-8 --env PYTHONUTF8=1 -- $server --db-path $db
codex mcp list
Start a new Codex conversation and request the duckdb tools. Expect list_tables, list_columns and execute_query. The default mode is read-only.
For Claude Code, run from the project directory:
claude mcp add duckdb --scope project --transport stdio --env PYTHONIOENCODING=utf-8 --env PYTHONUTF8=1 -- $server --db-path $db
claude mcp list
The health check should show Connected. For Cursor, follow its MCP setup guide, using the same absolute executable path, database argument and two UTF-8 environment variables.
Three questions worth asking
Discover the schema before guessing SQL
Use only DuckDB MCP. Call list_tables, then list_columns for orders.
Report table names, column names and types. Do not modify data.
The sample contains orders, with region VARCHAR and amount INTEGER. Establishing those facts first avoids invented column names.
Reconcile sales by region
Use execute_query for SELECT region, SUM(amount) AS total FROM orders
GROUP BY region ORDER BY total DESC. Report the actual rows and ordering.
Expect Taipei 200 and Kaohsiung 50. Taipei's two records become one regional total; counting records would answer a different question.
Cross-check the grand total
Run SELECT SUM(amount) AS total FROM orders through execute_query.
Compare it with the previous regional totals. If they differ, check filters
and grouping before proposing an explanation.
The grand total is 250, matching 200 + 50. Keeping both SQL statements in the answer makes the result easier to inspect.
Windows troubleshooting
A Python client connects, but Codex exposes no tools
Our direct Codex connection failed with handshaking with MCP server failed: connection closed: initialize response. Captured startup output included bytes that were invalid UTF-8. Passing PYTHONIOENCODING=utf-8 and PYTHONUTF8=1 to the original executable allowed the same server to complete the queries.
Use codex mcp get duckdb to inspect an existing entry, update its environment and start a fresh conversation. Setting required = true in that server's TOML entry makes startup failure explicit; it diagnoses availability rather than repairing encoding.
A table or column cannot be found
Inspect the actual schema and check the full database path. Creating a matching table in another empty database would make the query run against different data, defeating reconciliation.
A write is rejected
The default connection is read-only. Prepare data separately, close the write connection and then query it through MCP. Read-only mode is not a complete sandbox for arbitrary SQL or local file access; connect only data you intend the client to query.
Another process holds the file
Close the database creation script or other writer first. DuckDB has specific cross-process concurrency rules. Establish a working single-client path before introducing multiple processes.
Limits, cost and alternatives
The local MIT-licensed server has no separate query fee. Model usage and optional MotherDuck cloud services have their own costs. Defaults limit a result to 1024 rows and 50,000 characters: aggregate large tables before returning samples, and never treat the returned row count as the full table size.
Use DuckDB directly for SQL you can already write. MCP helps when an assistant needs repeated schema discovery and queries. For preparing documents for AI rather than querying tables, see MarkItDown.
FAQ
Do I need a MotherDuck account?
Not for a local .duckdb file. The server also supports MotherDuck cloud connections, which involve separate credentials and service costs.
Can Codex and Claude Code both use it?
Both can launch this stdio server. Use absolute paths and pass the UTF-8 settings on Windows; inspect actual calls in Codex and the connection health check in Claude Code.
Can I import a CSV through this recipe?
This recipe creates a database before connecting a read-only MCP client. Other file-reading SQL paths require separate access checks. Read-only database access should not be mistaken for hiding every local file.
Why inspect columns before asking for a total?
The question alone does not establish column names, data types or units. Schema inspection reduces avoidable SQL errors. You still need the data definition to know what an amount means.
Sources and test scope
Local server, DuckDB concurrency, Codex MCP. Checked 2026-11-16.
Windows, Python 3.12, mcp-server-motherduck 1.1.1, DuckDB 1.5.6, FastMCP 4.2.0 and Codex CLI 0.169.0. Codex queried the table, columns, regional totals 200/50 and grand total 250; Claude Code add/list showed Connected. The failed handshake and UTF-8 correction were recorded. Cursor, cloud MotherDuck, arbitrary-file isolation, concurrent writes and large datasets were not tested.