postgres-mcp

# 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