PGMCP

PGMCP is an MCP server that provides a natural language interface to any PostgreSQL database.

It translates plain English questions into SQL queries, executes them safely with read-only access, and streams the results back to MCP clients like Claude Desktop or Cursor.

This MCP server enables you to interact with your existing database schemas, whether for analytics, e-commerce, or CRM, without writing a single line of SQL.

Features

• 🗣️ Natural Language to SQL: Ask questions in English; get SQL results.
• 🔒 Safe Read-Only Access: Blocks any write operations like INSERT, UPDATE, or DELETE.
• 🌊 Automatic Streaming: Handles huge result sets without eating all your memory.
• 🌐 Universal Compatibility: Works with any existing PostgreSQL database schema out of the box.
• 📄 Multiple Output Formats: Delivers results as a table, JSON, or CSV.
• 🛠️ Robust Error Handling: Recovers gracefully when the AI generates incorrect SQL.

Use Cases

• Rapid Data Exploration: You’re a developer jumping into a new project with a complex database. Instead of digging for schema diagrams, you can just ask PGMCP, “Show me all tables related to user profiles” or “What columns are in the orders table?” to get oriented quickly.
• Quick Answers for Non-Developers: A product manager needs to know how many users signed up last week. Instead of asking an engineer to run a query, they can use a client connected to PGMCP and ask the question directly, getting the number back instantly.
• Safe AI-Powered Development: You are using an AI assistant like Cursor to write code that interacts with the database. You can connect it to PGMCP to give the AI context about the database schema safely. The AI can then generate accurate code without having direct, risky access to the database credentials.
• Building Internal Tools: You can build a simple search interface for your support team on top of PGMCP. The search tool lets them perform a free-text search across all text columns to find customer records without needing a complicated admin panel.

How to Use It

1. Prerequisites:

• Go 1.23 or newer
• A PostgreSQL database
• An OpenAI API key (or another compatible API)

2. Compile the server and a test client from the source.

# Build the server and client
go build -o pgmcp-server ./server
go build -o pgmcp-client ./client

3. Point it to your own database.

# Set the connection string for your database
export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
# This step is optional, only needed for the sample marketplace data
psql $DATABASE_URL < schema.sql

4. Now, run the server. You need to provide an API key for the language model that generates the SQL.

# Set your API key and run the server
export OPENAI_API_KEY="your-api-key"
./pgmcp-server

5. You can test it with the client you compiled earlier.

# Ask a question and get a table-formatted response
./pgmcp-client -ask "Who is the user that places the most orders?" -format table
# Ask another question
./pgmcp-client -ask "Show me the top 40 most reviewed items in the marketplace" -format table

6. Control the server through these environment variables:

• DATABASE_URL (Required): The connection string for your PostgreSQL database.
• OPENAI_API_KEY: Your OpenAI API key.
• OPENAI_MODEL: The model you want to use. It defaults to “gpt-4o-mini”.
• HTTP_ADDR: The server address. It defaults to “:8080”.
• HTTP_PATH: The MCP endpoint path. It defaults to “/mcp”.
• AUTH_BEARER: A bearer token you can set for simple authentication.

7. To use it with Cursor, add the server to your settings file.

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

8. For Claude Desktop, edit ~/.config/claude-desktop/claude_desktop_config.json.

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

9. The MCP server exposes three main tools via the MCP protocol:

• stream: Provides advanced, paginated streaming for handling extremely large result sets.
• ask: Transforms a natural language question into SQL, executes it, and streams the results.
• search: Performs a case-insensitive text search across all textual columns in the database.

FAQs

Q: Does PGMCP make any changes to my database?
A: No. It operates in a strictly read-only mode. It cannot alter your schema or your data. The server blocks any SQL commands like INSERT, UPDATE, DELETE, or DROP.

Q: What happens if the AI generates a bad or harmful SQL query?
A: PGMCP has safety features to handle this. First, its read-only enforcement blocks any write attempts. Second, if the AI generates syntactically incorrect SQL (e.g., referencing a column that doesn’t exist), the server catches the database error and returns a helpful JSON message with a suggestion instead of crashing.

Q: Can I use this with a local LLM like Ollama instead of OpenAI?
A: Yes. As long as your local LLM exposes an OpenAI-compatible API endpoint, you can configure PGMCP to use it.