LangSQL - AI-Powered SQL Assistant

Transform natural language into powerful, verified SQL queries with dual-layer AI architecture.

---

🏗️ Architecture & AI Pipeline Overview

LangSQL uses a three-tier microservices architecture with a Domain-Trained Model First, Cloud LLM QA Second approach:

[ User Request ]
       │
       ▼
[ React 18 Client ]  (Port 5173 - Monaco Editor, TF.js, Query Visualizations)
       │
       ▼ HTTPS / REST
[ Node.js API Gateway ]  (Port 5000 - Orchestrator, AES-256 Crypto, Dialect Registry)
       │
       ├────────────────────────────────────────┐
       ▼ (Step 1: Primary Inference)            ▼ (Step 2: Correctness & QA)
[ Django DRF AI Service ]                [ OpenRouter Cloud LLM ]
  - Port 8000                              - Nemotron-3 Super / Ultra
  - T5-LM-Large fine-tuned on Spider       - Reasoning Suppressed (<1s SLA)
  - Beam Search (k=5) + Schema Serialization - Validates syntax & target dialect
       │                                        ▲
       └────────────────────────────────────────┘

1. Primary Generation: The query is generated by an on-premise Seq2Seq Transformer (gaussalgo/T5-LM-Large-text2sql-spider) fine-tuned on 166 databases from Yale's Spider dataset.
2. Mandatory Inspection: The generated query candidate passes through an OpenRouter LLM Quality Assurance check to verify table/column existence against schema metadata, resolve dialect differences (PostgreSQL vs MySQL vs Trino), and score query confidence.
3. Resilient Failover: If the local ML service is unreachable, OpenRouter takes over generation seamlessly.

---

📋 Prerequisites

Ensure the following tools are installed on your machine before running:

• Node.js: v18.x or v20.x (Download Node.js)
• Python: v3.11 or v3.12 (Download Python)
• Git: (Download Git)
• Hardware: Minimum 8GB RAM recommended for local ML model inference.

---

🚀 How to Run the Project Locally (Step-by-Step)

Clone the repository to your computer:

git clone https://github.com/Yashparmar1125/LangSQL.git
cd LangSQL

---

Step 1: Set Up & Start the Django AI Microservice (Port 8000)

Open your first terminal window:

# 1. Navigate to the DRF service directory
cd DRF-Server/LangSQL

# 2. Create a virtual environment
python -m venv venv

# 3. Activate the virtual environment
# Windows (PowerShell):
.\venv\Scripts\Activate.ps1
# Windows (CMD):
.\venv\Scripts\activate.bat
# macOS / Linux:
source venv/bin/activate

# 4. Install dependencies (PyTorch CPU + Transformers + Django)
pip install -r requirements.txt
pip install torch transformers sentencepiece huggingface-hub --extra-index-url https://download.pytorch.org/whl/cpu

# 5. Create .env file
cp .env.example .env

# 6. Run the AI server
python manage.py runserver 127.0.0.1:8000

Note on first launch: Hugging Face will automatically download the gaussalgo/T5-LM-Large-text2sql-spider weights (~1.5GB) once into your local cache.

You can verify the service is running by opening: http://127.0.0.1:8000/api/health/ (returns {"status": "ok"}).

---

Step 2: Set Up & Start the Node.js API Gateway (Port 5000)

Open your second terminal window:

# 1. Navigate to the Server directory
cd Server

# 2. Install Node dependencies
npm install

# 3. Create .env file
cp .env.example .env

# 4. Configure your environment variables in Server/.env:
# Ensure OPENROUTER_API_KEY is populated with your key (get one free at https://openrouter.ai/keys)

# 5. Start the backend development server
npm run dev

The Node.js server will start on http://localhost:5000.

---

Step 3: Set Up & Start the React Frontend (Port 5173)

Open your third terminal window:

# 1. Navigate to the Client directory
cd Client

# 2. Install dependencies
npm install

# 3. Create .env file
cp .env.example .env

# 4. Start Vite development server
npm run dev

Open your browser and visit: http://localhost:5173.

---

🔐 Crucial Environment Variables Note

Important

The encryption salt SALT in Server/.env and VITE_SALT in Client/.env must match exactly:

SALT=a3f1c8d9e27b4f56a8e2d5c6f3a9e4b7f1d2c3e4a5b6c7d8e9f0a1b2c3d4e5f6
VITE_SALT=a3f1c8d9e27b4f56a8e2d5c6f3a9e4b7f1d2c3e4a5b6c7d8e9f0a1b2c3d4e5f6

This salt is used to derive AES-256 keys for zero-knowledge credential encryption between the browser and backend. If they differ, database connections will throw Malformed UTF-8 data errors.

---

🛠️ Tech Stack

• Client: React 18, Vite, TailwindCSS, Monaco Editor, Redux Toolkit, Framer Motion, Chart.js / Nivo.
• API Server: Node.js, Express, Axios, CryptoJS (AES-256), pg (PostgreSQL), mysql2, Trino Client.
• AI Microservice: Python 3.12, Django 5.1, Django REST Framework, PyTorch (CPU/CUDA), Hugging Face Transformers (T5-LM-Large-text2sql-spider), SentencePiece.
• Validation & Refiner: OpenRouter AI (Nemotron-3 models with suppressed reasoning).
• Metadata Database: MongoDB Atlas.

---

📄 License

This project is licensed under the MIT License.