AI to DB - Natural Language Database Query System

📋 Project Overview

AI to DB is a comprehensive web application that enables users to query databases using natural language. The system leverages Large Language Models (LLMs) through LangChain to convert user questions into SQL queries, execute them safely, and return results in a user-friendly format.

Final Year Project | Built with professional-grade architecture and security best practices.

🎯 Key Features

• 🔌 Database Connection Manager: Support for multiple database types (PostgreSQL, MySQL, SQLite, etc.)
• 🧠 Natural Language Processing: Convert plain English questions to SQL using Google Gemini Pro (Free!)
• 🔍 Schema Introspection: Automatic extraction and analysis of database structures
• 🔒 Security First: Read-only query enforcement with SQL injection prevention
• ⚡ Real-time Execution: Fast query processing with detailed result display
• 🎨 Modern UI: Clean, responsive React interface with intuitive design
• 🐳 Docker Ready: One-command deployment with Docker Compose

---

🏗️ Architecture

System Components

┌─────────────────────────────────────────────────────────────┐
│                        Frontend (React)                      │
│  - Connection Form  - Query Interface  - Results Display    │
└──────────────────────────┬──────────────────────────────────┘
                           │ HTTP/REST API
┌──────────────────────────▼──────────────────────────────────┐
│                    Backend (FastAPI)                         │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐      │
│  │  Connection  │  │    Schema    │  │  SQL Agent   │      │
│  │   Manager    │  │  Extractor   │  │  (LangChain) │      │
│  └──────────────┘  └──────────────┘  └──────────────┘      │
│  ┌──────────────┐  ┌──────────────┐                        │
│  │   Security   │  │    Query     │                        │
│  │  Validator   │  │   Executor   │                        │
│  └──────────────┘  └──────────────┘                        │
└──────────────────────────┬──────────────────────────────────┘
                           │ SQLAlchemy
┌──────────────────────────▼──────────────────────────────────┐
│              Target Database (User-provided)                 │
│        PostgreSQL / MySQL / SQLite / Oracle / MSSQL         │
└─────────────────────────────────────────────────────────────┘

Technology Stack

Backend:

• FastAPI: High-performance Python web framework
• SQLAlchemy: Universal database toolkit
• LangChain: LLM orchestration framework
• Google Gemini Pro: Natural language to SQL conversion (Free tier!)
• sqlparse: SQL parsing and validation

Frontend:

• React 18: Modern UI framework
• Vite: Fast build tool and dev server
• Axios: HTTP client for API communication
• React Icons: Beautiful icon library

Infrastructure:

• Docker & Docker Compose: Containerization
• Nginx: Production web server
• PostgreSQL: Demo database (optional)

---

🚀 Quick Start

Prerequisites

• Docker and Docker Compose installed
• Google API key (Get one FREE here)
• A database to query (or use the included demo database)

Installation

1. Clone the repository:

   git clone <repository-url>
   cd aitodb

2. Configure environment variables:

   cp .env.example .env
   nano .env  # Edit and add your GOOGLE_API_KEY (get free key at https://makersuite.google.com/app/apikey)

3. Start the application:

   docker-compose up -d

4. Access the application:

   • Frontend: http://localhost
   • Backend API: http://localhost:8000
   • API Documentation: http://localhost:8000/docs

Using the Demo Database

The Docker Compose setup includes a demo PostgreSQL database with sample data:

Connection String:

postgresql://demo:demo123@postgres:5432/demo_db

Or from your host machine:

postgresql://demo:demo123@localhost:5432/demo_db

Sample Questions to Try:

• "Show me all users"
• "What are the top 5 products by price?"
• "How many orders has each user placed?"
• "List all electronics products with stock greater than 50"
• "Show me the total revenue from completed orders"

---

📖 Usage Guide

1. Connecting to a Database

1. Enter your database connection string in the connection form
2. Connection string format:

   dialect+driver://username:password@host:port/database
   

3. Click "Connect to Database"
4. The system will validate the connection and extract the schema

2. Asking Questions

1. Type your question in natural language
2. Click "Execute Query" or press Enter
3. View the generated SQL and results
4. Results are displayed in a formatted table

