Skip to main content

​
Connecting to MotherDuck

MotherDuck is a serverless analytics platform built on DuckDB. The MCP server supports both read-write and read-scaling connections.

​
Read-Write Connection

For full read and write access to your MotherDuck databases: 
{
  "mcpServers": {
    "MotherDuck (local, r/w)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "md:",
        "--read-write"
      ],
      "env": {
        "motherduck_token": "<YOUR_MOTHERDUCK_TOKEN>"
      }
    }
  }
}
Get your MotherDuck token from motherduck.com/settings/tokens

​
Read-Scaling Connection

For high-performance read-only access with automatic scaling: 
{
  "mcpServers": {
    "MotherDuck (read-scaling)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "md:"
      ],
      "env": {
        "motherduck_token": "<YOUR_READ_SCALING_TOKEN>"
      }
    }
  }
}
Read-scaling connections require a read-scaling token, not a regular token.

​
SaaS Mode (Secure Deployments)

SaaS mode restricts local filesystem access for secure third-party deployments: 
{
  "mcpServers": {
    "MotherDuck (SaaS)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "md:",
        "--read-write",
        "--motherduck-saas-mode"
      ],
      "env": {
        "motherduck_token": "<SERVICE_ACCOUNT_TOKEN>"
      }
    }
  }
}
For production deployments, use service accounts with read-scaling tokens and enable SaaS mode to restrict local access.

​
Querying MotherDuck Databases

​
Query Shared Sample Data

MotherDuck provides sample datasets for exploration: 
{
  "name": "execute_query",
  "arguments": {
    "sql": "SELECT title, year, rating FROM sample_data.kaggle.movies WHERE year > 2020 ORDER BY rating DESC LIMIT 5"
  }
}

​
List MotherDuck Databases

Use the list_databases tool to see all available databases: 
{
  "name": "list_databases",
  "arguments": {}
}

​
Query Your Own Databases

USE my_db;

​
Cross-Database Queries

Query across multiple databases in a single SQL statement: 
SELECT 
  u.name,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_spent
FROM my_db.main.users u
LEFT JOIN analytics.sales.orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY total_spent DESC
LIMIT 10

​
Attaching Multiple Databases

You can attach and query multiple MotherDuck databases simultaneously.

​
Attach Additional Databases

ATTACH 'md:analytics' AS analytics;
ATTACH 'md:warehouse' AS warehouse;

​
List Attached Databases

{
  "name": "execute_query",
  "arguments": {
    "sql": "SELECT database_name, type FROM duckdb_databases() WHERE database_name NOT IN ('system', 'temp')"
  }
}

​
Query Across Attached Databases

SELECT 
  'my_db' as source,
  COUNT(*) as record_count 
FROM my_db.main.users

UNION ALL

SELECT 
  'analytics' as source,
  COUNT(*) as record_count 
FROM analytics.main.events

UNION ALL

SELECT 
  'warehouse' as source,
  COUNT(*) as record_count 
FROM warehouse.main.transactions

​
MotherDuck-Specific Features

​
Check Current Database

{
  "name": "execute_query",
  "arguments": {
    "sql": "SELECT current_database() as db"
  }
}

​
MotherDuck Metadata Function

MotherDuck provides special functions for database metadata: 
SELECT alias, type, status 
FROM MD_ALL_DATABASES() 
WHERE alias IS NOT NULL
ORDER BY alias;

​
Check Connection Type

Verify if you’re using a read-scaling connection: 
SELECT * FROM __md_duckling_id();
Read-scaling connections have duckling IDs ending with .rs.{number} (e.g., my_database.rs.3), while read-write connections end with .rw.

​
Hybrid Local + Cloud Queries

Combine local DuckDB files with MotherDuck cloud databases in the same query.

​
Attach Local Database to MotherDuck Session

ATTACH '/path/to/local.duckdb' AS local_db (READ_ONLY);

​
Query Local and Cloud Together

SELECT 
  'cloud' as source,
  COUNT(*) as users
FROM my_db.main.users

UNION ALL

SELECT 
  'local' as source,
  COUNT(*) as users
FROM local_db.main.users

​
Export Cloud Data to Local

CREATE TABLE local_db.main.cloud_export AS 
SELECT * FROM my_db.main.large_table 
WHERE created_at > '2024-01-01';

​
Connection Parameters

Customize MotherDuck connection behavior with additional parameters: 
{
  "mcpServers": {
    "MotherDuck": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "md:",
        "--read-write",
        "--motherduck-connection-parameters", "session_hint=analytics&dbinstance_inactivity_ttl=0s"
      ],
      "env": {
        "motherduck_token": "<YOUR_TOKEN>"
      }
    }
  }
}

​
Default Parameters

The server includes these parameters by default:
  • session_hint=mcp - Identifies MCP sessions in MotherDuck logs
  • dbinstance_inactivity_ttl=0s - Keeps connections alive indefinitely

​
Best Practices

Use Read-Scaling for Analytics

Read-scaling tokens provide better performance for read-heavy analytical workloads with automatic horizontal scaling.

Service Accounts for Production

Use service accounts instead of personal tokens for production deployments.

Enable SaaS Mode

When giving third-party access, enable --motherduck-saas-mode to restrict local filesystem access.

Filter Cloud Queries

Always use WHERE clauses and LIMIT when querying large cloud datasets to minimize data transfer.

​
Troubleshooting

​
Token Authentication Errors

Error: “Please set the motherduck_token or MOTHERDUCK_TOKEN as an environment variable” Solution: Set the token in your MCP configuration: 
"env": {
  "motherduck_token": "your_token_here"
}

​
Read-Scaling Token Required

Error: “The —read-only flag with MotherDuck requires a read-scaling token” Solution: Either:
  1. Use a read-scaling token instead of a regular token, OR
  2. Add --read-write flag to use regular tokens

​
Connection Timeout

Error: Connection timeouts or slow queries Solution: Check your network connection and MotherDuck service status. Consider using connection parameters: 
--motherduck-connection-parameters "dbinstance_inactivity_ttl=300s"

Build docs developers (and LLMs) love

Get started for freeTalk to us