PostgreSQL Model Context Protocol (MCP) Server

A comprehensive Model Context Protocol (MCP) Server built with FastAPI that provides full PostgreSQL database operations through a standardized JSON-RPC 2.0 interface. This server can be integrated with VS Code and other MCP-compatible tools for seamless database management.

Features

🚀 Core MCP Functionality

• JSON-RPC 2.0 compliant API
• VS Code integration ready
• Authentication with API key
• Comprehensive error handling
• Auto-fallback to mock service when PostgreSQL is unavailable

🗄️ PostgreSQL Operations

Basic Operations

• Connection Testing - Test database connectivity and get server info
• Query Execution - Execute any SQL query (SELECT, INSERT, UPDATE, DELETE, etc.)
• Schema Information - Get complete database schema with tables and columns
• Table Information - Detailed table metadata, columns, indexes, and row counts

Advanced Operations

• Query Performance Analysis - EXPLAIN ANALYZE for query optimization
• Database Size Information - Database and table size statistics
• Table Statistics - Column statistics, null fractions, distinct values
• Active Connections - Monitor current database connections
• Locks Information - View database locks and blocking queries
• Slow Queries Analysis - Identify performance bottlenecks (requires pg_stat_statements)

Administrative Operations

• Table Backup - Create table copies with or without data
• Index Creation - Create indexes on tables (regular or unique)
• Table Optimization - VACUUM and ANALYZE tables for performance

Utility Operations

• Echo Tool - Simple echo for testing connectivity

Available Tools

Tool Name	Description	Parameters
echo	Echo back input message	message (string)
postgres_connection_test	Test PostgreSQL connection	None
postgres_query	Execute SQL query	query (string), params (array, optional)
postgres_schema	Get database schema	None
postgres_table_info	Get table information	table_name (string), schema (string, default: "public")
postgres_query_analyze	Analyze query performance	query (string)
postgres_database_size	Get database size info	None
postgres_table_stats	Get table statistics	table_name (string), schema (string, default: "public")
postgres_active_connections	Get active connections	None
postgres_backup_table	Backup table	source_table (string), backup_table (string), schema (string), include_data (boolean)
postgres_create_index	Create table index	table_name (string), column_names (array), index_name (string, optional), schema (string), unique (boolean)
postgres_slow_queries	Get slow queries	limit (integer, default: 10)
postgres_optimize_table	Optimize table	table_name (string), schema (string, default: "public")
postgres_locks_info	Get database locks	None

Installation

1. Clone the repository:

git clone <repository-url>
cd pg-mcp

2. Install dependencies:

pip install -r requirements.txt
# or using pyproject.toml
pip install -e .

3. Configure PostgreSQL connection: Edit app/config.py with your PostgreSQL settings:

POSTGRES_DB_CONFIG = {
    "host": "localhost",
    "port": "5432", 
    "dbname": "your_database",
    "user": "your_username",
    "password": "your_password"
}

4. Set API key: Update MCP_API_KEY in app/config.py or set environment variable.

Usage

Start the Server

# Development
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

# Production
uvicorn app.main:app --host 0.0.0.0 --port 8000

VS Code Integration

1. Install MCP extension in VS Code
2. Configure MCP settings in .vscode/mcp.json:

{
  "mcpServers": {
    "postgres-mcp": {
      "name": "PostgreSQL MCP Server",
      "url": "http://localhost:8000/mcp",
      "apiKey": "your-secret-mcp-api-key"
    }
  }
}

Direct API Usage

Test connection:

curl -X GET http://localhost:8000/mcp \
  -H "Authorization: Bearer your-secret-mcp-api-key"

Execute query:

curl -X POST http://localhost:8000/mcp \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer your-secret-mcp-api-key" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "postgres_query",
      "arguments": {
        "query": "SELECT version()"
      }
    }
  }'

Testing

Comprehensive Test Suite

# Run unit tests
pytest test/

# Run comprehensive integration test
python test_postgres_mcp_comprehensive.py

Individual Tool Testing

# Test specific functionality
python test_postgres_mcp.py

Architecture

pg-mcp/
├── app/
│   ├── main.py              # FastAPI application entry point
│   ├── mcp.py               # MCP server implementation
│   ├── postgres_service.py  # PostgreSQL service layer
│   ├── postgres_mock.py     # Mock service for testing
│   ├── json_rpc.py          # JSON-RPC 2.0 implementation
│   ├── auth.py              # Authentication middleware
│   ├── config.py            # Configuration settings
│   └── logger.py            # Logging configuration
├── test/                    # Test files
├── .vscode/
│   └── mcp.json            # VS Code MCP configuration
└── pyproject.toml          # Project dependencies

Key Features

🔒 Security

• API key authentication for all requests
• SQL injection protection through parameterized queries
• Error sanitization to prevent information leakage

🚀 Performance

• Connection pooling for database connections
• Async/await support throughout
• Query optimization tools and analysis

🛠️ Development

• Auto-reload support for development
• Comprehensive logging for debugging
• Mock service for testing without real database
• Type hints throughout codebase

🔧 Reliability

• Automatic fallback to mock service
• Comprehensive error handling
• Connection retry logic
• Graceful degradation

Examples

Query Execution

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "postgres_query",
    "arguments": {
      "query": "SELECT * FROM users WHERE active = $1",
      "params": ["true"]
    }
  }
}

Table Backup

{
  "jsonrpc": "2.0", 
  "id": 2,
  "method": "tools/call",
  "params": {
    "name": "postgres_backup_table",
    "arguments": {
      "source_table": "users",
      "backup_table": "users_backup_20240115",
      "schema": "public",
      "include_data": true
    }
  }
}

Index Creation

{
  "jsonrpc": "2.0",
  "id": 3, 
  "method": "tools/call",
  "params": {
    "name": "postgres_create_index",
    "arguments": {
      "table_name": "users",
      "column_names": ["email", "status"], 
      "index_name": "idx_users_email_status",
      "unique": false
    }
  }
}

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Ensure all tests pass
6. Submit a pull request

License

MIT License - see LICENSE file for details.

Support

For issues and questions:

• Create an issue on GitHub
• Check the test files for usage examples
• Review the comprehensive test suite for full API coverage