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:

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

Alternative Options

# 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

Docker/Kubernetes

# 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/

Quick Start

# 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

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

# 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

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:

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

AI Error Handling

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.

MCP Integration

Cursor Integration

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

Add to Cursor settings:

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

Claude Desktop Integration

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

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

API Tools

• 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

Safety Features

• 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

Testing

# Unit tests
go test ./server -v

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

License

Apache 2.0 - See LICENSE file for details.

Related Projects

• Model Context Protocol - The underlying protocol specification
• MCP Go SDK - Go implementation of MCP

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