pgmcp

PGMCP exposes a read-only MCP server that translates natural language queries into SQL against your PostgreSQL database. It connects to your existing database via a connection string and uses an AI model to generate and refine SQL queries, streaming results back to clients in multiple formats (table, JSON, CSV). You can access it through any MCP-compatible client or integration (Cursor, Claude Desktop, VS Code extensions, etc.). To start, run the server with a connected PostgreSQL database and an optional OpenAI API key for enhanced SQL generation. Then use the included client tools or your MCP client to ask questions like: What are the top customers by revenue, or Show me the orders placed in the last 7 days. The system handles read-only access, streaming, error recovery, and supports mixed-case PostgreSQL identifiers.

Prerequisites:

• PostgreSQL database (existing database with your schema)
• Docker installed (for the recommended container-based setup) or go compiler if you build from source
• Optional: OpenAI API key for AI-powered SQL generation

Installation (Docker-based):

1. Ensure you have Docker installed and running.
2. Run the container with required environment variables and port mapping:

docker run -i \
  -p 8080:8080 \
  -e DATABASE_URL="postgres://user:pass@host:5432/db" \
  -e OPENAI_API_KEY="your-api-key" \
  -e OPENAI_MODEL="gpt-4o-mini" \
  -e HTTP_ADDR=":8080" \
  -e HTTP_PATH="/mcp" \
  -e AUTH_BEARER="your-secret" \
  ghcr.io/subnetmarco/pgmcp:latest

If you prefer building from source (Go):

1. Install Go (1.18+).
2. Clone the repository and build:

go build -o pgmcp-server ./server

3. Run with a PostgreSQL connection string and optional API key:

export DATABASE_URL="postgres://user:pass@host:5432/db"
export OPENAI_API_KEY="your-api-key"  # Optional
./pgmcp-server

Alternative installation (pre-compiled binaries): download the appropriate binary from the GitHub Releases, extract, and run the server binary directly.

Tips:

• Use a read-only PostgreSQL user to maximize safety.
• If you encounter SQL errors from AI generation, enable verbose logging and review AI-generated SQL for correctness; the system is designed to gracefully recover and retry.
• Ensure the DATABASE_URL is accessible from the environment where the server runs (network access, correct host, and permissions).
• The default HTTP path is /mcp and default port is 8080; adjust HTTP_ADDR and HTTP_PATH as needed for your deployment.
• When using Docker, you can map a different host port if 8080 is in use, e.g., -p 8081:8080 and set HTTP_ADDR=":8081".
• If you run behind a reverse proxy, configure TLS at the proxy and forward to the MCP endpoint.