MCP PostgreSQL Demo

A FastMCP server that enables LLMs to connect and interact with PostgreSQL databases. This project demonstrates how to use the Model Context Protocol (MCP) to allow Language Models to query and explore database schemas and tables.

Features

Schema Exploration: Retrieve metadata about database schemas
Table Inspection: Get detailed information about table structures
Database Querying: Execute SQL queries against the database
YAML Formatting: Results are returned in YAML format for easy consumption by LLMs

Resources

The server exposes the following MCP resources:

database://{schema} - Get information about all tables in a schema
database://{schema}/tables/{table} - Get detailed information about a specific table

Tools

query_database - Execute SQL queries against the database (SELECT queries only)

Prompts

The server includes the following predefined prompts:

prompt_schema_description - Ask for a description of a database schema
prompt_table_description - Ask for a description of a specific table
prompt_query_database - Ask for data from a specific table

Prerequisites

Python 3.12 or higher
PostgreSQL database
UV package manager (recommended)

Installation

Clone the repository:
```
git clone <repository-url>
cd mcp-demo
```

Create a virtual environment:

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

Install UV (if not already installed):
```
pip install uv
```
Install dependencies with UV:
```
uv sync
```
Configure environment variables:
- Copy .env.example to .env
- Update the values according to your PostgreSQL configuration

Configuration

The application is configured using environment variables:

| Variable | Description | Default | | ----------- | ------------------------ | --------- | | APP_NAME | Application name | mcp-demo | | DB_HOST | PostgreSQL host | localhost | | DB_PORT | PostgreSQL port | 5432 | | DB_USER | PostgreSQL username | postgres | | DB_PASSWORD | PostgreSQL password | postgres | | DB_NAME | PostgreSQL database name | postgres |

Usage

First, uncomment the run function in src/main.py by removing the comment from these lines at the bottom of the file:
```
# if __name__ == "__main__":
#     print("Starting FastMCP server...")
#     mcp.run()
```
Start the FastMCP server:
```
python -m src.main
```
The server will be available for LLMs to connect to and query your PostgreSQL database. With the server running, the MCP can be loaded into client applications for interaction.

Client Configuration

To use this MCP in a client application, add the following configuration to your client's MCP configuration file (e.g., .cursor/mcp.json):

{
  "mcpServers": {
    "postgres-mcp-server": {
      "command": "/path/to/your/venv/bin/mcp",
      "args": ["run", "/path/to/your/postgres-mcp/src/main.py"],
      "env": {
        "APP_NAME": "mcp-demo",
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_USER": "postgres",
        "DB_PASSWORD": "postgres",
        "DB_NAME": "postgres"
      }
    }
  }
}

Be sure to replace the paths with the actual paths to your virtual environment and project directory, and update the environment variables to match your PostgreSQL configuration.

Development

Install development dependencies with UV:

uv pip install -e ".[dev]"

Development tools included:

JupyterLab for notebooks
Pyright for type checking
Ruff for linting

Docker

To run the application with Docker:

Build the Docker image:
```
docker build -t mcp-demo .
```

Run the container:

docker run --env-file .env.docker -p 8000:8000 mcp-demo

Example Usage

Get Schema Information

from mcp.client import get_client

client = get_client("http://localhost:8000")
schema_info = client.get_resource("database://public")
print(schema_info)

Get Table Details

table_info = client.get_resource("database://public/tables/users")
print(table_info)

Execute a Query

result = client.invoke_tool("query_database", {"query": "SELECT * FROM users LIMIT 10"})
print(result)

License

[Add your license information here]

Contributors

Ricardo Santos [email protected]