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

Clone the repository

Create a virtual environment:

python -m venv venv
venv\Scripts\activate

Install dependencies:
```
pip install -r requirements.txt
```

Configure .env:

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

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:

Orchestrator: The central brain that manages the lifecycle of a request.
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.
Executor Agent:
- Role: Validates the plan and executes the cached tool.
- Safety: Ensures only allowed tools are called and handles parameter types safely.
MCP Tool Layer: A sandboxed layer that prevents direct SQL access.
Reasoner Agent: (Optional) Summarizes the raw data into a human-readable answer.

How MCP Works as a Connector Layer

User Query → "Fetch employee details where department is AI"
Planner Agent → LLM interprets query, creates plan without DB access
MCP Tools → Translates plan to allowed operations (get_employees_by_department)
Secure Execution → Only predefined MCP tools can access the database
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