efeabio/mcp-postgresql-server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides AI agents with secure and efficient access to PostgreSQL databases.

Features

• Dynamic Connection Management: Connect to PostgreSQL databases without storing credentials
• Comprehensive Query Execution: Execute SELECT, INSERT, UPDATE, DELETE queries with proper error handling
• Schema Analysis: Analyze table structures, relationships, and database schema
• Advanced Analytics: Performance statistics, integrity checks, and optimization suggestions
• Security First: Input validation, query sanitization, and secure credential handling

Installation

npm install

Development

# Build the project
npm run build

# Run in development mode
npm run dev

# Watch mode for development
npm run watch

# Lint code
npm run lint

# Run tests
npm run test

Usage

This MCP server is designed to be used with MCP-compatible AI systems like Kiro IDE.

Quick Start

1. Install dependencies:

   npm install
   

2. Build the project:

   npm run build
   

3. Test the server:

   node test-server.js
   

4. Add to your MCP configuration:

   Windows (using start-mcp.cmd):

   {
     "mcpServers": {
       "postgresql-mcp": {
         "command": "start-mcp.cmd",
         "cwd": "/path/to/postgresql-mcp-server"
       }
     }
   }
   

   Windows PowerShell (using start-mcp.ps1):

   {
     "mcpServers": {
       "postgresql-mcp": {
         "command": "powershell.exe",
         "args": ["-ExecutionPolicy", "Bypass", "-File", "start-mcp.ps1"],
         "cwd": "/path/to/postgresql-mcp-server"
       }
     }
   }
   

   Linux/macOS (using start-mcp.sh):

   {
     "mcpServers": {
       "postgresql-mcp": {
         "command": "./start-mcp.sh",
         "cwd": "/path/to/postgresql-mcp-server"
       }
     }
   }
   

Available Tools

The server provides the following tools:

Core Tools

• connect_database: Establish dynamic connection to PostgreSQL database
• execute_query: Execute SQL queries with timeout and row limits

Schema Analysis Tools

• describe_table: Get detailed table information (columns, constraints, indexes)
• list_tables: List all database objects (tables, views, functions)
• analyze_schema_relationships: Analyze foreign key relationships and dependencies

Advanced Analysis Tools

• performance_stats: Database performance analysis (slow queries, index usage, table stats)
• integrity_check: Data integrity validation (constraint violations, orphaned records)
• data_distribution: Data quality analysis (null values, distinct counts, distributions)
• schema_suggestions: Optimization recommendations (indexes, normalization, performance)

Example Usage

{
  "tool": "connect_database",
  "arguments": {
    "host": "localhost",
    "database": "mydb", 
    "username": "user",
    "password": "pass"
  }
}

The server provides the following tools:

Phase 1 (Setup) - ✅ Complete

• Project structure and configuration
• TypeScript setup with strict type checking
• Logging and error handling utilities
• MCP server foundation

Phase 2 (Core Implementation) - ✅ Complete

• ✅ MCP server with SDK v1.17.5 integration
• ✅ Database connection service with pooling
• ✅ Core tool infrastructure
• ✅ connect_database: Establish database connections
• ✅ execute_query: Execute SQL queries

Phase 3 (Extended Database Tools) - ✅ Complete

• ✅ describe_table: Analyze table structure with columns, constraints, and indexes
• ✅ list_tables: List database objects (tables, views, functions)
• ✅ analyze_schema_relationships: Schema relationship and dependency analysis

Phase 4 (Advanced Analysis) - ⏳ Planned

• performance_stats: Database performance metrics
• integrity_check: Data integrity validation
• data_distribution: Data distribution analysis
• schema_suggestions: Optimization recommendations

Phase 5 (Security & Production) - ⏳ Planned

• SQL injection prevention
• Query validation and sanitization
• Rate limiting and timeouts
• Comprehensive testing suite

Architecture

The server follows a modular architecture:

• src/index.ts: Entry point and server lifecycle
• src/server.ts: MCP server configuration
• src/tools/: MCP tool implementations
• src/services/: Business logic and database operations
• src/types/: TypeScript type definitions
• src/utils/: Shared utilities and helpers

Configuration

The server uses environment variables for configuration:

• LOG_LEVEL: Logging level (debug, info, warn, error)
• Additional configuration will be added in subsequent phases

Security

This server implements several security measures:

• No credential storage - dynamic connection requests
• SQL query validation and sanitization
• Input parameter validation
• Secure error handling without information leakage
• Audit logging for all operations

Contributing

1. Follow the existing code style and TypeScript conventions
2. Add tests for new functionality
3. Update documentation for new features
4. Ensure all phases are implemented according to the specification

License

MIT License - see LICENSE file for details.

Development Status

• ✅ Phase 1: Project setup and structure (Complete)
• ✅ Phase 2: Core MCP server implementation (Complete)
  • ✅ MCP Server with SDK v1.17.5
  • ✅ Database connection service
  • ✅ Basic tools: connect_database, execute_query
  • ✅ Error handling and logging
• ✅ Phase 3: Extended database tools (Complete)
  • ✅ describe_table: Table structure analysis
  • ✅ list_tables: Database object listing
  • ✅ analyze_schema_relationships: Relationship analysis
• ✅ Phase 4: Advanced analysis tools (Complete)
  • ✅ performance_stats: Database performance analysis
  • ✅ integrity_check: Data integrity validation
  • ✅ data_distribution: Data quality analysis
  • ✅ schema_suggestions: Optimization recommendations
• ✅ Phase 5: Security and production readiness (Complete)
  • ✅ SQL query validation and security checks
  • ✅ Production configuration management
  • ✅ Comprehensive deployment documentation
  • ✅ Security utilities and audit logging