scionoftech/pg_mcp

PostgreSQL MCP Server

A Model Context Protocol (MCP) server for PostgreSQL that enables natural language querying of PostgreSQL databases using AI. This project includes a complete MCP server, client, and web interface.

Features

• Natural Language to SQL: Convert plain English queries to SQL using Claude AI
• MCP Server: Full-featured MCP server with PostgreSQL tools
• Web Interface: Simple and intuitive web UI for querying the database
• Direct SQL Execution: Support for both natural language and direct SQL queries
• Schema Inspection: Browse tables and view detailed schema information
• Sample Database: Includes a pre-populated PostgreSQL database with realistic test data
• Docker Support: Complete Docker setup for easy deployment

Architecture

┌─────────────────┐
│   Web Browser   │
└────────┬────────┘
         │
         ↓
┌─────────────────┐
│   Flask Web UI  │
└────────┬────────┘
         │
         ↓
┌─────────────────┐      ┌──────────────┐
│   MCP Client    │◄─────┤  Anthropic   │
└────────┬────────┘      │  Claude API  │
         │               └──────────────┘
         ↓
┌─────────────────┐
│   MCP Server    │
└────────┬────────┘
         │
         ↓
┌─────────────────┐
│   PostgreSQL    │
└─────────────────┘

Project Structure

pg_mcp/
├── mcp_server/              # MCP Server implementation
│   ├── server.py           # Main MCP server with PostgreSQL tools
│   └── requirements.txt    # Server dependencies
├── mcp_client/              # MCP Client implementation
│   └── client.py           # Client for interacting with MCP server
├── web_ui/                  # Web interface
│   ├── app.py              # Flask web application
│   ├── templates/          # HTML templates
│   │   └── index.html     # Main web interface
│   └── requirements.txt    # Web UI dependencies
├── db/                      # Database initialization
│   └── init.sql            # Sample database schema and data
├── Dockerfile              # Docker image configuration
├── docker-compose.yml      # Docker Compose orchestration
├── .env.example           # Environment variables template
└── README.md              # This file

Prerequisites

• Docker and Docker Compose
• Anthropic API Key (for text-to-SQL conversion)

Quick Start

1. Clone the Repository

git clone <repository-url>
cd pg_mcp

2. Configure Environment Variables

Create a .env file from the example:

cp .env.example .env

Edit .env and add your Anthropic API key:

POSTGRES_HOST=postgres
POSTGRES_PORT=5432
POSTGRES_DB=sampledb
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
ANTHROPIC_API_KEY=your_api_key_here

3. Start the Services

docker-compose up -d

This will start:

• PostgreSQL database on port 5432
• Web UI on port 5000

4. Access the Web Interface

Open your browser and navigate to:

http://localhost:5000

Usage

Web Interface

The web interface provides three main tabs:

1. Natural Language Query

• Enter queries in plain English
• Examples:
  • "show me all users"
  • "count orders by status"
  • "find the top 5 products by price"
  • "list customers from New York"
• The system will convert your query to SQL and display results

2. Direct SQL

• Execute SQL queries directly
• Examples:
  • SELECT * FROM users LIMIT 10;
  • SELECT status, COUNT(*) FROM orders GROUP BY status;
  • UPDATE users SET is_active = true WHERE user_id = 1;

3. Database Schema

• Browse all tables in the database
• Click on a table to view its schema and sample data
• See column types, constraints, and relationships

MCP Client (Command Line)

You can also use the MCP client directly from the command line:

# Enter the container
docker exec -it pg_mcp_web bash

# Run the client
python mcp_client/client.py

Available commands:

• query <natural language> - Execute a natural language query
• sql <SQL query> - Execute a SQL query directly
• tables - List all tables
• describe <table> - Describe a table
• quit - Exit

Sample Database

The system includes a pre-populated database with the following tables:

• users - Sample user accounts (10 users)
• products - Product catalog (15 products)
• orders - Customer orders (10 orders)
• order_items - Order line items
• reviews - Product reviews

Example Queries

Try these natural language queries in the web interface:

1. "show me all users from the USA"
2. "list the top 5 most expensive products"
3. "count how many orders are in each status"
4. "find all products in the Electronics category"
5. "show me users who have placed orders"
6. "what is the average rating for each product"
7. "list all orders with their total amounts"
8. "find customers who have reviewed products"

MCP Tools

The MCP server provides the following tools:

1. query

Execute a natural language query against the database.

Parameters:

• natural_query (string): Natural language query

Example:

{
  "natural_query": "show me all users from New York"
}

2. execute_sql

Execute a direct SQL query.

Parameters:

• sql (string): SQL query to execute

Example:

{
  "sql": "SELECT * FROM users WHERE city = 'New York';"
}

3. list_tables

List all tables in the database.

Parameters: None

4. describe_table

Get detailed schema information for a specific table.

Parameters:

• table_name (string): Name of the table

Example:

{
  "table_name": "users"
}

Development

Running Locally (Without Docker)

1. Install Dependencies

# Install MCP server dependencies
pip install -r mcp_server/requirements.txt

# Install web UI dependencies
pip install -r web_ui/requirements.txt

2. Start PostgreSQL

# Using Docker for PostgreSQL only
docker run -d \
  --name postgres \
  -e POSTGRES_DB=sampledb \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -v ./db/init.sql:/docker-entrypoint-initdb.d/init.sql \
  postgres:16-alpine

3. Configure Environment

export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
export POSTGRES_DB=sampledb
export POSTGRES_USER=postgres
export POSTGRES_PASSWORD=postgres
export ANTHROPIC_API_KEY=your_api_key_here

4. Run the Web UI

python web_ui/app.py

5. Or Run the MCP Client

python mcp_client/client.py

Configuration

Environment Variables

Troubleshooting

Connection Issues

If the web UI can't connect to the database:

1. Check that PostgreSQL is running:

   docker-compose ps
   

2. Check PostgreSQL logs:

   docker-compose logs postgres
   

3. Verify connection from the web container:

   docker exec -it pg_mcp_web pg_isready -h postgres -U postgres
   

MCP Client Issues

If the MCP client fails to connect:

1. Ensure the MCP server is accessible
2. Check that the ANTHROPIC_API_KEY is set correctly
3. Verify PostgreSQL connection settings

Database Reset

To reset the database with fresh sample data:

docker-compose down -v
docker-compose up -d

API Endpoints

The web UI exposes the following REST API endpoints:

• GET / - Web interface
• POST /api/query - Execute natural language query
• POST /api/execute_sql - Execute SQL query
• GET /api/tables - List all tables
• GET /api/describe/<table_name> - Describe table schema

Security Considerations

• The sample configuration uses default passwords - change these in production
• The ANTHROPIC_API_KEY should be kept secure and not committed to version control
• SQL injection protection is handled by parameterized queries
• Consider implementing authentication for the web interface in production
• Limit database user permissions based on your use case

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License.

Acknowledgments

• Built with MCP (Model Context Protocol)
• Uses Anthropic's Claude for text-to-SQL conversion
• PostgreSQL database

Support

For issues, questions, or contributions, please open an issue on the GitHub repository.