🐘 PostgreSQL MCP Server for Claude Desktop

Connect Claude Desktop to your PostgreSQL database using the Model Context Protocol (MCP). Ask Claude questions in plain English and it will query your database, explore schemas, and analyze data — no SQL required.

---

✨ Features

| Category | Tools | |---|---| | 🗄️ Database Info | get_database_info, list_databases | | 📂 Schema Exploration | list_schemas, list_tables, describe_table, search_schema, list_indexes | | 🔍 Querying | execute_query, get_table_sample, get_table_stats, explain_query | | ✏️ Write Operations | execute_write (disabled by default — opt-in via .env) | | 📄 Resources | schema://overview, table://{schema}/{table} | | 💬 Prompts | analyze_table, write_sql_query |

---

🚀 Quick Start

1. Clone the Repository

git clone https://github.com/sarotechhub/Claude-Desktop-to-PostgreSQL.git
cd Cluade-MCP-PostgreSQL

2. Create a Virtual Environment

# Windows
python -m venv .venv
.venv\Scripts\activate

# macOS / Linux
python -m venv .venv
source .venv/bin/activate

3. Install Dependencies

pip install -r requirements.txt

4. Configure Your Database

# Copy the example env file
cp .env.example .env

Edit .env with your PostgreSQL credentials:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database_name
DB_USER=your_username
DB_PASSWORD=your_password

# Set to "true" to allow INSERT/UPDATE/DELETE
ALLOW_WRITE_OPERATIONS=false

All credentials live only in .env — they are never put in the Claude Desktop config file.

5. Test the Server

python main.py

You should see: `` 🚀 Starting PostgreSQL MCP Server... ✅ Database connection pool ready. ``

Press Ctrl+C to stop.

6. Connect to Claude Desktop

Find your Claude Desktop config file:

| OS | Path | |---|---| | Windows | %APPDATA%\Claude\claude_desktop_config.json | | macOS | ~/Library/Application Support/Claude/claude_desktop_config.json | | Linux | ~/.config/Claude/claude_desktop_config.json |

Add the postgres block to the mcpServers section (update the path):

{
  "mcpServers": {
    "postgres": {
      "command": "[PATH_TO_YOUR_VENV_PYTHON_EXE]",
      "args": ["[PATH_TO_YOUR_PROJECT_ROOT]\\main.py"],
      "cwd": "[PATH_TO_YOUR_PROJECT_ROOT]",
      "env": {
        "PYTHONPATH": "[PATH_TO_YOUR_PROJECT_ROOT]"
      }
    }
  }
}

Note: No DB credentials go in this file. The server reads them automatically from your .env file using python-dotenv.

Restart Claude Desktop after saving the config.

---

💬 Example Conversations with Claude

Once connected, try asking Claude:

"List all tables in my database"
"Describe the structure of the users table"
"Show me 10 sample rows from the orders table"
"How many rows are in each table in the public schema?"
"Find all columns related to 'email' across all tables"
"What indexes exist on the products table?"
"Write a query to find the top 10 customers by total order value"
"Explain why this query might be slow: SELECT * FROM orders WHERE status = 'pending'"

---

🔧 Tool Reference

Database Info

| Tool | Description | |---|---| | get_database_info | PostgreSQL version, DB size, connection count, server info | | list_databases | All databases on the server with sizes and encoding |

Schema Exploration

| Tool | Description | |---|---| | list_schemas() | All user-defined schemas in the connected database | | list_tables(schema) | Tables in a schema with row counts and sizes | | describe_table(table, schema) | Columns, types, nullable, defaults, PKs, FKs | | search_schema(keyword) | Find tables/columns by keyword (case-insensitive) | | list_indexes(table, schema) | Indexes on a table with type and columns |

Querying

| Tool | Description | |---|---| | execute_query(sql, limit) | Read-only SELECT (enforced via read-only transaction) | | get_table_sample(table, schema, limit) | Sample rows from a table (max 100) | | get_table_stats(table, schema) | Row counts, sizes, vacuum/analyze timestamps | | explain_query(sql) | EXPLAIN execution plan (no data modification) |

Write Operations

| Tool | Description | |---|---| | execute_write(sql, confirm) | INSERT/UPDATE/DELETE — requires ALLOW_WRITE_OPERATIONS=true in .env AND confirm=True |

---

🔒 Security

Read-only by default — write operations require explicit opt-in in .env
Credentials in .env only — never in the Claude Desktop config
Read-only transactions — SELECT queries run inside readonly=True transactions
Input validation — table/schema names validated with regex to prevent injection
No DDL — DROP, CREATE, ALTER are always blocked even when writes are enabled
.env excluded from git — credentials never committed

---

📁 Project Structure

Cluade-MCP-PostgreSQL/
├── main.py                    # 🚀 MCP server entry point (FastMCP + stdio)
├── database.py                # 🔌 Async connection pool (asyncpg)
├── tools/
│   ├── __init__.py
│   ├── schema_tools.py        # list_schemas, list_tables, describe_table, search_schema, list_indexes
│   ├── query_tools.py         # execute_query, get_table_sample, get_table_stats, explain_query
│   ├── write_tools.py         # execute_write (opt-in)
│   └── database_tools.py     # get_database_info, list_databases
├── .env                       # ✅ Your credentials (NOT committed to git)
├── .env.example               # Template for new users
├── .gitignore                 # Excludes .env, .venv, __pycache__
├── requirements.txt           # mcp[cli], asyncpg, python-dotenv, pydantic, orjson
├── claude_desktop_config.json # Example Claude Desktop config snippet
└── README.md

---

⚙️ Environment Variables

All configuration is done via .env:

| Variable | Default | Description | |---|---|---| | DB_HOST | localhost | PostgreSQL host | | DB_PORT | 5432 | PostgreSQL port | | DB_NAME | — | Database name | | DB_USER | — | Database user | | DB_PASSWORD | — | Database password | | DB_MIN_CONNECTIONS | 1 | Min pool connections | | DB_MAX_CONNECTIONS | 10 | Max pool connections | | ALLOW_WRITE_OPERATIONS | false | Enable INSERT/UPDATE/DELETE | | LOG_LEVEL | INFO | Logging level (DEBUG/INFO/WARNING) |

---

🛠️ Troubleshooting

Claude doesn't see the MCP server

Fully quit and reopen Claude Desktop after editing the config
Check the cwd path — it must point to the project folder
Verify the .venv Python path is correct: .venv\Scripts\python.exe

Connection refused / authentication failed

Check your .env credentials match your PostgreSQL setup
Test directly: psql -h localhost -U your_user -d your_db
Ensure PostgreSQL is running: pg_isready

`mcp` or `asyncpg` module not found

.venv\Scripts\pip install -r requirements.txt

Server starts but tools don't appear in Claude

Open Claude Desktop → Settings → Developer → MCP Servers
Check for error messages next to the postgres server entry
Run python main.py manually and check stderr for import errors

---

📋 Requirements

Python 3.10+
PostgreSQL 12+
Claude Desktop (with MCP support)

---

📄 License

MIT License — free to use, modify, and distribute.

---

🤝 Contributing

Pull requests welcome! Please fork the repo, create a feature branch, and submit a PR with a clear description.

PostgreSQL MCP Server