# DB Insights MCP Server

An MCP server that lets an AI assistant answer questions about a SQL database —
**read-only, by design.** The model can discover the schema and run `SELECT`
queries through a guarded interface, but it physically cannot insert, update, or
drop anything. The server is the safety wall between the model and your data.

[MCP](https://modelcontextprotocol.io) (Model Context Protocol) is the standard
way to give an AI client — Claude Desktop, Cursor, an agent — access to external
tools and data. This server exposes a database as a set of safe tools any MCP
client can use.

## Why read-only is the whole point

"Let our AI talk to our database" is easy to ask for and scary to ship. A model
(or a prompt injection riding in on some untrusted text) might try
`DROP TABLE customers`. This server makes that impossible: every query is checked
before it runs, and anything that isn't a single read-only `SELECT` is rejected.

```
Rejected: Only SELECT (or WITH ... SELECT) queries are allowed.
```

The guard (`server/safety.py`) strips comments, allows a single statement only,
requires it to start with `SELECT`/`WITH`, blocks write and DDL keywords, and
blocks `SELECT ... INTO`. On top of that, queries run in a connection that never
commits (and `SET TRANSACTION READ ONLY` on Postgres), and every result is capped.

## Architecture

```
MCP client (Claude Desktop / MCP Inspector / agent)
       │   MCP over stdio (local) or Streamable HTTP (deployed)
       ▼
  this server  (FastMCP)
   ├─ tools:     list_tables · describe_table · sample_rows · run_query
   ├─ resource:  schema://overview      (schema, loaded into the model's context)
   ├─ prompt:    explore(question)       (a starter prompt template)
   ├─ safety:    SELECT-only · single statement · no DDL/DML · row cap · no commit
   └─ SQLAlchemy ──> SQLite (seeded sample DB)  |  Postgres/MySQL via DATABASE_URL
```

The client's model decides which tools to call and writes the SQL. This server
validates and runs it. **The server never calls an LLM itself** — so it needs no
API key. Following MCP's own distinction: the schema is a *resource* (data read
into context), and the queries are *tools* (actions).



## Run and test it


### 1. Install dependencies

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

### 2. Test with MCP Inspector (no LLM needed)

*Use forward slashes — backslashes cause a path mangling bug on Windows:*

```bash
mcp dev server/main.py
```

That opens the Inspector in your browser. Click **Connect**, then go to the
**Tools** tab. Call `list_tables`, then `run_query` with something like:

```sql
SELECT p.category, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi JOIN products p ON p.id = oi.product_id
GROUP BY p.category ORDER BY revenue DESC;
```

Then try `DELETE FROM customers` and watch it get rejected.

> **Windows note:** if the Inspector shows a `VIRTUAL_ENV` warning on connect,
> that's harmless — `uv` ignores the active venv and manages its own environment.

### 3. Wire up Claude Desktop

Open the config via Claude Desktop → **profile icon** → **Settings** → **Developer** → **Edit Config**.

Or navigate to it directly:
- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`

Set `cwd` to the absolute path of this folder and `command` to the full path of
the Python executable in your virtual environment:

```json
{
  "mcpServers": {
    "db-insights": {
      "command": "C:\\path\\to\\venv\\Scripts\\python.exe",
      "args": ["-m", "server.main"],
      "cwd": "C:\\path\\to\\db-insights-mcp"
    }
  }
}
```

Fully quit Claude Desktop (`system tray → Quit` or `clear from Task-manager`), then relaunch it. Ask something
like *"which product category made the most revenue?"* and it will call the tools
on its own.

### 4. Run the tests

```bash
pytest
```

## SQLite by default, your database by env var

One setting, no code change:

| Variable       | Default                  | What it does                                 |
|----------------|--------------------------|----------------------------------------------|
| `DATABASE_URL` | `sqlite:///./sample.db`  | seeded sample DB; point at Postgres for real |
| `MAX_ROWS`     | `200`                    | hard cap on rows any query returns           |
| `TRANSPORT`    | `stdio`                  | `stdio` locally, `streamable-http` to deploy |

For a real database:

```
DATABASE_URL=postgresql+psycopg://user:pass@host:5432/dbname
```

Seeding only ever touches the default SQLite file — it never writes to a database
you point it at.

## When this helps (and when it doesn't)

Good fit: internal analytics assistants, "ask our data in plain English," letting
an agent explore a reporting database safely.

Be careful when: the database holds sensitive rows the model shouldn't read (the
guard stops *writes*, not *reads* — scope the connection's user/permissions for
that), or queries can be expensive (set `MAX_ROWS` and a DB-side statement timeout).

## Notes

- Built on the official MCP Python SDK (`mcp.server.fastmcp.FastMCP`).
- SSE transport is deprecated upstream; this uses stdio and Streamable HTTP.
- The guard is intentionally strict — a literal like `SELECT 'DROP'` is refused.
  Better a rare false reject than one destructive query slipping through.