Repository: https://github.com/pypi-ahmad/duckdb-analytics-mcp

# Project 3: Production-Grade MCP Server (DuckDB Analytics)

A **simple but powerful**, deployable MCP server built with the official Python MCP SDK.

This server exposes secure, read-only analytics tools over local CSV/Parquet/JSON datasets using DuckDB.

- Server name: `duckdb_analytics_mcp`
- MCP transports: `stdio` and `streamable-http`
- Runtime: Python `3.12.10` with `uv`

## Why this project is resume-worthy

- Implements a real MCP server (not toy tools) with production concerns: validation, pagination, security guardrails, CI, Docker, and typed code.
- Uses a practical architecture: thin MCP layer + testable service layer.
- Ships with evaluation fixtures (`evaluations/duckdb_analytics_eval.xml`) and sample datasets for reproducible demos.

## What it can do

### Tools

1. `duckdb_analytics_health`
- Server health and dataset catalog status.

2. `duckdb_analytics_list_datasets`
- Paginated dataset discovery with metadata.

3. `duckdb_analytics_describe_dataset`
- Schema + row count + sample rows for a dataset.

4. `duckdb_analytics_query_dataset`
- Read-only SQL query execution against a dataset using `source` alias.
- Supports pagination and Markdown/JSON responses.

### Resources

- `dataset://catalog`
- `dataset://schema/{dataset_name}`

## Security and production hardening

- Read-only SQL guard (`SELECT`/`WITH` only).
- Blocks dangerous SQL keywords and SQL comments.
- Single-statement enforcement (no multi-statement execution).
- Query length, row limit, and timeout controls.
- DuckDB extension auto-install/loading disabled.
- Stdio-safe logging to `stderr` (never `stdout`).
- Optional bearer token auth using MCP auth primitives (`STATIC_BEARER_TOKEN` + auth URLs).

## Project structure

```text
.
├── src/duckdb_analytics_mcp/
│   ├── config.py
│   ├── security.py
│   ├── catalog.py
│   ├── service.py
│   ├── formatter.py
│   ├── server.py
│   └── cli.py
├── tests/
├── data/
├── evaluations/
├── Dockerfile
└── .github/workflows/ci.yml
```

## Setup (uv + Python 3.12.10)

```bash
git clone https://github.com/pypi-ahmad/duckdb-analytics-mcp.git
cd duckdb-analytics-mcp
```


```bash
uv python pin 3.12.10
uv sync --dev
cp .env.example .env
```

## Run

### Streamable HTTP (remote deploy/default)

```bash
uv run duckdb-analytics-mcp run --transport streamable-http
```

Default endpoint:
- `http://127.0.0.1:8000/mcp`

If port `8000` is busy:

```bash
PORT=8015 uv run duckdb-analytics-mcp run --transport streamable-http
```

### stdio (local MCP client integration)

```bash
uv run duckdb-analytics-mcp run --transport stdio
```

### Deployment readiness check

```bash
uv run duckdb-analytics-mcp doctor
```

## Real validation outputs (from latest run)

`uv run duckdb-analytics-mcp doctor`:

```json
{
  "health": {
    "status": "ok",
    "server": "duckdb_analytics_mcp",
    "dataset_dir": "/home/ahmad/AI/Projects/duckdb-analytics-mcp/data",
    "dataset_count": 2,
    "checked_at": "2026-06-12T07:13:37.540625Z"
  },
  "dataset_preview_count": 2,
  "dataset_total": 2
}
```

Revenue query result (`sales_orders.csv`):

```json
{
  "dataset": "sales_orders.csv",
  "total_count": 4,
  "rows": [
    {"region": "North", "revenue": 24646.93},
    {"region": "West", "revenue": 23259.54},
    {"region": "South", "revenue": 18548.94},
    {"region": "East", "revenue": 14552.55}
  ]
}
```

## Testing and quality gates

```bash
uv run ruff check .
uv run mypy src
uv run pytest
```

Current status:
- `ruff`: pass
- `mypy`: pass
- `pytest`: pass (`10 passed`)

## Docker

Build and run:

```bash
docker build -t duckdb-analytics-mcp:latest .
docker run --rm -p 8000:8000 \
  -e HOST=0.0.0.0 \
  -e PORT=8000 \
  -e TRANSPORT=streamable-http \
  duckdb-analytics-mcp:latest
```

Or with Compose:

```bash
docker compose up --build
```

## MCP client config example (stdio)

```json
{
  "mcpServers": {
    "duckdb-analytics": {
      "command": "uv",
      "args": [
        "run",
        "--project",
        "/home/ahmad/AI/Projects/duckdb-analytics-mcp",
        "duckdb-analytics-mcp",
        "run",
        "--transport",
        "stdio"
      ]
    }
  }
}
```

## Tradeoff (intentional)

The server is intentionally **read-only** and blocks write/admin SQL paths. This reduces blast radius for production MCP usage at the cost of not supporting ETL/DDL workflows.