CoryFrench/openai_mcp_server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides PostgreSQL database tools. This server can run in two modes:

• MCP Mode: Standard JSON-RPC 2.0 protocol for OpenAI integration
• HTTP Mode: REST API for microservices architecture

Features

• PostgreSQL Tools: Query and explore PostgreSQL databases with automatic server detection
• Dual Transport: Supports both stdio (MCP) and HTTP transports
• Automatic Server Detection: Switches between local and remote PostgreSQL servers
• Docker Ready: Containerized for microservices deployment

Installation

1. Install dependencies:

npm install

2. Create environment configuration:

cp .env.example .env

3. Edit .env with your PostgreSQL credentials:

# PostgreSQL Configuration
PG_LOCAL_HOST=10.0.2.221
PG_REMOTE_HOST=50.172.126.210
PG_PORT=5432
PG_USER=cursor
PG_PASSWORD=your_password
PG_DATABASE=postgres
PG_SSL=false

# Server Configuration
SERVER_NAME=PostgreSQL MCP Server
SERVER_VERSION=1.0.0

Usage

For OpenAI Integration (MCP Protocol)

Run the server in MCP mode using stdio transport:

npm run mcp
# or
npm start

Then configure OpenAI to use this server:

import { MCPServerStdio } from 'openai/mcp';

const server = new MCPServerStdio({
  params: {
    command: "npm",
    args: ["run", "mcp"],
    cwd: "/path/to/openai_mcp_server"
  }
});

For Microservices (HTTP API)

Run the server in HTTP mode:

npm run http

The server will be available at http://localhost:14500 with these endpoints:

• GET /health - Server health check
• GET /tools - List available tools
• POST /execute - Execute a tool
• GET / - Server information

Example tool execution:

curl -X POST http://localhost:14500/execute \
  -H "Content-Type: application/json" \
  -d '{
    "toolName": "postgres_ultra_simple", 
    "arguments": {"query": "SELECT version();"}
  }'

Docker Deployment

The included Docker setup runs in HTTP mode for microservices integration:

docker-compose up openai-mcp-server

Access via reverse proxy: http://localhost/api/openai-mcp/

Available Tools

PostgreSQL Tools

• test_tool: Simple test tool for debugging
• postgres_ultra_simple: Execute basic SQL queries
• postgres_single_table: Get information about a specific table
• postgres_list_tables: List all database tables
• postgres_describe_table: Get detailed table schema
• postgres_query_builder: Advanced query building and execution
• postgres_connection_test: Test database connectivity
• postgres_performance_stats: Get database performance metrics

Smart Features

Automatic PostgreSQL Server Detection

The system automatically detects which PostgreSQL server is accessible:

1. Attempts connection to local server (PG_LOCAL_HOST)
2. Falls back to remote server (PG_REMOTE_HOST) if local fails
3. Provides clear error messages if both fail

Flexible Configuration

• Password is optional (warns but doesn't fail if missing)
• Supports both local development and production environments
• Configurable SSL settings for secure connections

Development

Available Scripts

• npm start - Run MCP server (stdio transport)
• npm run mcp - Run MCP server (stdio transport)
• npm run http - Run HTTP API server
• npm run dev - Development mode with auto-restart (MCP)
• npm run dev:mcp - Development mode (MCP)
• npm run dev:http - Development mode (HTTP)

Project Structure

openai_mcp_server/
├── server.mjs          # MCP server (stdio transport)
├── http-server.mjs     # HTTP API server
├── run-server.js       # Executable wrapper
├── tools/              # Tool implementations
│   ├── index.mjs       # Tool registry
│   ├── postgres/       # PostgreSQL tools
│   └── test-tool.mjs   # Test tool
├── utils/              # Utilities
│   ├── config.mjs      # Configuration management
│   └── logging.mjs     # Logging utilities
└── .env                # Environment variables

Security

• Read-only database operations (SELECT queries only)
• Environment variable validation
• Secure container configuration with non-root user
• CORS headers for web integration

Troubleshooting

Connection Issues

1. Verify PostgreSQL credentials in .env
2. Check network connectivity to database servers
3. Ensure PostgreSQL server allows connections from your IP
4. Review server logs for detailed error messages

Docker Issues

1. Ensure containers are running: docker-compose ps
2. Check logs: docker-compose logs openai-mcp-server
3. Restart if needed: docker-compose restart openai-mcp-server

License

ISC