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

Clone and build:

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

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

Add server configuration:

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

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

Fork the repository
Create a feature branch
Make your changes
Add tests if applicable
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

PostgreSQL