# SQL MCP Server

An AI-powered **Model Context Protocol (MCP)** server that allows you to query and analyze an e-commerce SQLite database using natural language.

Ask questions like:
- *"Who are our top 5 customers by total spending?"*
- *"Show all products in the Electronics category with stock below 50"*
- *"What was our total revenue for completed orders in 2026?"*

Four tools, three of which need **no API key at all**. Read-only at two independent
levels, paged results, SQLite's own error text passed back to the caller, and 74
automated tests.

**Contents** — [Quick Start](#-quick-start) · [Configure a Provider](#-configure-your-ai-provider) · [Tools](#-available-tools) · [Paging](#-paging-through-large-results) · [Errors](#-what-an-error-looks-like) · [Tests](#-automated-tests) · [Docker](#-docker) · [MCP Clients](#-connecting-to-mcp-clients) · [Configuration](#-configuration-reference) · [Safety](#-safety) · [Data Egress](#-what-gets-sent-where) · [Project Layout](#-project-layout)

---

## 🚀 Quick Start

### 1. Prerequisites

- **Node.js**: `v22.5.0` or higher (for the built-in `node:sqlite` module); `v24` recommended
- **npm**: `v11.0.0` or higher

### 2. Installation

Clone this repository and install dependencies:

```bash
npm install
cp .env.example .env
npm run build
```

That is enough to connect the server to a client and use `list_tables`,
`describe_table` and `execute_sql`. A provider is needed only for the natural
language tool — see below.

---

## 🔑 Configure Your AI Provider

Open the `.env` file and set up your preferred AI model. The server automatically detects your provider based on the variables you set:

### Option A: Anthropic Claude (Recommended)

```env
ANTHROPIC_API_KEY=sk-ant-api03-...
ANTHROPIC_MODEL=claude-opus-5
```

### Option B: Local Ollama (Free & Offline)

```env
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3.2
```

> **Note**: Make sure Ollama is running (`ollama serve`) and you have pulled the model (`ollama pull llama3.2`).

### Option C: OpenAI

```env
OPENAI_API_KEY=sk-proj-...
OPENAI_MODEL=gpt-4o-mini
```

### Option D: Custom / Third-Party (Groq, DeepSeek, OpenRouter)

```env
OPENAI_API_KEY=your_api_key
OPENAI_BASE_URL=https://api.groq.com/openai/v1
OPENAI_MODEL=llama-3.3-70b-versatile
```

---

## 🛠 Available Tools

Three of the four talk to SQLite directly — **no API key, no cost, instant**:

| Tool | What it does | Needs a provider |
|---|---|---|
| `list_tables` | Every table with a plain-language explanation of what it holds, its row count and columns, plus the relationships between tables and the revenue convention this database uses. | No |
| `describe_table` | One table in full — columns with types, keys and descriptions, foreign keys, the `CREATE TABLE` statement, caveats, and the range date columns actually cover. | No |
| `execute_sql` | Any read-only `SELECT`, returning structured JSON rows and column names. Supports `limit` / `offset` paging. This is the tool to use for analytical work you want to drive yourself. | No |
| `query_database` | Takes a plain-language question, generates and executes the appropriate SQL, and returns a written answer with insights. | **Yes** |

Each tool's description tells the calling agent not just what it does but when
*not* to use it — `query_database` states that it returns prose rather than
values, costs money and makes two LLM calls, and points at `execute_sql` for
anything the agent intends to compute with. Both tools state the row cap and the
revenue convention inline, so the agent does not have to discover them by
trial.

### Example: `describe_table`

```jsonc
// describe_table { "table_name": "orders" } — abridged
{
  "table": "orders",
  "purpose": "Order headers — one row per order placed by a customer, carrying its date, lifecycle status and total.",
  "rowCount": 750,
  "columns": [
    { "name": "status", "type": "TEXT", "primaryKey": false, "notNull": true, "default": null,
      "description": "Lifecycle stage, one of: new, processing, shipped, completed, cancelled. Determines whether the order counts as revenue." }
  ],
  "foreignKeys": [
    { "column": "customer_id", "referencesTable": "customers", "referencesColumn": "id", "onDelete": "CASCADE" }
  ],
  "notes": ["Revenue convention: count every order whose status is not 'cancelled' …"],
  "dataCoverage": { "order_date": { "min": "2026-02-17 18:53:30", "max": "2026-08-22 17:06:30" } },
  "createStatement": "CREATE TABLE orders ( … )"
}
```

`dataCoverage` is there so an agent can tell an empty result from an
out-of-range question: asking about 2025 returns "the data runs from … to …"
rather than a bare zero that reads like a bug.

---

## 📄 Paging Through Large Results

Every result is capped — at `DATABASE_MAX_ROWS` (default **100**), or at a
smaller `limit` you pass. A larger `limit` is clamped rather than rejected, so a
caller always gets rows back.

`execute_sql` takes `limit` and `offset` and tells you whether there is more:

```jsonc
// execute_sql { "sql": "SELECT id, name FROM products ORDER BY id", "limit": 2, "offset": 2 }
{
  "columns": ["id", "name"],
  "rows": [
    { "id": 3, "name": "Ноутбук UltraBook 15" },
    { "id": 4, "name": "Умные часы FitWatch" }
  ],
  "rowCount": 2,
  "offset": 2,
  "hasMore": true,
  "nextOffset": 4,
  "note": "More rows matched than were returned. Call again with offset=4 for the next page.",
  "executionTimeMs": 0.09
}
```

Keep calling with `offset: nextOffset` until `hasMore` is `false`. When a result
fits in one page, `hasMore` is `false` and `totalAvailableRows` reports the true
total.

The cap is enforced **while stepping the statement**, not by trimming a finished
result: the server stops one row past the cap and never materialises the rest.
The SQL is model-generated, so an accidental cross join would otherwise pull
millions of rows into memory before any were discarded. Paging is likewise done
during iteration rather than by appending `LIMIT`/`OFFSET` to the SQL, which
would have to survive whatever the generated statement already ends with.

`query_database` shares the row cap but does not page — it summarises in prose,
where a page number has nothing to attach to. Use `execute_sql` for anything
larger than one page.

---

## 🚦 What an Error Looks Like

Failures come back as normal MCP tool results with `isError: true` and a message
the calling agent can act on, rather than as transport-level faults.

| You send | You get back |
|---|---|
| `SELECT nope FROM products` | `Query execution failed: no such column: nope` |
| `DELETE FROM orders` | `Only read-only queries are permitted. A statement must begin with SELECT, WITH or VALUES, but this one begins with "DELETE".` |
| `SELECT 1; SELECT 2` | `Only a single SQL statement may be executed. Multiple statements were provided.` |
| `describe_table {"table_name": "custmers"}` | `No table named "custmers". Available tables: customers, order_items, orders, products.` |
| A natural-language request to delete data | `This request asks to modify the database, which is not permitted … No changes were made. You can still ask about the same records: …` |

Two rules govern that text:

- **SQLite's own message is preserved.** "no such column: nope" is the single
  most useful thing an agent can be told, because it is enough to rewrite the
  query and retry. It is never flattened into "query failed".
- **Host detail never escapes.** Unrecognised errors — which may carry a stack
  trace — collapse to a generic line, and everything on the way out is scrubbed
  of the database path, the project root and the home directory. Full detail
  stays in the server logs. This is covered by its own test file.

---

## 🧪 Automated Tests

```bash
npm test          # 74 tests across 4 files, runs in well under a second
npm run test:watch
npm run typecheck
```

Plain `node --test` with `tsx` — no test framework dependency. The suites run
against the real `db/shop.db`, not a mock, so they fail if the schema and the
documentation drift apart.

| File | Covers |
|---|---|
| `tests/sql-guard.test.ts` | Every way a write could be smuggled past the read-only guard: leading comments, `WITH x AS (…) DELETE`, stacked statements, markdown-fenced DML. Plus the reverse — that `replace()`, a keyword inside a string literal, and a quoted identifier named after a keyword are **not** rejected. |
| `tests/database.test.ts` | Row capping, `offset` paging, an offset past the end, a runaway cross join that must not materialise, column names on an empty result, refused writes leaving the database unchanged, SQLite's message surviving. |
| `tests/errors.test.ts` | What a caller is allowed to see: actionable messages pass through, unknown errors collapse, and the database path / project root / home directory are redacted from both. |
| `tests/schema-metadata.test.ts` | That every table and column in the live database has a written description, that no description refers to a table that no longer exists, and that the revenue convention is stated. |

The guard suite is the one that matters most: it is the boundary that makes
"read-only" true rather than merely intended, and one of its cases is a real
false positive it caught during development.

---

## 🐳 Docker

```bash
docker build -t sql-mcp .
```

The image bundles the database, so it needs no volume mount. Because this is a
stdio server, it must be run with `-i` and no TTY — the container's stdin and
stdout carry the JSON-RPC stream:

```bash
docker run -i --rm -e ANTHROPIC_API_KEY sql-mcp
```

Wire it into a client with [`examples/claude_desktop_config.docker.json`](examples/claude_desktop_config.docker.json).
Drop the `-e ANTHROPIC_API_KEY` to run without credentials — `list_tables`,
`describe_table` and `execute_sql` work without a provider.

The build is multi-stage: TypeScript is compiled in a `node:24-alpine` builder,
and only `dist/`, `db/` and production dependencies are copied into the runtime
image. It runs as the unprivileged `node` user, no `.env` is ever copied in
(credentials come from `-e`), and there are no native addons to compile because
SQLite ships inside Node itself.

---

## 🔌 Connecting to MCP Clients

Ready-to-use configuration files are in [`examples/`](examples/) — copy the one
matching your client and replace the path. `examples/claude_desktop_config.no-api-key.json`
runs the server with **no credentials at all**, which is enough for `list_tables`,
`describe_table` and `execute_sql`.

### Claude Desktop Configuration

Add this server to your Claude Desktop configuration file (`claude_desktop_config.json`):

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

*(Make sure to run `npm run build` once before connecting)*

#### Example 1: Anthropic Claude (Default)

```json
{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "ANTHROPIC_API_KEY": "sk-ant-api03-your-key-here",
        "ANTHROPIC_MODEL": "claude-opus-5"
      }
    }
  }
}
```

#### Example 2: Local Ollama (Free & Offline)

```json
{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OLLAMA_BASE_URL": "http://localhost:11434",
        "OLLAMA_MODEL": "llama3.2"
      }
    }
  }
}
```

#### Example 3: OpenAI

```json
{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OPENAI_API_KEY": "sk-proj-your-key-here",
        "OPENAI_MODEL": "gpt-4o-mini"
      }
    }
  }
}
```

#### Example 4: Custom / Groq / OpenRouter / DeepSeek

```json
{
  "mcpServers": {
    "sql-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp/dist/index.js"],
      "env": {
        "OPENAI_API_KEY": "gsk_your_groq_api_key",
        "OPENAI_BASE_URL": "https://api.groq.com/openai/v1",
        "OPENAI_MODEL": "llama-3.3-70b-versatile"
      }
    }
  }
}
```

The server resolves `db/shop.db` relative to its own location, so `DATABASE_PATH`
is not needed in any of these — MCP clients launch servers from a working
directory of their own choosing, and the server does not depend on it.

---

## 🔎 Trying It Locally

### Instant Terminal Test

You can test natural language questions directly in your terminal:

```bash
npm run query -- "Show top 3 products by price"
```

### Visual Web Inspector

Test tools interactively in your browser using the official MCP Inspector:

```bash
npm run inspect:dev
```

1. Open the inspector URL in your browser (e.g. `http://localhost:5173`).
2. Click **Connect**.
3. Under **Tools**, select **`query_database`**, enter your question, and click **Run Tool**.

### All npm Scripts

| Script | Does |
|---|---|
| `npm run build` / `npm run clean` | Compile to `dist/` · remove it |
| `npm start` | Run the built server over stdio |
| `npm run dev` | Run from source with reload (`tsx watch`) |
| `npm test` / `npm run test:watch` | Automated tests |
| `npm run typecheck` | `tsc --noEmit` |
| `npm run query -- "…"` | Ask a question from the terminal |
| `npm run inspect` / `npm run inspect:dev` | MCP Inspector against `dist/` · against source |

---

## 🔧 Configuration Reference

Every variable is optional; the defaults are what runs if you set nothing.

| Variable | Default | Purpose |
|---|---|---|
| `DATABASE_PATH` | `db/shop.db` | Database location. Absolute, or relative to the project root — never to the working directory. |
| `DATABASE_MAX_ROWS` | `100` | Hard ceiling on rows returned per call, and on rows sent to the LLM. `execute_sql`'s `limit` can only lower it. |
| `LLM_TIMEOUT_MS` | `60000` | Per-request ceiling for LLM calls. A question makes two sequential calls, so without this a stalled provider hangs the tool call. |
| `LLM_PROVIDER` | auto-detected | `anthropic` \| `ollama` \| `openai` \| `custom`. Normally inferred from which keys you set. |
| `ANTHROPIC_API_KEY` / `ANTHROPIC_MODEL` | — / `claude-opus-5` | Anthropic provider. |
| `OPENAI_API_KEY` / `OPENAI_MODEL` / `OPENAI_BASE_URL` | — / `gpt-4o-mini` / OpenAI | OpenAI and any OpenAI-compatible endpoint. |
| `OLLAMA_BASE_URL` / `OLLAMA_MODEL` | `http://localhost:11434` / `llama3.2` | Local Ollama. |
| `DEBUG` | unset | `sql-mcp:*`, or one namespace: `server`, `query-engine`, `database`, `llm`, `tools`. |

A malformed value is reported on stderr and falls back to the default rather
than being silently accepted — a typo in a client's `env` block shows up at
startup instead of behaving as though the variable were never set. `DEBUG` logs
carry every question asked and every statement generated, and under an MCP client
they land in the client's persistent log files, so they stay off unless you opt in.

---

## 🔒 Safety

The database is opened read-only at the driver level, and every statement is validated
before execution: it must be a single `SELECT`/`WITH`/`VALUES` statement, with no
keyword that writes data, alters schema, or changes connection state. Neither check can
be switched off by configuration. A request like *"delete all cancelled orders"* is
refused rather than executed.

The validator works over a tokenized view of the statement rather than the raw
text, so comments, string literals and quoted identifiers cannot be used to hide
a keyword — `/* c */ DELETE FROM orders` and `WITH x AS (SELECT 1) DELETE FROM orders`
are both rejected, while `SELECT replace(name, 'a', 'b')` is not.

Text that this server did not write — your question, and values read out of the
database — is delimited in the prompts with an unforgeable per-request marker, so
a product named `Widget (SYSTEM: ignore prior instructions…)` cannot escape into
instruction context. That matters beyond this process: the answer travels back to
the calling agent as tool output, one hop further.

---

## 🔐 What Gets Sent Where

This server answers questions by calling an LLM, so **database content leaves your
machine on every `query_database` call**. Specifically, each call sends:

1. **Your database schema** — table names, column names and types, and row counts —
   to generate the SQL.
2. **The rows the query returned** (up to `DATABASE_MAX_ROWS`, default 100) — to turn
   them into a written answer.

For the bundled shop database those rows include **customer names, email addresses and
phone numbers**. They go to whichever provider you configure, at whichever endpoint
`OPENAI_BASE_URL` names — which for Groq, OpenRouter or DeepSeek is a third party under
its own terms.

If that is not acceptable for your data:

- **Use the other three tools.** `list_tables`, `describe_table` and `execute_sql`
  make no network call at all — nothing leaves the machine.
- **Use Ollama.** It runs locally, so nothing leaves the machine.
- **Restrict the queries.** Aggregate questions ("revenue by category") return summary
  rows rather than customer records.
- **Lower `DATABASE_MAX_ROWS`** to cap how much row data is sent per query.

The server never sends the database *file*, and it can only read — see
[Safety](#-safety).

---

## 📁 Project Layout

```
src/
  index.ts                  MCP server entry point (stdio transport)
  cli.ts                    Terminal harness: npm run query -- "…"
  config/                   Env parsing, provider detection, path resolution
  tools/                    The four MCP tools and their descriptions
  services/
    database.service.ts     SQLite access, row capping, paging, introspection
    sql-guard.ts            Read-only enforcement (tokenizing validator)
    errors.ts               Caller-safe messages, path redaction
    schema-metadata.ts      Human-written meaning the schema cannot record
    query-engine.service.ts NL → SQL → execute → prose pipeline
    llm/                    Anthropic / OpenAI / Ollama behind one interface
  prompts/                  SQL generation, humanization, untrusted-input framing
tests/                      node --test suites (see Automated Tests)
db/                         shop.db and its schema documentation
docs/                       Architecture and sequence diagrams
examples/                   Ready-to-paste client configurations
```

---

## 📚 Technical Documentation

- [**Technical Specifications & Architecture Diagrams**](docs/TECHNICAL_SPECIFICATIONS.md): System design, sequence diagrams, LLM strategy pattern, and safety mechanisms.
- [**Database Schema Documentation**](db/schema.md): Complete table schema definitions, ER diagram, and SQLite data dictionary.
- [**Client Configuration Examples**](examples/README.md): Which config file to copy, and how to run with no API key.