MCP Postgresql Demo

🚀 MCP PostgreSQL Demo

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

🚀 Quick Start

This FastMCP server offers a seamless way for LLMs to interact with a PostgreSQL database. By leveraging the Model Context Protocol (MCP), it allows language models to perform various operations on the database, such as exploring the schema, inspecting tables, and executing SQL queries.

✨ Features

• Schema Exploration: Retrieve metadata about the database schema.
• Table Inspection: Obtain detailed structural information about specific tables.
• Database Querying: Execute SQL queries on the database.
• YAML Format: Results are returned in YAML format, facilitating consumption by LLMs.

📚 Documentation

Resources

The server exposes the following MCP resources:

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

Tools

• query_database - Execute SQL queries on the database (SELECT queries only).

Prompts

The server includes the following predefined prompts:

• prompt_schema_description - Request a description of a database schema.
• prompt_table_description - Request a description of a specific table.
• prompt_query_database - Request data from a specific table.

📦 Installation

Prerequisites

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

Steps

1. Clone the repository:

git clone <repository-url>
cd mcp-demo

2. Create a virtual environment:

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

3. Install UV (if not already installed):

pip install uv

4. Install dependencies using UV:

uv sync

5. Configure environment variables:

• Copy .env.example to .env.
• Update the values according to your PostgreSQL configuration.

🔧 Configuration

The application is configured using the following environment variables:

💻 Usage Examples

Basic Usage

1. First, uncomment the following at the bottom of the src/main.py file to enable debug mode:

# from debugpy import _input, _output

2. Now you can run the project.

🛠️ Development

• The code adheres to standard Python naming conventions and coding styles.
• Add new features to main.py and modify the existing logic as needed.
• Write documentation in Markdown format.

🐳 Docker Run

1. Compress the project folder into a .tar.gz file:

tar -czvf mcp-demo.tar.gz .

2. Build the image using the following Dockerfile:

# Base image
FROM python:3.8-slim

# Author information
LABEL maintainer="Ricardo Santos <ricardo.santos.diaz@gmail.com>"

# Install dependencies
EXPOSE 5000
RUN mkdir -p /app
WORKDIR /app
COPY . .
RUN pip install --no-cache-dir -r requirements.txt

# Run command
CMD ["python", "main.py"]

3. Build and run the image:

docker build -t mcp-demo:latest .
docker run -p 5000:5000 --name mcp_demo mcp-demo:latest

📄 License

[Add your license information here]

👥 Contributors

• Ricardo Santos ricardo.santos.diaz@gmail.com