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
.envfile 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 stringparams: 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 statementparams: 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
| 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.