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

Related MCP server: Model Context Protocol PostgreSQL Server

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

2. Run Tests

3. Try Manual Testing

4. Clean Up

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

Building

Running Locally

Testing

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

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