# Database MCP Server

A secure, read-only Model Context Protocol (MCP) server that enables AI assistants (Claude Code, Cursor, etc.) to safely query and explore SQL Server and PostgreSQL databases.

## What is this?

This MCP server acts as a bridge between AI assistants and your databases. It provides safe, read-only access so AI can help you understand your database schema, query data, and discover relationships — all without risking data modification.

## Features

- **Read-Only by Design** — Only SELECT queries allowed, preventing accidental data changes
- **Multi-Database** — Supports both SQL Server and PostgreSQL
- **Multiple Profiles** — Connect to multiple databases simultaneously (local, staging, production)
- **Relationship Discovery** — Automatically discover foreign key relationships between tables
- **Safety Features** — Automatic row limiting, query validation, cross-database blocking
- **PostgreSQL Extras** — EXPLAIN plans, materialized views, extensions, enum types

## Quick Start

### Prerequisites

- Node.js 18+
- SQL Server and/or PostgreSQL database
- Claude Code or Cursor IDE

### Installation

```bash
cd sqlserver-mcp
npm install
npm run build
```

### Add to Your Project

Add a `.mcp.json` file to the root of any project where you want database access:

**PostgreSQL:**
```json
{
  "mcpServers": {
    "database": {
      "command": "node",
      "args": ["/absolute/path/to/sqlserver-mcp/build/index.js"],
      "env": {
        "SQLSERVER_CONNECTIONS": "{\"mydb\":{\"databaseType\":\"postgresql\",\"connectionString\":\"postgresql://user:pass@localhost:5432/dbname\"}}"
      }
    }
  }
}
```

**SQL Server:**
```json
{
  "mcpServers": {
    "database": {
      "command": "node",
      "args": ["/absolute/path/to/sqlserver-mcp/build/index.js"],
      "env": {
        "SQLSERVER_CONNECTIONS": "{\"mydb\":{\"server\":\"localhost\",\"database\":\"MyDB\",\"user\":\"sa\",\"password\":\"yourpassword\",\"options\":{\"encrypt\":false,\"trustServerCertificate\":true}}}"
      }
    }
  }
}
```

**Multiple databases:**
```json
{
  "mcpServers": {
    "database": {
      "command": "node",
      "args": ["/absolute/path/to/sqlserver-mcp/build/index.js"],
      "env": {
        "SQLSERVER_CONNECTIONS": "{\"pg_local\":{\"databaseType\":\"postgresql\",\"connectionString\":\"postgresql://user:pass@localhost:5432/appdb\"},\"sql_prod\":{\"server\":\"prod.server.com\",\"database\":\"ProdDB\",\"user\":\"readonly\",\"password\":\"pass\",\"options\":{\"encrypt\":true}}}"
      }
    }
  }
}
```

The profile name (e.g., `mydb`, `pg_local`) is what gets passed as the `profile` parameter to every tool call.

### Alternative: Config File

Instead of inline JSON, you can use a config file:

```json
{
  "mcpServers": {
    "database": {
      "command": "node",
      "args": ["/absolute/path/to/sqlserver-mcp/build/index.js"],
      "env": {
        "SQLSERVER_CONFIG_FILE": "/path/to/config.json"
      }
    }
  }
}
```

Where `config.json` contains:
```json
{
  "local_pg": {
    "databaseType": "postgresql",
    "connectionString": "postgresql://user:pass@localhost:5432/mydb"
  },
  "local_sql": {
    "server": "localhost",
    "database": "MyDB",
    "user": "sa",
    "password": "yourpassword",
    "options": {
      "encrypt": false,
      "trustServerCertificate": true
    }
  }
}
```

Restart your IDE after adding or changing `.mcp.json`.

## Available Tools

