bjoydeep/search-mcp-server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides access to PostgreSQL databases. This server supports both the latest MCP Streamable HTTP transport (protocol version 2025-03-26) and legacy SSE transport for backward compatibility.

Features

• Database Query Execution: Execute SQL queries with parameterized inputs
• Database Statistics: Get comprehensive database statistics
• Table Management: List, search, and explore database tables
• Schema Information: Retrieve detailed table schemas and column information
• Multiple Transport Options:
  • Latest: MCP Streamable HTTP transport (/mcp) - Protocol version 2025-03-26
  • Legacy: HTTP server with SSE (/sse) - Protocol version 2024-11-05
• Docker Support: Easy containerization and deployment
• Real-time Streaming: SSE support for live query results
• Session Management: Proper MCP session handling with resumability

MCP SDK Integration

This server uses the latest MCP TypeScript SDK (@modelcontextprotocol/sdk@1.16.0) with:

• ✅ Streamable HTTP Transport: Latest protocol (2025-03-26)
• ✅ Session Management: Proper session handling with InMemoryEventStore
• ✅ Backward Compatibility: Legacy SSE endpoints still available
• ✅ Official SDK: Uses official Anthropic MCP SDK patterns

Installation

Prerequisites

• Node.js 18+
• PostgreSQL database
• Docker (optional, for containerized deployment)

Local Installation

1. Clone the repository:

git clone <repository-url>
cd search-mcp-server

2. Install dependencies:

npm install

3. Build the project:

npm run build

Configuration

Database Connection

The server uses a PostgreSQL connection string passed as a command line argument:

# Basic usage
npm run dev postgresql://username:password@host:port/database

# Examples
npm run dev postgresql://postgres:pgadmin1234@localhost:5432/mydb
npm run dev postgresql://user:pass@host.docker.internal:5432/production
npm run dev postgresql://admin:secret@db.example.com:5432/analytics

Usage

MCP Stdio Server (for Claude Desktop)

Start the traditional MCP server:

# Development
npm run dev postgresql://postgres:pgadmin1234@localhost:5432/search

# Production
npm start postgresql://postgres:pgadmin1234@localhost:5432/search

HTTP Server with SSE

Start the HTTP server for web applications:

# Development
npm run dev:http postgresql://postgres:pgadmin1234@localhost:5432/search 3000

# Production
npm run start:http postgresql://postgres:pgadmin1234@localhost:5432/search 3000

# Using environment variables
DATABASE_URL=postgresql://postgres:pgadmin1234@localhost:5432/search PORT=3000 npm run start:http

HTTP API Endpoints

• GET /health - Health check
• POST /query - Execute SQL query (SSE streaming)
• POST /tool/:name - Call MCP tool (SSE streaming)
• GET /tools - List available tools
• GET /stats - Database statistics
• GET /tables - List tables

SSE Query Example

// Connect to SSE endpoint
const eventSource = new EventSource('/query');

// Send query via POST
fetch('/query', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    sql: 'SELECT * FROM resources LIMIT 5',
    maxRows: 10
  })
});

// Listen for SSE events
eventSource.onmessage = (event) => {
  const data = JSON.parse(event.data);
  switch (data.type) {
    case 'connection':
      console.log('Connected:', data.message);
      break;
    case 'results':
      console.log('Query results:', data.data);
      break;
    case 'complete':
      console.log('Query completed:', data.message);
      eventSource.close();
      break;
    case 'error':
      console.error('Query error:', data.error);
      eventSource.close();
      break;
  }
};

Docker Deployment

Quick Start with Docker Compose

1. Start the complete stack:

docker-compose up -d

This will start:

• PostgreSQL database with sample data
• MCP HTTP server on port 3000

2. Access the API:

curl http://localhost:3000/health
curl http://localhost:3000/stats

Manual Docker Build

1. Build the image:

docker build -t postgres-mcp-server .

2. Run the container with environment variables:

# Using environment variables (recommended)
docker run -p 3000:3000 \
  -e DATABASE_URL="postgresql://user:pass@host:5432/db" \
  -e PORT=3000 \
  postgres-mcp-server

# Or with command line arguments (legacy)
docker run -p 3000:3000 postgres-mcp-server \
  node dist/http-server.js postgresql://user:pass@host:5432/db 3000

Environment Variables

You can customize the Docker deployment with environment variables:

# Database connection (required)
DATABASE_URL=postgresql://postgres:pgadmin1234@localhost:5432/search

# Server configuration (optional)
PORT=3000
NODE_ENV=production

Development

Project Structure

src/
├── database/
│   ├── connection.ts    # Database connection management
│   └── queries.ts       # Database query operations
├── server.ts           # MCP server implementation
├── http-server.ts      # HTTP server with SSE
├── types/
│   └── index.ts        # TypeScript type definitions
└── index.ts           # Main entry point

Available Scripts

• npm run build - Build the TypeScript project
• npm run dev - Start development server (stdio)
• npm run dev:http - Start development HTTP server
• npm start - Start production server (stdio)
• npm run start:http - Start production HTTP server
• npm test - Run tests

Adding New Tools

1. Define the tool in src/server.ts
2. Add the tool to the getAvailableTools() method
3. Implement the tool logic in src/database/queries.ts

Security Considerations

• Database Credentials: Never commit database credentials to version control
• Connection String: Use environment variables or secure configuration management
• Network Access: Restrict database access to trusted networks
• Input Validation: All SQL queries are executed as-is; implement proper validation
• CORS: Configure CORS appropriately for production deployments

Troubleshooting

Common Issues

1. Connection Failed: Check database URL and network connectivity
2. Permission Denied: Verify database user permissions
3. Port Already in Use: Change the port or stop conflicting services
4. Docker Issues: Ensure Docker and Docker Compose are properly installed

Logs

Check logs for detailed error information:

# Docker logs
docker-compose logs postgres-mcp-server

# Local logs
npm run dev:http postgresql://user:pass@localhost:5432/db 3000

License

MIT License - see LICENSE file for details.