# Query Executor MCP Server

A [Model Context Protocol](https://modelcontextprotocol.io) server that gives AI agents read (and optionally write) access to one or more PostgreSQL databases. Each database is registered as a named **project** — the agent picks the right one per call via `project_id`.

---

## How it works

```mermaid
flowchart TD
    A[AI Agent\nClaude / Cursor / etc.] -->|MCP stdio| B[Query Executor\nMCP Server]

    B --> C{Resolve project_id}
    C -->|default| D[(default DB)]
    C -->|project-alpha| E[(project-alpha DB)]
    C -->|project-beta| F[(project-beta DB)]

    subgraph Tools
        T1[describe_postgres_schema]
        T2[execute_postgres]
        T3[explain_postgres]
        T4[pg_stat_statements]
    end

    B --> Tools
    Tools --> C
```

```mermaid
sequenceDiagram
    participant Agent
    participant Server as MCP Server
    participant Config as databases.json
    participant DB as PostgreSQL

    Agent->>Server: tool call (project_id, sql)
    Server->>Config: resolve project_id → DSN + mode
    Config-->>Server: { dsn, mode }
    alt mode = readonly
        Server->>Server: assert first token is SELECT/WITH
    end
    Server->>DB: execute query
    DB-->>Server: rows
    Server-->>Agent: JSON { rows, row_count }
```

---

## Features

- **Multi-project** — connect to any number of PostgreSQL databases simultaneously; each call targets one via `project_id`
- **Per-project mode** — `readonly` blocks all writes at the first SQL token; `readwrite` allows all SQL
- **Safe fallback** — omitting `project_id` routes to the configured default project
- **Four tools** — schema inspection, query execution, EXPLAIN ANALYZE, and slow-query analysis
- **stdio transport** — works with any MCP client (Claude Desktop, Cursor, Claude Code, etc.)
- **Docker-ready** — single image, credentials baked in at build time from gitignored files

---

## Project layout

```
query-executor/
├── Dockerfile
├── Makefile
├── pyproject.toml
├── uv.lock
├── main.py                     # connection check entrypoint
├── .env                        # gitignored — copy from .env.example
├── databases.json              # gitignored — copy from databases.example.json
├── .env.example
├── databases.example.json
└── query_executor/
    ├── config.py               # loads .env + databases.json; single source of truth
    ├── query_connector.py      # raw asyncpg functions (no MCP imports)
    ├── tools.py                # Pydantic input models + tool implementations
    └── server.py               # FastMCP bootstrap + entrypoint
```

---

## Quick start

**Requires:** [UV](https://docs.astral.sh/uv/) and [Docker](https://docs.docker.com/get-docker/).

### 1. Install dependencies

```bash
uv sync
```

### 2. Configure

```bash
cp .env.example .env
cp databases.example.json databases.json
```

Edit `databases.json` with your real connection strings:

```json
{
  "default": {
    "dsn": "postgresql://user:password@localhost:5432/mydb",
    "mode": "readonly"
  },
  "staging": {
    "dsn": "postgresql://user:password@staging-host:5432/stagingdb",
    "mode": "readwrite"
  }
}
```

| `mode`      | Behaviour |
|-------------|-----------|
| `readonly`  | Only `SELECT` / `WITH` queries are allowed. Any write or DDL raises an error before reaching the database. |
| `readwrite` | All SQL is permitted. Use only on non-production databases. |

If a project does not specify `mode`, it defaults to `readonly`.

### 3. Check connections

```bash
make check
```

```
Welcome to Query Executor!
  Default project : default
    • default  [readonly] (default)
    • staging  [readwrite]

Testing database connections...
  [default]  mode=readonly  ... OK — PostgreSQL 15.4
  [staging]  mode=readwrite ... OK — PostgreSQL 15.4

All 2 connection(s) OK.
```

### 4. Build the Docker image

```bash
make build
```

---

## MCP client configuration

The server runs over **stdio** — the MCP client spawns the process and communicates via stdin/stdout.

### Claude Desktop

Config file: `~/Library/Application Support/Claude/claude_desktop_config.json`

**With UV (local):**
```json
{
  "mcpServers": {
    "query-executor": {
      "command": "uv",
      "args": ["run", "python", "-m", "query_executor.server"],
      "cwd": "/Users/niteshnandan/workspace/2026/query-executor"
    }
  }
}
```

**With Docker:**
```json
{
  "mcpServers": {
    "query-executor": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "query-executor"]
    }
  }
}
```

### Cursor

Global config: `~/.cursor/mcp.json`  
Project config: `.cursor/mcp.json`

**With UV (local):**
```json
{
  "mcpServers": {
    "query-executor": {
      "command": "uv",
      "args": ["run", "python", "-m", "query_executor.server"],
      "cwd": "/Users/niteshnandan/workspace/2026/query-executor"
    }
  }
}
```

**With Docker:**
```json
{
  "mcpServers": {
    "query-executor": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "query-executor"]
    }
  }
}
```

### Claude Code

Add to your project's `.claude/mcp.json`:

**With UV (local):**
```json
{
  "mcpServers": {
    "query-executor": {
      "type": "stdio",
      "command": "uv",
      "args": ["run", "python", "-m", "query_executor.server"],
      "cwd": "/Users/niteshnandan/workspace/2026/query-executor"
    }
  }
}
```

**With Docker:**
```json
{
  "mcpServers": {
    "query-executor": {
      "type": "stdio",
      "command": "docker",
      "args": ["run", "-i", "--rm", "query-executor"]
    }
  }
}
```

> **Note:** Replace `/Users/niteshnandan/workspace/2026/query-executor` with the actual path on your machine if sharing config with others.

---

## Tools reference

### `describe_postgres_schema`

Inspect tables, columns, foreign keys, and indexes for a given schema.

**Call this first** before writing any query — it gives you exact column names and types so you write correct SQL on the first attempt.

| Parameter        | Type   | Default         | Description |
|------------------|--------|-----------------|-------------|
| `postgres_schema`| string | `"public"`      | Schema to inspect |
| `project_id`     | string | default project | Target database |

Returns `{ columns, foreign_keys, indexes }`.

---

### `execute_postgres`

Run a SQL query and get rows back as JSON.

| Parameter    | Type   | Required | Description |
|--------------|--------|----------|-------------|
| `sql`        | string | yes      | SQL to execute. In `readonly` mode only SELECT/WITH are allowed. |
| `project_id` | string | no       | Target database |

Returns `{ rows: [...], row_count: N }`.

---

### `explain_postgres`

Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` on a query and return the execution plan.

| Parameter    | Type   | Required | Description |
|--------------|--------|----------|-------------|
| `sql`        | string | yes      | SELECT query to analyse (do not include `EXPLAIN`) |
| `project_id` | string | no       | Target database |

Returns `{ plan: [...] }`. Key things to check in the plan:

- **Seq Scan on a large table** → missing index on the `WHERE` column
- **Actual rows ≫ Plan rows** → stale statistics; run `ANALYZE <table>`
- **High shared_blks_read** → I/O-bound; working set does not fit in `shared_buffers`

---

### `pg_stat_statements`

Return the top N query patterns ranked by total cumulative execution time.

| Parameter    | Type    | Default | Description |
|--------------|---------|---------|-------------|
| `limit`      | integer | 20      | Number of queries to return (max 100) |
| `project_id` | string  | no      | Target database |

Returns rows with `total_exec_sec`, `mean_exec_sec`, `max_exec_sec`, `calls`, `shared_blks_hit`, `shared_blks_read`.

Requires the `pg_stat_statements` extension. Enabled by default on AWS RDS, GCP Cloud SQL, and Supabase. On self-hosted Postgres: `CREATE EXTENSION pg_stat_statements;`

---

## Recommended workflows

**Explore an unknown database**
```
describe_postgres_schema → execute_postgres
```

**Debug a slow query**
```
describe_postgres_schema  (check what indexes exist)
→ explain_postgres        (verify the planner uses them)
→ execute_postgres        (run once plan looks correct)
```

**Performance audit**
```
pg_stat_statements        (find the most expensive patterns)
→ explain_postgres        (drill into the worst offender)
→ describe_postgres_schema (check if a missing index would help)
```

---

## Environment variables

| Variable                        | Default          | Description |
|---------------------------------|------------------|-------------|
| `DATABASES_FILE`                | `databases.json` | Path to the databases registry file |
| `DEFAULT_PROJECT`               | `default`        | Fallback project when `project_id` is omitted |
| `LOG_LEVEL`                     | `INFO`           | Python logging level |
| `CONNECT_TIMEOUT`               | `10.0`           | asyncpg connection timeout in seconds |
| `EXPLAIN_TIMEOUT_MS`            | `30000`          | Statement timeout for EXPLAIN ANALYZE in milliseconds |
| `STAT_STATEMENTS_DEFAULT_LIMIT` | `20`             | Default row limit for `pg_stat_statements` |