NukitaOkamu/mcp-server-postgresql

MCP Server for PostgreSQL

Overview

This project provides an MCP (Model Context Protocol) server that connects to a PostgreSQL database, exposing its schema and data to Large Language Models (LLMs) like Claude via the MCP Python SDK. It allows users to interact with any PostgreSQL database through resources and tools, integrating seamlessly with the Claude Desktop app for natural language queries. Database credentials are managed securely using a .env file.

Features

• Resources:
  • schema://tables: Lists all tables in the database as JSON.
  • schema://schema/{table_name}: Provides detailed schema info for a specific table in JSON.
• Tools:
  • all_table_names: Returns a comma-separated list of all table names.
  • filter_table_names: Filters table names by a search term.
  • schema_definitions: Returns detailed schema info (columns, primary/foreign keys) for specified tables.
  • execute_query: Executes read-only SQL queries and returns formatted results.
• Integration: Works with Claude Desktop for natural language database exploration.

Prerequisites

• Python: 3.11 or higher (tested with 3.11).
• PostgreSQL: A running instance with a database you have access to.
• Claude Desktop: Optional, for LLM integration (download from claude.ai/download).
• Conda: Recommended for environment management (optional but used in this guide).

Installation

Using Conda (Recommended)

1. Clone the Repository:

   git clone https://github.com/yourusername/mcp-server-postgresql.git
   cd mcp-server-postgresql
   

2. Create and Activate Conda Environment:

   conda create -n mcp_env python=3.11
   conda activate mcp_env
   

3. Install Dependencies:

   conda install sqlalchemy psycopg2
   pip install mcp python-dotenv
   

Using Pip (Alternative)

1. Clone the Repository:

   git clone https://github.com/yourusername/mcp-server-postgresql.git
   cd mcp-server-postgresql
   

2. Install Dependencies:

   python -m pip install mcp sqlalchemy psycopg2-binary python-dotenv --user
   

Environment Setup

1. Create a .env File:

   • Copy .env.example to .env:

     cp .env.example .env
     

   • Edit .env with your PostgreSQL credentials:

     # .env - DO NOT COMMIT TO GIT
     DB_USER=your_db_username
     DB_PASSWORD=your_db_password
     DB_HOST=your_db_host
     DB_NAME=your_db_name
     

   • .env is ignored by .gitignore to keep credentials secure.

2. Verify .gitignore:

   • Ensure .env is listed in .gitignore to prevent accidental commits.

Setup for Claude Desktop

To integrate with Claude Desktop, configure it to use this server with your PostgreSQL database.

1. Locate Your Python Path:

   • In your Conda env:

     conda activate mcp_env
     where python  # Windows
     # or
     which python  # Unix-like
     

   • Example output: /path/to/conda/envs/mcp_env/python.

2. Edit Claude Config:

   • Open the Claude Desktop config file:
     • Windows: %APPDATA%\Claude\claude_desktop_config.json
     • MacOS: ~/Library/Application Support/Claude/claude_desktop_config.json
     • Linux: ~/.config/Claude/claude_desktop_config.json
   • Add:

     {
       "mcpServers": {
         "postgres-explorer": {
           "command": "/path/to/conda/envs/mcp_env/python",
           "args": [
             "/path/to/mcp_server_postgresql.py"
           ]
         }
       }
     }
     

   • Replace:
     • /path/to/conda/envs/mcp_env/python with your Conda Python path.
     • /path/to/mcp_server_postgresql.py with the absolute path to the script.
   • Note: The database URL is now loaded from .env, so no need to specify it here.

3. Restart Claude Desktop:

   • Close and reopen the app. Look for a hammer icon near the input field.

Testing Standalone

Test the server without Claude to ensure it connects to your database using the .env configuration.

1. Run the Server:

   conda activate mcp_env
   cd /path/to/mcp-server-postgresql
   python mcp_server_postgresql.py
   

   • Success: Terminal stays open with no errors (assumes .env is set up).

2. Use MCP Inspector:

   mcp dev mcp_server_postgresql.py
   

   • Open http://localhost:8000 in a browser.
   • Test tools like all_table_names or resources like schema://tables.

3. Use Test Script:

   • Run the provided client script:

     python test_mcp_server_postgresql.py
     

   • Expected Output:

     Connecting to database: postgresql://your_db_username:***@your_db_host/your_db_name
     Attempting to list tools...
     Available tools: ['all_table_names', 'filter_table_names', 'schema_definitions', 'execute_query']
     Attempting to call 'all_table_names' tool...
     Table names: users, orders
     

   • Verifies tool listing and execution.

Testing in Claude Desktop

Once connected, use these prompts to verify functionality:

1. List All Tables:

   • Prompt: What tables are in my database?
   • Expected: Comma-separated list (e.g., users, orders).

2. Filter Tables:

   • Prompt: Show me tables in my database that contain "user" in their name.
   • Expected: Filtered list (e.g., users, user_profiles).

3. Schema Details:

   • Prompt: Give me the schema details for the "users" table in my database.
   • Expected: Formatted schema (e.g., users:\n id: INTEGER, primary key\n name: VARCHAR(255), nullable).

4. Execute Query:

   • Prompt: Run this SQL query on my database: SELECT * FROM users LIMIT 5
   • Expected: Formatted rows (e.g., Row 1:\n id: 1\n name: Alice).

5. Table List Resource:

   • Prompt: What’s the list of tables available in my database schema?
   • Expected: JSON list (e.g., ["users", "orders"]).

6. Table Schema Resource:

   • Prompt: Show me the schema for the "users" table as JSON.
   • Expected: JSON schema (e.g., {"columns": [{"name": "id", "type": "INTEGER", "nullable": false, "primary_key": true}]}).

Configuration

• Database Credentials: Managed via .env (see Environment Setup). Required variables:
  • DB_USER: PostgreSQL username.
  • DB_PASSWORD: PostgreSQL password.
  • DB_HOST: Database host (defaults to localhost).
  • DB_NAME: Database name.
• Query Limits: Results truncate at 4000 characters by default. Override with:

  export EXECUTE_QUERY_MAX_CHARS=8000
  python mcp_server_postgresql.py
  

Troubleshooting

• No Hammer Icon:
  • Check logs: <Claude_config_dir>/logs/mcp*.log.
  • Verify Python and script paths in config.
• Database Connection:
  • Test: psql "postgresql://<username>:<password>@<host>/<database>".
  • Ensure .env is correctly set up and PostgreSQL is running.
• ModuleNotFoundError:
  • Confirm dependencies in the Conda env: conda list.
  • Ensure python-dotenv is installed.

Contributing

Fork, submit issues, or send pull requests! See (TBD).

License

Licensed under the MIT License - see (TBD).

Acknowledgments

• Built with MCP Python SDK.
• Thanks to xAI for support in developing this integration.