SQLite MCP Server

# Option B: Auto-Initialization Architecture ## Overview **Option B** implements automatic database initialization on the MCP server side, eliminating the need for Django to explicitly open the database. ### Before vs After #### Option A (Before: Manual Initialization) ``` Django startup: 1. MCPClient connects to MCP server 2. Django calls: mcp_client.open_database(path) 3. MCP server opens database 4. Database ready for queries Issue: Requires Django to manage database opening ``` #### Option B (After: Auto-Initialization) ``` MCP Server startup: 1. Main.py starts 2. Startup event triggers 3. Auto-initializes database with DEFAULT_DATABASE_PATH 4. Database ready immediately Django startup: 1. MCPClient connects to MCP server 2. Django checks status endpoint: /status 3. Confirms database is initialized 4. Ready for queries Advantage: Cleaner separation of concerns ``` --- ## Implementation Details ### 1. MCP Server Changes (main.py) #### Configuration Block ```python import os # Default database path - can be overridden by environment variable DEFAULT_DATABASE_PATH = os.getenv( 'MCP_DATABASE_PATH', '/Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db' ) AUTO_INITIALIZE = os.getenv('MCP_AUTO_INIT', 'true').lower() == 'true' ``` #### New Status Endpoint ```python @app.get("/status") async def status_check(): """Detailed status endpoint showing database and configuration info.""" database_open = db.connection is not None tables = [] if database_open: try: result = db.list_tables() tables = result.get("tables", []) except: pass return { "status": "healthy", "database_connected": database_open, "tables_available": tables, "table_count": len(tables), "auto_init_enabled": AUTO_INITIALIZE, "default_database_path": DEFAULT_DATABASE_PATH, "service": "SQLite MCP HTTP API" } ``` #### Enhanced Startup Event ```python @app.on_event("startup") async def startup_event(): """Run on server startup and auto-initialize database if configured.""" print("🚀 SQLite MCP HTTP API Server Started") # Auto-initialize database if enabled if AUTO_INITIALIZE: try: print(f"🔄 Auto-initializing database from: {DEFAULT_DATABASE_PATH}") db.open(DEFAULT_DATABASE_PATH) print(f"✅ Database initialized successfully") # Verify by listing tables try: result = db.list_tables() tables = result.get("tables", []) print(f"✅ Tables available: {', '.join(tables)}") except Exception as e: print(f"⚠️ Warning: Could not list tables: {str(e)}") except Exception as e: print(f"❌ Failed to auto-initialize database: {str(e)}") print(f"⚠️ Database will be unavailable until opened manually") ``` ### 2. Django Changes (views.py) #### Before (Manual) ```python # Try multiple database paths db_paths = [...] for db_path in db_paths: try: abs_db_path = os.path.abspath(db_path) mcp_client.open_database(abs_db_path) logger.info(f"✓ MCP Database opened successfully: {abs_db_path}") break except Exception as e: continue ``` #### After (Auto-Initialization Aware) ```python # Database is auto-initialized by MCP server # Just check that it's ready logger.info("Checking MCP server database status...") try: import requests status_response = requests.get(f"{mcp_server_url}/status", timeout=5) if status_response.status_code == 200: status_data = status_response.json() if status_data.get('database_connected'): tables = status_data.get('tables_available', []) logger.info(f"✓ MCP Database is auto-initialized with {len(tables)} tables") mcp_db_opened = True ``` --- ## Startup Sequence ### MCP Server Startup ``` 1. uvicorn starts FastAPI app ↓ 2. @app.on_event("startup") triggers ↓ 3. Check AUTO_INITIALIZE env var (default: true) ↓ 4. Call db.open(DEFAULT_DATABASE_PATH) ↓ 5. Database connection established ↓ 6. List tables to verify ↓ 7. Print success message ↓ 8. Server ready for requests Output: 🚀 SQLite MCP HTTP API Server Started 🔄 Auto-initializing database from: /Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db ✅ Database initialized successfully ✅ Tables available: Spam_number, KTP ``` ### Django Server Startup ``` 1. Django starts ↓ 2. views.py initialization block runs ↓ 3. Create MCPClient instance ↓ 4. GET /status to check database ↓ 5. Parse response, confirm database_connected=true ↓ 6. Log success ↓ 7. Server ready Output: ✓ MCP Client initialized successfully Checking MCP server database status... ✓ MCP Database is auto-initialized with 2 tables: Spam_number, KTP MCP Client Status: Available=True, Database Auto-Initialized=True ``` --- ## Configuration ### Environment Variables #### MCP_DATABASE_PATH Override the default database path: ```bash export MCP_DATABASE_PATH="/path/to/custom/database.db" uvicorn main:app --host 0.0.0.0 --port 8000 ``` #### MCP_AUTO_INIT Enable/disable auto-initialization (default: true): ```bash # Disable auto-init export MCP_AUTO_INIT="false" uvicorn main:app --host 0.0.0.0 --port 8000 # The database won't open automatically, but can be opened manually via API ``` ### Status Endpoint Response ```json { "status": "healthy", "database_connected": true, "tables_available": ["Spam_number", "KTP"], "table_count": 2, "auto_init_enabled": true, "default_database_path": "/Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db", "service": "SQLite MCP HTTP API" } ``` --- ## Advantages of Option B | Aspect | Option A (Manual) | Option B (Auto-Init) | |--------|-------------------|----------------------| | **Startup Logic** | Django handles | MCP server handles | | **Initialization** | Explicit `open_database()` call | Automatic on startup | | **Django Code** | Complex path handling | Simple status check | | **Reliability** | Database may not be open when queries arrive | Database always ready | | **Separation of Concerns** | Django owns database | MCP owns database | | **Error Handling** | Django logs failed paths | MCP logs initialization errors | | **Configuration** | Hardcoded in views.py | Environment variables | | **Testing** | Need to mock database opening | Just check /status | | **Flexibility** | Can open different databases | Fixed default database | --- ## API Endpoints ### Health Check ``` GET /health Response: {"status": "healthy", "database_connected": true, "service": "SQLite MCP HTTP API"} ``` ### Status Check (New in Option B) ``` GET /status Response: { "status": "healthy", "database_connected": true, "tables_available": ["Spam_number", "KTP"], "table_count": 2, "auto_init_enabled": true, "default_database_path": "...", "service": "SQLite MCP HTTP API" } ``` ### Manual Database Opening (Still Available) ``` POST /tools/open_database Body: {"path": "/path/to/database.db"} Response: {"success": true, "message": "Database opened successfully...", "database_path": "..."} ``` --- ## Migration from Option A to Option B ### Step 1: Update MCP Server The MCP server code is already updated with: - Configuration block with `DEFAULT_DATABASE_PATH` - Auto-init logic in startup event - New `/status` endpoint ### Step 2: Update Django Replace the database opening logic with status checking: - Remove: `mcp_client.open_database()` calls - Add: `requests.get(f"{mcp_server_url}/status")` check ### Step 3: Restart Servers ```bash # Terminal 1: Restart MCP server cd /Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp uvicorn main:app --host 0.0.0.0 --port 8000 # Terminal 2: Restart Django server cd /Users/supriyadi/Projects/LLM/B2B-LLM-Project/chatbot python manage.py runserver ``` ### Expected Logs **MCP Server:** ``` 🚀 Uvicorn running on http://0.0.0.0:8000 🚀 SQLite MCP HTTP API Server Started 🔄 Auto-initializing database from: /Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db ✅ Database initialized successfully ✅ Tables available: Spam_number, KTP ``` **Django Server:** ``` ✓ MCP Client initialized successfully Checking MCP server database status... ✓ MCP Database is auto-initialized with 2 tables: Spam_number, KTP MCP Client Status: Available=True, Database Auto-Initialized=True ``` --- ## Testing ### Run the Auto-Init Test ```bash cd /Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp python3 test_auto_init.py ``` ### Expected Output ``` ✅ MCP server is running ✅ Status endpoint is available ✅ Database Connected: True ✅ Auto-Init Enabled: True ✅ Tables Available: 2 tables ✅ Table Names: Spam_number, KTP ✅ Database auto-initialization SUCCESS! ✅ list_tables endpoint works ✅ Query executed successfully! Spam_number table has 100 records ``` --- ## Troubleshooting ### Database Not Auto-Initializing **Symptom:** `database_connected: false` in status response **Solution:** 1. Check MCP server logs for errors 2. Verify database file exists: `/Users/supriyadi/Projects/MCP/SQL-MCP-SERVER/sqlite-mcp/data/sample_data.db` 3. Check file permissions: `ls -la /path/to/sample_data.db` 4. Try manual opening: `curl -X POST http://localhost:8000/tools/open_database -H "Content-Type: application/json" -d '{"path": "/path/to/sample_data.db"}'` ### AUTO_INIT Environment Variable Not Working **Symptom:** Auto-init doesn't start despite being set to true **Solution:** 1. Verify env var is set: `echo $MCP_AUTO_INIT` 2. Check if you're in the right terminal session 3. Set it before running uvicorn: ```bash export MCP_AUTO_INIT="true" uvicorn main:app --host 0.0.0.0 --port 8000 ``` ### Django Can't Reach /status Endpoint **Symptom:** Django logs show "MCP status endpoint not available" **Solution:** 1. Check MCP server is running: `curl http://localhost:8000/health` 2. Check network connectivity: `ping localhost` 3. Check port 8000 is correct: `netstat -an | grep 8000` 4. Verify MCP_SERVER_URL in Django settings --- ## Architecture Diagram ``` MCP Server Startup Django Startup ==================== ================ FastAPI start Django start ↓ ↓ @startup event views.py init ↓ ↓ Check AUTO_INIT MCPClient(url) ↓ ↓ db.open(path) GET /status ↓ ↓ Database Parse response connected ↓ ↓ Verify List tables database_connected ↓ ↓ Ready for Ready for requests requests ↓ ↓ ✅ Server listening ✅ Django ready Query Flow ========== Client request ↓ MCPClient.execute_query() ↓ POST /tools/execute_query ↓ main.py endpoint ↓ Check: db.connection is not None ✅ ↓ db.execute_query(query, params) ↓ SQLite database ↓ Return results ``` --- ## Summary **Option B: Auto-Initialization** provides: 1. **Simpler Django Code** - No complex database path handling 2. **Better Reliability** - Database is ready before queries arrive 3. **Cleaner Architecture** - MCP owns its database connection 4. **Better Error Handling** - Issues visible in MCP server logs 5. **Flexible Configuration** - Environment variables for different deployments 6. **Production Ready** - Automated initialization without manual intervention This is the recommended approach for production systems. --- **Status:** ✅ IMPLEMENTED & READY FOR TESTING **Date:** 2025-11-18