MCP Dummy DB Integration and Data Retrieval POC

Executive Summary

This project demonstrates a secure, production-ready implementation of the Model Context Protocol (MCP) as a connector layer between AI agents and PostgreSQL databases. The solution enables natural language queries without exposing database credentials to the LLM.

Key Achievement: LLM cannot access database directly - only through predefined MCP tools.

---

Architecture Overview

┌────────────────────────────────────────────────────────┐
│                    USER QUERY                         │
│          "Fetch employees in AI department"           │
└───────────────────┬────────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────────┐
│         PLANNER AGENT (LLM)                           │
│  ✓ Natural Language Understanding                     │
│  ✗ NO database credentials                            │
│  Output: {"tool": "get_employees_by_department",      │
│           "parameters": {"department": "AI"}}         │
└───────────────────┬────────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────────┐
│         EXECUTOR AGENT                                 │
│  ✓ Validates tool request                            │
│  ✓ Maps to allowed operations only                   │
│  ✗ Cannot execute arbitrary SQL                      │
└───────────────────┬────────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────────┐
│         MCP TOOLS LAYER (Sandbox)                     │
│  ✓ get_employees_by_department("AI")                │
│  ✓ get_projects_by_status("Completed")              │
│  ✓ get_issues_by_priority("High")                   │
│  ✗ Cannot run arbitrary SQL                          │
└───────────────────┬────────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────────┐
│         DATABASE CONNECTION (Secure)                  │
│  ✓ Credentials in environment variables             │
│  ✓ Only parameterized queries (SQL injection safe)  │
└───────────────────┬────────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────────┐
│         RESULT TO USER                               │
│    [Secure data retrieval via MCP]                   │
└────────────────────────────────────────────────────────┘

Security Features

Feature	With MCP
DB Credentials	Secure in .env ✅
SQL Access	Predefined tools only ✅
Attack Surface	Limited operations only ✅
Audit Trail	Full logging ✅
Connection Pool	Yes ✅

Project Structure

MCP Task/
├── demo_agent_workflow.py  # Main Entry point (Agentic Workflow)
├── main.py                 # Interactive CLI entry point
├── .env                    # Database credentials (not in git)
├── README.md               # This file
├── database/
│   └── db_executor.py     # Database connection & queries
├── mcp/
│   └── tools.py           # MCP tool definitions
├── agents/
│   ├── orchestrator.py    # Manages the Agentic Workflow
│   ├── planner_agent.py   # Query planning agent (w/ Robust Fallback)
│   ├── executor_agent.py  # Query execution agent (Safe validation)
│   ├── reasoner_agent.py  # Result explanation agent
│   └── llm_provider.py    # LLM Interface
├── utils/
│   └── serializer.py      # Custom JSON serialization
└── datas_insert/
    └── sample_data.sql    # Sample database setup (PostgreSQL)

Setup Instructions

Prerequisites

• Python 3.8+
• PostgreSQL 12+
• Ollama (running locally) or other LLM provider
• pip packages: psycopg2, python-dotenv

Installation

1. Clone the repository
2. Create a virtual environment:

   python -m venv venv
   venv\Scripts\activate
   

3. Install dependencies:

   pip install -r requirements.txt
   

4. Configure .env:

   DB_HOST=localhost
   DB_PORT=5432
   DB_USER=postgres
   DB_PASSWORD=your_password
   DB_NAME=mcp_db
   

5. Initialize database:

   psql -U postgres -d mcp_db -f datas_insert/sample_data.sql
   

Running the Demo

python demo_agent_workflow.py

Running the Interactive CLI

python main.py

Agentic Workflow Design

This project uses a multi-agent secure architecture:

1. Orchestrator: The central brain that manages the lifecycle of a request.
2. Planner Agent:
   • Role: Analyzes the user query and selects the appropriate MCP tool.
   • Robustness: Uses a Dual-Layer Strategy.
     • Layer 1: Tries to parse the LLM's JSON output.
     • Layer 2: If LLM output is malformed (common with small models), it falls back to a deterministic keyword extraction strategy to ensure the query is always answered correctly.
3. Executor Agent:
   • Role: Validates the plan and executes the cached tool.
   • Safety: Ensures only allowed tools are called and handles parameter types safely.
4. MCP Tool Layer: A sandboxed layer that prevents direct SQL access.
5. Reasoner Agent: (Optional) Summarizes the raw data into a human-readable answer.

How MCP Works as a Connector Layer

1. User Query → "Fetch employee details where department is AI"
2. Planner Agent → LLM interprets query, creates plan without DB access
3. MCP Tools → Translates plan to allowed operations (get_employees_by_department)
4. Secure Execution → Only predefined MCP tools can access the database
5. Result → Data returned to user

Security Benefit: The LLM never sees or uses database credentials directly.

MCP Tools Available

• get_employees_by_department(department) - Fetch employees by department
• get_projects_by_status(status) - Fetch projects by status
• get_issues_by_priority(priority) - Fetch issues by priority

Example Queries

• "Fetch employee details where department is AI"
• "Show all projects with status completed"
• "List all high priority issues"

Created by: Muniasamy K