DuckDB MCP Server

Common errors

• Command 'mcp-server-duckdb' not found

  cause The `mcp-server-duckdb` package is not installed, or its executable script is not in your system's PATH.
  fix Ensure the package is installed using `pip install mcp-server-duckdb` and that your shell's PATH includes the directory where pip installs scripts (e.g., `~/.local/bin` on Linux/macOS or `Scripts` directory in your Python environment on Windows).

• RuntimeError: Attempted to write to a database in read-only mode.

  cause The server was started with the `--readonly` flag, preventing any data modification or schema changes.
  fix If you intend to perform write operations, restart the `mcp-server-duckdb` server without the `--readonly` flag.

• requests.exceptions.HTTPError: 422 Unprocessable Entity (or similar for missing query parameter)

  cause The request body for the `/queries` endpoint is malformed or missing the required `query` field.
  fix Ensure your POST request to `/queries` sends a JSON body like `{'query': 'YOUR_SQL_QUERY_HERE'}` and the `Content-Type` header is `application/json`.

Warnings

• breaking The API for database queries was consolidated into a single `/queries` endpoint. Before v1.0.0, there were separate endpoints for read and write operations (e.g., `/read-query`, `/write-query`).
  Fix: Migrate all database interactions to use the `/queries` endpoint with a single `query` parameter in the request body.
• gotcha Temporary tables and macros created within a session will not persist across multiple queries by default. Each query opens and closes a new DuckDB connection.
  Fix: Update to v1.1.0 or later and start the server with the `--keep-connection` flag to maintain a persistent DuckDB connection across queries.
• gotcha Attempting write operations (e.g., INSERT, UPDATE, CREATE TABLE) when the server is started with the `--readonly` flag will result in an error.
  Fix: Ensure the server is not started with `--readonly` if write operations are intended. If using an older version (<0.2.2), fix for readonly logic might be necessary.

Install

• pip install mcp-server-duckdb Install stable version

Imports

• run_server

  from mcp_server_duckdb.main import run_server

  Primarily intended for CLI execution, but can be called programmatically to start the server. Best practice is to use the `mcp-server-duckdb` CLI command.
• create_app

  from mcp_server_duckdb.server import create_app

  Use to create the FastAPI application instance for testing or embedding within another ASGI application. Requires a `Config` object.

Quickstart

This quickstart demonstrates how to start the `mcp-server-duckdb` server programmatically and then interact with it using HTTP requests. It creates a simple table, inserts data, and queries it. For persistent databases, specify a file path with `--db-path` when starting the server.

import os
import requests
from subprocess import Popen, PIPE, TimeoutExpired
import time

# Start the server in a separate process
# For simplicity, we use a temporary in-memory database here
# In production, specify a persistent .duckdb file, e.g., --db-path my_database.duckdb
print('Starting mcp-server-duckdb...')
server_process = Popen(['mcp-server-duckdb', '--port', '8001'], stdout=PIPE, stderr=PIPE)
time.sleep(2) # Give the server a moment to start

# Example: Send a query
try:
    # Create a table
    response = requests.post(
        'http://localhost:8001/queries',
        json={'query': "CREATE TABLE users (id INTEGER, name VARCHAR);"}
    )
    response.raise_for_status()
    print('CREATE TABLE response:', response.json())

    # Insert data
    response = requests.post(
        'http://localhost:8001/queries',
        json={'query': "INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');"}
    )
    response.raise_for_status()
    print('INSERT INTO response:', response.json())

    # Select data
    response = requests.post(
        'http://localhost:8001/queries',
        json={'query': "SELECT * FROM users;"}
    )
    response.raise_for_status()
    print('SELECT * response:', response.json())

except requests.exceptions.RequestException as e:
    print(f"Error communicating with server: {e}")
finally:
    # Terminate the server process
    print('Stopping mcp-server-duckdb...')
    server_process.terminate()
    try:
        stdout, stderr = server_process.communicate(timeout=5)
        print('Server stdout:', stdout.decode())
        print('Server stderr:', stderr.decode())
    except TimeoutExpired:
        server_process.kill()
        stdout, stderr = server_process.communicate()
        print('Server (killed) stdout:', stdout.decode())
        print('Server (killed) stderr:', stderr.decode())