A powerful web application that transforms natural language into SQL queries and data visualizations. Built with LangGraph, FastAPI, and Streamlit, featuring intelligent agent workflows and secure execution environments.
- Natural Language to SQL: Convert plain English questions into optimized SQL queries
- Intelligent Data Visualization: Automatic chart and plot generation with matplotlib and plotly
- Multi-table Support: Smart table joins and relationship detection across complex schemas
- Local & Secure Execution: Run code locally with sandbox protection or use Docker containers
- Interactive Chat Interface: Conversational AI with context awareness and session memory
- Real-time Feedback System: User satisfaction tracking with comprehensive admin dashboard
- Universal Database Support: Works with SQLite, PostgreSQL, MySQL, SQL Server, Oracle, and more
- Frontend (Streamlit): Interactive web interface for users and administrators
- Backend (FastAPI): RESTful API serving the LangGraph agent
- LangGraph Agent: Intelligent workflow orchestration for text-to-SQL conversion
- Security Layer: Secure SQL executor and code execution sandbox
- Feedback Database: SQLite database for storing user feedback
- Read-only SQL execution with intelligent query validation and injection prevention
- Flexible execution modes: Local sandbox with
FORCE_LOCAL_EXECUTION=trueor Docker containers - Advanced input sanitization with comprehensive prompt injection protection
- Resource limits for query execution, memory usage, and runtime constraints
- Secure code generation with validated matplotlib and plotly visualization libraries
- Python 3.11+ with pip
- Docker (optional - for containerized code execution)
- OpenRouter API key for LLM access
- Basic database knowledge (helpful but not required)
-
Clone the repository
git clone <repository-url> cd open-genie
-
Run setup script
./scripts/setup.sh
-
Configure environment
- Edit
.envfile with your OpenRouter API key:
OPENROUTER_API_KEY=your_api_key_here - Edit
-
Start the application
Option A: Local Development (Recommended for development)
# Start backend with local code execution FORCE_LOCAL_EXECUTION=true uvicorn app.main:app --reload --host 0.0.0.0 --port 8000 # In another terminal - Start frontend streamlit run app/frontend/main_page.py --server.port 8501 # In another terminal - Start admin dashboard streamlit run app/frontend/admin_page.py --server.port 8502
Option B: Using Docker Compose (Production)
docker-compose up -d
-
Access the applications
- Main App: http://localhost:8501
- Admin Dashboard: http://localhost:8502
- API Documentation: http://localhost:8000/docs
Open Genie comes with sample e-commerce data to get you started immediately:
- Start the application following the installation steps above
- The app automatically connects to the included demo SQLite database
- Try these example queries:
- "Give me a quantity line plot over the latest 5 records"
- "Show me a pie chart of products by category"
- "What are the top selling items?"
- Open the main application
- In the sidebar, enter your database connection string:
# SQLite sqlite:///path/to/your/database.db # PostgreSQL postgresql://username:password@host:port/database # MySQL mysql://username:password@host:port/database - Click "Connect" to test the connection
- Select the tables you want to query
Once connected, you can ask questions in natural language:
- "Show me all users from the last month"
- "What are the top 10 selling products?"
- "Create a line chart showing sales trends by region"
- "Give me a pie chart of order quantities by product"
- "Plot customer distribution across different segments"
- "Show revenue growth over time with a bar chart"
- Database Permissions: Always use read-only database credentials
- Network Security: The application connects to your database - ensure proper firewall rules
- API Key Security: Keep your OpenRouter API key secure and never commit it to version control
- Code Execution: Generated Python code runs in a Docker sandbox, but always review generated queries before execution
| Variable | Description | Default |
|---|---|---|
OPENROUTER_API_KEY |
OpenRouter API key for LLM access | Required |
DATABASE_URL |
Feedback database URL | sqlite:///./feedback.db |
DEBUG |
Enable debug mode | false |
LOG_LEVEL |
Logging level | INFO |
FORCE_LOCAL_EXECUTION |
Force local code execution (bypass Docker) | false |
ALLOW_LOCAL_EXECUTION |
Allow local execution as fallback | false |
DOCKER_TIMEOUT |
Code execution timeout (seconds) | 120 |
DOCKER_MEMORY_LIMIT |
Memory limit for code execution | 512m |
DOCKER_CPU_LIMIT |
CPU limit for code execution | 1.0 |
- SQLite
- PostgreSQL
- MySQL
- Microsoft SQL Server
- Oracle Database
- Any database supported by SQLAlchemy
# Run all tests
pytest
# Run with coverage
pytest --cov=app tests/
# Run specific test categories
pytest tests/unit/
pytest tests/integration/# Format code
black .
# Lint code
ruff check .
# Type checking
mypy app/-
Install development dependencies:
pip install -e ".[dev]" -
Set up pre-commit hooks:
pre-commit install
The admin dashboard provides insights into:
- Feedback Overview: User satisfaction metrics and trends
- Query Analysis: Most common queries and performance
- Session Analytics: User engagement and session patterns
- System Health: API performance and error rates
Access at: http://localhost:8502
-
Database Access:
- Use dedicated read-only database users
- Restrict network access to database
- Regularly rotate credentials
-
API Security:
- Use HTTPS in production
- Implement rate limiting
- Monitor for suspicious activity
-
Code Execution:
- Docker sandbox provides isolation
- Resource limits prevent DoS
- No network access from sandbox
-
Build and run with Docker Compose:
docker-compose -f docker-compose.prod.yml up -d
-
Environment Configuration:
- Set production environment variables
- Configure reverse proxy (nginx/traefik)
- Set up SSL certificates
- Configure monitoring and logging
- Use a production-grade database for feedback storage
- Implement proper logging and monitoring
- Set up backup strategies
- Configure auto-scaling if needed
- Regular security updates
- Fork the repository
- Create a feature branch
- Make your changes
- Add tests for new functionality
- Ensure all tests pass
- Submit a pull request
This project is licensed under the MIT License - see the LICENSE file for details.
Docker Connection Issues:
# Check Docker is running
docker ps
# Pull required images
docker pull python:3.11-slimDatabase Connection Failures:
- Verify connection string format
- Check network connectivity
- Ensure database user has appropriate permissions
- Check firewall settings
API Key Issues:
- Verify OpenRouter API key is valid
- Check rate limits and usage
- Ensure environment variable is set correctly
Code Execution Issues:
- For local execution: Set
FORCE_LOCAL_EXECUTION=trueenvironment variable - For Docker issues: Ensure Docker is installed and running
- Check Docker daemon socket permissions
- Verify Python sandbox image is available
- For plot generation: Ensure matplotlib and plotly are installed in your environment
- Check the Issues page for common problems
- Review the API Documentation when running
- Check logs in the Docker containers or terminal output
- LangChain - LLM application framework
- LangGraph - Agent workflow orchestration
- Streamlit - Web app framework
- FastAPI - Modern Python API framework
Built with β€οΈ using Python, LangGraph, FastAPI, and Streamlit