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