# sql-mcp
# sql-mcp

An MCP server for read-only SQL Server access, plus a chat app (Express + React) that lets you ask
questions about your databases in plain English.

Two things live in this repo:

1. **An MCP server** ([src/index.ts](src/index.ts)) exposing `query`, `list_tables`, `describe_table`,
   and `list_databases` tools — usable from Claude Desktop, Claude Code, or any other MCP client.
2. **A standalone chat app** ([src/server.ts](src/server.ts) + [web/](web/)) that drives that same MCP
   server with an LLM (Gemini, with free OpenRouter models as a fallback), so you can just type a
   question instead of writing SQL.

See [ARCHITECTURE.md](ARCHITECTURE.md) for how it's built and why (in particular, why it talks to SQL
Server via `sqlcmd` instead of a normal Node driver).

## Prerequisites

- Node.js 18+ (20+ recommended)
- `sqlcmd` on `PATH` — ships with the [ODBC Driver 17 (or later) for SQL Server](https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server) Client SDK
- A SQL Server instance reachable from this machine (Shared Memory, Named Pipes, or TCP — `sqlcmd` handles whichever is available; no manual protocol configuration needed)

## Setup

1. Install dependencies:
   ```bash
   npm install
   cd web && npm install && cd ..
   ```
2. Create a `.env` file in the project root:
   ```bash
   SQL_SERVER=your-server-name
   SQL_DATABASE=your-database-name
   SQL_USER=sa
   SQL_PASSWORD=your-password

   GEMINI_API_KEY=your-gemini-api-key
   ```
   `GEMINI_API_KEY` is required for the chat app (get one from [Google AI Studio](https://aistudio.google.com/apikey)); the MCP server on its own doesn't need it.

3. Build:
   ```bash
   npm run build
   ```

## Running

**MCP server** (for use with an MCP client like Claude Desktop):
```bash
npm start          # runs the compiled dist/index.js
npm run dev         # runs src/index.ts directly via tsx, no build step
```

**Chat app** (two terminals):
```bash
npm run server       # backend, http://localhost:3001
cd web && npm run dev  # frontend, http://localhost:5173
```

**Quick connection sanity check:**
```bash
npm run test-connection
```

## MCP tools

| Tool | Description |
|---|---|
| `query` | Run a read-only `SELECT`/`WITH` statement. Writes are rejected. |
| `list_tables` | List all tables and views. |
| `describe_table` | List a table's columns, types, and nullability. |
| `list_databases` | List the configured database aliases (see below). |

Every tool takes an optional `database` argument to target a specific configured database.

## Multiple databases

By default there's one database, aliased `app`, configured via `SQL_SERVER`/`SQL_DATABASE`/`SQL_USER`/`SQL_PASSWORD`. To add more:

```bash
SQL_CONNECTIONS=idp,billing
SQL_IDP_SERVER=...
SQL_IDP_DATABASE=...
SQL_IDP_USER=...
SQL_IDP_PASSWORD=...
SQL_BILLING_SERVER=...
# etc.
```

Each alias in `SQL_CONNECTIONS` needs its own `SQL_<ALIAS>_SERVER` / `_DATABASE` / `_USER` / `_PASSWORD` set. The chat UI's database picker and the `list_databases` tool pick these up automatically.

## Optional: OpenRouter fallback

If `GEMINI_API_KEY` hits its rate limit, the chat backend can fall back to free models on
[OpenRouter](https://openrouter.ai/):

```bash
OPENROUTER_API_KEY=your-openrouter-key
# OPENROUTER_MODELS=openai/gpt-oss-20b:free,nvidia/nemotron-3-nano-30b-a3b:free,google/gemma-4-31b-it:free
```

Leaving `OPENROUTER_API_KEY` unset disables this fallback entirely — Gemini alone is used.

You can also override which Gemini models are tried, and in what order:
```bash
# GEMINI_MODELS=gemini-flash-latest,gemini-flash-lite-latest,gemini-2.5-flash-lite,gemini-pro-latest
```

## Scripts

| Script | What it does |
|---|---|
| `npm run build` | Compile TypeScript (`src/` → `dist/`) |
| `npm start` | Run the compiled MCP server |
| `npm run dev` | Run the MCP server from source (no build) |
| `npm run server` | Build, then run the chat backend on port 3001 |
| `npm run test-connection` | Verify the database connection and print the table list |

## Security notes

- The `query` tool only allows `SELECT`/`WITH` statements — no `INSERT`/`UPDATE`/`DELETE`/`DROP`/etc., enforced in [src/db.ts](src/db.ts).
- `.env` is gitignored. Never commit real credentials or API keys.