PostgreSQL MCP Server
A Model Context Protocol (MCP) server that provides comprehensive PostgreSQL database management capabilities for AI assistants.
Features
🚀 What's New: This server has been completely redesigned from 46 individual tools to 17 intelligent tools through consolidation (34→8 meta-tools) and enhancement (+4 new tools), providing better AI discovery while adding powerful data manipulation and comment management capabilities.
Quick Start
Prerequisites
- Node.js ≥18.0.0
- Access to a PostgreSQL server
- (Optional) An MCP client like Cursor or Claude for AI integration
Option 1: npm (Recommended)
# Install globally
npm install -g @henkey/postgres-mcp-server
# Or run directly with npx (no installation)
# Use env var for connection string (optional)
export POSTGRES_CONNECTION_STRING="postgresql://user:pass@localhost:5432/db"
npx @henkey/postgres-mcp-server
# Or pass directly:
npx @henkey/postgres-mcp-server --connection-string "postgresql://user:pass@localhost:5432/db"
Verify installation
npx @henkey/postgres-mcp-server --help
Add to your MCP client configuration:
{
"mcpServers": {
"postgresql-mcp": {
"command": "npx",
"args": [
"@henkey/postgres-mcp-server",
"--connection-string", "postgresql://user:password@host:port/database"
]
}
}
}
Option 2: Install via Smithery
npx -y @smithery/cli install @HenkDz/postgresql-mcp-server --client claude
Option 3: Docker (Recommended for Production)
# Build the Docker image
docker build -t postgres-mcp-server .
# Run with environment variable
docker run -i --rm \
-e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
postgres-mcp-server
Add to your MCP client configuration:
{
"mcpServers": {
"postgresql-mcp": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"henkey/postgres-mcp:latest",
"-e",
"POSTGRES_CONNECTION_STRING"
],
"env": {
"POSTGRES_CONNECTION_STRING": "postgresql://user:password@host:port/database"
}
}
}
}
Option 4: Manual Installation (Development)
git clone <repository-url>
cd postgresql-mcp-server
npm install
npm run build
Add to your MCP client configuration:
{
"mcpServers": {
"postgresql-mcp": {
"command": "node",
"args": [
"/path/to/postgresql-mcp-server/build/index.js",
"--connection-string", "postgresql://user:password@host:port/database"
]
}
}
}
What's Included
17 powerful tools organized into three categories:
- 🔄 Consolidation: 34 original tools consolidated into 8 intelligent meta-tools
- 🔧 Specialized: 5 tools kept separate for complex operations
- 🆕 Enhancement: 4 brand new tools (not in original 46)
📊 Consolidated Meta-Tools (8 tools)
- Schema Management - Tables, columns, ENUMs, constraints
- User & Permissions - Create users, grant/revoke permissions
- Query Performance - EXPLAIN plans, slow queries, statistics
- Index Management - Create, analyze, optimize indexes
- Functions - Create, modify, manage stored functions
- Triggers - Database trigger management
- Constraints - Foreign keys, checks, unique constraints
- Row-Level Security - RLS policies and management
🚀 Enhancement Tools (4 NEW tools)
Brand new capabilities not available in the original 46 tools
- Execute Query - SELECT operations with count/exists support
- Execute Mutation - INSERT/UPDATE/DELETE/UPSERT operations
- Execute SQL - Arbitrary SQL execution with transaction support
- Comments Management - Comprehensive comment management for all database objects
🔧 Specialized Tools (5 tools)
- Database Analysis - Performance and configuration analysis
- Debug Database - Troubleshoot connection, performance, locks
- Data Export/Import - JSON/CSV data migration
- Copy Between Databases - Cross-database data transfer
- Real-time Monitoring - Live database metrics and alerts
Example Usage
// Analyze database performance
{ "analysisType": "performance" }
// Create a table with constraints
{
"operation": "create_table",
"tableName": "users",
"columns": [
{ "name": "id", "type": "SERIAL PRIMARY KEY" },
{ "name": "email", "type": "VARCHAR(255) UNIQUE NOT NULL" }
]
}
// Query data with parameters
{
"operation": "select",
"query": "SELECT * FROM users WHERE created_at > $1",
"parameters": ["2024-01-01"],
"limit": 100
}
// Insert new data
{
"operation": "insert",
"table": "users",
"data": {"name": "John Doe", "email": "john@example.com"},
"returning": "*"
}
// Find slow queries
{
"operation": "get_slow_queries",
"limit": 5,
"minDuration": 100
}
// Execute a parameterized SELECT query
{
"operation": "select",
"query": "SELECT * FROM users WHERE id = $1",
"parameters": [1]
}
// Perform an INSERT mutation
{
"operation": "insert",
"table": "products",
"data": {"name": "New Product", "price": 99.99},
"returning": "id"
}
// Manage database object comments
{
"operation": "set",
"objectType": "table",
"objectName": "users",
"comment": "Main user account information table"
}
📚 Documentation
📋 Complete Tool Schema Reference - All 18 tool parameters & examples in one place
For additional information, see the docs/ folder:
- 📖 Usage Guide - Comprehensive tool usage and examples
- 🛠️ Development Guide - Setup and contribution guide
- ⚙️ Technical Details - Architecture and implementation
- 👨💻 Developer Reference - API reference and advanced usage
- 📋 Documentation Index - Complete documentation overview
Features Highlights
🔄 Consolidation Achievements
✅ 34→8 meta-tools - Intelligent consolidation for better AI discovery
✅ Multiple operations per tool - Unified schemas with operation parameters
✅ Smart parameter validation - Clear error messages and type safety
🆕 Enhanced Data Capabilities
✅ Complete CRUD operations - INSERT/UPDATE/DELETE/UPSERT with parameterized queries
✅ Flexible querying - SELECT with count/exists support and safety limits
✅ Arbitrary SQL execution - Transaction support for complex operations
🔧 Production Ready
✅ Flexible connection - CLI args, env vars, or per-tool configuration
✅ Security focused - SQL injection prevention, parameterized queries
✅ Robust architecture - Connection pooling, comprehensive error handling
Docker Usage
The PostgreSQL MCP Server is fully Docker-compatible and can be used in production environments.
Building the Image
# Build locally
docker build -t postgres-mcp-server .
# Or pull from Docker Hub
docker pull henkey/postgres-mcp:latest
Running with Environment Variables
# Basic usage (using Docker Hub image)
docker run -i --rm \
-e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
henkey/postgres-mcp:latest
# Or with locally built image
docker run -i --rm \
-e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
postgres-mcp-server
# With tools configuration
docker run -i --rm \
-e POSTGRES_CONNECTION_STRING="postgresql://user:password@host:port/database" \
-e POSTGRES_TOOLS_CONFIG="/app/config/tools.json" \
-v /path/to/config:/app/config \
postgres-mcp-server
Docker Compose Example
version: '3.8'
services:
postgres-mcp:
build: .
environment:
- POSTGRES_CONNECTION_STRING=postgresql://user:password@postgres:5432/database
depends_on:
- postgres
stdin_open: true
tty: true
postgres:
image: postgres:15
environment:
- POSTGRES_DB=database
- POSTGRES_USER=user
- POSTGRES_PASSWORD=password
ports:
- "5432:5432"
MCP Client Configuration
For use with MCP clients like Cursor or Claude Desktop:
{
"mcpServers": {
"postgresql-mcp": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"henkey/postgres-mcp:latest",
"-e",
"POSTGRES_CONNECTION_STRING"
],
"env": {
"POSTGRES_CONNECTION_STRING": "postgresql://user:password@host:port/database"
}
}
}
}
Prerequisites
- Node.js ≥ 18.0.0 (for local development)
- Docker (for containerized deployment)
- PostgreSQL server access
- Valid connection credentials
Contributing
- Fork the repository
- Create a feature branch
- Commit your changes
- Create a Pull Request
See Development Guide for detailed setup instructions.
License
AGPLv3 License - see LICENSE file for details.