Postgres MCP

PGMCP connects toyour existing PostgreSQL databaseand makes it accessible to AI assistants through natural language queries.

- PostgreSQL database (existing database with your schema)
- OpenAI API key (optional, for AI-powered SQL generation)

# Set up environment variables export DATABASE_URL="postgres://user:password@localhost:5432/your-existing-db" export OPENAI_API_KEY="your-api-key" # Optional # Run server (using pre-compiled binary) ./pgmcp-server # Test with client in another terminal ./pgmcp-client -ask "What tables do I have?" -format table ./pgmcp-client -ask "Who is the customer that has placed the most orders?" -format table ./pgmcp-client -search "john" -format table

👤 User / AI Assistant │ │ "Who are the top customers?" ▼ ┌─────────────────────────────────────────────────────────────┐ │ Any MCP Client │ │ │ │ PGMCP CLI │ Cursor │ Claude Desktop │ VS Code │ ... │ │ JSON/CSV │ Chat │ AI Assistant │ Editor │ │ └─────────────────────────────────────────────────────────────┘ │ │ Streamable HTTP / MCP Protocol ▼ ┌─────────────────────────────────────────────────────────────┐ │ PGMCP Server │ │ │ │ 🔒 Security 🧠 AI Engine 🌊 Streaming │ │ • Input Valid • Schema Cache • Auto-Pagination │ │ • Audit Log • OpenAI API • Memory Management │ │ • SQL Guard • Error Recovery • Connection Pool │ └─────────────────────────────────────────────────────────────┘ │ │ Read-Only SQL Queries ▼ ┌─────────────────────────────────────────────────────────────┐ │ Your PostgreSQL Database │ │ │ │ Any Schema: E-commerce, Analytics, CRM, etc. │ │ Tables • Views • Indexes • Functions │ └─────────────────────────────────────────────────────────────┘ External AI Services: OpenAI API • Anthropic • Local LLMs (Ollama, etc.) Key Benefits: ✅ Works with ANY PostgreSQL database (no assumptions about schema) ✅ No schema modifications required ✅ Read-only access (100% safe) ✅ Automatic streaming for large results ✅ Intelligent query understanding (singular vs plural) ✅ Robust error handling (graceful AI failure recovery) ✅ PostgreSQL case sensitivity support (mixed-case tables) ✅ Production-ready security and performance ✅ Universal database compatibility ✅ Multiple output formats (table, JSON, CSV) ✅ Free-text search across all columns ✅ Authentication support ✅ Comprehensive testing suite

- 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

- DATABASE_URL: PostgreSQL connection string to your existing database

# Example for macOS/Linux tar xzf pgmcp_.tar.gz cd pgmcp_ ./pgmcp-server

# Homebrew (macOS/Linux) - Available after first release brew tap subnetmarco/homebrew-tap brew install pgmcp # Build from source go build -o pgmcp-server ./server go build -o pgmcp-client ./client

Add-ldflags="-s -w -extldflags=-static" -trimpathif you want to get stripped executables (no debug info):

go build -ldflags="-s -w -extldflags=-static" -trimpath -o pgmcp-server ./server go build -ldflags="-s -w -extldflags=-static" -trimpath -o pgmcp-client ./client

# Docker docker run -e DATABASE_URL="postgres://user:pass@host:5432/db" \ -p 8080:8080 ghcr.io/subnetmarco/pgmcp:latest # Kubernetes (see examples/ directory for full manifests) kubectl create secret generic pgmcp-secret \ --from-literal=database-url="postgres://user:pass@host:5432/db" kubectl apply -f examples/k8s/

# Set up database (optional - works with any existing PostgreSQL database) export DATABASE_URL="postgres://user:password@localhost:5432/mydb" psql $DATABASE_URL < schema.sql # Run server export OPENAI_API_KEY="your-api-key" ./pgmcp-server # Test with client ./pgmcp-client -ask "Who is the user that places the most orders?" -format table ./pgmcp-client -ask "Show me the top 40 most reviewed items in the marketplace" -format table

- DATABASE_URL: PostgreSQL connection string

- 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

# Ask questions in natural language ./pgmcp-client -ask "What are the top 5 customers?" -format table ./pgmcp-client -ask "How many orders were placed today?" -format json # Search across all text fields ./pgmcp-client -search "john" -format table # Multiple questions at once ./pgmcp-client -ask "Show tables" -ask "Count users" -format table # Different output formats ./pgmcp-client -ask "Export all data" -format csv -max-rows 1000

- schema.sql: Full Amazon-like marketplace with 5,000+ records
- schema_minimal.sql: Minimal test schema with mixed-case"Categories"table

- Mixed-case table names("Categories") for testing case sensitivity
- Composite primary keys(order_items) for testing AI assumptions
- Realistic relationshipsand data types

export DATABASE_URL="postgres://user:pass@host:5432/your_db" ./pgmcp-server ./pgmcp-client -ask "What tables do I have?"

When AI generates incorrect SQL, PGMCP handles it gracefully:

{ "error": "Column not found in generated query", "suggestion": "Try rephrasing your question or ask about specific tables", "original_sql": "SELECT non_existent_column FROM table..." }

Instead of crashing, the system provides helpful feedback and continues operating.

# Start server export DATABASE_URL="postgres://user:pass@localhost:5432/your_db" ./pgmcp-server

{ "mcp.servers": { "pgmcp": { "transport": { "type": "http", "url": "http://localhost:8080/mcp" } } } }

Edit~/.config/claude-desktop/claude_desktop_config.json:

{ "mcpServers": { "pgmcp": { "transport": { "type": "http", "url": "http://localhost:8080/mcp" } } } }

- ask: Natural language questions → SQL queries with automatic streaming
- search: Free-text search across all database text columns
- stream: Advanced streaming for very large result sets with pagination

- Read-Only Enforcement: Blocks write operations (INSERT, UPDATE, DELETE, etc.)
- Query Timeouts: Prevents long-running queries
- Input Validation: Sanitizes and validates all user input
- Transaction Isolation: All queries run in read-only transactions

# Unit tests go test ./server -v # Integration tests (requires PostgreSQL) go test ./server -tags=integration -v

Apache 2.0 - See LICENSE file for details.

PGMCP makes your PostgreSQL database accessible to AI assistants through natural language while maintaining security through read-only access controls.

Official Airtable MCP server and skills for working with bases, records, workflows, and business operations from AI agents.

MCP Server For Apache Doris, an MPP-based real-time data warehouse.

Official MCP Server from Atlan which enables you to bring the power of metadata to your AI tools

Query Onchain data, like ERC20 tokens, transaction history, smart contract state.

Read and write access to your Baserow tables.

Introspect and query your apps deployed to Convex.

Interact with the data stored in Couchbase clusters using natural language.