| Tool | Database | Description |
|------|----------|-------------|
| `list-schemas` | Both | List schemas with owner info and table counts |
| `list-tables` | Both | List tables with row counts and type info |
| `describe-table` | Both | Column details: types, nullability, PKs, defaults, identity |
| `get-relationships` | Both | Foreign key relationships (outgoing and incoming) |
| `get-indexes` | Both | Index details: type, columns, uniqueness, filters |
| `run-select-query` | Both | Execute read-only SELECT queries with parameters |
| `explain-query` | Both | Get estimated execution plan for a query |
| `estimate-cost` | Both | Estimate query cost and row counts |
| `list-materialized-views` | PostgreSQL | List materialized views with size and status |
| `list-extensions` | PostgreSQL | List installed and available extensions |
| `list-enums` | PostgreSQL | List user-defined enum types with values |

## Usage Examples

Ask your AI assistant:

- *"List all tables in the mydb database"*
- *"Describe the Users table in mydb"*
- *"Show me the relationships for the Orders table"*
- *"Run this query on mydb: SELECT * FROM users WHERE active = true"*
- *"Explain this query: SELECT u.*, o.total FROM users u JOIN orders o ON u.id = o.user_id"*
- *"What extensions are installed on mydb?"*

## Connection Profile Options

### SQL Server
```json
{
  "server": "hostname",
  "database": "database_name",
  "user": "username",
  "password": "password",
  "port": 1433,
  "options": {
    "encrypt": true,
    "trustServerCertificate": false,
    "applicationIntent": "ReadOnly",
    "requestTimeout": 30000,
    "connectionTimeout": 15000
  }
}
```

### PostgreSQL (structured)
```json
{
  "databaseType": "postgresql",
  "server": "hostname",
  "database": "database_name",
  "user": "username",
  "password": "password",
  "port": 5432,
  "pgOptions": {
    "ssl": true,
    "statement_timeout": 30000,
    "application_name": "mcp-server"
  }
}
```

### PostgreSQL (connection string)
```json
{
  "databaseType": "postgresql",
  "connectionString": "postgresql://user:pass@host:5432/dbname?sslmode=require"
}
```

When using `connectionString`, the `server`, `database`, `user`, and `password` fields are still required but can be set to placeholder values — the connection string takes precedence.

## Security

Security is layered, and the layers have different jobs.

**The boundary is the database.** The strongest control by far is connecting with a least-privilege, read-only database login (see below) — writes are then rejected by the database engine itself, not by string inspection. In addition, PostgreSQL user queries run inside a `SET TRANSACTION READ ONLY` transaction, so the server refuses any write or DDL regardless of how the query is written.

**Pre-flight gate (defense-in-depth).** Before a query reaches the database, a validator fails obvious writes early with a friendly message:

- **Read-only check** — single-statement only; must start with SELECT/WITH; INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, EXEC, MERGE, GRANT, REVOKE, `SELECT ... INTO`, and known side-effecting functions are blocked
- **Automatic row limiting** — capped at 1000 rows by default (max 10,000), enforced on the returned rows regardless of query shape
- **Cross-database blocking** — multi-part names (database.schema.table and linked-server names) are rejected
- **Parameter validation** — only alphanumeric parameter names allowed
- **Error sanitization** — credentials (including connection-string and URL forms), IPv4/IPv6 addresses, and file paths are masked in error output

> The validator is a fast pre-flight, not the guarantee. A string-based check can never be complete (side-effecting functions, engine-specific syntax). **Always** point the server at a read-only login:

```sql
-- PostgreSQL
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;

-- SQL Server
CREATE LOGIN mcp_readonly WITH PASSWORD = 'secure_password';
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;
EXEC sp_addrolemember 'db_datareader', 'mcp_readonly';
```

## Development

```bash
npm run build            # Compile TypeScript
npm run dev              # Watch mode
npm test                 # Run all tests
npm run test:coverage    # Coverage report
```

## Troubleshooting

**"Unknown connection profile"** — The profile name in your tool call doesn't match what's in the config. Check spelling.

**"connect ECONNREFUSED"** — The database isn't running or the host/port is wrong. Verify the database is accessible.

**"Cannot find module"** — Run `npm run build` first. Check the path in `.mcp.json` is absolute and correct.

## License

MIT