DuckDB MCP Extension

A Model Context Protocol (MCP) extension for DuckDB that enables seamless integration between SQL databases and AI assistants like Claude.

Features

• Run as MCP Server: Expose your DuckDB database to AI assistants with built-in tools for querying, describing, and exploring data
• HTTP Transport: Run as an HTTP server with authentication for web-based clients
• Act as MCP Client: Connect to external MCP servers and query their resources using SQL
• Custom Tools: Publish parameterized SQL queries as tools that AI assistants can discover and call
• Multiple Formats: Output results as JSON, JSONL, Markdown (token-efficient), CSV, or plain text

Quick Start

As an MCP Server (for AI Assistants)

Create init-server.sql:

LOAD 'duckdb_mcp';

CREATE TABLE products (id INT, name VARCHAR, price DECIMAL(10,2));
INSERT INTO products VALUES (1, 'Widget', 9.99), (2, 'Gadget', 24.99);

PRAGMA mcp_server_start('stdio');

stdio transport owns stdout. JSON-RPC is framed on stdout, so anything else printed there corrupts the channel. The extension takes the descriptor away from other writers when the server starts, and the client tolerates host output that arrives before the handshake (the CLI prints an -- Loading resources from … banner for -init, plus a result table per statement). For a server whose output you cannot afford to have parsed at all, put .output /dev/null as the first line of the init script.

Add to Claude Desktop configuration:

{
  "mcpServers": {
    "my-database": {
      "command": "duckdb",
      "args": ["-init", "/path/to/init-server.sql"]
    }
  }
}

Now Claude can query your database directly!

As an HTTP Server

Run DuckDB as an HTTP server that any HTTP client can connect to:

LOAD 'duckdb_mcp';

-- Start HTTP server with authentication
PRAGMA mcp_server_start('http', 'localhost', 8080, '{"auth_token": "secret"}');

Then connect via HTTP:

# Health check
curl http://localhost:8080/health

# Query with authentication
curl -X POST http://localhost:8080/mcp \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer secret" \
    -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"query","arguments":{"sql":"SELECT 1"}}}'

As an MCP Client

Windows: MCP client connections are not available — attaching to a server means spawning it over stdio, which is not implemented on Windows (#67). Use WSL. Running DuckDB as an MCP server works on Windows.

LOAD 'duckdb_mcp';

-- Connect to an MCP server
ATTACH 'python3' AS server (TYPE mcp, ARGS '["path/to/server.py"]');

-- Read data through MCP
SELECT * FROM read_csv('mcp://server/file:///data.csv');

-- Call tools
SELECT mcp_call_tool('server', 'analyze', '{"dataset": "sales"}');

Built-in Server Tools

Each is individually switchable in the server config, and builtin_tools turns the whole set off at once — for a server that should publish only its own curated tools:

-- Publish only what mcp_publish_tool registered; no query/export/describe/...
PRAGMA mcp_server_start('stdio', 'localhost', 0, '{"builtin_tools": false}');

-- Or keep just one of them
PRAGMA mcp_server_start('stdio', 'localhost', 0,
    '{"builtin_tools": false, "enable_query_tool": true}');

An explicit enable_<tool>_tool always wins over builtin_tools, whichever order they appear in. builtin_tools: true means the default set, so it never switches on execute. See configuration reference.

Publishing Custom Tools

-- 5-arg form (JSON output, the default)
PRAGMA mcp_publish_tool(name, description, sql_template, properties_json, required_json);

-- 6-arg form (explicit output format: 'json', 'markdown', or 'csv')
PRAGMA mcp_publish_tool(name, description, sql_template, properties_json, required_json, format);

All parameters are VARCHAR. Pass JSON as string literals, not json_object(...) or JSON type expressions.

Example:

PRAGMA mcp_publish_tool(
    'search_products',                                                      -- name
    'Search products by name with optional category filter',                -- description
    'SELECT * FROM products
     WHERE name ILIKE ''%'' || $query || ''%''
       AND ($category IS NULL OR category = $category)',                    -- sql_template ($param placeholders)
    '{
        "query": {"type": "string", "description": "Search term"},
        "category": {"type": "string", "description": "Category filter"}
    }',                                                                     -- properties_json (JSON Schema)
    '["query"]',                                                            -- required_json (category is optional)
    'markdown'                                                              -- format
);

Optional parameters omitted by callers are substituted as SQL NULL. See the Custom Tools Guide for full details.

PRAGMA vs SELECT

All server management and publishing functions are available in two forms:

PRAGMA versions produce no output, making init scripts clean. SELECT versions return status messages or structs.

Documentation

Full documentation is available at duckdb-mcp.readthedocs.io

• Getting Started
• Server Functions Reference
• Client Functions Reference
• Configuration
• Security Guide
• Examples

Examples

The examples/ directory contains ready-to-use configurations:

Installation

# Clone and build
git clone https://github.com/teague/duckdb_mcp.git
cd duckdb_mcp
make

# Run
./build/release/duckdb

LOAD 'duckdb_mcp';

Testing

make test

License

Apache License 2.0 - see LICENSE and NOTICE for details.