MCP Server for PostgreSQL

A Model Context Protocol (MCP) server implementation for PostgreSQL, providing a simple interface to interact with PostgreSQL databases through MCP.

Features

• Execute SQL queries with parameterized inputs
• Run INSERT/UPDATE/DELETE operations
• Create new databases
• Create or update table schemas
• Debug PostgreSQL connections
• Containerized with Docker for easy deployment
• Environment-based configuration

Prerequisites

• Python 3.8+
• PostgreSQL 10+
• Docker (optional, for containerized deployment)
• Docker Compose (optional, for development)

Installation

Using Docker (Recommended)

1. Clone the repository:

   git clone https://github.com/asadudin/mcp-server-postgres.git
   cd mcp-server-postgres
   

2. Copy the example environment file:

   cp .env.example .env
   

3. Update the .env file with your PostgreSQL credentials:

   PG_HOST=postgres
   PG_PORT=5432
   PG_USER=postgres
   PG_PASSWORD=your_password
   PG_DATABASE=your_database
   HOST=0.0.0.0
   PORT=8056
   

4. Start the service using Docker Compose:

   docker-compose up -d
   

Manual Installation

1. Clone the repository:

   git clone https://github.com/asadudin/mcp-server-postgres.git
   cd mcp-server-postgres
   

2. Create and activate a virtual environment:

   python -m venv venv
   source venv/bin/activate  # On Windows: .\venv\Scripts\activate
   

3. Install dependencies:

   pip install -r requirements.txt
   

4. Copy the example environment file and update it:

   cp .env.example .env
   # Edit .env with your configuration
   

5. Run the server:

   python mcp_server_postgres.py
   

Usage

The MCP server provides the following endpoints:

sql_query

Run a SELECT query and return results as JSON.

Parameters:

• query: SQL query string
• params: Optional JSON string of query parameters (list or dict)

Example:

{
  "query": "SELECT * FROM users WHERE id = $1",
  "params": [1]
}

sql_execute

Execute an INSERT/UPDATE/DELETE statement.

Parameters:

• query: SQL statement
• params: Optional JSON string of query parameters (list or dict)

Example:

{
  "query": "INSERT INTO users (name, email) VALUES ($1, $2)",
  "params": ["John Doe", "john@example.com"]
}

create_database

Create a new PostgreSQL database.

Parameters:

• database_name: Name of the database to create

create_or_update_table

Create or update a table schema.

Parameters:

• sql: CREATE TABLE or ALTER TABLE SQL statement

debug_postgres_connection

Debug the PostgreSQL connection.

Environment Variables

Development

Running Tests

# Install test dependencies
pip install -r requirements-dev.txt

# Run tests
pytest

Building the Docker Image

docker build -t mcp-server-postgres .

API Documentation

For detailed API documentation, refer to the OpenAPI specification.

Contributing

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

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• FastMCP - The MCP server framework
• asyncpg - PostgreSQL client for Python
• FastAPI - Modern, fast web framework for building APIs