PostgreSQL MCP Server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that enables AI assistants to interact with PostgreSQL databases using natural language queries. This server provides secure, read-only access to database schemas and allows for natural language to SQL translation.

Features

• 🔒 Secure: Read-only operations only (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH)
• 🗄️ Schema Inspection: List tables, describe structures, view relationships
• 🔍 Query Execution: Execute SQL queries with safety checks and result limits
• 🌐 Natural Language: Enables AI assistants to convert natural language to SQL
• ⚡ Fast: Built with TypeScript and the official MCP SDK

Available Tools

Database Connection

• connect_database - Connect to a PostgreSQL database
• disconnect_database - Disconnect from the current database

Schema Inspection

• list_tables - List all tables in the current database
• describe_table - Get detailed information about a table's structure
• get_schema - Get an overview of the database schema and relationships

Query Execution

• execute_query - Execute read-only SQL queries with safety checks

Quick Test with Docker

1. Start Test Database

npm run docker:up

2. Run Tests

npm run test

3. Try Manual Testing

npm run test:manual

4. Clean Up

npm run docker:down

Installation

Prerequisites

• Node.js 16 or higher
• PostgreSQL database access
• Claude for Desktop (for testing with AI assistant)

Setup

1. Clone and build:

   git clone https://github.com/melihbirim/pg-mcp.git
   cd pg-mcp
   npm install
   npm run build
   

2. Configure Claude for Desktop: Edit your Claude configuration file:

   # macOS
   code ~/Library/Application\ Support/Claude/claude_desktop_config.json
   
   # Windows
   code %APPDATA%\\Claude\\claude_desktop_config.json
   

3. Add server configuration:

   {
     "mcpServers": {
       "postgresql": {
         "command": "node",
         "args": ["/ABSOLUTE/PATH/TO/pg-mcp/build/index.js"]
       }
     }
   }
   

4. Restart Claude for Desktop

Usage Examples

Once connected to Claude for Desktop, you can use natural language to query your database:

Connect to Database

"Connect to my PostgreSQL database named 'inventory' on localhost with username 'admin'"

Schema Exploration

• "What tables are in this database?"
• "Describe the users table structure"
• "Show me the database schema"

Natural Language Queries

• "How many users are in the system?"
• "Show me all products with price greater than $100"
• "What are the top 5 customers by order count?"
• "Find all orders placed in the last 30 days"

Security Features

• Read-only operations: Only SELECT, SHOW, DESCRIBE, EXPLAIN, and WITH statements allowed
• Query validation: Automatic checking for dangerous operations
• Result limits: Automatic LIMIT clauses added to prevent large result sets
• Error handling: Secure error messages without exposing sensitive information

Development

Project Structure

pg-mcp/
├── src/
│   └── index.ts          # Main MCP server implementation
├── build/                # Compiled JavaScript output
├── .github/
│   └── copilot-instructions.md
├── .vscode/
│   └── mcp.json          # VS Code MCP configuration
├── package.json
├── tsconfig.json
└── README.md

Building

npm run build

Running Locally

npm run dev

Testing

The server communicates via stdio, so you can test it by running:

echo '{"jsonrpc": "2.0", "id": 1, "method": "initialize", "params": {"protocolVersion": "2024-11-05", "capabilities": {}, "clientInfo": {"name": "test", "version": "1.0.0"}}}' | node build/index.js

Environment Variables

You can optionally set these environment variables:

• PG_HOST - PostgreSQL host (default: localhost)
• PG_PORT - PostgreSQL port (default: 5432)
• PG_DATABASE - Default database name
• PG_USER - Default username
• PG_PASSWORD - Default password

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

License

ISC License

Troubleshooting

Claude for Desktop Issues

• Check Claude's logs: ~/Library/Logs/Claude/mcp*.log
• Ensure absolute paths in configuration
• Restart Claude for Desktop after config changes

Database Connection Issues

• Verify PostgreSQL is running
• Check connection credentials
• Ensure network access to database host

Permission Issues

chmod +x build/index.js