subnetmarco/pgmcp

PGMCP connects AI assistants to **any PostgreSQL database** through natural language queries. Ask questions in plain English and get structured SQL results with automatic streaming and robust error handling.
**Works with**: Cursor, Claude Desktop, VS Code extensions, and any MCP-compatible client
Quick Start
PGMCP connects to **your existing PostgreSQL database** and makes it accessible to AI assistants through natural language queries.
Prerequisites
- PostgreSQL database (existing database with your schema)
- OpenAI API key (optional, for AI-powered SQL generation)
Basic Usage
Here is how it works:
Features
- **Natural Language to SQL**: Ask questions in plain English
- **Automatic Streaming**: Handles large result sets automatically
- **Safe Read-Only Access**: Prevents any write operations
- **Text Search**: Search across all text columns
- **Multiple Output Formats**: Table, JSON, and CSV
- **PostgreSQL Case Sensitivity**: Handles mixed-case table names correctly
- **Universal Compatibility**: Works with any PostgreSQL database
Environment Variables
**Required:**
- DATABASE_URL: PostgreSQL connection string to your existing database
**Optional:**
- OPENAI_API_KEY: OpenAI API key for AI-powered SQL generation
- OPENAI_MODEL: Model to use (default: "gpt-4o-mini")
- HTTP_ADDR: Server address (default: ":8080")
- HTTP_PATH: MCP endpoint path (default: "/mcp")
- AUTH_BEARER: Bearer token for authentication
Installation
Download Pre-compiled Binaries
1. Go to GitHub Releases
2. Download the binary for your platform (Linux, macOS, Windows)
3. Extract and run:
Alternative Options
Add -ldflags="-s -w -extldflags=-static" -trimpath if you want to get stripped executables (no debug info):
Docker/Kubernetes
Quick Start
Environment Variables
**Required:**
- DATABASE_URL: PostgreSQL connection string
**Optional:**
- OPENAI_API_KEY: OpenAI API key for SQL generation
- OPENAI_MODEL: Model to use (default: "gpt-4o-mini")
- HTTP_ADDR: Server address (default: ":8080")
- HTTP_PATH: MCP endpoint path (default: "/mcp")
- AUTH_BEARER: Bearer token for authentication
Usage Examples
Example Database
The project includes two schemas:
- **schema.sql**: Full Amazon-like marketplace with 5,000+ records
- **schema_minimal.sql**: Minimal test schema with mixed-case "Categories" table
**Key features:**
- **Mixed-case table names** ("Categories") for testing case sensitivity
- **Composite primary keys** (order_items) for testing AI assumptions
- **Realistic relationships** and data types
Use your own database:
AI Error Handling
When AI generates incorrect SQL, PGMCP handles it gracefully:
Instead of crashing, the system provides helpful feedback and continues operating.
MCP Integration
Cursor Integration
Add to Cursor settings:
Claude Desktop Integration