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

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