Content
<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
