# DuckDB MCP Server
An MCP server implementation that provides SQL analytics capabilities for local DuckDB databases to AI Assistants and IDEs.
> **Note:** This project is based on the excellent [mcp-server-motherduck](https://github.com/motherduckdb/mcp-server-motherduck) implementation by MotherDuck. We've adapted it to focus on local DuckDB usage.
## Features
- **Local database support**: query data from local DuckDB databases or in-memory databases
- **Cloud storage integration**: access data stored in Amazon S3 or other cloud storage
- **SQL analytics**: use DuckDB's powerful SQL dialect to query data directly from your AI Assistant or IDE
- **Flexible connections**: support for file-based, in-memory, and S3-backed databases
## Components
### Prompts
The server provides one prompt:
- `duckdb-initial-prompt`: A prompt to initialize a connection to DuckDB and start working with it
### Tools
The server offers one tool:
- `query`: Execute a SQL query on the DuckDB database
- **Inputs**:
- `query` (string, required): The SQL query to execute
All interactions with DuckDB are done through writing SQL queries.
**Result Limiting**: Query results are automatically limited to prevent using up too much context:
- Maximum 1024 rows by default (configurable with `--max-rows`)
- Maximum 50,000 characters by default (configurable with `--max-chars`)
- Truncated responses include a note about truncation
## Command Line Parameters
The MCP server supports the following parameters:
| Parameter | Type | Default | Description |
|-----------|------|---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `--transport` | Choice | `stdio` | Transport type. Options: `stdio`, `sse`, `stream` |
| `--port` | Integer | `8000` | Port to listen on for sse and stream transport mode |
| `--host` | String | `127.0.0.1` | Host to bind the MCP server for sse and stream transport mode |
| `--db-path` | String | `:memory:` | Path to local DuckDB database file or S3 URL (e.g., `s3://bucket/path/to/db.duckdb`). Use `:memory:` for in-memory database |
| `--read-only` | Flag | `False` | Flag for connecting to DuckDB in read-only mode. Uses short-lived connections to enable concurrent access |
| `--home-dir` | String | `None` | Home directory for DuckDB (uses `HOME` env var by default) |
| `--json-response` | Flag | `False` | Enable JSON responses for HTTP stream. Only supported for `stream` transport |
| `--max-rows` | Integer | `1024` | Maximum number of rows to return from queries. |
| `--max-chars` | Integer | `50000` | Maximum number of characters in query results. |
| `--query-timeout` | Integer | `-1` | Query execution timeout in seconds. Set to -1 to disable timeout (default). |
### Quick Usage Examples
```bash
# Connect to in-memory database
uvx mcp-server-duckdb --db-path :memory:
# Connect to local DuckDB file
uvx mcp-server-duckdb --db-path /path/to/local.db
# Connect to local DuckDB file in read-only mode
uvx mcp-server-duckdb --db-path /path/to/local.db --read-only
# Customize result truncation limits
uvx mcp-server-duckdb --db-path /path/to/local.db --max-rows 2048 --max-chars 100000
# Enable query timeout (5 minutes)
uvx mcp-server-duckdb --db-path /path/to/local.db --query-timeout 300
```
## Getting Started
### General Prerequisites
- `uv` installed, you can install it using `pip install uv` or `brew install uv`
If you plan to use the MCP with Claude Desktop or any other MCP compatible client, the client needs to be installed.
### Prerequisites for DuckDB
- No prerequisites. The MCP server can create an in-memory database on-the-fly
- Or connect to an existing local DuckDB database file, or one stored on remote object storage (e.g., AWS S3).
### Usage with Cursor
1. Install Cursor from [cursor.com/downloads](https://www.cursor.com/downloads) if you haven't already
2. Open Cursor:
- To set it up globally for the first time, go to Settings->MCP and click on "+ Add new global MCP server".
- This will open a `mcp.json` file to which you add the following configuration:
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
```
### Usage with VS Code
Add the following JSON block to your User Settings (JSON) file in VS Code. You can do this by pressing `Ctrl + Shift + P` and typing `Preferences: Open User Settings (JSON)`.
```json
{
"mcp": {
"servers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
}
```
Optionally, you can add it to a file called `.vscode/mcp.json` in your workspace. This will allow you to share the configuration with others.
```json
{
"servers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
```
### Usage with Claude Desktop
1. Install Claude Desktop from [claude.ai/download](https://claude.ai/download) if you haven't already
2. Open the Claude Desktop configuration file:
- To quickly access it or create it the first time, open the Claude Desktop app, select Settings, and click on the "Developer" tab, finally click on the "Edit Config" button.
- Add the following configuration to your `claude_desktop_config.json`:
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
```
### Usage with Claude Code
Claude Code supports MCP servers through CLI commands or JSON configuration. Here are two ways to set it up:
#### Option 1: Using CLI Commands
Add the DuckDB MCP server directly using the Claude Code CLI:
```bash
claude mcp add duckdb uvx mcp-server-duckdb -- --db-path :memory:
```
#### Option 2: Using JSON Configuration
Add the server using a JSON configuration:
```bash
claude mcp add-json duckdb '{
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}'
```
**Scoping Options**:
- Use `--local` (default) for project-specific configuration
- Use `--project` to share the configuration with your team via `.mcp.json`
- Use `--user` to make the server available across all your projects
## Connect to local DuckDB
To connect to a local DuckDB, specify the path to your local DuckDB database file or use `:memory:` for an in-memory database.
In-memory database:
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
```
Local DuckDB file:
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
"/path/to/your/local.db"
]
}
}
}
```
Local DuckDB file in [readonly mode](https://duckdb.org/docs/stable/connect/concurrency.html):
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
"/path/to/your/local.db",
"--read-only"
]
}
}
}
```
**Note**: readonly mode for local file-backed DuckDB connections also makes use of
short lived connections. Each time the query MCP tool is used a temporary,
readonly connection is created + query is executed + connection is closed. This
feature was motivated by a workflow where [DBT](https://www.getdbt.com) was used for
modeling data within duckdb and then an MCP client (Windsurf/Cline/Claude/Cursor)
was used for exploring the database. The short lived connections allow each tool
to run and then release their connection, allowing the next tool to connect.
## Connect to DuckDB on S3
You can connect to DuckDB databases stored on Amazon S3 by providing an S3 URL as the database path. The server will automatically configure the necessary S3 credentials from your environment variables.
```json
{
"mcpServers": {
"duckdb": {
"command": "uvx",
"args": [
"mcp-server-duckdb",
"--db-path",
"s3://your-bucket/path/to/database.duckdb"
],
"env": {
"AWS_ACCESS_KEY_ID": "<your_key>",
"AWS_SECRET_ACCESS_KEY": "<your_secret>",
"AWS_DEFAULT_REGION": "<your_region>"
}
}
}
}
```
**Note**: For S3 connections:
- AWS credentials must be provided via environment variables (`AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY`, and optionally `AWS_DEFAULT_REGION`)
- For temporary credentials (AWS SSO), set the `AWS_SESSION_TOKEN` environment variable (and optionally `AWS_DEFAULT_REGION`) to automatically use DuckDB's `credential_chain` provider.
- The S3 database is attached to an in-memory DuckDB instance
- The httpfs extension is automatically installed and configured for S3 access
- Both read and write operations are supported
## Example Queries
Once configured, you can ask Claude to run queries like:
- "Create a new table in my DuckDB database"
- "Query data from my local CSV file"
- "Analyze data stored in Amazon S3"
- "Show me the schema of my database"
## Running in SSE mode
The server can run in SSE mode in two ways:
### Direct SSE mode
Run the server directly in SSE mode using the `--transport sse` flag:
```bash
uvx mcp-server-duckdb --transport sse --port 8000 --db-path :memory:
```
This will start the server listening on the specified port (default 8000) and you can point your clients directly to this endpoint.
### Using supergateway
Alternatively, you can run SSE mode using `supergateway`:
```bash
npx -y supergateway --stdio "uvx mcp-server-duckdb --db-path :memory:"
```
Both methods allow you to point your clients such as Claude Desktop, Cursor to the SSE endpoint.
## Development configuration
To run the server from a local development environment, use the following configuration:
```json
{
"mcpServers": {
"duckdb": {
"command": "uv",
"args": [
"--directory",
"/path/to/your/local/mcp-server-duckdb",
"run",
"mcp-server-duckdb",
"--db-path",
":memory:"
]
}
}
}
```
## Troubleshooting
- For local file access problems, ensure the `--home-dir` parameter is set correctly
- Check that the `uvx` command is available in your PATH
- If you encounter `spawn uvx ENOENT` errors, try specifying the full path to `uvx` (output of `which uvx`)
## Acknowledgments
This project is based on the [mcp-server-motherduck](https://github.com/motherduckdb/mcp-server-motherduck) implementation created by MotherDuck. We are grateful for their excellent work in creating the original MCP server for DuckDB integration.
## License
This MCP server is licensed under the MIT License. This means you are free to use, modify, and distribute the software, subject to the terms and conditions of the MIT License. For more details, please see the LICENSE file in the project repository.
