PostgreSQL Manager + MCP Server

A complete Python application for PostgreSQL database management with two modes:

• Desktop GUI (Tkinter) — for direct human use
• MCP Server — for AI-assisted database analysis via Claude Desktop

What It Does

🔌 Connection Management

• Save multiple named PostgreSQL connection profiles in config.ini
• Test connections before saving
• Fields: host, port, database, username, password, SSL mode

🌲 Object Explorer

• Browse: Schemas → Tables → Columns (with types, nullability, PK markers)
• Browse: Schemas → Views
• Double-click a table → auto-generates SELECT * FROM ... LIMIT 100
• Right-click → Select Top 100, Show Columns

📝 SQL Query Editor

• Syntax highlighting for SQL keywords
• Press F5 to execute (or click Run)
• Query history (session-based, navigate with ↑/↓ buttons)
• Select specific text to run only that portion

📊 Results Grid

• Scrollable table with column headers
• Row count + elapsed time display
• Export to CSV with one click

🧪 SP Analyzer + Test Data Generator

Upload a .sql file containing a stored procedure or function:

1. Analyse — extracts SELECT queries, detects referenced tables, infers WHERE conditions
2. Generate Preview — creates Faker-based test data respecting types, FKs, enums, UNIQUE
3. Insert Test Data — inserts into an open transaction (NOT committed)
4. Run your SP manually in the query editor to verify
5. Commit or Rollback — on-demand, manual control

🤖 MCP Server (AI-Powered Mode)

When connected to Claude Desktop, Claude can:

• Fetch SP bodies directly from pg_proc
• Execute SELECT queries found in the SP
• Recursively follow SQL strings stored inside DB column values (the killer feature)
• Understand schema, FKs, enums, CHECK constraints
• Generate and insert test data in FK-dependency order
• Wait for you to verify, then commit or rollback

---

Prerequisites

Mandatory

Requirement	Version	Check Command
Python	3.10+	python --version
pip	any	pip --version
PostgreSQL	10+ (any server)	psql --version

Optional (for MCP mode)

Requirement	Purpose
Claude Desktop	AI client that connects to our MCP server

⚠️ Python must be in your system PATH. Run python --version in a terminal to verify.

---

Installation

Option 1: Run the installer script (Windows)

install.bat

This checks for Python, creates a virtual environment, and installs all dependencies.

Option 2: Manual

pip install -r requirements.txt

Dependencies installed:

• psycopg2-binary — PostgreSQL driver
• sqlparse — SQL statement parser
• Faker — realistic test data generation
• mcp[cli] — Anthropic's MCP SDK for the AI server

---

Running the Application

Desktop GUI

run.bat

Or manually:

python main.py

MCP Server (for Claude Desktop)

python mcp_server.py

With auto-connect:

python mcp_server.py --host localhost --port 5432 --db mydb --user postgres --password secret
python mcp_server.py --profile local
python mcp_server.py --dsn "postgresql://user:pass@host:5432/db"

---

Configuring Claude Desktop

1. Copy claude_desktop_config.json to %APPDATA%\Claude\claude_desktop_config.json (or merge into your existing config)
2. Restart Claude Desktop
3. The postgres-mcp tools will appear automatically

Example config:

{
  "mcpServers": {
    "postgres-mcp": {
      "command": "python",
      "args": ["C:/Balaji/MyProjects/postgres-mcp/mcp_server.py"],
      "cwd": "C:/Balaji/MyProjects/postgres-mcp"
    }
  }
}

---

Configuration — config.ini

[app]
theme = clam
font_size = 11
row_limit = 1000
default_test_rows = 10

[profile_local]
name = Local PostgreSQL
host = localhost
port = 5432
database = postgres
username = postgres
password =
ssl_mode = prefer

Add more profiles by duplicating the [profile_xxx] section with a unique name.

---

MCP Tools Reference

Tool	Description
connect_to_postgres	Connect with host/port/db/user/password
get_connection_status	Check if connected
get_sp_body	Fetch SP/function source from pg_proc
list_sps	List all SPs/functions in a schema
list_tables	List base tables
list_views	List views
get_view_definition	Get view SQL (discover real tables behind views)
get_table_schema	Full metadata: columns, PKs, FKs, UNIQUEs, CHECKs
get_fk_reference_values	Read valid values from FK parent tables
get_enum_values	List valid enum labels
sample_table_data	Sample existing rows
execute_query	Run any SELECT/read SQL
execute_in_transaction	Run INSERT/UPDATE/DELETE inside safe test tx
generate_test_data	Faker-based row generation
begin_test_transaction	Open a write transaction
insert_rows	Insert row dicts into the open transaction
commit_test_data	Commit permanently
rollback_test_data	Roll back all test inserts
transaction_status	Check if a test transaction is open

---

Troubleshooting

Issue	Solution
python not found	Add Python to system PATH
psycopg2 install fails	Install Visual C++ Build Tools, or use psycopg2-binary (already in requirements)
MCP server not showing in Claude	Check %APPDATA%\Claude\claude_desktop_config.json path is correct
Connection refused	Verify PostgreSQL is running and accepting connections on the configured host:port
ModuleNotFoundError: mcp	Run pip install "mcp[cli]>=1.27,<2"

---

License

MIT