ahmedmustahid/postgres-mcp-server

README

MCP PostgreSQL Server (Stateful and Dual Transport)

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:

Either environment variables

# PostgreSQL Database Connection String
export POSTGRES_URL=your_connection_string

# PostgreSQL Database Configuration if POSTGRES_URL is not provided
export POSTGRES_USERNAME=your_username
export POSTGRES_PASSWORD=your_password
export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432 # this is the default
export POSTGRES_DATABASE=your_database

# HTTP Server Configuration
# Following are the default values
export PORT=3000
export HOST=0.0.0.0

# CORS Configuration (comma-separated list of allowed origins)
# Following are the default values
export CORS_ORIGIN=http://localhost:8080,http://localhost:3000

# Environment
# Following are the default values
export NODE_ENV=development

Or in the working directory (directory where npx command will be run): create a .env file (the package uses dotenv package)

# .env.example
# PostgreSQL Database Configuration
POSTGRES_USERNAME=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_HOST=localhost
POSTGRES_DATABASE=your_database

# HTTP Server Configuration
PORT=3000
HOST=0.0.0.0

# CORS Configuration (comma-separated list of allowed origins)
CORS_ORIGIN=http://localhost:8080,http://localhost:3000

# Environment
NODE_ENV=development

Run using `npx`

Download node.js and npm from here
Run the package. By default, it will run streamable http on port 3000:

npx @ahmedmustahid/postgres-mcp-server
# or npx @ahmedmustahid/postgres-mcp-server --port 3000 --verbose

For stdio transport:

npx @ahmedmustahid/postgres-mcp-server stdio
# npx @ahmedmustahid/postgres-mcp-server stdio --verbose

Environment Setup

Copy environment template

cp .env.example .env

Edit your database credentials

nano .env

Podman(or Docker) Usage

Install podman from here
Install uv from here
Install podman compose package: uv add podman-compose (or uv sync to sync packages in pyproject.toml)

#get the environment variables
set -a
source .env
set +a
podman machine start
make podman-up

Test using Claude Desktop

First, install node.js and npm, build the project following the above instructions. Edit your claude_desktop_config.json

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "npx",
      "args": [
        "@ahmedmustahid/postgres-mcp-server",
        "stdio"
      ],
      "env": {
        "POSTGRES_USERNAME": "your-username",
        "POSTGRES_PASSWORD": "your-password",
        "POSTGRES_HOST": "hostname",
        "POSTGRES_DATABASE": "database-name"
      }
    }
  }
}

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

npx @modelcontextprotocol/inspector npx @ahmedmustahid/postgres-mcp-server stdio

Check Streamable HTTP MCP Server

First, run the server (shell where environment has been configured):

npx @ahmedmustahid/postgres-mcp-server

Run the mcp inspector from another terminal

