# PostgreSQL MCP Server

A Model Context Protocol (MCP) server for PostgreSQL that enables LLMs (including GLM 4.7 via z.ai) to interact deeply with PostgreSQL databases — query data, manage schema, analyze performance, and administer the database.

## Prerequisites

- Node.js >= 20
- PostgreSQL >= 13

## Features

- **40+ tools** across 8 categories: Query, Execute, Schema Inspection, Table Management, Index Management, Performance Analysis, Data Export, Administration
- **4 resources** exposing live database context (schema, tables, stats, config)
- **5 prompts** for guided workflows (query building, optimization, schema design, debugging, migration planning)
- **Security**: parameterized queries, permission levels, O(1) rate limiting, SQL injection guards (including WHERE clause validation), export SELECT-only enforcement, dangerous operation guards
- **Transaction support** with automatic rollback on failure

## Quick Start

### 1. Install dependencies

```bash
npm install
```

### 2. Build

```bash
npm run build
```

### 3. Configure

Copy `.env.example` to `.env` and fill in your PostgreSQL credentials:

```bash
cp .env.example .env
```

### 4. Run

```bash
npm run start
```

## Development

```bash
npm run dev        # watch TypeScript changes
npm run typecheck  # type-check only
npm run clean      # remove dist/
```

## Testing

```bash
npm test            # run all tests (vitest)
npm run test:watch  # watch mode
npm run test:coverage # with v8 coverage report
```

189 tests across 14 test files covering tools, guards, resources, prompts, validation, utils, and DB layer.

## Operations

```bash
npm run check       # typecheck + build
npm run healthcheck # verify DB connectivity
```

`npm run start` now runs `prestart` automatically to ensure fresh build output before launch.

## Release (npm)

```bash
npm run check     # must pass before publish
npm pack          # verify package contents locally
# npm publish     # publish when ready
```

`prepublishOnly` is configured to run `npm run check` automatically.

## MCP Client Configuration

### Claude Desktop / Cursor / VS Code

Add to your MCP settings:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_DATABASE": "mydb",
        "POSTGRES_USER": "postgres",
        "POSTGRES_PASSWORD": "secret",
        "PERMISSION_LEVEL": "read_write"
      }
    }
  }
}
```

Or use a connection string:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/postgres-mcp/dist/index.js"],
      "env": {
        "POSTGRES_CONNECTION_STRING": "postgresql://user:pass@localhost:5432/mydb"
      }
    }
  }
}
```

### GLM 4.7 (z.ai) Integration

Use OpenAI-compatible API to connect GLM 4.7 with this MCP server's tools converted to function calling format:

```typescript
import OpenAI from 'openai';

const client = new OpenAI({
  apiKey: 'YOUR_Z_AI_API_KEY',
  baseURL: 'https://open.z.ai/api/paas/v4'
});

const response = await client.chat.completions.create({
  model: 'glm-4-plus',
  messages: [
    { role: 'system', content: 'You are a database assistant.' },
    { role: 'user', content: 'Show me all tables' }
  ],
});
```

## Permission Levels

| Level         | Allowed Operations                              |
| ------------- | ----------------------------------------------- |
| `read_only`   | SELECT, EXPLAIN, schema inspection              |
| `read_write`  | + INSERT, UPDATE, DELETE                         |
| `admin`       | + DDL (CREATE/ALTER TABLE), VACUUM, index mgmt   |
| `dangerous`   | + DROP, TRUNCATE, terminate connections          |

Set via `PERMISSION_LEVEL` env var. `ENABLE_DANGEROUS_OPERATIONS=true` is also required for dangerous ops.

## Tools Overview

| Category     | Tools                                                                                   |
| ------------ | --------------------------------------------------------------------------------------- |
| Query        | `query`, `query_with_limit`, `search_data`                                              |
| Execute      | `execute`, `insert_row`, `update_rows`, `delete_rows`, `upsert`, `bulk_insert`, `transaction` |
| Schema       | `list_databases`, `list_schemas`, `list_tables`, `describe_table`, `list_constraints`, `list_indexes`, `list_views`, `list_functions`, `list_triggers`, `list_enums`, `get_foreign_keys`, `get_table_size`, `get_full_schema` |
| Table Mgmt   | `create_table`, `alter_table`, `drop_table`, `rename_table`, `truncate_table`, `add_column`, `drop_column` |
| Index Mgmt   | `create_index`, `drop_index`, `reindex`, `list_unused_indexes`                          |
| Performance  | `explain_query`, `get_slow_queries`, `get_table_stats`, `get_connection_stats`, `get_lock_info`, `get_cache_hit_ratio`, `vacuum_analyze`, `get_bloat_info` |
| Export       | `export_csv`, `export_json`, `generate_ddl`                                             |
| Admin        | `list_roles`, `get_database_size`, `get_active_queries`, `cancel_query`, `terminate_connection`, `get_replication_status`, `get_config_settings` |

## License

MIT