MCP PostgreSQL Chatbot

A simple chatbot that uses the Model Context Protocol (MCP) to interact with a PostgreSQL database.

Project Structure

openai-agent-mcp/
├── config/
│   └── mcp_config.json   # MCP Server configuration
├── src/
│   ├── __init__.py
│   ├── chatbot.py        # Main chatbot implementation
│   └── postgres_client.py # PostgreSQL client utilities
├── .env                  # Environment variables (create from .env.template)
├── .env.template         # Template for environment variables
├── requirements.txt      # Dependencies
└── main.py               # Entry point

Prerequisites

1. Node.js and npm installed (for the MCP server)
2. Python 3.8+ installed
3. An OpenAI API key
4. Access to the PostgreSQL database specified in the configuration

Setup Instructions

1. Install Dependencies

   pip install -r requirements.txt
   

2. Configure Environment Variables

   Copy .env.template to .env and add your OpenAI API key:

   cp .env.template .env
   # Edit .env to add your API key
   

3. Verify Database Connection

   Ensure that the PostgreSQL connection string in config/mcp_config.json is correct and that you have access to the database.

4. Run the Chatbot

   python main.py
   

Usage

Once the chatbot is running, you can interact with it by typing messages. The chatbot will:

1. Parse your natural language query
2. Generate an appropriate SQL query if needed
3. Execute the query against the PostgreSQL database
4. Format and explain the results

Example queries you can try:

• "What tables are available in the database?"
• "Show me the schema of the [table_name] table"
• "Query all records from [table_name] where [condition]"
• "Count the number of records in [table_name]"
• "Find the average [column] in [table_name]"

Type 'exit' or 'quit' to end the session.

How It Works

The chatbot uses the Model Context Protocol (MCP) to communicate with a PostgreSQL database through a standardized interface. The key components are:

1. MCP Configuration: Defines how to connect to the PostgreSQL MCP server
2. PostgreSQL Client: Handles communication with the MCP server
3. Chatbot: Manages the conversation and uses OpenAI's API to generate responses
4. Main Application: Provides a command-line interface for user interaction

The MCP server exposes database operations as tools that the chatbot can use to fulfill user requests. This architecture allows for standardized communication between AI models and various data sources.