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

Related MCP server: PostgreSQL MCP Server

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.