Skip to main content

​
Connecting to Local Files

Local DuckDB files (.duckdb extension) provide persistent storage with file-based databases. Read-only mode allows concurrent access from multiple processes: 
{
  "mcpServers": {
    "DuckDB (read-only)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/absolute/path/to/your.duckdb"
      ]
    }
  }
}
Read-only mode uses ephemeral connections (enabled by default with --ephemeral-connections) that don’t hold file locks, allowing multiple readers.

​
Read-Write Connection

For write operations, enable read-write mode: 
{
  "mcpServers": {
    "DuckDB (read-write)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/absolute/path/to/your.duckdb",
        "--read-write"
      ]
    }
  }
}
Read-write mode holds an exclusive lock on the database file. Only one read-write connection can be active at a time.

​
Creating New Databases

Create a new DuckDB file automatically when it doesn’t exist: 
{
  "mcpServers": {
    "DuckDB (new)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/path/to/new_database.duckdb",
        "--read-write"
      ]
    }
  }
}
The database file will be created on first connection.

​
Working with Local Data

​
Create Tables

{
  "name": "execute_query",
  "arguments": {
    "sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR, email VARCHAR, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"
  }
}

​
Insert Data

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', '[email protected]'),
  (2, 'Bob', '[email protected]'),
  (3, 'Charlie', '[email protected]');

​
Query Data

{
  "name": "execute_query",
  "arguments": {
    "sql": "SELECT * FROM users ORDER BY id"
  }
}

​
Update and Delete

UPDATE users 
SET email = '[email protected]' 
WHERE id = 1;

​
Importing and Exporting Data

​
Import from CSV

CREATE TABLE sales AS 
SELECT * FROM read_csv_auto('/path/to/sales_data.csv');
DuckDB’s read_csv_auto() automatically detects column types, delimiters, and headers.

​
Import from Parquet

CREATE TABLE events AS 
SELECT * FROM read_parquet('/path/to/events/*.parquet');

​
Import from JSON

CREATE TABLE logs AS 
SELECT * FROM read_json_auto('/path/to/logs.json');

​
Export to CSV

COPY (SELECT * FROM users WHERE created_at > '2024-01-01') 
TO '/path/to/export/users.csv' 
WITH (HEADER, DELIMITER ',');

​
Export to Parquet

COPY users TO '/path/to/export/users.parquet' (FORMAT PARQUET);
Export operations require --read-write mode and may be restricted by --motherduck-saas-mode.

​
Export to JSON

COPY (SELECT * FROM users) TO '/path/to/export/users.json' (FORMAT JSON);

​
Switching Between Databases

The switch_database_connection tool lets you change databases dynamically.
Requires --allow-switch-databases flag to be enabled when starting the server.

​
Enable Database Switching

{
  "mcpServers": {
    "DuckDB (flexible)": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", ":memory:",
        "--read-write",
        "--allow-switch-databases"
      ]
    }
  }
}

​
Switch to Existing Database

{
  "name": "switch_database_connection",
  "arguments": {
    "path": "/path/to/another.duckdb"
  }
}

​
Create New Database on Switch

{
  "name": "switch_database_connection",
  "arguments": {
    "path": "/path/to/new_db.duckdb",
    "create_if_not_exists": true
  }
}
Creating databases requires the server to be started with --read-write mode.

​
Switch Between Local and In-Memory

{
  "name": "switch_database_connection",
  "arguments": {
    "path": ":memory:"
  }
}

​
Concurrent Access Patterns

​
Multiple Read-Only Clients

Multiple processes can read from the same file simultaneously: 
{
  "command": "uvx",
  "args": ["mcp-server-motherduck", "--db-path", "/shared/data.duckdb"]
}

​
Single Writer, Multiple Readers

One read-write connection alongside multiple read-only connections: 
{
  "command": "uvx",
  "args": [
    "mcp-server-motherduck",
    "--db-path", "/shared/data.duckdb",
    "--read-write"
  ]
}
If a file lock error occurs, ensure --ephemeral-connections is enabled (it’s on by default) and you’re not running multiple read-write connections.

​
Performance Optimization

​
Use Absolute Paths

{
  "args": ["--db-path", "/home/user/data/analytics.duckdb"]
}

​
Persistent vs Ephemeral Connections

# Releases file lock between queries
uvx mcp-server-motherduck --db-path /path/to/db.duckdb

​
Initialize with SQL

Run setup SQL on connection: 
{
  "mcpServers": {
    "DuckDB": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/path/to/db.duckdb",
        "--read-write",
        "--init-sql", "PRAGMA threads=4; PRAGMA memory_limit='4GB';"
      ]
    }
  }
}
Or use an SQL file: 
--init-sql /path/to/init.sql

​
Real-World Examples

​
Analytics Dashboard Backend

{
  "mcpServers": {
    "Analytics DB": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/data/analytics.duckdb",
        "--max-rows", "5000",
        "--query-timeout", "30"
      ]
    }
  }
}

​
ETL Pipeline Database

{
  "mcpServers": {
    "ETL Staging": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", "/etl/staging.duckdb",
        "--read-write",
        "--init-sql", "PRAGMA temp_directory='/fast/ssd/temp';"
      ]
    }
  }
}

​
Local Data Exploration

{
  "mcpServers": {
    "Data Explorer": {
      "command": "uvx",
      "args": [
        "mcp-server-motherduck",
        "--db-path", ":memory:",
        "--read-write",
        "--allow-switch-databases"
      ]
    }
  }
}
Now you can dynamically load different datasets: 
-- Load dataset 1
CREATE TABLE sales AS SELECT * FROM read_csv_auto('/datasets/sales.csv');

-- Switch to different database
-- Use switch_database_connection tool

-- Load dataset 2
CREATE TABLE customers AS SELECT * FROM read_parquet('/datasets/customers.parquet');

​
Troubleshooting

​
File Locked Error

Error: “Database file is locked” or “spawn uvx ENOENT” Solution:
  1. Ensure --ephemeral-connections is enabled (default)
  2. Close other read-write connections to the file
  3. Check that no other process has the file open

​
Permission Denied

Error: “Permission denied” when accessing database file Solution: Verify file permissions and that the path is accessible: 
ls -la /path/to/database.duckdb
chmod 644 /path/to/database.duckdb  # If needed

​
Relative Path Error

Error: “Relative paths are not allowed” Solution: Use absolute paths: 
// Bad
"--db-path", "./data.duckdb"

// Good  
"--db-path", "/home/user/project/data.duckdb"

​
Database Does Not Exist

Error: “Database file does not exist” Solution: Either create the file first or use create_if_not_exists when switching: 
{
  "name": "switch_database_connection",
  "arguments": {
    "path": "/path/to/new.duckdb",
    "create_if_not_exists": true
  }
}

Build docs developers (and LLMs) love

Get started for freeTalk to us