MCP Postgresql Demo

🚀 MCP PostgreSQL Demo

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

🚀 Quick Start

This MCP PostgreSQL demo provides a way for LLMs to interact with a PostgreSQL database. Follow the steps in the "Installation" and "Usage" sections to get started.

✨ 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 for easy 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.

💻 Usage Examples

Basic Usage

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

   # from debugpy import _input, _output
   

2. Now you can run the project.

🔧 Technical Details

Configuration

The application is configured using the following environment variables:

Development

• The code follows 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
   
   # Maintainer 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