PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides a standardized interface for interacting with PostgreSQL databases. This server enables LLMs and other tools to perform database operations through a consistent protocol.

Features

• Full CRUD Operations: Execute queries, insert, update, and delete data
• Schema Management: Create/drop tables, manage indexes
• Database Introspection: List tables, describe table structures, analyze indexes
• Performance Analysis: Query performance analysis and table statistics
• Configurable Schema Support: Use custom schemas or default to 'public'
• Connection Pooling: Efficient database connection management
• Type Safety: Full TypeScript support

Installation

npm install

Configuration

Create a .env file in the project root with your PostgreSQL connection details:

# PostgreSQL Database Configuration
DATABASE_URL=postgresql://username:password@localhost:5432/dbname

# Or use individual parameters
DB_HOST=localhost
DB_PORT=5432
DB_NAME=your_database
DB_USER=your_username
DB_PASSWORD=your_password
DB_SCHEMA=          # Optional: defaults to 'public' if not specified

# Connection Pool Settings (optional)
DB_POOL_MIN=2
DB_POOL_MAX=10
DB_POOL_IDLE_TIMEOUT=30000

# Logging Level (optional)
LOG_LEVEL=info

Schema Configuration

• If DB_SCHEMA is empty or not provided, the server uses 'public' schema
• If DB_SCHEMA is set (e.g., DB_SCHEMA=myschema), that schema is used as default
• Individual tool calls can override the default by providing a schema parameter

Building

npm run build

Running the Server

npm start

The server communicates via stdin/stdout using the MCP protocol.

Available Tools

Query Operations

• execute_query: Execute SELECT queries with optional parameters
• analyze_query_performance: Analyze query execution plans

Data Manipulation

• insert_data: Insert data into tables
• update_data: Update existing data
• delete_data: Delete data from tables

Schema Operations

• create_table: Create new tables with column definitions
• drop_table: Drop existing tables
• create_index: Create indexes on table columns

Introspection

• list_tables: List all tables in a schema
• describe_table: Get detailed table structure information
• list_indexes: List all indexes for a table
• get_table_stats: Get table statistics (row count, size, etc.)

Development

# Run in development mode with auto-reload
npm run dev

# Clean build artifacts
npm run clean

# Run tests (if available)
npm test

MCP Protocol

This server implements the Model Context Protocol, allowing it to be used with any MCP-compatible client. The protocol uses JSON-RPC 2.0 for communication.

Example Request

{
  "jsonrpc": "2.0",
  "method": "tools/call",
  "params": {
    "name": "list_tables",
    "arguments": {
      "schema": "public"
    }
  },
  "id": 1
}

Example Response

{
  "jsonrpc": "2.0",
  "result": {
    "content": [
      {
        "type": "text",
        "text": "{\"success\": true, \"tables\": [...]}"
      }
    ]
  },
  "id": 1
}

Security Considerations

• Never commit .env files with real credentials
• Use environment-specific configurations for different deployments
• Consider using connection pooling for production environments
• Implement proper access controls at the database level

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.