Gen AI SQL Query Generator Assistant

An intelligent assistant capable of understanding user requirements in natural language, converting them into optimized SQL queries, explaining clauses, highlighting schema associations, predicting row execution impacts, and executing queries safely against PostgreSQL, MySQL, and SQLite.

Key Features

1. Natural Language Input: Type plain English prompts to generate SQL (e.g. "Show all IT employees earning more than 50000").
2. Schema Browser: View database tables, columns, constraints, and data types directly in the side panel.
3. Candidates Picker: Generates multiple alternative queries side-by-side if there's any ambiguity in the prompt.
4. Impact & Safety Analysis: Estimates row count effects (dry-runs using EXPLAIN) and rates safety risk (safe, medium, high) with explanation badges.
5. Execution & Visual Grid: Click to run the generated query and view output in a responsive tabular layout.
6. Dialect Compatibility: Automatically formats queries for SQLite, PostgreSQL, or MySQL depending on active connector settings.
7. History Panel: Keeps a local persistent log of executed queries and results.

---

Technical Stack

• Frontend: React (Vite) + TypeScript + Premium Vanilla CSS (neon-dark theme with glassmorphic cards and custom flexbox layouts).
• Backend: Node.js + Express + TypeScript + SQLite, Postgres (pg), and MySQL (mysql2) libraries.
• AI Engine: Gemini 2.5 Flash (via @google/genai unified SDK).

---

Directory Structure

sql-query-generator/
├── backend/
│   ├── src/
│   │   ├── services/
│   │   │   ├── database.service.ts   # Database connections & EXPLAIN queries
│   │   │   └── gemini.service.ts     # Gemini AI JSON prompt builder
│   │   ├── index.ts                  # API Router and active state cache
│   │   └── initDb.ts                 # Database seeder (Employee/Students/Dept)
│   ├── data.db                       # Pre-seeded SQLite database file (autocreated)
│   ├── history.json                  # Persistent query execution history logs
│   ├── package.json
│   └── tsconfig.json
│
├── frontend/
│   ├── src/
│   │   ├── App.tsx                   # Main React entry & controller
│   │   ├── App.css                   # Premium grid & sidebar styling
│   │   └── index.css                 # Typography & global glassmorphic design tokens
│   ├── package.json
│   └── tsconfig.json
│
├── package.json                      # Root npm orchestrator
├── start.ps1                         # Windows quick launcher script
└── README.md                         # This guide

---

How to Run the Project

Prerequisites

Make sure you have Node.js (v18+) installed.

Configuration

1. Obtain a Gemini API Key from Google AI Studio.
2. Open the backend/.env file.
3. Replace the placeholder value with your actual key:

   GEMINI_API_KEY=your_actual_api_key_here

Running the Application

Option A: Quick Launch (Windows)

Double-click or run the start.ps1 PowerShell script in your terminal:

./start.ps1

This script automatically launches both the backend and frontend dev servers in separate terminal windows.

Option B: Unified NPM Command

Run the following commands in the project root:

# Install all dependencies (root, backend, frontend)
npm run install:all

# Run both servers concurrently
npm run dev

Option C: Manual Launch

If you prefer running them in separate terminal tabs manually:

1. Backend:

   cd backend
   npm run dev

   (Runs on http://localhost:5000)

2. Frontend:

   cd frontend
   npm run dev

   (Runs on http://localhost:5173)

---

Zero-Config Mock Testing

By default, the application is pre-configured to automatically connect to a local SQLite database (data.db) seeded with:

• Employee table (salaries, hire dates, departments).
• Students table (CGPAs, majors, enrollment years).
• Department table (budgets, managers).

You can immediately test natural language inputs (e.g., "Find the top 3 computer science students with highest CGPA") without setting up any local database instance!

To connect to your own live database, go to the Connection tab in the sidebar and enter your Postgres or MySQL host credentials.