# DB Graph MCP Server

An MCP (Model Context Protocol) server that connects to a SQL database and
returns query results as chart images. Any MCP-compatible AI client (Claude
Desktop, Claude.ai, Claude Code, etc.) can call it and display the chart
directly in the conversation.

## How it works

- `list_tables` — lets the AI see what tables/columns exist in your database.
- `query_and_visualize` — runs a `SELECT` query you (or the AI) provide,
  then renders the result as a bar / line / scatter / pie / histogram chart
  and returns it **inline** as a PNG image. Use this with GUI-based MCP
  clients (Claude Desktop, Claude.ai, Claude Code) that can display an
  image directly in the conversation.
- `query_and_visualize_to_file` — same chart types and query logic, but
  **saves the PNG to disk** and returns the file path instead of sending
  the image inline. Use this with terminal-based MCP clients (e.g. Gemini
  CLI) that can't render images in the chat — the AI will tell you the
  path, and you open it separately.

## 1. Install dependencies

```bash
pip install -r requirements.txt
```

If you're connecting to Postgres or MySQL, also install the driver:

```bash
pip install psycopg2-binary   # Postgres
# or
pip install pymysql           # MySQL
```

## 2. Point it at your database

Copy `.env.example` to `.env` and fill in your real connection string:

```bash
cp .env.example .env
```

Then edit `.env`:

```bash
# SQLite (good for local testing)
DB_URL=sqlite:///./example.db

# Postgres
DB_URL=postgresql+psycopg2://user:password@host:5432/dbname

# MySQL
DB_URL=mysql+pymysql://user:password@host:3306/dbname
```

`.env` is git-ignored, so credentials won't get committed. The server loads
it automatically on startup. You can also set `DB_URL` as a real OS
environment variable, or via the `env` block in your MCP client config
(e.g. Claude Desktop) — any of these work, and an OS/config-level value
takes priority over `.env`.

If `DB_URL` isn't set anywhere, it defaults to a local SQLite file
`example.db`.

## 3. Test it locally

Run the automated test suite (spins up a temporary SQLite DB, doesn't
touch your real `DB_URL`):

```bash
pytest test_server.py -v
```

This covers the happy path plus edge cases: blocked destructive/chained
queries, invalid chart types, empty results, mismatched columns,
non-numeric columns for pie/scatter, and row-limit truncation.

You can also use the MCP inspector to interactively test tool calls
(requires Node.js/npx):

```bash
mcp dev server.py
```

## 4. Connect it to an AI tool

Your server can run two ways, and which one you need depends on the client:

- **stdio mode** (default) — the client launches `server.py` directly as a
  local process. Used by **Claude Desktop**, **Gemini CLI**, and most
  desktop MCP clients.
- **HTTP mode** — the server runs as a standalone web service that remote
  clients connect to over a URL. Required for **ChatGPT** and
  **Gemini Enterprise**, which only support remote MCP servers, not local
  ones.

### Claude Desktop (stdio)

Edit your Claude Desktop config file:

- macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`
- Windows: `%APPDATA%\Claude\claude_desktop_config.json`

Add an entry like this:

```json
{
  "mcpServers": {
    "db-graph-server": {
      "command": "python",
      "args": ["/absolute/path/to/server.py"],
      "env": {
        "DB_URL": "postgresql+psycopg2://user:password@host:5432/dbname"
      }
    }
  }
}
```

Restart Claude Desktop. You should see `db-graph-server` listed under
available tools (hammer icon).

### Gemini CLI (stdio)

Gemini CLI uses a similar config format. Add this to your Gemini CLI
settings (typically `~/.gemini/settings.json` or via `mcp_config.json` —
check the Gemini CLI docs for your version):

```json
{
  "mcpServers": {
    "db-graph-server": {
      "command": "python",
      "args": ["/absolute/path/to/server.py"],
      "env": {
        "DB_URL": "postgresql+psycopg2://user:password@host:5432/dbname"
      }
    }
  }
}
```

### ChatGPT / Claude.ai custom connectors / any remote-only client (HTTP)

These clients connect to your server over a URL from their own cloud
infrastructure (not from your machine), so it must be:
1. Running in HTTP mode
2. Reachable on the public internet (once actually deployed/hosted)
3. **Protected with an API key** — required, since anything reachable from
   the internet needs it

1. **Set an API key and run the server in HTTP mode:**
   ```bash
   MCP_TRANSPORT=http MCP_API_KEY=your-long-random-key PORT=8000 python server.py
   ```
   Or set `MCP_API_KEY` in your `.env` file (see `.env.example`).

2. **For local testing only**, tunnel it:
   ```bash
   ngrok http 8000
   ```
   For real use, deploy the server somewhere with a persistent public URL
   (a cloud VM, Render, Railway, Fly.io, etc.) — this part is typically
   handled by whoever manages your team's infrastructure.

3. **Connect the client:**
   - **Claude.ai:** Customize → Connectors → "+" → Add custom connector →
     paste your server's URL → under Advanced settings, add your API key
     as a Bearer token / custom header if the UI supports it, or use the
     OAuth fields if you've set up OAuth instead.
   - **ChatGPT:** Settings → Apps & Connectors → Advanced → enable
     **Developer Mode** (requires ChatGPT Plus/Pro/Business/Enterprise/Edu),
     then add your server's URL as a new connector, including the API key.

Every request to the server must include the key as either:
```
X-API-Key: your-long-random-key
```
or
```
Authorization: Bearer your-long-random-key
```
Requests without a matching key get a `401 Unauthorized` response.

⚠️ **If `MCP_API_KEY` is not set**, the server logs a warning and runs
without authentication — fine for local testing, but do not deploy it
that way to a real, internet-reachable environment.

### Other MCP clients

Any client that supports the standard MCP stdio transport can launch this
the same way as Claude Desktop — point it at `python /absolute/path/to/server.py`
with the `DB_URL` environment variable set. Any client that only supports
remote servers can connect the same way as ChatGPT, using HTTP mode.

## 5. Example usage

Once connected, you can ask the AI something like:

> "What tables are in the database?"
> "Show me total sales by month as a bar chart."

The AI will call `list_tables` to explore the schema, then call
`query_and_visualize` with a generated SQL query and chart type, and the
resulting chart image will appear inline.

## Safety and validation

- Only a **single read-only `SELECT` statement** is allowed. The server
  rejects: non-SELECT statements, chained statements (e.g. `SELECT ...; DROP TABLE ...`),
  and queries containing write/DDL keywords (`INSERT`, `UPDATE`, `DELETE`,
  `DROP`, `ALTER`, `CREATE`, `TRUNCATE`, `ATTACH`, `PRAGMA`) anywhere in the
  text. For production use, also connect with a dedicated read-only DB user
  as a second layer of defense.
- Query results are capped at 100 rows (`MAX_ROWS` in `server.py`). The cap
  is enforced with a SQL-level `LIMIT` wrapped around your query, not just
  applied after fetching — so a query against a huge table won't pull
  everything into memory first. The chart title notes when results were
  truncated.
- `x_column`/`y_column` are validated against the actual query result
  columns before charting, with a clear error if they don't match.
- `scatter` and `pie` charts require a numeric `y_column`; a clear error is
  raised otherwise.
- Database connection and query failures are caught and re-raised as clear
  `RuntimeError`/`ValueError` messages instead of leaking raw stack traces.

## Notes / next steps

- **Authentication for HTTP mode:** implemented via `MCP_API_KEY`. Set a
  long random value before deploying anywhere reachable from the internet.
  This is a simple shared-secret check — for stricter needs (per-user
  access, revocable tokens), consider upgrading to real OAuth, which
  Claude.ai's custom connectors support natively.
- Supports `bar`, `line`, `scatter`, `pie`, and `histogram` charts. Easy to
  extend `_render_chart` in `server.py` with more chart types (e.g. heatmap)
  if needed.
- Two tools, one for inline display (`query_and_visualize`) and one for
  file output (`query_and_visualize_to_file`) — the AI picks whichever
  fits the client it's running in, or you can ask for a specific one
  by name.
- For NoSQL databases (MongoDB, etc.), the SQLAlchemy layer won't work as-is
  — you'd swap the `engine`/`list_tables`/`query_and_visualize` internals
  for a driver like `pymongo`, but the MCP tool interface stays the same.

## Before submitting

- [ ] Run `pytest test_server.py -v` and confirm all tests pass.
- [ ] Remove local scratch artifacts (`test_chart.png`, `example.db`) if
      they're not meant to ship — `test_server.py` itself is fine to keep,
      it's now a proper automated test suite, not a throwaway script.
- [ ] Double check `DB_URL` (in `.env` or your Claude Desktop config)
      points to the real database, not the local test SQLite file.
- [ ] Make sure `.env` is not committed to git if you're using version
      control — `.gitignore` already excludes it.
- [ ] Read through this README top to bottom as if you're a teammate
      seeing it for the first time.