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)

Clone the repository:

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

Copy the example environment file:
```
cp .env.example .env
```

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

Start the service using Docker Compose:
```
docker-compose up -d
```

Manual Installation

Clone the repository:

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

Create and activate a virtual environment:

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

Install dependencies:
```
pip install -r requirements.txt
```

Copy the example environment file and update it:

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

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", "[email protected]"]
}

`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

Variable	Default	Description
PG_HOST	localhost	PostgreSQL host
PG_PORT	5432	PostgreSQL port
PG_USER	postgres	PostgreSQL username
PG_PASSWORD		PostgreSQL password
PG_DATABASE	postgres	Default database name
HOST	0.0.0.0	Host to bind the MCP server to
PORT	8056	Port to run the MCP server on

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

MCP Server for PostgreSQL