📌Overview

Purpose: To provide a seamless integration of MotherDuck and local DuckDB for enhanced SQL analytics capabilities.

Overview: The MotherDuck MCP Server combines cloud-based SQL analytics from MotherDuck with local DuckDB, offering users the ability to execute SQL queries across both environments efficiently.

Key Features:

Hybrid execution: Allows querying data from both MotherDuck and local DuckDB, enabling flexibility in data management.
Cloud storage integration: Grants access to data stored in services like Amazon S3, streamlining data retrieval processes.
Data sharing: Facilitates the creation and sharing of databases, enhancing collaboration capabilities.
SQL analytics: Leverages DuckDB's SQL dialect for querying data, accommodating large datasets directly from the user interface.
Serverless architecture: Operates without the need for manual configuration of instances or clusters, simplifying the user experience.

MotherDuck's DuckDB MCP Server

An MCP server implementation that interacts with DuckDB and MotherDuck databases, providing SQL analytics capabilities to AI Assistants and IDEs.

Features

Hybrid execution: query data from local DuckDB or/and cloud-based MotherDuck databases
Cloud storage integration: access data stored in Amazon S3 or other cloud storage via MotherDuck's integrations
Data sharing: create and share databases
SQL analytics: use DuckDB's SQL dialect to query any size of data directly from your AI Assistant or IDE
Serverless architecture: run analytics without needing to configure instances or clusters

Components

Prompts

duckdb-motherduck-initial-prompt: A prompt to initialize a connection to DuckDB or MotherDuck and start working with it.

Tools

query: Execute a SQL query on the DuckDB or MotherDuck database
- Inputs:
  - query (string, required): The SQL query to execute

All interactions with DuckDB and MotherDuck are done through writing SQL queries.

Getting Started

General Prerequisites

uv installed (pip install uv or brew install uv)
If using with MCP compatible clients like Claude Desktop or Cursor, install the client first.

Prerequisites for DuckDB

No prerequisites for in-memory database creation.
Can connect to an existing local DuckDB database file or one stored on remote storage (e.g., AWS S3).

See Connect to local DuckDB section.

Prerequisites for MotherDuck

Sign up for a MotherDuck account: https://app.motherduck.com/?auth_flow=signup
Generate an access token via the MotherDuck UI: https://app.motherduck.com/settings/tokens?auth_flow=signup
Store the token securely for configuration.

Usage

With Cursor

Install Cursor from https://www.cursor.com/downloads if not already installed.
Open Cursor settings, add a new global MCP server by adding the following configuration to mcp.json:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        "md:",
        "--motherduck-token",
        "<YOUR_MOTHERDUCK_TOKEN_HERE>"
      ]
    }
  }
}

With VS Code

For quick installation, use the "Install with UV" buttons provided in tool integrations.
Alternatively, add the following to your User Settings (JSON) or workspace .vscode/mcp.json:

User Settings (JSON):

{
  "mcp": {
    "inputs": [
      {
        "type": "promptString",
        "id": "motherduck_token",
        "description": "MotherDuck Token",
        "password": true
      }
    ],
    "servers": {
      "motherduck": {
        "command": "uvx",
        "args": [
          "mcp-server-motherduck",
          "--db-path",
          "md:",
          "--motherduck-token",
          "${input:motherduck_token}"
        ]
      }
    }
  }
}

Workspace .vscode/mcp.json:

{
  "inputs": [
    {
      "type": "promptString",
      "id": "motherduck_token",
      "description": "MotherDuck Token",
      "password": true
    }
  ],
  "servers": {
    "motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        "md:",
        "--motherduck-token",
        "${input:motherduck_token}"
      ]
    }
  }
}

With Claude Desktop

Install Claude Desktop from https://claude.ai/download if not installed.
Open the Claude Desktop app, go to Settings > Developer tab > Edit Config, and add:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        "md:",
        "--motherduck-token",
        "<YOUR_MOTHERDUCK_TOKEN_HERE>"
      ]
    }
  }
}

Replace <YOUR_MOTHERDUCK_TOKEN_HERE> with your actual MotherDuck token.
Ensure the HOME environment variable is set properly for DuckDB file operations.

Securing your MCP Server when querying MotherDuck

For read-only access and enhanced security when exposing the MCP server to third parties, use a Read Scaling Token and run the MCP server in SaaS mode.

Read Scaling Tokens allow up to 4 concurrent read replicas with write restrictions, improving multi-user performance.
SaaS Mode restricts access to local files, databases, extensions, and configurations, ideal for third-party integrations.

Example secure configuration:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        "md:",
        "--motherduck-token",
        "<YOUR_READ_SCALING_TOKEN_HERE>",
        "--saas-mode"
      ]
    }
  }
}

Connect to local DuckDB

To use a local DuckDB database, specify the path instead of using a MotherDuck token.

In-memory database:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        ":memory:"
      ]
    }
  }
}

Local DuckDB file:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path",
        "/path/to/your/local.db"
      ]
    }
  }
}

Example Queries

You can ask your AI assistant to run queries such as:

Create a new database and table in MotherDuck
Query data from a local CSV file
Join data from local DuckDB with a MotherDuck table
Analyze data stored in Amazon S3

Testing

You can start the server manually specifying the database path:

Default MotherDuck database:

uvx mcp-server-motherduck --db-path md: --motherduck-token <your_motherduck_token>

Specific MotherDuck database:

uvx mcp-server-motherduck --db-path md:your_database_name --motherduck-token <your_motherduck_token>

Local DuckDB database:

uvx mcp-server-motherduck --db-path /path/to/your/local.db

In-memory database:

uvx mcp-server-motherduck --db-path :memory:

If the motherduck_token environment variable is set, the server connects automatically to md:.

Running in SSE mode

Run the server using supergateway:

npx -y supergateway --stdio "uvx mcp-server-motherduck --db-path md: --motherduck-token <your_motherduck_token>"

Clients like Claude Desktop and Cursor can connect to this SSE endpoint.

Development configuration

For local development, use:

{
  "mcpServers": {
    "mcp-server-motherduck": {
      "command": "uv",
      "args": [
        "--directory",
        "/path/to/your/local/mcp-server-motherduck",
        "run",
        "mcp-server-motherduck",
        "--db-path",
        "md:",
        "--motherduck-token",
        "<YOUR_MOTHERDUCK_TOKEN_HERE>"
      ]
    }
  }
}

Troubleshooting

Verify your MotherDuck token correctness if connection fails.
For local file access, ensure the --home-dir parameter is set properly.
Confirm uvx command is available in your system PATH.
For spawn uvx ENOENT errors, specify the full path to uvx.
Parameters replaced environment variables from version v0.4.0 onwards.

License

This MCP server is licensed under the MIT License. You are free to use, modify, and distribute the software under the terms of the MIT License. See the LICENSE file in the project repository for details.