# linkit-dev-sql-mcp

MCP server for **read-only SQL Server queries** across LinkIt environments:
`internal` / `staging` / `uat` / `preprod` / `prod` / `au`.

Same UX as `teamwork-mcp`: connections are stored in the OS keychain
(or an AES-256-GCM encrypted file), so MCP client config files never contain secrets.
The first tool call without a configured env opens a **local setup page** where you
enter host + database + user + password per env (each connection is tested before saving).

## Features

- MCP server over stdio
- **Read-only by construction**: only single `SELECT` / `WITH...SELECT` statements pass
  validation — `INSERT/UPDATE/DELETE/MERGE`, DDL, and `EXEC` are blocked
  (inspect procedures with `explore/procedure_info` instead of executing them).
  Still, prefer a `db_datareader` login for defense in depth.
- **Multi-env**: every tool takes an explicit `env` param — there is no default,
  so `prod` is only touched when you say `env="prod"`.
- **Smart result guard**: `maxRows` (default 50, max 500), server-side `SET ROWCOUNT`
  cap when your query has no `TOP`/`OFFSET`, long-text cell trimming, ~120 KB response
  byte-cap, `truncated` flag + hints, opt-in `countTotal`.
- **Catalog discovery for huge DBs**: all `explore` actions are paginated
  (`page`/`pageSize`, max 100) with `LIKE` search — never dumps the whole catalog.
- Setup page + CLI (`auth` / `status` / `test` / `logout`), CI overrides via env vars.

## Install as a Claude Code / Cowork plugin (easiest, no config editing)

```bash
claude plugin marketplace add sondv5/linkit-dev-sql-mcp
claude plugin install linkit-sql@linkit-dev-sql-mcp
```

The first time anyone calls a SQL tool for an unconfigured env, the guided setup page
opens automatically for them to enter **their own** per-env connections
(stored locally in their OS keychain) — nothing to configure by hand.

## Install / Run

```bash
npx -y linkit-dev-sql-mcp@latest
```

The first time you call any tool for an env with no connection, the server will:

1. Open your browser to a local setup page (`http://127.0.0.1:<port>/setup/<nonce>`)
2. You enter host + port + database + user + password for that env → the server
   **tests the connection**, then saves it
3. Retry the tool you just called — everything works, no restart needed

For local development:

```bash
npm install
npm run build
node dist/bin.js
```

A CLI is also available for terminal users:

```bash
npx -y linkit-dev-sql-mcp@latest auth     # pick envs, enter connections, test and save
npx -y linkit-dev-sql-mcp@latest status   # show configured envs
npx -y linkit-dev-sql-mcp@latest test     # test connectivity (or: test prod)
npx -y linkit-dev-sql-mcp@latest logout   # remove all stored connections
```

## MCP Client Config

```json
{
  "mcpServers": {
    "linkit-sql": {
      "command": "npx",
      "args": ["-y", "linkit-dev-sql-mcp@latest"]
    }
  }
}
```

Ready-made templates are included in this repository:

```
.cursor/mcp.json
.mcp.json
.codex/config.toml
opencode.json
```

(See `teamwork-mcp` README for per-client instructions — same pattern, server name `linkit-sql`.)

## Tools (3 grouped tools, action-dispatched)

| Tool | Type | Actions (via `action` param) |
| --- | --- | --- |
| `query` | read | `select` — one SELECT/WITH statement; `maxRows` (default 50, max 500), `countTotal`, `trimChars`, per-call `database` override |
| `explore` | read | `list_databases`, `list_tables` (search/schema filter), `table_info` (columns/indexes/FKs/approx rows), `list_views`, `list_procedures`, `procedure_info` (params + definition ≤8000 chars), `list_functions`, `function_info`, `search_objects` (unioned LIKE search, type filter) — all paginated |
| `system` | local | `status` (envs/storage, no passwords), `test` (connectivity + latency, all or one env), `logout` (remove connections) |

Typical flow for a big unknown DB:

1. `explore/list_databases` (or `system/status` to see what's configured)
2. `explore/search_objects` with a keyword, or `explore/list_tables` with `search`
3. `explore/table_info` for the shortlist
4. `query/select` with a filtered `SELECT ... WHERE ...` and small `maxRows`

## Environment Variables (optional, for CI)

Per-env overrides (they win over stored connections):

```
LINKIT_SQL_<ENV>_HOST / _PORT / _DB / _USER / _PASSWORD / _ENCRYPT / _TRUST_CERT
```

`ENV` is one of `INTERNAL, STAGING, UAT, PREPROD, PROD, AU`.
Note: env vars are plaintext — prefer the keychain for interactive use.

## Security

- The server is **read-only at the SQL-text layer**, but SQL Server cannot enforce
  that by itself — always connect with a **read-only login** (`db_datareader`,
  no `db_owner`/`db_ddladmin`), especially for `prod`.
- `EXEC`/`sp_executesql`/`SELECT...INTO` are blocked, so stored procedures can be
  **inspected but not executed** through this server.
- `env` is required on every tool — there is no implicit default env, so an agent
  cannot "accidentally" query prod while meaning staging.
- Never pass credentials via `args` in `mcp.json` (visible in process lists).
- The server only logs to stderr; stdout is reserved for JSON-RPC.

## Dev

```bash
npm install
npm run build        # tsc -> dist/
npm run dev          # watch mode
node dist/bin.js --help
```

## License

MIT