ahmedmustahid/postgres-mcp-server

A Model Context Protocol (MCP) server that provides both HTTP and Stdio transports for interacting with PostgreSQL databases. This server exposes database resources and tools through both transport methods, allowing for flexible integration in different environments.
Features
- **Dual Transport Support**: Both HTTP (StreamableHTTPServerTransport) and Stdio (StdioServerTransport)
- **Database Resources**: List tables and retrieve schema information
- **Query Tool**: Execute read-only SQL queries
- **Stateful Sessions**: HTTP transport supports session management
- **Docker Support**: Containerized deployments for both transports
- **Production Ready**: Graceful shutdown, error handling, and logging
Quick Start
Environment Setup
The database credentials must be passed as:
1. Either environment variables
2. Or in the working directory (directory where npx command will be run):
create a .env file (the package uses dotenv package)
Run using npx
1. Download node.js and npm from here
2. Run the package. By default, it will run streamable http on port 3000:
3. For stdio transport:
Environment Setup
Copy environment template
Edit your database credentials
Podman(or Docker) Usage
1. Install podman from here
2. Install uv from here
3. Install podman compose package: uv add podman-compose (or uv sync to sync packages in pyproject.toml)
Test using Claude Desktop
First, install node.js and npm, build the project following the above instructions.
Edit your claude_desktop_config.json
Check if MCP Server has been enabled
Verify from Claude Desktop Window
Using MCP Server from Claude Desktop
Prompt: Show sales table from last year.
Test using MCP Inspector
First, install node.js and npm, build the project following the above instructions.
Install MCP Inspector: instructions: here
Check Stdio MCP Server
Check Streamable HTTP MCP Server
First, run the server (shell where environment has been configured):
Run the mcp inspector from another terminal
After selecting Streamable HTTP from drop down menu, insert http://localhost:3000/mcp(default) into URL.
MCP tools:
MCP Resource:
Configuration
Environment Variables
You have to specify these inside the .env file.
| Variable | Description | Default | Required |
|----------|-------------|---------|----------|
| POSTGRES_USERNAME | PostgreSQL username | - | Yes |
| POSTGRES_PASSWORD | PostgreSQL password | - | Yes |
| POSTGRES_HOST | PostgreSQL host | - | Yes |
| POSTGRES_DATABASE | PostgreSQL database name | - | Yes |
| PORT | HTTP server port | 3000 | No |
| HOST | HTTP server host | 0.0.0.0 | No |
| CORS_ORIGIN | Allowed CORS origins (comma-separated) | localhost:8080,localhost:3000 | No |
| NODE_ENV | Environment mode | development | No |
Resources
Hello World (hello://world)
A simple greeting message for testing.
Database Tables (database://tables)
Lists all tables in the public schema with their schema URIs.
Database Schema (database://tables/{tableName}/schema)