Unleash LLMs on Your Data: mcp-server-duckdb - A Powerful MCP Server for Local Data Analysis

This document details mcp-server-duckdb, a crucial component for bridging the gap between Large Language Models (LLMs) and the power of local data analysis using DuckDB. By implementing a Model Context Protocol (MCP) server, this tool enables seamless interaction between LLMs and DuckDB databases, opening up a world of possibilities for AI-driven data exploration and manipulation.

Core Functionality: Connecting LLMs to DuckDB

mcp-server-duckdb acts as a translator, allowing LLMs to understand and interact with data stored in DuckDB. This is achieved through the Model Context Protocol (MCP), a standardized communication framework for LLMs and external tools. The server exposes a query tool that allows LLMs to execute arbitrary SQL queries against the DuckDB database. This empowers LLMs to:

• Analyze data: Extract insights, identify trends, and perform complex calculations.
• Create and manage data: Define new tables, insert data, and modify existing structures (subject to read-only restrictions).
• Enhance AI workflows: Integrate database operations into custom AI pipelines.

Key Components and Design

The server's architecture is intentionally streamlined, focusing on providing a robust and flexible interface for LLM interaction.

Resources: (Currently None)

The current implementation does not define any custom resources. This reflects a design choice to prioritize the core functionality of SQL query execution.

Prompts: (Currently None)

Similarly, no custom prompts are implemented. The server relies on the LLM's ability to generate appropriate SQL based on user instructions.

Tools: The query Powerhouse

The heart of the server is the query tool, which provides a single, unified endpoint for all database interactions.

• Functionality: Executes any valid DuckDB SQL statement.
• Input: A string containing the SQL query (query).
• Output: Query results as text or a success message for data manipulation operations (e.g., CREATE TABLE, INSERT).

Rationale for a Unified query Function:

Instead of providing separate, specialized functions for different database operations (e.g., select, create_table), mcp-server-duckdb leverages the advanced SQL generation capabilities of modern LLMs. This approach offers several advantages:

• Flexibility: LLMs can dynamically construct complex SQL queries, including SELECT, CREATE TABLE, JOIN, and more, without requiring pre-defined endpoints.
• Reduced Complexity: Simplifies the server's design and maintenance.
• Efficiency: Avoids the overhead of managing multiple specialized functions.

Read-Only Mode for Enhanced Security:

The server supports a crucial --readonly mode, which enforces DuckDB's native read-only protection. When enabled:

• The DuckDB database is opened with read_only=True, preventing any write operations.
• If the database file does not exist, it will not be created.
• This safeguards data integrity by preventing LLMs from inadvertently modifying the database.

Configuration: Tailoring the Server to Your Needs

The server's behavior is controlled through command-line parameters:

Required Parameter: --db-path

• Description: Specifies the path to the DuckDB database file.
• Behavior:
  • If the file and parent directories do not exist, the server will automatically create them.
  • In --readonly mode, the server will fail to start if the database file does not exist.

Optional Parameter: --readonly

• Description: Enables read-only mode.
• Security Implication: Prevents LLMs from performing write operations (e.g., CREATE, INSERT, UPDATE, DELETE).
• Reference: See the DuckDB Python API documentation for more details on read-only connections.

Installation and Integration

Installation via Smithery

The recommended installation method is through Smithery, which simplifies the process:

npx -y @smithery/cli install mcp-server-duckdb --client claude

Claude Desktop Integration

To integrate mcp-server-duckdb with Claude Desktop, configure the MCP server in the claude_desktop_config.json file:

MacOS

Location: ~/Library/Application Support/Claude/claude_desktop_config.json

Windows

Location: %APPDATA%/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "duckdb": {
      "command": "uvx",
      "args": [
        "mcp-server-duckdb",
        "--db-path",
        "~/mcp-server-duckdb/data/data.db"
      ]
    }
  }
}

Important: Replace ~/mcp-server-duckdb/data/data.db with the actual path to your DuckDB database file.

Development and Debugging

Prerequisites

• Python with the uv package manager
• DuckDB Python package
• MCP server dependencies

Debugging with MCP Inspector

Debugging MCP servers can be challenging due to their stdio-based communication. The MCP Inspector is highly recommended for a streamlined debugging experience.

Steps:

1. Install the inspector using npm:

   npx @modelcontextprotocol/inspector uv --directory ~/codes/mcp-server-duckdb run mcp-server-duckdb --db-path ~/mcp-server-duckdb/data/data.db

2. Open the provided URL in your browser to access the debugging interface.

The inspector provides valuable insights into:

• Request/response communication
• Tool execution
• Server state
• Error messages

By leveraging the MCP Inspector, developers can efficiently identify and resolve issues, ensuring the smooth operation of mcp-server-duckdb.