mcp-server-bridge by nandeshkanagaraju - MCP Server

Universal MCP: An Intelligent Database Gateway

Universal MCP is an intelligent database gateway designed to provide secure and intelligent access to various databases. It supports both direct SQL execution via the MCP protocol and natural language queries (Text-to-SQL) through a REST API, leveraging OpenAI's GPT models for advanced natural language processing capabilities.

🏗️ Architecture

The server architecture integrates an intelligence layer directly, offering a hybrid approach to data access. This design allows for flexible interaction with physical databases while incorporating advanced AI functionalities.

┌─────────────────┐    MCP Protocol     ┌──────────────────────────────────┐
│                 │    (HTTP+SSE)       │                                  │
│   MCP Clients   │◄──────────────────►│     Universal MCP Server         │
│ (VSCode, etc.)  │                     │                                  │
└─────────────────┘                     │ ┌──────────────────────────────┐ │
                                        │ │       Intelligence           │ │
┌─────────────────┐    REST API         │ │                                │ │
│                 │                     │ │ • NLP-to-SQL (via REST API)  │ │
│  REST Clients   │◄───────────────────►│ │ • LLM Integration (ChatGPT)  │ │
│ (Web Apps, etc.)│                     │ │ • Conversation Memory        │ │
└─────────────────┘                     │ │ • SQL Generation & Validation│ │
                                        │ └──────────────────────────────┘ │
                                        │ ┌──────────────────────────────┐ │
                                        │ │    Core Services & Tools     │ │
                                        │ │                              │ │
                                        │ │ • Schema Management          │ │
                                        │ │ • Secure Query Executor      │ │
                                        │ │ • Multi-DB Support           │ │
                                        │ └───────────────┬──────────────┘ │
                                        └─────────────────│────────────────┘
                                                          │
                                                          ▼
                                        ┌──────────────────────────────────┐
                                        │         Physical Databases       │
                                        │ (PostgreSQL, MySQL, SQLite, etc.)│
                                        └──────────────────────────────────┘

🎯 Purpose

Universal MCP functions as a hybrid database gateway with the following core purposes:

Secure REST API: Provides an interface for converting natural language questions into SQL queries.
Conversation Management: Manages conversation history to support contextual, multi-turn questions.
Secure SQL Execution: Offers secure and validated SQL execution via the traditional MCP protocol.
Schema Generation: Generates database schema YAML for tool configuration and LLM context.
Multi-Database Support: Supports various database types through a flexible adapter pattern.

✨ New Feature: Natural Language Queries (Text-to-SQL)

The server now incorporates a robust REST API and an interactive terminal client that utilizes ChatGPT to translate natural language into SQL queries. Key aspects of this feature include:

Conversational Memory: The system maintains conversation context using a persistent file-based session store, enabling users to ask follow-up questions.
Secure by Design: All LLM-generated queries undergo a security validation process, ensuring that only safe SELECT statements are executed.
Interactive Terminal Client: A user-friendly terminal interface (scripts/chat.py) facilitates full conversational interaction with the database.

🚀 Quick Start (Local Setup)

This guide outlines the steps to run the application directly on a local machine (macOS/Linux).

1. Prerequisites

Python 3.11+
A running local MySQL server.

2. Setup Environment

git clone <repository-url>
cd universal-mcp

# Create and activate virtual environment
python -m venv venv
source venv/bin/activate

# Install dependencies
pip install -r requirements.txt

3. Configure Environment

# Copy environment template
cp .env.example .env

# Edit .env and add your local database credentials and OpenAI API key
nano .env

Important: Ensure OPENAI_API_KEY is added and DB_HOST, DB_USER, and DB_PASSWORD match your local MySQL setup.

4. Initialize the Database

This script creates necessary tables and populates them with sample data in your local MySQL database.

python scripts/setup_database.py

5. Start the Server

This command starts the FastAPI server, which provides the REST API.

# Leave this terminal running
uvicorn api.main:app --reload --port 8000

6. Chat with Your Database

Open a new terminal window and start the interactive chat client.

# Make sure your virtual environment is activated
source venv/bin/activate

# Run the chat script
python scripts/chat.py

🔧 Available Tools & APIs

Natural Language API

Endpoint	Method	Description
`/api/v1/query/natural-language`	`POST`	Converts a natural language question to a SQL query and executes it.

Traditional MCP Tools

Tool	Description
`execute_query`	Execute validated SQL queries safely.
`get_schema`	Retrieve complete database schema.
`list_tables`	Get list of available tables.
`describe_table`	Get detailed table structure.
`get_table_data`	Retrieve paginated data from tables.
`validate_query`	Validate SQL syntax and security.

Using Pagination

The get_table_data tool supports pagination for efficient handling of large tables. Use the page and page_size parameters to request specific data chunks.

📊 Monitoring & Observability

How to Access Logs

Logs are crucial for debugging and can be accessed in two ways:

Real-time Console Output: All logs are streamed directly to the terminal where the uvicorn server is running.
Persistent Log File: The server also writes all logs to mcp_server.log in the project's root directory.

# Watch the log file for new entries in real-time
tail -f mcp_server.log

The log verbosity can be controlled via the LOG_LEVEL variable in your .env file (options: DEBUG, INFO, WARNING, ERROR).

📁 Project Structure (Highlights)

universal-mcp/
├── mcp_server.log              # Main log file
├── config/
│   └── settings.py             # Loads configs, including OPENAI_API_KEY
├── core/
│   ├── security/
│   │   └── query_validator.py  # Security check for LLM queries
│   └── mcp/
│       └── tools/
│           └── natural_language_query.py # Orchestrates NLQ
├── api/
│   ├── main.py                 # Main FastAPI application
│   └── query_routes.py         # Defines the NLQ endpoint
├── services/
│   └── llm_service.py          # Handles all interaction with OpenAI API
├── storage/
│   └── session/
│       └── context_store.py    # Manages file-based session memory
├── scripts/
│   ├── setup_database.py       # Initializes the local database
│   └── chat.py                 # The interactive terminal chat client
└── tests/
    ├── unit/
    │   └── test_query_validator.py
    └── integration/
        └── test_nlq_endpoint.py

🔒 Security Features

LLM Query Validation: All generated SQL queries are validated to be SELECT-only and free of chained commands.
SQL Injection Prevention: Parameterized query validation is implemented for direct SQL execution.
Secure Credential Management: API keys and database passwords are loaded securely from the .env file.

🧪 Development & Testing

# Run unit tests
pytest tests/unit/

# Run integration tests
pytest tests/integration/