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
- SQLite database file (
🚀 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:
- Access 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 variable - User 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 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
, 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 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.
This server cannot be installed
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 -1713PythonMIT License
- -securityAlicense-qualityA Model Context Protocol server implementation that enables AI assistants to execute SQL queries and interact with SQLite databases through a structured interface.Last updated -7TypeScriptMIT License
- -securityFlicense-qualityA tool that provides simple API to execute SQL queries and manage MySQL databases, designed to integrate with Cursor IDE for AI assistants to directly perform database operations.Last updated -Python
- AsecurityFlicenseAqualityA 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 -74617JavaScript