3. Understanding Results

• Generated SQL: The AI-generated query is shown for transparency
• Results Table: Data displayed in a clean, sortable format
• Execution Time: Performance metrics for the query
• Row Count: Number of results returned

4. Security Features

• All queries are validated to be read-only (SELECT only)
• SQL injection patterns are automatically detected and blocked
• Dangerous keywords (INSERT, UPDATE, DELETE, DROP) are prevented
• Connection strings are never stored

---

🛠️ Development Setup

Backend Development

1. Create virtual environment:

   cd backend
   python -m venv venv
   source venv/bin/activate  # Linux/Mac
   # or
   venv\Scripts\activate  # Windows

2. Install dependencies:

   pip install -r requirements.txt

3. Configure environment:

   cp .env.example .env
   # Edit .env with your settings

4. Run development server:

   uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

5. Access API documentation:

   • Swagger UI: http://localhost:8000/docs
   • ReDoc: http://localhost:8000/redoc

Frontend Development

1. Install dependencies:

   cd frontend
   npm install

2. Run development server:

   npm run dev

3. Build for production:

   npm run build

---

📁 Project Structure

aitodb/
├── backend/
│   ├── app/
│   │   ├── api/              # API route handlers
│   │   │   ├── connection.py # Connection management endpoints
│   │   │   └── query.py      # Query execution endpoints
│   │   ├── core/             # Core configuration
│   │   │   └── config.py     # Application settings
│   │   ├── models/           # Pydantic models
│   │   │   └── schemas.py    # Request/response schemas
│   │   ├── services/         # Business logic
│   │   │   ├── connection.py    # Connection manager
│   │   │   ├── schema_extractor.py  # Schema introspection
│   │   │   ├── security.py      # SQL validation
│   │   │   ├── sql_agent.py     # NL to SQL conversion
│   │   │   └── executor.py      # Query execution
│   │   └── main.py           # FastAPI application
│   ├── Dockerfile
│   └── requirements.txt
├── frontend/
│   ├── src/
│   │   ├── components/       # React components
│   │   │   ├── Header.jsx
│   │   │   ├── ConnectionForm.jsx
│   │   │   ├── QueryInterface.jsx
│   │   │   └── ResultsDisplay.jsx
│   │   ├── services/         # API client
│   │   │   └── api.js
│   │   ├── App.jsx           # Main app component
│   │   └── main.jsx          # Entry point
│   ├── Dockerfile
│   ├── nginx.conf
│   └── package.json
├── demo/
│   └── init.sql              # Demo database schema
├── docker-compose.yml        # Container orchestration
├── .env.example              # Environment template
└── README.md                 # This file

---

🔒 Security Features

SQL Injection Prevention

• Keyword Blacklist: Blocks dangerous SQL operations
• Pattern Detection: Identifies injection attempts
• Query Validation: Ensures only SELECT statements
• Parameterized Queries: Uses SQLAlchemy's safe execution

Read-Only Enforcement

All queries are validated to ensure they:

• Start with SELECT or WITH (CTEs)
• Don't contain modification keywords
• Don't access system tables maliciously
• Don't use dangerous functions

Connection Security

• Connection strings are not persisted
• No database credentials stored
• Secure environment variable handling
• CORS protection enabled

---

🔧 Configuration

Environment Variables

Backend (.env):

GOOGLE_API_KEY=your_api_key_here  # Required: Your Google API key (FREE at https://makersuite.google.com/app/apikey)
APP_NAME=AI to DB               # Application name
APP_VERSION=1.0.0               # Version number
DEBUG=True                      # Debug mode (False in production)
ALLOWED_ORIGINS=http://localhost  # CORS origins
SECRET_KEY=your-secret-key      # Security key

Supported Databases

• PostgreSQL (9.6+)
• MySQL (5.7+)
• SQLite (3+)
• Oracle
• Microsoft SQL Server
• MariaDB

Connection String Examples:

# PostgreSQL
postgresql://user:password@host:5432/database

# MySQL
mysql+pymysql://user:password@host:3306/database

# SQLite
sqlite:///./database.db

