SQL MCP Tool
README.md
# SQL MCP Tool
## AI-Powered Database Analyst MCP Server
SQL MCP Tool is a modular **Model Context Protocol (MCP) server** that
allows AI assistants such as **Amazon Q in VS Code** to interact with
relational databases using natural-language questions.
Instead of writing SQL manually, a user can ask:
> "What is the total sales for each category?"
The MCP server:
1. Connects to the selected database.
2. Discovers the requested schema and its actual tables/columns.
3. Builds database context dynamically.
4. Sends the question and schema context to the configured AI provider.
5. Generates a read-only SQL query.
6. Validates the generated SQL.
7. Executes the query against the selected database.
8. Returns the results to the MCP client.
The design is **AI-provider independent** and **database independent**.
The application uses interfaces and factories so that adding or changing
an AI provider or database adapter does not require changes to the core
business workflow.
------------------------------------------------------------------------
## Architecture
``` text
┌──────────────────────────────┐
│ MCP CLIENTS │
│ │
│ Amazon Q / VS Code │
│ Other MCP-compatible clients │
└──────────────┬───────────────┘
│
MCP / STDIO / JSON-RPC
│
▼
┌───────────────────────────────────────────────────────────────────────┐
│ SQL MCP SERVER │
│ │
│ ┌──────────────────┐ │
│ │ MCP Server │ │
│ │ │ │
│ │ list_connections │ │
│ │ list_schemas │ │
│ │ list_tables │ │
│ │ describe_table │ │
│ │ inspect_schema │ │
│ │ ask_database │ │
│ └────────┬─────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ QueryService │ │
│ │ │ │
│ │ Schema Inspection → Context → SQL Generation → Validation │ │
│ │ → Read-only Execution → Result │ │
│ └───────────────┬───────────────────────────────┬─────────────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌───────────────────┐ ┌──────────────────────────┐ │
│ │ AI Layer │ │ Database Layer │ │
│ │ │ │ │ │
│ │ AIProvider │ │ DatabaseAdapter │ │
│ │ <<interface>> │ │ <<interface>> │ │
│ └─────────┬─────────┘ └────────────┬─────────────┘ │
│ │ │ │
│ ┌─────────┼─────────┐ ┌─────────┼──────────┐ │
│ ▼ ▼ ▼ ▼ ▼ ▼ │
│ Groq Claude Gemini MySQL PostgreSQL Oracle │
│ │
│ SQL Validator / Security │
│ SELECT-only protection │
└───────────────────────────────────────────────────────────────────────┘
```
------------------------------------------------------------------------
## Key Design Principles
### 1. AI Provider Abstraction
The application does not directly depend on Groq, Claude, or Gemini.
All providers implement:
``` python
class AIProvider(ABC):
@abstractmethod
def generate(self, prompt: str) -> str:
raise NotImplementedError
```
The factory selects the provider:
``` text
AIProviderFactory
│
├── GroqProvider
├── ClaudeProvider
└── GeminiProvider
```
The rest of the application only knows about `AIProvider`.
Therefore:
``` env
AI_PROVIDER=groq
```
can be changed to:
``` env
AI_PROVIDER=claude
```
or:
``` env
AI_PROVIDER=gemini
```
without changing `AIService`, `SQLGenerator`, `QueryService`, or the MCP
tools.
------------------------------------------------------------------------
### 2. Database Adapter Abstraction
The database layer follows the same design.
``` text
DatabaseAdapter
│
├── MySQLAdapter
├── PostgreSQLAdapter
└── OracleAdapter
```
The application works with the interface instead of database-specific
code.
This allows the same query workflow to work with different relational
databases.
------------------------------------------------------------------------
### 3. Dynamic Schema Discovery
The application does **not** assume a fixed table such as
`retail_sales`.
Instead:
``` text
Connection
↓
Schema
↓
Tables
↓
Columns
↓
Primary Keys
↓
Foreign Keys
↓
LLM Context
```
The AI receives the actual structure of the selected database.
This reduces hallucinated table and column names.
------------------------------------------------------------------------
## End-to-End Request Flow
Suppose the user asks:
``` text
What is the total sales for each category?
```
The request follows this flow:
``` text
User
│
▼
Amazon Q
│
▼
MCP Server
│
▼
ask_database()
│
▼
QueryService
│
├──► SchemaInspector
│ │
│ └──► DatabaseAdapter
│
├──► DatabaseContextBuilder
│
├──► SQLGenerator
│ │
│ ▼
│ AIService
│ │
│ ▼
│ AIProvider
│ │
│ ├── Groq
│ ├── Claude
│ └── Gemini
│
├──► SQLValidator
│
└──► DatabaseAdapter
│
▼
Database
│
▼
Results
│
▼
Amazon Q
```
------------------------------------------------------------------------
# Project Structure
``` text
sql_mcp_tool/
│
├── app/
│ │
│ ├── ai/
│ │ ├── providers/
│ │ │ ├── __init__.py
│ │ │ ├── base.py
│ │ │ ├── groq.py
│ │ │ ├── claude.py
│ │ │ └── gemini.py
│ │ │
│ │ ├── context/
│ │ │ └── database_context.py
│ │ │
│ │ ├── provider_factory.py
│ │ ├── ai_service.py
│ │ └── sql_generator.py
│ │
│ ├── database/
│ │ ├── adapters/
│ │ │ ├── mysql.py
│ │ │ ├── postgresql.py
│ │ │ └── oracle.py
│ │ │
│ │ ├── base.py
│ │ ├── database_config.py
│ │ ├── database_factory.py
│ │ ├── connection_manager.py
│ │ └── schema_inspector.py
│ │
│ ├── security/
│ │ └── sql_validator.py
│ │
│ ├── services/
│ │ └── query_service.py
│ │
│ └── mcp/
│ ├── __init__.py
│ └── server.py
│
├── tests/
│ ├── ai/
│ ├── database/
│ ├── security/
│ ├── services/
│ └── mcp_tests/
│
├── .env
├── .gitignore
├── requirements.txt
└── README.md
```
------------------------------------------------------------------------
# MCP Tools
The server exposes six main tools.
## `list_connections`
Returns all configured database connections.
Example:
``` text
["mysql_connection", "postgres_connection"]
```
------------------------------------------------------------------------
## `list_schemas`
Lists schemas available on a selected connection.
Example:
``` text
project1
information_schema
mysql
performance_schema
```
------------------------------------------------------------------------
## `list_tables`
Lists tables in a selected schema.
Example:
``` text
retail_sales
customers
orders
```
The names are discovered dynamically from the database.
------------------------------------------------------------------------
## `describe_table`
Returns information about a table:
- columns
- data types
- nullable information
- primary keys
- foreign keys
------------------------------------------------------------------------
## `inspect_schema`
Inspects the complete structure of a schema.
The resulting structure is used to construct context for the AI model.
------------------------------------------------------------------------
## `ask_database`
Main natural-language analytics tool.
Example:
``` text
Question:
What is the total sales for each category?
```
The tool generates and executes a read-only SQL query and returns:
``` json
{
"connection": "mysql_connection",
"schema": "project1",
"question": "What is the total sales for each category?",
"sql": "SELECT ...",
"rows": []
}
```
------------------------------------------------------------------------
# AI Providers
The project uses a provider abstraction.
## Groq
Configuration:
``` env
AI_PROVIDER=groq
GROQ_API_KEY=your_key
GROQ_MODEL=your_model
```
Implementation:
``` text
app/ai/providers/groq.py
```
------------------------------------------------------------------------
## Claude
Configuration:
``` env
AI_PROVIDER=claude
ANTHROPIC_API_KEY=your_key
CLAUDE_MODEL=your_model
```
Implementation:
``` text
app/ai/providers/claude.py
```
Install:
``` powershell
pip install anthropic
```
------------------------------------------------------------------------
## Gemini
Configuration:
``` env
AI_PROVIDER=gemini
GEMINI_API_KEY=your_key
GEMINI_MODEL=your_model
```
Implementation:
``` text
app/ai/providers/gemini.py
```
Install:
``` powershell
pip install google-genai
```
------------------------------------------------------------------------
# Database Support
The database layer is designed around:
``` python
class DatabaseAdapter(ABC):
...
```
Each database implements the same operations:
``` text
connect()
disconnect()
test_connection()
list_schemas()
list_tables()
describe_table()
execute_readonly_query()
get_primary_keys()
get_foreign_keys()
```
## MySQL
Driver:
``` text
SQLAlchemy + PyMySQL
```
Example:
``` text
Host: 127.0.0.1
Port: 3306
Database: project1
```
Implementation:
``` text
app/database/adapters/mysql.py
```
------------------------------------------------------------------------
## PostgreSQL
Driver:
``` text
SQLAlchemy + psycopg
```
Install:
``` powershell
pip install "psycopg[binary]"
```
Implementation:
``` text
app/database/adapters/postgresql.py
```
Typical configuration:
``` text
Host: localhost
Port: 5432
Database: mydatabase
Schema: public
```
------------------------------------------------------------------------
## Oracle
Driver:
``` text
SQLAlchemy + python-oracledb
```
Install:
``` powershell
pip install oracledb
```
Implementation:
``` text
app/database/adapters/oracle.py
```
Typical configuration:
``` text
Host: localhost
Port: 1521
Service Name: FREEPDB1
Schema: MY_SCHEMA
```
------------------------------------------------------------------------
# Connection Management
Connections are represented by `DatabaseConfig`.
``` python
DatabaseConfig(
name="mysql_connection",
database_type="mysql",
host="127.0.0.1",
port=3306,
username="root",
password="...",
database="project1",
)
```
The `ConnectionManager` manages multiple connections:
``` text
ConnectionManager
│
├── mysql_connection
│ └── MySQLAdapter
│
├── postgres_connection
│ └── PostgreSQLAdapter
│
└── oracle_connection
└── OracleAdapter
```
This means multiple databases can coexist in the same MCP server.
------------------------------------------------------------------------
# Security
The application is designed for **read-only analytics**.
Before execution, generated SQL passes through:
``` text
SQLValidator
```
The validator:
- requires `SELECT`
- rejects `INSERT`
- rejects `UPDATE`
- rejects `DELETE`
- rejects `DROP`
- rejects `ALTER`
- rejects `TRUNCATE`
- rejects `CREATE`
- rejects `REPLACE`
- rejects `GRANT`
- rejects `REVOKE`
- rejects `MERGE`
- rejects multiple SQL statements
Example:
``` text
SELECT SUM(total_sale)
FROM retail_sales
```
is allowed.
But:
``` text
DELETE FROM retail_sales
```
is rejected.
The security layer is independent of the AI provider and database
implementation.
------------------------------------------------------------------------
# Configuration
Create a `.env` file in the project root.
Example:
``` env
# -------------------------
# AI
# -------------------------
AI_PROVIDER=groq
GROQ_API_KEY=your_groq_api_key
GROQ_MODEL=your_groq_model
ANTHROPIC_API_KEY=your_anthropic_api_key
CLAUDE_MODEL=your_claude_model
GEMINI_API_KEY=your_gemini_api_key
GEMINI_MODEL=your_gemini_model
# -------------------------
# MySQL
# -------------------------
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_DATABASE=project1
MYSQL_USERNAME=root
MYSQL_PASSWORD=your_password
```
Never commit `.env` to Git.
Add:
``` text
.env
```
to `.gitignore`.
------------------------------------------------------------------------
# Installation
## 1. Clone the project
``` powershell
git clone <repository-url>
cd sql_mcp_tool
```
## 2. Create a virtual environment
``` powershell
python -m venv venv
```
## 3. Activate it
Windows PowerShell:
``` powershell
.\venv\Scripts\Activate.ps1
```
## 4. Install dependencies
``` powershell
pip install -r requirements.txt
```
If provider-specific dependencies are not already present:
``` powershell
pip install groq anthropic google-genai
```
For databases:
``` powershell
pip install pymysql
pip install "psycopg[binary]"
pip install oracledb
```
------------------------------------------------------------------------
# Running the MCP Server
The MCP server uses STDIO transport.
Run:
``` powershell
python -m app.mcp.server
```
The MCP client should launch this process automatically when configured.
Do not add normal `print()` statements to STDOUT in the MCP server
because STDIO is used for MCP communication.
------------------------------------------------------------------------
# Amazon Q Integration
Amazon Q Developer in VS Code can act as the MCP client.
For a workspace configuration, the MCP server can be configured in:
``` text
.amazonq/default.json
```
Example:
``` json
{
"mcpServers": {
"sql-mcp-tool": {
"type": "stdio",
"command": "C:\\Users\\prabh\\sql_mcp_tool\\venv\\Scripts\\python.exe",
"args": [
"-m",
"app.mcp.server"
],
"timeout": 60
}
}
}
```
The exact path must point to the Python executable in the project's
virtual environment.
After configuring the MCP server, restart VS Code if Amazon Q does not
immediately refresh the server list.
------------------------------------------------------------------------
# Testing
Run the complete test suite:
``` powershell
pytest -q
```
Tests should cover:
``` text
AI providers
AI provider factory
SQL generation
Database adapters
Connection manager
Schema inspection
SQL validation
Query service
MCP tools
```
Integration tests should be separated from unit tests because
integration tests require actual database and/or external AI
credentials.
------------------------------------------------------------------------
# Example Workflow
A user asks:
``` text
How many transactions are there?
```
The server first discovers the schema.
For example:
``` text
Schema: project1
Table: retail_sales
Columns:
transactions_id
sale_date
sale_time
customer_id
gender
age
category
quantiy
price_per_unit
cogs
total_sale
```
The AI receives this context and generates a query such as:
``` sql
SELECT COUNT(*) AS transaction_count
FROM retail_sales
```
The SQL validator checks the query.
The database adapter executes it.
The MCP server returns the result.
The important point is that the application did not hardcode the
`retail_sales` table. The table came from schema inspection.
------------------------------------------------------------------------
# Design Patterns Used
## Adapter Pattern
Used for databases.
``` text
DatabaseAdapter
│
├── MySQLAdapter
├── PostgreSQLAdapter
└── OracleAdapter
```
------------------------------------------------------------------------
## Factory Pattern
Used to create database and AI implementations.
``` text
AIProviderFactory
DatabaseFactory
```
------------------------------------------------------------------------
## Strategy Pattern
The AI provider acts as a replaceable strategy.
``` text
AIService
│
└── AIProvider
├── Groq
├── Claude
└── Gemini
```
------------------------------------------------------------------------
## Dependency Injection
Core services receive their dependencies rather than creating them
internally.
Example:
``` python
QueryService(
connection_manager,
sql_generator,
context_builder,
)
```
This makes the application easier to test and extend.
------------------------------------------------------------------------
# Extensibility
The architecture is designed so new providers can be added
independently.
For example, adding another AI provider requires:
``` text
1. Create provider implementation
2. Implement AIProvider
3. Register it in AIProviderFactory
4. Add configuration
5. Add tests
```
The following components do not need to be rewritten:
``` text
MCP Server
QueryService
SQLGenerator
AIService
SQLValidator
Database adapters
```
Similarly, a new database requires:
``` text
1. Implement DatabaseAdapter
2. Register it in DatabaseFactory
3. Add tests
```
The MCP tools and query workflow remain unchanged.
------------------------------------------------------------------------
# Why This Architecture?
Traditional approach:
``` text
MCP Server
↓
Groq-specific code
↓
MySQL-specific code
↓
SQL
```
This creates tight coupling.
This project uses:
``` text
Interfaces
│
┌─────────┴─────────┐
│ │
AIProvider DatabaseAdapter
│ │
┌──────┼──────┐ ┌─────┼────────┐
│ │ │ │ │ │
Groq Claude Gemini MySQL PostgreSQL Oracle
```
The core application depends on abstractions rather than concrete
vendors.
------------------------------------------------------------------------
# Production-Oriented Improvements
The current architecture can be extended with:
- connection pooling
- credential managers / secret stores
- database role-based access
- query timeout controls
- result-size limits
- query cost estimation
- audit logging
- structured application logging
- distributed tracing
- metrics
- rate limiting
- prompt-injection defenses
- schema caching
- query-result caching
- retry policies
- circuit breakers
- container deployment
- CI/CD
- AWS Secrets Manager
- Amazon CloudWatch
- ECS/Fargate or Kubernetes deployment
These are natural extensions and do not require changing the core
provider/adapter abstractions.
------------------------------------------------------------------------
# Technology Stack
Layer Technology
------------------- --------------------------------
Language Python
Protocol Model Context Protocol (MCP)
MCP Transport STDIO
AI Abstraction Custom `AIProvider` interface
AI Providers Groq / Claude / Gemini
ORM / DB Toolkit SQLAlchemy
Databases MySQL / PostgreSQL / Oracle
MySQL Driver PyMySQL
PostgreSQL Driver psycopg
Oracle Driver python-oracledb
Validation Custom SQL Validator
Configuration Environment variables / `.env`
Client Amazon Q in VS Code
Testing pytest
------------------------------------------------------------------------
# Project Highlights
### Model Agnostic
Switch between AI providers without modifying the business logic.
### Database Agnostic
Use the same MCP workflow across MySQL, PostgreSQL, and Oracle.
### Dynamic Schema Discovery
The AI works with the actual database structure instead of hardcoded
tables.
### Read-only by Design
Generated SQL is validated before execution.
### Multiple Connections
The connection manager can manage multiple database connections.
### MCP Compatible
The server exposes database capabilities as MCP tools to AI clients.
### Testable
Interfaces, factories, services, adapters, and validators can be unit
tested independently.
### Extensible
New AI providers and database systems can be added without rewriting the
core architecture.
------------------------------------------------------------------------
# Future Architecture
``` text
AI CLIENTS
│
┌──────────────┼──────────────┐
│ │ │
Amazon Q Claude Other MCP
VS Code Client Clients
│ │ │
└──────────────┼──────────────┘
│
MCP / STDIO
│
▼
┌──────────────────┐
│ MCP SERVER │
└────────┬─────────┘
│
┌────────▼─────────┐
│ QueryService │
└──────┬───┬───────┘
│ │
┌────────────┘ └─────────────┐
▼ ▼
┌──────────────┐ ┌────────────────┐
│ AI Provider │ │ DB Adapter │
│ Abstraction │ │ Abstraction │
└──────┬───────┘ └───────┬────────┘
│ │
┌──────┼────────┐ ┌──────────┼──────────┐
▼ ▼ ▼ ▼ ▼ ▼
Groq Claude Gemini MySQL PostgreSQL Oracle
│ │ │ │ │ │
└──────┼────────┘ └──────────┼──────────┘
│ │
▼ ▼
AI Model APIs Databases
```
------------------------------------------------------------------------
# Author
**SQL MCP Tool**
AI-powered, model-agnostic, database-agnostic MCP server for
natural-language SQL analytics.
> **One MCP server. Multiple AI providers. Multiple databases. One
> consistent query workflow.**
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues