# sql-explorer-mcp

Personal, read-only MCP server for browsing/querying a SQL Server database from
Claude Code (or any MCP client). Built for local dev use — not intended to be
exposed over a network or shared with other people.

## Tools exposed

- `list_schemas` — list all schemas
- `list_tables` — list tables, optionally filtered by schema
- `list_views` — list views, optionally filtered by schema
- `list_procedures` — list stored procedures, optionally filtered by schema
- `list_functions` — list functions (with return type), optionally filtered by schema
- `describe_table` — columns, types, nullability, primary key for a table
- `describe_view` — columns and the `CREATE VIEW` definition for a view
- `describe_procedure` — parameters and the `CREATE PROCEDURE` definition (metadata only — never executes it)
- `describe_function` — parameters, return type, and the `CREATE FUNCTION` definition (metadata only — never executes it)
- `browse_table` — `SELECT TOP N * FROM schema.table` (no filter)
- `query_table` — same as above with one validated `WHERE column op value` condition
- `join_tables` — `SELECT` across multiple tables/views with explicit join conditions; every table, alias, and column is validated the same way as the single-table tools
- `run_query` — arbitrary read-only SQL: CTEs (`WITH ...`), subqueries, `GROUP BY`/aggregates, window functions, `UNION`, `ORDER BY` — anything a single `SELECT` statement can express. See safety notes below.
- `find_similar_names` — Jaro-Winkler fuzzy search over table/view/column/procedure/function names, for when you don't remember the exact spelling
- `fuzzy_search_column` — Jaro-Winkler fuzzy search over the distinct values of a single text column

## Safety notes

- **Point this at a read-only login.** Create a SQL login scoped to `db_datareader`
  (or equivalent) on whatever database you connect it to. The tool code itself
  never issues writes, but the real safety boundary should be the DB permission,
  not the app logic.
- Every schema/table/column name used in a query is checked against a live
  `INFORMATION_SCHEMA` lookup before being used — the server never interpolates
  an unverified identifier into SQL.
- Filter values in `query_table` and `join_tables` are always passed as
  parameters (`@value`), never string-concatenated.
- `join_tables` accepts no raw SQL — table/column names are whitelisted per
  the rule above, and join aliases (which aren't real DB identifiers) are
  restricted to a safe `[A-Za-z_][A-Za-z0-9_]*` pattern before being quoted in.
- `run_query` is the one tool that accepts raw SQL text, so it doesn't get the
  whitelist treatment — it gets two layers instead. First, a text-level guard
  rejects anything but a single `SELECT`/`WITH ... SELECT` statement and
  rejects the query outright if it contains a second `;`-separated statement
  or any of `INSERT/UPDATE/DELETE/MERGE/EXEC/EXECUTE/DROP/ALTER/CREATE/
  TRUNCATE/GRANT/REVOKE/DENY/OPENROWSET/OPENDATASOURCE/OPENQUERY/BULK/INTO/
  sp_executesql`. Second, even after passing that check, the query runs
  inside a transaction that is unconditionally rolled back afterwards — so a
  write that somehow slips past the text filter still can't persist. Neither
  layer is a parser, and neither is a substitute for the read-only DB login
  recommended above — they're defense in depth on top of it, not instead of it.
- `describe_procedure`/`describe_function` only ever read metadata
  (`INFORMATION_SCHEMA.PARAMETERS`, `OBJECT_DEFINITION()`) — there is no tool
  that calls `EXEC`, since running a procedure could itself be a write.
- The fuzzy-search tools (`find_similar_names`, `fuzzy_search_column`) compute
  Jaro-Winkler similarity in-process after a normal validated `SELECT`; the
  search term never becomes part of the SQL text.
- Row limits are capped server-side at 200 regardless of what's requested.

## Setup

```bash
npm install
npm run build
```

## Environment variables

| Variable | Required | Notes |
|---|---|---|
| `DB_SERVER` | yes | e.g. `your-server.database.windows.net` |
| `DB_NAME` | yes | database name |
| `DB_USER` | yes | read-only SQL login |
| `DB_PASSWORD` | yes | |
| `DB_ENCRYPT` | no | defaults to `true` (needed for Azure SQL) |
| `DB_TRUST_CERT` | no | set `true` only for local dev instances with self-signed certs |

## Claude Code configuration

Add to your MCP config (project-level `.mcp.json` or global config):

```json
{
  "mcpServers": {
    "sql-browser": {
      "command": "node",
      "args": ["/absolute/path/to/sql-mcp-server/dist/index.js"],
      "env": {
        "DB_SERVER": "your-server.database.windows.net",
        "DB_NAME": "your-db",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "your-password",
        "DB_ENCRYPT": "true"
      }
    }
  }
}
```

Don't commit real credentials into `.mcp.json` if the repo is shared — use a
local, gitignored config, or reference environment variables already set on
your machine.

## Extending

The `query_table` tool intentionally supports only a single `WHERE` condition
to keep the validation surface small. If you need multiple conditions later,
extend the input schema to accept an array of `{ column, operator, value }`
and validate each one against `getValidColumns()` the same way — resist the
temptation to accept a raw SQL fragment.