# SQL Server
mssql+pyodbc://user:password@host:1433/database?driver=ODBC+Driver+17+for+SQL+Server

---

📊 API Documentation

Connection Endpoints

Test Connection

POST /api/connection/test
Content-Type: application/json

{
  "connection_string": "postgresql://user:pass@host:5432/db",
  "alias": "My Database"
}

Get Schema

POST /api/connection/schema
Content-Type: application/json

{
  "connection_string": "postgresql://user:pass@host:5432/db"
}

Query Endpoints

Execute Natural Language Query

POST /api/query/execute
Content-Type: application/json

{
  "connection_string": "postgresql://user:pass@host:5432/db",
  "question": "Show me all users who signed up last month"
}

Validate SQL

POST /api/query/validate-sql?sql=SELECT * FROM users

---

🧪 Testing

Test the Demo Database

1. Start the application with Docker Compose
2. Connect using: postgresql://demo:demo123@postgres:5432/demo_db
3. Try these queries:
   • "How many users are there?"
   • "Show me all products sorted by price"
   • "What is the total revenue from completed orders?"
   • "List users who have placed more than one order"

Manual Testing

# Backend health check
curl http://localhost:8000/health

# Test connection
curl -X POST http://localhost:8000/api/connection/test \
  -H "Content-Type: application/json" \
  -d '{"connection_string": "sqlite:///./demo.db"}'

---

🚨 Troubleshooting

Common Issues

Google API Error:

• Ensure your API key is valid (get free key at https://makersuite.google.com/app/apikey)
• Check the key is correctly set in .env
• Verify no extra spaces in the key
• Note: Free tier has 60 requests/minute, 1500/day limit

Database Connection Failed:

• Verify connection string format
• Check database is accessible from Docker container
• Use host.docker.internal for local databases on Mac/Windows
• Use 172.17.0.1 for local databases on Linux

Frontend Can't Connect to Backend:

• Ensure both containers are running: docker-compose ps
• Check backend logs: docker-compose logs backend
• Verify CORS settings in backend config

Port Already in Use:

• Change port mapping in docker-compose.yml
• Stop conflicting services: sudo lsof -i :8000

Logs

# View all logs
docker-compose logs

# View specific service
docker-compose logs backend
docker-compose logs frontend

# Follow logs in real-time
docker-compose logs -f backend

---

🎓 Academic Context

This project serves as a Final Year Project demonstrating:

1. Full-Stack Development: Complete web application with modern technologies
2. AI Integration: Practical application of Large Language Models
3. Software Architecture: Clean, modular, maintainable code structure
4. Security Best Practices: Input validation, SQL injection prevention
5. DevOps: Containerization and deployment automation
6. Database Management: Multi-database support with SQLAlchemy
7. API Design: RESTful API with comprehensive documentation

Learning Outcomes

• Building production-ready web applications
• Integrating AI/ML models into software systems
• Implementing security measures in database applications
• Container orchestration with Docker
• Modern frontend development with React
• Backend API development with FastAPI
• Database abstraction with SQLAlchemy

---

🤝 Contributing

Contributions are welcome! Please follow these guidelines:

1. Fork the repository
2. Create a feature branch: git checkout -b feature-name
3. Commit your changes: git commit -am 'Add feature'
4. Push to the branch: git push origin feature-name
5. Submit a pull request

---

📄 License

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

---

👨‍💻 Author

Samuel - Final Year Computer Science Student

---

🙏 Acknowledgments

• Google for Gemini Pro API (Free tier!)
• FastAPI team for excellent documentation
• LangChain community
• React and Vite communities
• SQLAlchemy maintainers

---

📞 Support

For questions or issues:

1. Check the Troubleshooting section
2. Review API documentation at http://localhost:8000/docs
3. Check application logs with docker-compose logs

---

🔮 Future Enhancements

• Support for query history and favorites
• Export results to CSV/JSON/Excel
• Query performance optimization suggestions
• Multi-language support
• Voice input for queries
• Data visualization (charts and graphs)
• Collaborative query sharing
• Query result caching
• Support for more LLM providers
• Advanced analytics and insights

---

Built with ❤️ for database enthusiasts and AI practitioners