MotherDuck

<p align="center"> <img src="src/mcp_server_motherduck/assets/duck_feet_square.png" alt="MotherDuck / DuckDB Local MCP Server" width="120"> </p> <h1 align="center">DuckDB / MotherDuck Local MCP Server</h1> <p align="center"> SQL analytics and data engineering for AI Assistants and IDEs. </p> --- Connect AI assistants to your data using DuckDB's powerful analytical SQL engine. Supports connecting to local DuckDB files, in-memory databases, S3-hosted databases, and MotherDuck. Allows executing SQL read- and write-queries, browsing database catalogs, and switching between different database connections on-the-fly. **Looking for a fully-managed remote MCP server for MotherDuck?** → [Go to the MotherDuck Remote MCP docs](https://motherduck.com/docs/sql-reference/mcp/) ### Remote vs Local MCP | | **[Remote MCP](https://motherduck.com/docs/sql-reference/mcp/)** | **Local MCP** (this repo) | |---|---|---| | **Hosting** | Hosted by MotherDuck | Runs locally/self-hosted | | **Setup** | Zero-setup | Requires local installation | | **Access** | Read-write supported | Read-write supported | | **Local filesystem** | - | Query across local and remote databases, ingest data from / export data to local filesystem | > 📝 **Migrating from v0.x?** > - **Read-only by default**: The server now runs in read-only mode by default. Add `--read-write` to enable write access. See [Securing for Production](#securing-for-production). > - **Default database changed**: `--db-path` default changed from `md:` to `:memory:`. Add `--db-path md:` explicitly for MotherDuck. > - **MotherDuck read-only requires read-scaling token**: MotherDuck connections in read-only mode require a [read-scaling token](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#read-scaling-tokens). Regular tokens require `--read-write`. ## Quick Start **Prerequisites**: Install `uv` via `pip install uv` or `brew install uv` ### Connecting to In-Memory DuckDB (Dev Mode) ```json { "mcpServers": { "DuckDB (in-memory, r/w)": { "command": "uvx", "args": ["mcp-server-motherduck", "--db-path", ":memory:", "--read-write", "--allow-switch-databases"] } } } ``` Full flexibility with no guardrails — read-write access and the ability to switch to any database (local files, S3, or MotherDuck) at runtime. ### Connecting to a Local DuckDB File in Read-Only Mode ```json { "mcpServers": { "DuckDB (read-only)": { "command": "uvx", "args": ["mcp-server-motherduck", "--db-path", "/absolute/path/to/your.duckdb"] } } } ``` Connects to a specific DuckDB file in read-only mode. Won't hold on to the file lock, so convenient to use alongside a write connection to the same DuckDB file. You can also connect to remote DuckDB files on S3 using `s3://bucket/path.duckdb` — see [Environment Variables](#environment-variables) for S3 authentication. If you're considering third-party access to the MCP, see [Securing for Production](#securing-for-production). ### Connecting to MotherDuck in Read-Write Mode ```json { "mcpServers": { "MotherDuck (local, r/w)": { "command": "uvx", "args": ["mcp-server-motherduck", "--db-path", "md:", "--read-write"], "env": { "motherduck_token": "<YOUR_MOTHERDUCK_TOKEN>" } } } } ``` See [Command Line Parameters](#command-line-parameters) for more options, [Securing for Production](#securing-for-production) for deployment guidance, and [Troubleshooting](#troubleshooting) if you encounter issues. ## Client Setup | Client | Config Location | One-Click Install | |--------|-----------------|-------------------| | **Claude Desktop** | Settings → Developer → Edit Config | [.mcpb (MCP Bundle)](https://github.com/motherduckdb/mcp-server-motherduck/releases/latest/download/mcp-server-motherduck.mcpb) | | **Claude Code** | Use CLI commands below | - | | **Codex CLI** | Use CLI commands below or `~/.codex/config.toml` | - | | **Gemini CLI** | Use CLI commands below or `~/.gemini/settings.json` | - | | **Cursor** | Settings → MCP → Add new global MCP server | [<img src="https://cursor.com/deeplink/mcp-install-dark.svg" alt="Install in Cursor" height="20">](https://cursor.com/en/install-mcp?name=DuckDB&config=eyJjb21tYW5kIjoidXZ4IG1jcC1zZXJ2ZXItbW90aGVyZHVjayAtLWRiLXBhdGggOm1lbW9yeTogLS1yZWFkLXdyaXRlIC0tYWxsb3ctc3dpdGNoLWRhdGFiYXNlcyIsImVudiI6e319) | | **VS Code** | `Ctrl+Shift+P` → "Preferences: Open User Settings (JSON)" | [](https://insiders.vscode.dev/redirect/mcp/install?name=mcp-server-motherduck&config=%7B%22command%22%3A%22uvx%22%2C%22args%22%3A%5B%22mcp-server-motherduck%22%2C%22--db-path%22%2C%22%3Amemory%3A%22%2C%22--read-write%22%2C%22--allow-switch-databases%22%5D%7D) | | **Kiro** | `~/.kiro/settings/mcp.json` (global) or `.kiro/settings/mcp.json` (project) | [](https://kiro.dev/launch/mcp/add?name=DuckDB&config=%7B%22command%22%3A%22uvx%22%2C%22args%22%3A%5B%22mcp-server-motherduck%22%2C%22--db-path%22%2C%22%3Amemory%3A%22%2C%22--read-write%22%2C%22--allow-switch-databases%22%5D%7D) | Any MCP-compatible client can use this server. Add the JSON configuration from [Quick Start](#quick-start) to your client's MCP config file. Consult your client's documentation for the config file location. <details> <summary><b>Claude Code CLI commands</b></summary> **In-Memory DuckDB (Dev Mode):** ```bash claude mcp add --scope user duckdb --transport stdio -- uvx mcp-server-motherduck --db-path :memory: --read-write --allow-switch-databases ``` **Local DuckDB (Read-Only):** ```bash claude mcp add --scope user duckdb --transport stdio -- uvx mcp-server-motherduck --db-path /absolute/path/to/db.duckdb ``` **MotherDuck (Read-Write):** ```bash claude mcp add --scope user motherduck --transport stdio --env motherduck_token=YOUR_TOKEN -- uvx mcp-server-motherduck --db-path md: --read-write ``` </details> <details> <summary><b>Codex CLI commands</b></summary> **In-Memory DuckDB (Dev Mode):** ```bash codex mcp add duckdb -- uvx mcp-server-motherduck --db-path :memory: --read-write --allow-switch-databases ``` **Local DuckDB (Read-Only):** ```bash codex mcp add duckdb -- uvx mcp-server-motherduck --db-path /absolute/path/to/db.duckdb ``` **MotherDuck (Read-Write):** ```bash codex mcp add motherduck --env motherduck_token=YOUR_TOKEN -- uvx mcp-server-motherduck --db-path md: --read-write ``` </details> <details> <summary><b>Gemini CLI commands</b></summary> **In-Memory DuckDB (Dev Mode):** ```bash gemini mcp add -s user duckdb uvx mcp-server-motherduck --db-path :memory: --read-write --allow-switch-databases ``` **Local DuckDB (Read-Only):** ```bash gemini mcp add -s user duckdb uvx mcp-server-motherduck --db-path /absolute/path/to/db.duckdb ``` **MotherDuck (Read-Write):** ```bash gemini mcp add -s user -e motherduck_token=YOUR_TOKEN motherduck uvx mcp-server-motherduck --db-path md: --read-write ``` </details> <details> <summary><b>Kiro manual JSON config</b></summary> Add the following to your Kiro MCP config file (`~/.kiro/settings/mcp.json` for global, or `.kiro/settings/mcp.json` for project-scoped). See the [Kiro MCP documentation](https://kiro.dev/docs/mcp/) for more details. **In-Memory DuckDB (Dev Mode):** ```json { "mcpServers": { "DuckDB (in-memory, r/w)": { "command": "uvx", "args": ["mcp-server-motherduck", "--db-path", ":memory:", "--read-write", "--allow-switch-databases"] } } } ``` **MotherDuck (Read-Write):** ```json { "mcpServers": { "MotherDuck (local, r/w)": { "command": "uvx", "args": ["mcp-server-motherduck", "--db-path", "md:", "--read-write"], "env": { "motherduck_token": "<YOUR_MOTHERDUCK_TOKEN>" } } } } ``` </details> ## Tools | Tool | Description | Required Inputs | Optional Inputs | |------|-------------|-----------------|-----------------| | `execute_query` | Execute SQL query (DuckDB dialect) | `sql` | - | | `list_databases` | List all databases (useful for MotherDuck or multiple attached DBs) | - | - | | `list_tables` | List tables and views | - | `database`, `schema` | | `list_columns` | List columns of a table/view | `table` | `database`, `schema` | | `switch_database_connection`* | Switch to different database | `path` | `create_if_not_exists` | *Requires `--allow-switch-databases` flag All tools return JSON. Results are limited to 1024 rows / 50,000 chars by default (configurable via `--max-rows`, `--max-chars`). ## Securing for Production When giving third parties access to a self-hosted MCP server, **read-only mode alone is not sufficient** — it still allows access to the local filesystem, changing DuckDB settings, and other potentially sensitive operations. For production deployments with third-party access, we recommend **[MotherDuck Remote MCP](https://motherduck.com/docs/sql-reference/mcp/)** — zero-setup, read-write capable, and hosted by MotherDuck. **Self-hosting MotherDuck MCP:** Fork this repo and customize as needed. Use a **[service account](https://motherduck.com/docs/key-tasks/service-accounts-guide/)** with **[read-scaling tokens](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/#creating-a-read-scaling-token)** and enable **[SaaS mode](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-saas-mode)** to restrict local file access. **Self-hosting DuckDB MCP:** Use `--init-sql` to apply security settings. See the [Securing DuckDB guide](https://duckdb.org/docs/stable/operations_manual/securing_duckdb/overview) for available options. ## Command Line Parameters | Parameter | Default | Description | |-----------|---------|-------------| | `--db-path` | `:memory:` | Database path: local file (absolute), `md:` (MotherDuck), or `s3://` URL | | `--motherduck-token` | `motherduck_token` env var | MotherDuck access token | | `--read-write` | `False` | Enable write access | | `--motherduck-saas-mode` | `False` | MotherDuck SaaS mode (restricts local access) | | `--allow-switch-databases` | `False` | Enable `switch_database_connection` tool | | `--max-rows` | `1024` | Max rows returned | | `--max-chars` | `50000` | Max characters returned | | `--query-timeout` | `-1` | Query timeout in seconds (-1 = disabled) | | `--init-sql` | `None` | SQL to execute on startup | | `--motherduck-connection-parameters` | `session_hint=mcp&`<br>`dbinstance_inactivity_ttl=0s` | Additional MotherDuck connection string parameters (`key=value` pairs separated by `&`) | | `--ephemeral-connections` | `True` | Use temporary connections for read-only local files | | `--transport` | `stdio` | Transport type: `stdio` or `http` | | `--stateless-http` | `False` | For protocol compatibility only (e.g. with [AWS Bedrock AgentCore Runtime](https://docs.aws.amazon.com/bedrock-agentcore/latest/devguide/runtime-mcp-protocol-contract.html)). Server still maintains global state via the shared DatabaseClient. | | `--port` | `8000` | Port for HTTP transport | | `--host` | `127.0.0.1` | Host for HTTP transport | ## Environment Variables | Variable | Description | |----------|-------------| | `motherduck_token` or `MOTHERDUCK_TOKEN` | MotherDuck access token (alternative to `--motherduck-token`) | | `HOME` | Used by DuckDB for extensions and config. Override with `--home-dir` if not set. | | `AWS_ACCESS_KEY_ID` | AWS access key for S3 database connections | | `AWS_SECRET_ACCESS_KEY` | AWS secret key for S3 database connections | | `AWS_SESSION_TOKEN` | AWS session token for temporary credentials (IAM roles, SSO, EC2 instance profiles) | | `AWS_DEFAULT_REGION` | AWS region for S3 connections | | `AWS_ENDPOINT` | AWS endpoint for S3 connections | ## Troubleshooting - **`spawn uvx ENOENT`**: Specify full path to `uvx` (run `which uvx` to find it) - **File locked**: Make sure `--ephemeral-connections` is turned on (default: true) and that you're not connected in read-write mode ## Resources - [MotherDuck MCP Documentation](https://motherduck.com/docs/sql-reference/mcp/) - [Close the Loop: Faster Data Pipelines with MCP, DuckDB & AI (Blog)](https://motherduck.com/blog/faster-data-pipelines-with-mcp-duckdb-ai/) - [Faster Data Pipelines with MCP and DuckDB (YouTube)](https://www.youtube.com/watch?v=yG1mv8ZRxcU) ## Development To run from source: ```json { "mcpServers": { "Local DuckDB (Dev)": { "command": "uv", "args": ["--directory", "/path/to/mcp-server-motherduck", "run", "mcp-server-motherduck", "--db-path", "md:"], "env": { "motherduck_token": "<YOUR_MOTHERDUCK_TOKEN>" } } } } ``` ## Release Process 1. Run the `Release New Version` GitHub Action 2. Enter version in `MAJOR.MINOR.PATCH` format 3. The workflow bumps version, publishes to PyPI/MCP registry, and creates the GitHub release with MCPB package ## License MIT License - see [LICENSE](LICENSE) file. ## mcp-name: io.github.motherduckdb/mcp-server-motherduck