PGMCP - PostgreSQL Model Context Protocol Server

1[![ci](https://github.com/subnetmarco/pgmcp/actions/workflows/ci.yml/badge.svg)](https://github.com/subnetmarco/pgmcp/actions/workflows/ci.yml)
2[![Go Report Card](https://goreportcard.com/badge/github.com/subnetmarco/pgmcp)](https://goreportcard.com/report/github.com/subnetmarco/pgmcp)
3[![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://opensource.org/licenses/Apache-2.0)
4 
5# PGMCP - PostgreSQL Model Context Protocol Server
6 
7PGMCP 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.
8 
9**Works with**: Cursor, Claude Desktop, VS Code extensions, and any [MCP-compatible client](https://modelcontextprotocol.io/)
10 
11## Quick Start
12 
13PGMCP connects to **your existing PostgreSQL database** and makes it accessible to AI assistants through natural language queries.
14 
15### Prerequisites
16- PostgreSQL database (existing database with your schema)
17- OpenAI API key (optional, for AI-powered SQL generation)
18 
19### Basic Usage
20 
21```bash
22# Set up environment variables
23export DATABASE_URL="postgres://user:password@localhost:5432/your-existing-db"
24export OPENAI_API_KEY="your-api-key"  # Optional
25 
26# Run server (using pre-compiled binary)
27./pgmcp-server
28 
29# Test with client in another terminal
30./pgmcp-client -ask "What tables do I have?" -format table
31./pgmcp-client -ask "Who is the customer that has placed the most orders?" -format table
32./pgmcp-client -search "john" -format table
33```
34 
35Here is how it works:
36 
37```
38👤 User / AI Assistant
39         │
40         │ "Who are the top customers?"
41         ▼
42┌─────────────────────────────────────────────────────────────┐
43│                    Any MCP Client                           │
44│                                                             │
45│  PGMCP CLI  │  Cursor  │  Claude Desktop  │  VS Code  │ ... │
46│  JSON/CSV   │  Chat    │  AI Assistant    │  Editor   │     │
47└─────────────────────────────────────────────────────────────┘
48         │
49         │ Streamable HTTP / MCP Protocol
50         ▼
51┌─────────────────────────────────────────────────────────────┐
52│                    PGMCP Server                             │
53│                                                             │
54│  🔒 Security    🧠 AI Engine      🌊 Streaming               │
55│  • Input Valid  • Schema Cache    • Auto-Pagination         │
56│  • Audit Log    • OpenAI API      • Memory Management       │
57│  • SQL Guard    • Error Recovery  • Connection Pool         │
58└─────────────────────────────────────────────────────────────┘
59         │
60         │ Read-Only SQL Queries
61         ▼
62┌─────────────────────────────────────────────────────────────┐
63│                Your PostgreSQL Database                     │
64│                                                             │
65│  Any Schema: E-commerce, Analytics, CRM, etc.               │
66│  Tables • Views • Indexes • Functions                       │
67└─────────────────────────────────────────────────────────────┘
68 
69External AI Services:
70OpenAI API • Anthropic • Local LLMs (Ollama, etc.)
71 
72Key Benefits:
73✅ Works with ANY PostgreSQL database (no assumptions about schema)
74✅ No schema modifications required  
75✅ Read-only access (100% safe)
76✅ Automatic streaming for large results
77✅ Intelligent query understanding (singular vs plural)
78✅ Robust error handling (graceful AI failure recovery)
79✅ PostgreSQL case sensitivity support (mixed-case tables)
80✅ Production-ready security and performance
81✅ Universal database compatibility
82✅ Multiple output formats (table, JSON, CSV)
83✅ Free-text search across all columns
84✅ Authentication support
85✅ Comprehensive testing suite
86```
87 
88## Features
89 
90- **Natural Language to SQL**: Ask questions in plain English
91- **Automatic Streaming**: Handles large result sets automatically  
92- **Safe Read-Only Access**: Prevents any write operations
93- **Text Search**: Search across all text columns
94- **Multiple Output Formats**: Table, JSON, and CSV
95- **PostgreSQL Case Sensitivity**: Handles mixed-case table names correctly
96- **Universal Compatibility**: Works with any PostgreSQL database
97 
98### Environment Variables
99 
100**Required:**
101- `DATABASE_URL`: PostgreSQL connection string to your existing database
102 
103**Optional:**
104- `OPENAI_API_KEY`: OpenAI API key for AI-powered SQL generation
105- `OPENAI_MODEL`: Model to use (default: "gpt-4o-mini")
106- `HTTP_ADDR`: Server address (default: ":8080")
107- `HTTP_PATH`: MCP endpoint path (default: "/mcp")
108- `AUTH_BEARER`: Bearer token for authentication
109 
110## Installation
111 
112### Download Pre-compiled Binaries
113 
1141. Go to [GitHub Releases](https://github.com/subnetmarco/pgmcp/releases)
1152. Download the binary for your platform (Linux, macOS, Windows)
1163. Extract and run:
117 
118```bash
119# Example for macOS/Linux
120tar xzf pgmcp_*.tar.gz
121cd pgmcp_*
122./pgmcp-server
123```
124 
125### Alternative Options
126 
127```bash
128# Homebrew (macOS/Linux) - Available after first release
129brew tap subnetmarco/homebrew-tap
130brew install pgmcp
131 
132# Build from source
133go build -o pgmcp-server ./server
134go build -o pgmcp-client ./client
135```
136 
137### Docker/Kubernetes
138 
139```bash
140# Docker
141docker run -e DATABASE_URL="postgres://user:pass@host:5432/db" \
142  -p 8080:8080 ghcr.io/subnetmarco/pgmcp:latest
143 
144# Kubernetes (see examples/ directory for full manifests)
145kubectl create secret generic pgmcp-secret \
146  --from-literal=database-url="postgres://user:pass@host:5432/db"
147kubectl apply -f examples/k8s/
148```
149 
150#### Quick Start
151 
152```bash
153# Set up database (optional - works with any existing PostgreSQL database)
154export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
155psql $DATABASE_URL < schema.sql
156 
157# Run server
158export OPENAI_API_KEY="your-api-key"
159./pgmcp-server
160 
161# Test with client
162./pgmcp-client -ask "Who is the user that places the most orders?" -format table
163./pgmcp-client -ask "Show me the top 40 most reviewed items in the marketplace" -format table
164```
165 
166### Environment Variables
167 
168**Required:**
169- `DATABASE_URL`: PostgreSQL connection string
170 
171**Optional:**
172- `OPENAI_API_KEY`: OpenAI API key for SQL generation
173- `OPENAI_MODEL`: Model to use (default: "gpt-4o-mini")
174- `HTTP_ADDR`: Server address (default: ":8080")
175- `HTTP_PATH`: MCP endpoint path (default: "/mcp")
176- `AUTH_BEARER`: Bearer token for authentication
177 
178## Usage Examples
179 
180```bash
181# Ask questions in natural language
182./pgmcp-client -ask "What are the top 5 customers?" -format table
183./pgmcp-client -ask "How many orders were placed today?" -format json
184 
185# Search across all text fields
186./pgmcp-client -search "john" -format table
187 
188# Multiple questions at once
189./pgmcp-client -ask "Show tables" -ask "Count users" -format table
190 
191# Different output formats
192./pgmcp-client -ask "Export all data" -format csv -max-rows 1000
193```
194 
195## Example Database
196 
197The project includes two schemas:
198- **`schema.sql`**: Full Amazon-like marketplace with 5,000+ records
199- **`schema_minimal.sql`**: Minimal test schema with mixed-case `"Categories"` table
200 
201**Key features:**
202- **Mixed-case table names** (`"Categories"`) for testing case sensitivity
203- **Composite primary keys** (`order_items`) for testing AI assumptions
204- **Realistic relationships** and data types
205 
206Use your own database:
207```bash
208export DATABASE_URL="postgres://user:pass@host:5432/your_db"
209./pgmcp-server
210./pgmcp-client -ask "What tables do I have?"
211```
212 
213## AI Error Handling
214 
215When AI generates incorrect SQL, PGMCP handles it gracefully:
216 
217```json
218{
219  "error": "Column not found in generated query",
220  "suggestion": "Try rephrasing your question or ask about specific tables",
221  "original_sql": "SELECT non_existent_column FROM table..."
222}
223```
224 
225Instead of crashing, the system provides helpful feedback and continues operating.
226 
227## MCP Integration
228 
229### Cursor Integration
230 
231```bash
232# Start server
233export DATABASE_URL="postgres://user:pass@localhost:5432/your_db"
234./pgmcp-server
235```
236 
237Add to Cursor settings:
238```json
239{
240  "mcp.servers": {
241    "pgmcp": {
242      "transport": {
243        "type": "http",
244        "url": "http://localhost:8080/mcp"
245      }
246    }
247  }
248}
249```
250 
251### Claude Desktop Integration
252 
253Edit `~/.config/claude-desktop/claude_desktop_config.json`:
254```json
255{
256  "mcpServers": {
257    "pgmcp": {
258      "transport": {
259        "type": "http",
260        "url": "http://localhost:8080/mcp"
261      }
262    }
263  }
264}
265```
266 
267## API Tools
268 
269- **`ask`**: Natural language questions → SQL queries with automatic streaming
270- **`search`**: Free-text search across all database text columns  
271- **`stream`**: Advanced streaming for very large result sets with pagination
272 
273## Safety Features
274 
275- **Read-Only Enforcement**: Blocks write operations (INSERT, UPDATE, DELETE, etc.)
276- **Query Timeouts**: Prevents long-running queries
277- **Input Validation**: Sanitizes and validates all user input
278- **Transaction Isolation**: All queries run in read-only transactions
279 
280## Testing
281 
282```bash
283# Unit tests
284go test ./server -v
285 
286# Integration tests (requires PostgreSQL)
287go test ./server -tags=integration -v
288```
289 
290## License
291 
292Apache 2.0 - See LICENSE file for details.
293 
294## Related Projects
295 
296- [Model Context Protocol](https://modelcontextprotocol.io/) - The underlying protocol specification
297- [MCP Go SDK](https://github.com/modelcontextprotocol/go-sdk) - Go implementation of MCP
298 
299---
300 
301PGMCP makes your PostgreSQL database accessible to AI assistants through natural language while maintaining security through read-only access controls.
302