# db-mcp — Read-Only Database MCP Server

A [Model Context Protocol](https://modelcontextprotocol.io) server that lets AI
assistants **safely explore a PostgreSQL database** — schema, tables, columns,
relationships, indexes, constraints, DDL, and sample data — **without ever
modifying it**.

## Safety model

Read-only is enforced at **three independent layers**, so a failure in any one
layer is still caught by the others:

1. **Least-privilege database role.** You connect with a role that only has
   `CONNECT` / `USAGE` / `SELECT`. The database itself rejects anything else.
2. **Read-only transaction.** Every query runs inside `BEGIN TRANSACTION
   READ ONLY` on a session forced to `default_transaction_read_only = on` with a
   `statement_timeout`. The query is always rolled back.
3. **Application-level validation.** Arbitrary SQL (the `run_readonly_query`
   tool) is parsed with [`libpg-query`](https://github.com/launchql/libpg-query-node)
   — the *real* PostgreSQL parser compiled to wasm — and rejected unless it is a
   single read-only statement. This catches multi-statement input, DDL/DML,
   `EXPLAIN ANALYZE`, `SELECT INTO`, and data-modifying CTEs
   (`WITH x AS (INSERT ... RETURNING ...) SELECT ...`).

Additional guards: a maximum query length, a hard row cap on every result, a
sample-row cap (default 50), and **secrets are never logged** (config is redacted
before any diagnostic output).

## Tools

| Tool | Description |
| --- | --- |
| `list_databases` | Databases the read-only role can connect to. |
| `list_schemas` | Schemas visible to the role (system schemas excluded). |
| `list_tables` | Tables/views in a schema: type, row estimate, comment. |
| `describe_table` | Columns, PK, FKs, unique/check constraints, indexes. |
| `get_table_ddl` | Reconstructed `CREATE TABLE` DDL (+ indexes). |
| `get_relationships` | Foreign keys, referenced tables, cardinality hint. |
| `get_indexes` | Index names, columns, order, uniqueness, type, predicate. |
| `sample_rows` | A small sample of rows (capped at 50). |
| `run_readonly_query` | Validated read-only SQL; returns columns, rows, timing. |
| `search_schema` | Search names + comments across tables/columns/constraints/indexes. |

## Requirements

- Node.js >= 18.17
- A reachable PostgreSQL database and a read-only role (see below).

## Setup

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

### Create a least-privilege read-only role

Run as a superuser or the database owner:

```sql
CREATE ROLE readonly_user LOGIN PASSWORD 'CHANGE_ME';
GRANT CONNECT ON DATABASE your_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
-- Optional extra safety:
ALTER ROLE readonly_user SET default_transaction_read_only = on;
```

### Configure

Copy `.env.example` and fill in your connection. Configuration is read from the
environment (no `.env` is loaded automatically — export the vars, or have your
MCP client pass them in its `env` block).

| Variable | Default | Purpose |
| --- | --- | --- |
| `DATABASE_URL` | — | Full connection string (wins over `PG*`). |
| `PGHOST` / `PGPORT` / `PGDATABASE` / `PGUSER` / `PGPASSWORD` | — | Discrete connection settings. |
| `PGSSLMODE` | `disable` | `require`/`verify-*` enable TLS. |
| `DB_MCP_DRIVER` | `postgres` | Database driver. |
| `DB_MCP_MAX_ROWS` | `1000` | Hard cap for `run_readonly_query`. |
| `DB_MCP_SAMPLE_MAX_ROWS` | `50` | Hard cap for `sample_rows`. |
| `DB_MCP_STATEMENT_TIMEOUT_MS` | `5000` | Per-statement timeout. |
| `DB_MCP_MAX_QUERY_LENGTH` | `10000` | Reject longer queries. |
| `DB_MCP_DEFAULT_SCHEMA` | `public` | Schema used when omitted. |

## Running locally

```bash
export DATABASE_URL="postgres://readonly_user:***@localhost:5432/your_db"
npm start
```

The server speaks MCP over stdio. To poke at it interactively, use the MCP
Inspector:

```bash
npx @modelcontextprotocol/inspector node dist/server.js
```

## Connecting from Claude Code

Add an entry to your MCP config (e.g. `.mcp.json` in your project, or the global
Claude Code config):

```json
{
  "mcpServers": {
    "db-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/db-mcp/dist/server.js"],
      "env": {
        "DATABASE_URL": "postgres://readonly_user:***@localhost:5432/your_db"
      }
    }
  }
}
```

Then ask Claude to, e.g., "list the tables in the public schema" or "show me the
DDL for the orders table".

## Tests

```bash
npm test
```

Tests are pure unit tests (no live database required): exhaustive SQL-validation
cases (allowed reads vs. rejected mutations and bypass attempts), identifier
quoting, DDL reconstruction, and config parsing/redaction.

## Extending to other databases

Tools depend only on the `DatabaseDriver` interface (`src/db/driver.ts`). To add
MySQL, SQLite, or Snowflake:

1. Implement `DatabaseDriver` for the new engine under `src/db/<engine>/`.
2. Register it in `createDriver` (`src/db/factory.ts`).
3. Provide an engine-appropriate read-only enforcement (read-only role +
   read-only session/transaction) and SQL validation.

## Project layout

```
src/
  server.ts                 MCP server setup + stdio transport
  config.ts                 env parsing + secret redaction
  errors.ts                 typed error classes
  types.ts                  shared result types
  validation/
    validateReadOnly.ts     libpg-query-based read-only enforcement
  db/
    driver.ts               DatabaseDriver interface
    factory.ts              driver selection
    postgres/
      pool.ts               read-only pool, transactions, row-capped cursor
      identifiers.ts        safe identifier quoting
      introspection.ts      catalog/information_schema queries
      ddl.ts                CREATE TABLE reconstruction (pure)
      PostgresDriver.ts     DatabaseDriver implementation
  tools/                    one module per MCP tool + registry
tests/                      pure unit tests
```