# sqlens-mcp

[![CI](https://github.com/dicoy/sqlens-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/dicoy/sqlens-mcp/actions/workflows/ci.yml)

An MCP server that gives Claude read-only access to your local development databases — inspect schemas, run queries, and explain query plans across Postgres, MySQL, and SQLite without leaving the conversation.

Built with the [Model Context Protocol](https://modelcontextprotocol.io/) TypeScript SDK and a dialect-agnostic provider pattern. Only SELECT statements are permitted; SQLite connections open in `readonly` mode at the driver level.

---

## Tools

| Tool | What it answers |
|---|---|
| `list_connections` | What databases are configured? (credentials masked) |
| `list_tables` | What tables and views exist? How many rows? How large on disk? |
| `describe_table` | What are the columns, types, nullability, defaults, indexes, and foreign keys? |
| `run_query` | Run a SELECT and get results as a formatted table (max 500 rows, default 50). |
| `explain_query` | What query plan does the engine choose? (`EXPLAIN ANALYZE` on Postgres, `EXPLAIN QUERY PLAN` on SQLite). |

![demo](demo/demo.gif)

---

## Installation

### Via npm (recommended)

```bash
npm install -g sqlens-mcp
```

Or use it without installing — `npx` will fetch and run it on demand (see Claude config below).

### From source

```bash
git clone https://github.com/dicoy/sqlens-mcp.git
cd sqlens-mcp
npm install
npm run build
```

### Add to Claude Code

```bash
claude mcp add sqlens -- npx -y sqlens-mcp
```

### Add to Claude Desktop

Add to `~/Library/Application Support/Claude/claude_desktop_config.json` on macOS:

```json
{
  "mcpServers": {
    "sqlens": {
      "command": "npx",
      "args": ["-y", "sqlens-mcp"],
      "env": {
        "DEVDB_URL": "postgres://localhost/myapp"
      }
    }
  }
}
```

---

## Configuration

Connections are configured with environment variables. No config files.

### Single connection

```bash
DEVDB_URL=postgres://localhost/myapp
```

### Multiple named connections

Any `DEVDB_<NAME>` variable registers a named connection. The suffix is lowercased and underscores become hyphens.

```bash
DEVDB_URL=postgres://localhost/myapp          # "default"
DEVDB_STAGING=mysql://staging.internal/myapp  # "staging"
DEVDB_LOCAL=sqlite:///absolute/path/to/dev.db # "local"
```

Claude selects a connection by name: `run_query({ sql: "...", connection: "staging" })`. If no connection is specified, the default is used.

### Supported dialects

| Dialect | URL prefix | Example |
|---|---|---|
| PostgreSQL | `postgres://` or `postgresql://` | `postgres://user:pass@localhost:5432/mydb` |
| MySQL | `mysql://` | `mysql://user:pass@localhost:3306/mydb` |
| SQLite | `sqlite://` or `.db` / `.sqlite` path | `sqlite:///Users/you/dev.db` |

### Claude Desktop: multiple connections

```json
{
  "mcpServers": {
    "sqlens": {
      "command": "npx",
      "args": ["-y", "sqlens-mcp"],
      "env": {
        "DEVDB_URL": "postgres://localhost/myapp",
        "DEVDB_ANALYTICS": "postgres://localhost/analytics",
        "DEVDB_LOCAL": "sqlite:///Users/you/local.db"
      }
    }
  }
}
```

---

## Safety

- **SELECT only** — every query is validated before execution. Anything other than `SELECT` or `WITH` is rejected with a typed error before it reaches the database.
- **SQLite readonly mode** — SQLite connections use `readonly: true` at the `better-sqlite3` level. Writes are blocked by the OS, not just by the check above.
- **Credential masking** — `list_connections` shows URLs with passwords replaced by `****`. Credentials never appear in tool output.
- **Row cap** — `run_query` returns at most 500 rows; default is 50.

---

## Architecture

```
src/
├── providers/
│   ├── db.ts                 # IDbProvider interface + shared types
│   ├── postgres.ts           # PostgresProvider  — pg.Pool, information_schema + pg_index
│   ├── mysql.ts              # MySqlProvider     — mysql2/promise, information_schema
│   ├── sqlite.ts             # SqliteProvider    — better-sqlite3 (readonly: true), PRAGMAs
│   └── connection-config.ts  # env parsing, createProvider() factory, maskCredentials()
├── errors/
│   └── index.ts              # DevDbError hierarchy (ConnectionNotFoundError, ReadOnlyViolationError, …)
├── tools/                    # One directory per tool: schema.ts + handler.ts + handler.test.ts
└── registry/
    └── tool-registry.ts      # resolveProvider(), per-call provider lifecycle
```

**Design principles:**

- **Single interface, three dialects** — `IDbProvider` exposes `listTables`, `describeTable`, `runQuery`, `explainQuery`, and `close`. Tool handlers never import a concrete provider class.
- **Connection-per-call** — each tool call opens a fresh provider and closes it in a `finally` block. No shared state between calls, no connection leaks.
- **One Zod schema per tool** — the same schema drives both MCP input validation and TypeScript types. No duplication.
- **Typed error hierarchy** — `ConnectionNotFoundError`, `ReadOnlyViolationError`, `TableNotFoundError`, and others. The registry catches `DevDbError` and formats each one as a clear message for Claude rather than a stack trace.

---

## Development

```bash
npm run dev          # build in watch mode
npm run typecheck    # tsc --noEmit
npm run lint         # biome check
npm run lint:fix     # biome check --write
npm run test         # vitest run
npm run test:watch   # vitest (interactive)
npm run ci           # typecheck + lint + test + build
npm run demo         # run the demo script (Node 20+ required)
```

### Adding a new dialect

1. Implement `IDbProvider` in `src/providers/<dialect>.ts`
2. Add the URL pattern to `detectDialect()` in `connection-config.ts`
3. Add the case to `createProvider()` in `connection-config.ts`

### Adding a new tool

1. Create `src/tools/your-tool/schema.ts` — Zod input schema
2. Create `src/tools/your-tool/handler.ts` — pure function, injected `IDbProvider`
3. Create `src/tools/your-tool/handler.test.ts` — mock `IDbProvider`, not a real database
4. Register in `src/registry/tool-registry.ts`

---

## Tech stack

| | |
|---|---|
| Runtime | Node.js 20+ |
| MCP SDK | `@modelcontextprotocol/sdk` |
| Validation | `zod` |
| PostgreSQL | `pg` |
| MySQL | `mysql2` |
| SQLite | `better-sqlite3` |
| Build | `tsup` |
| Tests | `vitest` |
| Lint + format | `biome` |