npx @modelcontextprotocol/inspector

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`)

Returns column information for a specific table.

Tools

query

Execute read-only SQL queries against the database.

Parameters:

sql (string): The SQL query to execute

Transport Differences

Feature	HTTP Transport	Stdio Transport
Session Management	✅ Stateful sessions	❌ Stateless
Concurrent Connections	✅ Multiple clients	❌ Single process
Web Integration	✅ REST API compatible	❌ CLI only
Interactive Use	✅ Via HTTP clients	✅ Direct stdio
Docker Deployment	✅ Web service	✅ CLI container

Health Checks

The HTTP server includes a basic health check endpoint accessible at the /health endpoint with a GET request (returns 405 Method Not Allowed, confirming the server is responsive).

Troubleshooting

Common Issues

Database Connection Errors

# Check your database credentials in .env
# Ensure PostgreSQL is running and accessible

Port Already in Use

# Change PORT in .env or stop conflicting services
lsof -i :3000

Docker Build Issues

# Clean Docker cache
npm run docker:clean
docker system prune -a

Session Management (HTTP)

# Sessions are stored in memory and will reset on server restart
# For production, consider implementing persistent session storage

Development

Adding New Resources

Create a new file in src/resources/
Implement the resource registration function
Add it to src/server/server.ts

Adding New Tools

Create a new file in src/tools/
Implement the tool registration function
Add it to src/server/server.ts

License

MIT

Contributing

Please read the contributing guidelines and submit pull requests to the main repository.

README

MCP PostgreSQL Server (Stateful and Dual Transport)

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:

Either environment variables

# PostgreSQL Database Connection String
export POSTGRES_URL=your_connection_string

# PostgreSQL Database Configuration if POSTGRES_URL is not provided
export POSTGRES_USERNAME=your_username
export POSTGRES_PASSWORD=your_password
export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432 # this is the default
export POSTGRES_DATABASE=your_database

# HTTP Server Configuration
# Following are the default values
export PORT=3000
export HOST=0.0.0.0

# CORS Configuration (comma-separated list of allowed origins)
# Following are the default values
export CORS_ORIGIN=http://localhost:8080,http://localhost:3000

# Environment
# Following are the default values
export NODE_ENV=development

Or in the working directory (directory where npx command will be run): create a .env file (the package uses dotenv package)

# .env.example
# PostgreSQL Database Configuration
POSTGRES_USERNAME=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_HOST=localhost
POSTGRES_DATABASE=your_database

# HTTP Server Configuration
PORT=3000
HOST=0.0.0.0

# CORS Configuration (comma-separated list of allowed origins)
CORS_ORIGIN=http://localhost:8080,http://localhost:3000

# Environment
NODE_ENV=development

Run using `npx`

Download node.js and npm from here
Run the package. By default, it will run streamable http on port 3000:

npx @ahmedmustahid/postgres-mcp-server
# or npx @ahmedmustahid/postgres-mcp-server --port 3000 --verbose

For stdio transport:

npx @ahmedmustahid/postgres-mcp-server stdio
# npx @ahmedmustahid/postgres-mcp-server stdio --verbose

Environment Setup

Copy environment template

cp .env.example .env

Edit your database credentials

nano .env

Podman(or Docker) Usage

Install podman from here
Install uv from here
Install podman compose package: uv add podman-compose (or uv sync to sync packages in pyproject.toml)

#get the environment variables
set -a
source .env
set +a
podman machine start
make podman-up

Test using Claude Desktop

First, install node.js and npm, build the project following the above instructions. Edit your claude_desktop_config.json

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "npx",
      "args": [
        "@ahmedmustahid/postgres-mcp-server",
        "stdio"
      ],
      "env": {
        "POSTGRES_USERNAME": "your-username",
        "POSTGRES_PASSWORD": "your-password",
        "POSTGRES_HOST": "hostname",
        "POSTGRES_DATABASE": "database-name"
      }
    }
  }
}

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

npx @modelcontextprotocol/inspector npx @ahmedmustahid/postgres-mcp-server stdio

Check Streamable HTTP MCP Server

First, run the server (shell where environment has been configured):

npx @ahmedmustahid/postgres-mcp-server

Run the mcp inspector from another terminal

npx @modelcontextprotocol/inspector

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`)

Returns column information for a specific table.

Tools

query

Execute read-only SQL queries against the database.

Parameters:

sql (string): The SQL query to execute

Transport Differences

Feature	HTTP Transport	Stdio Transport
Session Management	✅ Stateful sessions	❌ Stateless
Concurrent Connections	✅ Multiple clients	❌ Single process
Web Integration	✅ REST API compatible	❌ CLI only
Interactive Use	✅ Via HTTP clients	✅ Direct stdio
Docker Deployment	✅ Web service	✅ CLI container

Health Checks

The HTTP server includes a basic health check endpoint accessible at the /health endpoint with a GET request (returns 405 Method Not Allowed, confirming the server is responsive).

Troubleshooting

Common Issues

Database Connection Errors

# Check your database credentials in .env
# Ensure PostgreSQL is running and accessible

Port Already in Use

# Change PORT in .env or stop conflicting services
lsof -i :3000

Docker Build Issues

# Clean Docker cache
npm run docker:clean
docker system prune -a

Session Management (HTTP)

# Sessions are stored in memory and will reset on server restart
# For production, consider implementing persistent session storage

Development

Adding New Resources

Create a new file in src/resources/
Implement the resource registration function
Add it to src/server/server.ts

Adding New Tools

Create a new file in src/tools/
Implement the tool registration function
Add it to src/server/server.ts

License

MIT

Contributing

Please read the contributing guidelines and submit pull requests to the main repository.