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": "[email protected]"},
  "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

1. Fork the repository
2. Create a feature branch
3. Commit your changes
4. Create a Pull Request

See Development Guide for detailed setup instructions.

License

AGPLv3 License - see LICENSE file for details.