MotherDuck MCP Server for Local DuckDB Queries

Overview

The MotherDuck MCP Server exposes local DuckDB databases to Model Context Protocol (MCP) clients and language models in a controlled, easy-to-integrate way. It runs a small HTTP service that accepts JSON requests for SQL execution and schema inspection, returning structured results that can be incorporated into prompts, tool chains, or analytics workflows. By serving data locally, it keeps sensitive data on-premise while enabling fast, programmatic access for model-assisted analysis.

This server is useful when you want LLMs or automation agents to query local analytics data (Parquet, CSV, or native DuckDB files) without shipping data to a third party. It is designed to be low-friction for developers: run it next to your DuckDB file(s), configure a few environment variables, and call the simple JSON endpoints from your app, BI tool, or LLM integration.

Features

• Execute SQL against local DuckDB databases and return JSON-structured rows and metadata
• Schema and table introspection to support automated prompt construction
• Configurable limits: row caps, timeouts, and concurrent queries for safety
• Secure by design: local hosting, optional host/origin allowlists, and CORS controls
• Docker-friendly and easy to run alongside existing data services
• Small footprint and fast query execution leveraging DuckDB’s single-node performance
• Outputs formatted for MCP-compatible model tooling (tool manifests / descriptors)

Installation / Configuration

Run the server with Docker (recommended for isolation):

# Run with a local DuckDB file mounted into the container
docker run --rm -p 8080:8080 \
  -v /path/to/data:/data \
  -e DUCKDB_PATH=/data/my.db \
  -e PORT=8080 \
  motherduck/mcp-server-motherduck:latest

Run from source (typical pattern—adapt to the repo’s language/build tool):

# Clone, install dependencies, and start
git clone https://github.com/motherduckdb/mcp-server-motherduck.git
cd mcp-server-motherduck
# replace with the actual build/install commands for the project (npm, pip, cargo, etc.)
npm install
npm run start -- --duckdb /path/to/my.db --port 8080

Basic environment variables (table)

Adjust these through env vars, command-line flags, or a small config file depending on deployment needs.

Available Resources

Typical HTTP endpoints provided by the server (names and shapes are representative; check your deployed server’s /docs or OpenAPI if available):

• GET /health
  • Simple liveness check, returns status and server metadata.
• POST /query
  • Body: { “sql”: “”, “max_rows”: 100 }
  • Response: { “columns”: […], “rows”: […], “stats”: { “runtime_ms”: 12 } }
  • GET /schema
    • Returns tables and column metadata to help tooling and prompt generation.
  • GET /tools (MCP discovery)
    • Returns tool manifest(s) in MCP format so LLMs can discover available dataset tools.

  Example curl usage:

  curl -sS -X POST "http://localhost:8080/query" \
    -H "Content-Type: application/json" \
    -d '{"sql":"SELECT count(*) as cnt FROM events WHERE event_date >= DATE(\'2024-01-01\')"}'
  

  Example Python client (requests):

  import requests
  resp = requests.post(
      "http://localhost:8080/query",
      json={"sql": "SELECT name, amount FROM sales ORDER BY amount DESC LIMIT 10"}
  )
  data = resp.json()
  print(data["columns"])
  for row in data["rows"]:
      print(row)
  

  Use Cases

  • LLM-assisted analytics: Allow a model to fetch small, relevant slices of DuckDB data to augment prompts (example: fetch last 30 days of sales by region before asking the model to summarize trends).
  • Data-aware agents: Expose a secure, discoverable tool so autonomous agents can inspect schema and run sanctioned SQL for decision-making tasks.
  • Embedded dashboards and notebooks: Use the server as a microservice to run SQL queries from lightweight dashboards or Jupyter notebooks without bundling a DB client into every consumer.
  • CI/data validation: Run automated checks against local datasets (row counts, null-rate thresholds) as part of pipelines, returning structured results to the pipeline runner.
  • Local-only analytics for privacy-conscious teams: Keep data on-premise while enabling language models or microservices to reason over it without remote uploads.

  Concrete example: prompt augmentation for summarization

  1. Use GET /schema to find table and column names related to “sales”.
  Tags:database