MCP Sql

🚀 MCP Database Assistant

An AI-powered multi-database assistant built with OpenAI's GPT models and Model Context Protocol (MCP). It creates an intelligent database query interface that understands natural language requests and executes SQL queries across MySQL, Oracle, and SQLite databases with full schema awareness.

🚀 Quick Start

The MCP Database Assistant is an AI - powered tool that enables users to interact with MySQL, Oracle, and SQLite databases using natural language. Follow the steps below to get started.

✨ Features

• 🤖 AI-Powered SQL Assistant: Convert natural language to SQL queries using OpenAI GPT - 4o.
• 🔧 Model Context Protocol Integration: Seamless tool calling and context management.
• 🗄️ Multi - Database Support: Works with MySQL, Oracle, and SQLite databases.
• 🌐 Modern Web Interface: A clean, responsive chat interface with real - time query processing.
• 📊 Schema Discovery: Automatically explore and validate the database structure.
• 🔍 Smart Search: Find tables and columns by keywords.
• 💾 Session Management: Persistent chat history during browser sessions.
• ⚡ Real - time Processing: Async handling for fast query execution.
• 🛡️ Safe Query Execution: Protected SQL execution with error handling.
• 🔄 Dual API Support: Multiple endpoint formats for different frontend requirements.

📦 Installation

1. Install uv (if not already installed)

macOS/Linux:

curl -LsSf https://astral.sh/uv/install.sh | sh

Windows:

powershell -c "irm https://astral.sh/uv/install.ps1 | iex"

Alternative (via pip):

pip install uv

2. Clone and Setup Project

git clone https://github.com/sharansahu/mcp-sql
cd mcp-sql

# Create virtual environment and install dependencies
uv sync

3. Environment Configuration

Create a .env file in the project root with your database configuration:

For SQLite:

# OpenAI Configuration
OPENAI_API_KEY=your_openai_api_key_here

# Database Configuration
DB_TYPE=sqlite
DB_PATH=./dod_synthetic.db

For MySQL:

# OpenAI Configuration  
OPENAI_API_KEY=your_openai_api_key_here

# Database Configuration
DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=your_database_name
DB_USER=your_username
DB_PASSWORD=your_password

For Oracle:

# OpenAI Configuration
OPENAI_API_KEY=your_openai_api_key_here

# Database Configuration
DB_TYPE=oracle
DB_USER=your_username
DB_PASSWORD=your_password
DB_DSN=hostname:port/service_name

💻 Usage Examples

Web Interface (Recommended)

1. Start the Flask application:

   uv run python mcp_client.py
   

2. Access the web interface: Open your browser and go to: http://localhost:10000

3. Start querying:

   • Type natural language questions about your database
   • Example: "Show me all tables in the database"
   • Example: "Find personnel who worked on tank maintenance in the last 90 days"
   • Example: "What's the structure of the users table?"

Example Queries

Basic Usage

The AI assistant can handle various types of database queries:

Schema Exploration

• "What tables are available in this database?"
• "Describe the structure of the personnel table"
• "Search for tables related to maintenance"
• "Show me the schema for all tables"

Data Analysis

• "How many records are in each table?"
• "Show me the first 5 personnel records"
• "Find all equipment of type 'tank'"
• "What are the column names in the orders table?"

Advanced Usage

Complex Queries

• "Show personnel who performed maintenance on tanks in the last 90 days"
• "What's the average number of maintenance tasks per person?"
• "List equipment that hasn't been maintained recently"
• "Find the top 10 customers by order value"

📚 Documentation

Project Structure

mcp-database-assistant/
├── README.md           # Project documentation
├── mcp_client.py       # Flask web application (main entry point)
├── servers/            # MCP server implementations
│   ├── mcp_server_sqlite.py   # SQLite MCP server with database tools
│   ├── mcp_server_mysql.py    # MySQL MCP server with database tools  
│   └── mcp_server_oracle.py   # Oracle MCP server with database tools
├── dod_synthetic.db    # Sample SQLite database (if using SQLite)
├── pyproject.toml      # Project dependencies and configuration
├── .env                # Environment variables (create this)
├── .python-version     # Python version specification
├── static/             # Web interface files
│   ├── index.html      # Main web interface
│   ├── script.js       # Frontend JavaScript
│   └── styles.css      # Interface styling
├── .gitignore          # Git ignore file
└── .venv/              # Virtual environment (created by uv)

Database Tools

The MCP servers provide several powerful tools for database interaction:

• get_schema(): Get complete database schema with sample data
• list_tables(): List all available tables
• describe_table(table_name): Detailed table information including columns and sample data
• search_tables(keyword): Find tables/columns by keyword
• query_data(sql): Execute SQL queries safely

API Endpoints

The Flask app provides several REST API endpoints:

• GET /: Serve the main web interface
• POST /api/query: Process natural language queries (returns detailed status)
• POST /api/chat: Alternative query endpoint (returns simplified response)
• POST /api/clear: Clear chat session history
• GET /api/history: Retrieve chat history
• GET /health: Health check endpoint

How It Works

1. Database Type Detection: The system loads the appropriate MCP server based on the DB_TYPE environment variable.
2. User Input: Users enter natural language queries via the web interface.
3. Schema Discovery: The AI explores the database structure using MCP tools.
4. Query Generation: The AI generates appropriate SQL based on the schema and database type.
5. Safe Execution: SQL queries are executed with proper error handling.
6. Result Formatting: Results are formatted and returned to the user.
7. Session Management: Conversation history is maintained for context.

Database - Specific Features

SQLite

• File - based database support
• Full schema introspection
• Sample data preview

MySQL

• Connection pooling
• UTF - 8 support with proper collation
• Primary key detection
• Row count and sample data

Oracle

• Case - sensitive table/column handling (uppercase)
• ROWNUM - based pagination
• Primary key constraint detection
• User schema awareness

Troubleshooting

Common Issues

"Invalid DB_TYPE" error

• Ensure DB_TYPE is set to one of: sqlite, mysql, or oracle.
• Check that your .env file is properly formatted.

"No module named 'openai'"

uv sync  # Reinstall dependencies

"OPENAI_API_KEY not found"

• Ensure your .env file exists and contains your API key.
• Check that the API key is valid and has sufficient credits.

Database connection errors

• SQLite: Verify the DB_PATH points to your database file.
• MySQL: Check DB_HOST, DB_PORT, DB_NAME, DB_USER, and DB_PASSWORD.
• Oracle: Verify DB_USER, DB_PASSWORD, and DB_DSN format.

Web interface not loading

• Check that Flask is running on the correct port (10000).
• Verify static files are in the static/ directory.

Database - Specific Issues

MySQL Connection Issues:

• Ensure the MySQL server is running.
• Verify the user has proper permissions.
• Check firewall settings if connecting remotely.

Oracle Connection Issues:

• Verify the Oracle Instant Client is installed.
• Check TNS names configuration.
• Ensure the service name in DSN is correct.

Debug Mode

Run with additional logging:

FLASK_DEBUG=True uv run python mcp_client.py

Security Considerations

⚠️ Important Note

• Never commit your .env file with real credentials.
• Use environment variables or secure vaults in production.
• Implement proper database user permissions.
• Consider SQL injection protection (built into the MCP tools).
• Use HTTPS in production environments.

Deployment

Local Development

The current setup is optimized for local development with the Flask development server.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.