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

Related MCP server: PostgreSQL MCP Server

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