Chat with SQL

A chatbot project that utilizes GPT-4o mini, Langchain, and SQLite to allow users to perform Q&A with SQL databases using natural language.

Architecture

Core Data Flow

┌─────────────────────────────────────────────────────────────────┐
│ USER INPUT │
│ (Natural Language Question) │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ LLM │
│ (OpenAI GPT-4o-mini via LangChain) │
│ │
│ • Load environment variables (API keys, config) │
│ • Initialize ChatOpenAI with temperature=0.0 │
│ • Parse user question │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ SQL GENERATION │
│ (create_sql_query_chain or SQL Agent) │
│ │
│ Option 1: Direct Chain │
│ • Generate optimized SELECT statement │
│ • Apply constraints (LIMIT, columns) │
│ │
│ Option 2: Intelligent Agent │
│ • List available tables │
│ • Fetch table schemas │
│ • Generate multi-step SQL if needed │
│ • Validate syntax before execution │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ DATABASE QUERY │
│ (SQLite via SQLDatabase utility) │
│ │
│ • SQLite Database (sqldb.db)
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ RESULTS │
│ (Raw Query Output from DB) │
│ │
│ • Retrieved data rows │
│ • Result metadata (row count, column info) │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ LLM FORMATTING │
│ (Answer formatting with PromptTemplate) │
│ │
│ Input: Question + SQL Query + Raw Results │
│ Process: LLM transforms raw data into natural language │
│ Output: Clear, formatted answer for user │
└──────────────────────────┬──────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────┐
│ USER OUTPUT │
│ (Formatted Response via Gradio UI) │
│ │
│ Display result to user in chat interface │
└─────────────────────────────────────────────────────────────────┘

Technology Stack

Layer	Technology	Purpose
LLM	OpenAI GPT-4o-mini	Language understanding & generation
Orchestration	LangChain	Chain management & tool integration
SQL Execution	SQLAlchemy	Database connection & query execution
Database	SQLite	Data storage (read-only)
UI	Gradio	Chat interface

Data Sources

1. Pre-loaded SQL Database (data/sqldb.db)
   • Chinook sample database
   • Contains Artist, Album, Customer, Invoice, etc. tables

Execution Modes

Mode 1: SQL Query Chain (Simple queries)

• Fast direct path: Question → SQL → Execute → Format
• Best for straightforward Q&A
• Lower token usage

Mode 2: SQL Agent (Complex queries)

• Intelligent exploration: List tables → Get schema → Generate SQL
• Error recovery and retry logic
• Multi-step query support
• Best for complex analytical questions

Security Note

⚠️Always use READ-ONLY database connections. The system:

• Restricts to SELECT statements only
• Validates SQL syntax before execution
• Limits table scope access
• Prevents DELETE, UPDATE, INSERT, or CREATE operations

Prerequisites

System Dependencies (macOS)

brew install sqlite

Python Requirements

• Python 3.11

Setup

1. Clone the repository

git clone git@github.com:variang/chat-with-sql.git
cd chat-with-sql

2. Create and activate virtual environment

python3.11 -m venv venv
source venv/bin/activate

3. Install dependencies

pip install -r requirements.txt

4. Set up environment variables

Create a .env file in the project root:

OPENAI_API_KEY=your_openai_api_keyOPENAI_MODEL_NAME=gpt-4o-mini

Usage

Option 1: Gradio Web Interface (Recommended)

The easiest way to interact with the chatbot is through the Gradio web interface.

Quick Start

1. Ensure you have the Chinook database set up:

sqlite3 data/sqldb.db
.read data/sql/Chinook_Sqlite.sql
.quit

2. Run the Gradio application:

python app.py

3. Access the web interface:
   • Open your browser and go to: http://localhost:7860
   • You should see the Chat with SQL interface

Features

• Auto-Database Discovery: All .db files in the data/ folder are automatically discovered and listed
• Database Selection: Choose from available databases using the dropdown selector
• Execution Modes: Switch between "SQL Agent" (default, more intelligent) and "SQL Chain" (faster, simpler)
• Chat Interface: Ask questions in natural language and get immediate responses
• SQL Query Visibility: Toggle to show/hide the generated SQL queries
• Chat History: View full conversation history with all questions and answers

---

Option 2: Jupyter Notebook

For development and testing, you can use the interactive Jupyter notebook.

1. Start Jupyter:

jupyter notebook

2. Open the notebook:

   • Navigate to notebooks/chat_with_sql.ipynb
   • Run cells sequentially to test different components

3. Features:

   • Test SQL query chains
   • Test SQL agents
   • Explore database schemas
   • Experiment with custom prompts

---

Chatbot User Interface

Dataset

• Diabetes dataset: Link
• Cancer dataset: Link
• Chinook database: Link

References

• YouTube Tutorial
• Langchain: introduction
• Gradio: Documentation
• OpenAI: Developer quickstart
• SQLAlchemy Documentation