# postgres-mcp

!!! DO NOT USE IN PRODUCTION !!!
!!! ONLY FOR LOCAL DEV & DB !!!


A PostgreSQL MCP (Model Context Protocol) server that enables Claude to safely interact with PostgreSQL databases via stdio/JSON-RPC 2.0.

## Features

- **Execute queries** — run parameterized SELECT statements with row limits
- **Explore structure** — list tables/views and inspect columns, indexes, foreign keys
- **Analyze performance** — generate EXPLAIN / EXPLAIN ANALYZE plans
- **Schema context** — expose full DDL-like schema as an MCP resource
- **Security** — read-only mode, schema whitelisting, SQL injection prevention

## Requirements

- Node.js ≥ 18 (ESM)
- PostgreSQL database

## Installation

```bash
npm install
```

## Configuration

Copy `.env.example` to `.env` and fill in your values:

```env
POSTGRES_URL=postgresql://user:password@localhost:5432/dbname
POSTGRES_SCHEMAS=public
POSTGRES_READONLY=true
POSTGRES_QUERY_TIMEOUT=30000
POSTGRES_MAX_ROWS=500
```

| Variable                 | Default      | Description                             |
|--------------------------|--------------|-----------------------------------------|
| `POSTGRES_URL`           | *(required)* | PostgreSQL connection string            |
| `POSTGRES_SCHEMAS`       | `public`     | Comma-separated list of allowed schemas |
| `POSTGRES_READONLY`      | `true`       | Block any write statement               |
| `POSTGRES_QUERY_TIMEOUT` | `30000`      | Query timeout in milliseconds           |
| `POSTGRES_MAX_ROWS`      | `500`        | Maximum rows returned per query         |

## Usage

### Integration with Claude Code

The server is designed to be run via `npx` directly from its local directory — no global install needed.

Add it to your Claude Code config (`~/.claude/settings.json` for global use, or `.claude/settings.json` at project level):

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["/absolute/path/to/postgres_mcp"],
      "env": {
        "POSTGRES_URL": "postgresql://user:password@localhost:5432/dbname",
        "POSTGRES_SCHEMAS": "public",
        "POSTGRES_READONLY": "true"
      }
    }
  }
}
```

Replace `/absolute/path/to/postgres_mcp` with the actual path to this repository on your machine. All configuration is passed via `env` — no `.env` file required when using this approach.

> **Note:** `npx` runs `npm install` automatically on first launch if `node_modules` is absent.

### Testing with MCP Inspector

The [MCP Inspector](https://github.com/modelcontextprotocol/inspector) provides a web UI to test the server interactively without Claude Code.

```bash
POSTGRES_URL=postgresql://user:password@localhost:5432/dbname \
npx @modelcontextprotocol/inspector npx /absolute/path/to/postgres_mcp
```


Then open `http://localhost:5173` in your browser. From there you can:
- Call any **tool** with custom parameters and see the response
- Read the **`postgres://schema`** resource
- Inspect the raw JSON-RPC messages exchanged

## Tools

### `query`

Execute a SQL SELECT query.

```json
{
  "sql": "SELECT * FROM users WHERE id = $1",
  "params": [42]
}
```

Returns: `rows`, `rowCount`, `fields`, `truncated` (if row limit was hit).

### `list_tables`

List all accessible tables and views.

Returns an array of `{ schema, name, type }` objects.

### `describe_table`

Get the full structure of a table: columns, indexes, and foreign keys.

```json
{
  "table": "users",
  "schema": "public"
}
```

Returns: `columns` (name, type, nullable, default, primary_key), `indexes`, `foreign_keys`.

### `explain_query`

Generate an execution plan for a SQL query.

```json
{
  "sql": "SELECT * FROM orders WHERE user_id = 1",
  "analyze": false
}
```

Set `analyze: true` to run EXPLAIN ANALYZE (actually executes the query).

## Resource

### `postgres://schema`

Auto-loaded resource exposing the full database schema in DDL-like format. Gives Claude upfront context about all accessible tables and their columns.

## Security

- **Read-only mode** (default): blocks INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE, GRANT, REVOKE, and other write operations via regex validation
- **Schema isolation**: queries are restricted to schemas listed in `POSTGRES_SCHEMAS`
- **Parameterized queries**: prevents SQL injection
- **Connection pooling**: max 5 connections with idle/connection timeouts
- **Row and time limits**: configurable caps prevent runaway queries

## Tests

```bash
npm test
```

Tests use Vitest with mocked database calls — no live database required.

## Project Structure

```
src/
├── index.js          # MCP server entry point
├── db.js             # Connection pool & configuration
├── security.js       # SQL and schema validation
├── tools/
│   ├── query.js
│   ├── list_tables.js
│   ├── describe_table.js
│   └── explain_query.js
└── resources/
    └── schema.js
tests/
├── security.test.js
├── query.test.js
├── list_tables.test.js
├── describe_table.test.js
├── explain_query.test.js
└── schema_resource.test.js
```