Utilizes .env files for configuration management of database connections and API keys
Uses Flask to create a web application interface with API endpoints for processing database queries and managing chat sessions
Provides tools for connecting to MySQL databases, executing SQL queries, exploring schema structure, and analyzing data with AI-assisted natural language processing
Integrates with OpenAI's GPT models to power natural language to SQL query conversion and database exploration capabilities
Enables interaction with SQLite databases through schema discovery, table exploration, and execution of natural language queries converted to SQL
MCP Database Assistant
An AI-powered multi-database assistant built with OpenAI's GPT models and Model Context Protocol (MCP). This project demonstrates how to create an intelligent database query interface that can understand natural language requests and execute SQL queries with full schema awareness across MySQL, Oracle, and SQLite databases.
🌟 Key Features
🤖 AI-Powered SQL Assistant - Natural language to SQL query conversion 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 - Clean, responsive chat interface with real-time query processing
📊 Schema Discovery - Automatic database structure exploration and validation
🔍 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
📋 Prerequisites
Python 3.12+ (specified in
.python-version
)OpenAI API Key - Get one from OpenAI Platform
Database - One of the following:
SQLite database file (
.db
)MySQL server with accessible database
Oracle database with proper connection string
🚀 Installation
1. Install uv (in case you haven't installed it yet)
macOS/Linux:
Windows:
Alternative (via pip):
2. Clone and Setup Project
3. Environment Configuration
Create a .env
file in the project root with your database configuration:
For SQLite:
For MySQL:
For Oracle:
📁 Project Structure
🎯 Usage
Web Interface (Recommended)
Start the Flask application:
uv run python mcp_client.pyAccess the web interface: Open your browser and go to: http://localhost:10000
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
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?"
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"
🛠️ Database Tools
The MCP servers provide several powerful tools for database interaction:
get_schema()
- Get complete database schema with sample datalist_tables()
- List all available tablesdescribe_table(table_name)
- Detailed table information including columns and sample datasearch_tables(keyword)
- Find tables/columns by keywordquery_data(sql)
- Execute SQL queries safely
📡 API Endpoints
The Flask app provides several REST API endpoints:
GET /
- Serve the main web interfacePOST /api/query
- Process natural language queries (returns detailed status)POST /api/chat
- Alternative query endpoint (returns simplified response)POST /api/clear
- Clear chat session historyGET /api/history
- Retrieve chat historyGET /health
- Health check endpoint
🔍 How It Works
Database Type Detection - System loads appropriate MCP server based on
DB_TYPE
environment variableUser Input - Natural language query via web interface
Schema Discovery - AI explores database structure using MCP tools
Query Generation - AI generates appropriate SQL based on schema and database type
Safe Execution - SQL query executed with proper error handling
Result Formatting - Results formatted and returned to user
Session Management - Conversation history 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
, ororacle
Check that your
.env
file is properly formatted
"No module named 'openai'"
"OPENAI_API_KEY not found"
Ensure your
.env
file exists and contains your API keyCheck that the API key is valid and has sufficient credits
Database connection errors
SQLite: Verify the
DB_PATH
points to your database fileMySQL: Check
DB_HOST
,DB_PORT
,DB_NAME
,DB_USER
, andDB_PASSWORD
Oracle: Verify
DB_USER
,DB_PASSWORD
, andDB_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 MySQL server is running
Verify user has proper permissions
Check firewall settings if connecting remotely
Oracle Connection Issues:
Verify Oracle Instant Client is installed
Check TNS names configuration
Ensure service name in DSN is correct
Debug Mode
Run with additional logging:
🛡️ Security Considerations
Never commit your
.env
file with real credentialsUse 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.
An AI-powered SQLite assistant that converts natural language to SQL queries with full schema awareness, enabling users to interact with databases using conversational language.
Related MCP Servers
- AsecurityAlicenseAqualityAllows AI assistants to list tables, read data, and execute SQL queries through a controlled interface, making database exploration and analysis safer and more structured.Last updated -870MIT License
- -securityAlicense-qualityHandles SQL query execution for a natural language interface to SQLite databases, enabling users to interact with databases using plain English rather than writing SQL manually.Last updated -1MIT License
- AsecurityAlicenseAqualityA tool service that enables AI agents to interact with MySQL databases through natural language, supporting SQL queries, table structure retrieval, and connection testing.Last updated -73920Apache 2.0
- AsecurityFlicenseAqualityEnables AI assistants to interact with PostgreSQL databases using natural language queries, providing secure read-only access to database schemas and SQL translation capabilities.Last updated -61