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

npm install

2. Build

npm run build

3. Configure

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

cp .env.example .env

4. Run

npm run start

Development

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

Testing

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

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)

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:

{
  "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:

{
  "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:

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

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

Tools Overview

License

MIT