Natural Language → SQL with AI confidence scoring, safety guardrails, multi-provider LLM support, and dynamic database connections.
┌─────────────────────────────────────────────────────────────────────────────┐
│ Execution accuracy: ~85% on 50-query golden set │
│ 100% of destructive queries blocked │
│ Multi-provider: OpenRouter / NVIDIA NIM / DeepSeek │
│ Zero paid infrastructure required │
└─────────────────────────────────────────────────────────────────────────────┘
- Features
- Architecture
- Data Flow
- Quick Start
- LLM Providers
- Guardrails — 7 Rules
- Confidence Scoring
- Security Model
- Dynamic Database Connections
- API Endpoints
- Running Evaluations
- Environment Variables
- Tech Stack
- Project Structure
- Natural Language → SQL: Ask questions in plain English, get optimized MySQL queries
- 7-Layer Guardrails: DDL block, DML block, multi-statement block, dangerous function detection, subquery depth limit, auto-LIMIT, comment stripping
- Confidence Scoring: Multi-signal validation — syntax, back-translation alignment, sanity checks, multi-query agreement
- Hallucination Detection: Back-translation check + result sanity verification catch bad queries before displaying results
- Schema-Aware: Auto-introspects your MySQL database, embeds schemas for smart table filtering (only sends relevant tables to the LLM)
- Multi-Provider LLM: OpenRouter (free), NVIDIA NIM (free credits), DeepSeek (near-free) — automatic fallback on failure
- Structured Output: Uses instructor library for reliable JSON responses from LLMs
- Dynamic DB Connections: Add any MySQL database through the UI — no code changes
- Query History & Feedback: Track queries, provide feedback to improve future accuracy
┌──────────────────────────────────────────────────────────────┐
│ CLIENT LAYER │
│ Next.js 16 frontend — query input, schema explorer, │
│ SQL viewer, results table, confidence gauge, history │
└──────────────────────────┬───────────────────────────────────┘
│ HTTP / REST
┌──────────────────────────▼───────────────────────────────────┐
│ API LAYER │
│ FastAPI — CORS, request logging, /health │
│ ├── POST /v1/query main query flow │
│ ├── GET /v1/schema DB introspection │
│ ├── GET /v1/history past queries │
│ ├── POST /v1/feedback correctness signal │
│ └── /v1/connections/* dynamic DB connection manager │
└──────────────────────────┬───────────────────────────────────┘
│
┌──────────────────────────▼───────────────────────────────────┐
│ CORE ENGINE │
│ ├── Schema extractor SQLAlchemy introspect + embeddings │
│ ├── SQL generator NL→MySQL via instructor + LLM │
│ ├── Guardrails 7-rule safety middleware │
│ ├── Executor read-only, 30s timeout │
│ └── Validator back-translation + sanity + multi │
└──────────────────────────┬───────────────────────────────────┘
│
┌──────────────────────────▼───────────────────────────────────┐
│ LLM LAYER │
│ Unified client — retry, fallback chain, structured output │
│ ├── OpenRouter DeepSeek / Llama / Gemma (free tier) │
│ ├── NVIDIA NIM Llama / Nemotron (free credits) │
│ ├── DeepSeek direct API (near-free) │
│ ├── Embeddings all-MiniLM-L6-v2 (local, free) │
│ └── instructor structured output library │
└──────────────────────────┬───────────────────────────────────┘
│
┌──────────────────────────▼───────────────────────────────────┐
│ DATA LAYER │
│ MySQL 8.0 │
│ ├── nlquery_app (read-write) — app system tables │
│ ├── nlquery_readonly (SELECT) — user query execution │
│ ├── query_history — session, SQL, results │
│ ├── guardrail_log — blocked query audit │
│ └── database_connections — encrypted user credentials │
└──────────────────────────────────────────────────────────────┘
A single user query travels through these steps in order:
User types question
│
▼
1. SCHEMA FILTERING
Embed the question → cosine similarity against table embeddings
→ select only relevant tables (e.g. 3 of 20)
│
▼
2. SQL GENERATION
Send filtered schema + few-shot examples to LLM
LLM returns structured JSON: { sql, explanation, confidence, tables_used }
Validate SQL syntax with sqlparse
│
▼
3. GUARDRAILS (7 checks)
DDL block → DML block → multi-statement block →
dangerous functions → subquery depth → auto-LIMIT → comment strip
Any rule fails → blocked, logged, user sees reason
│
▼
4. EXECUTION
Run query as nlquery_readonly (SELECT-only MySQL user)
Hard 30-second timeout
Capture results as DataFrame + EXPLAIN plan
│
▼
5. VALIDATION
Back-translation check → does the SQL answer the original question?
Sanity check → are results in a plausible range?
Multi-query validation → generate alternate SQL, compare results
│
▼
6. CONFIDENCE SCORE
Weighted average of 5 signals → 0–100%
│
▼
7. RESPONSE
SQL + explanation + results + confidence + warnings
Stored in query_history
# 1. Clone and configure
cp .env.example .env
# Edit .env — at minimum set OPENROUTER_API_KEY and ENCRYPTION_KEY
# 2. Start everything (MySQL + backend + frontend)
docker-compose up --build
# 3. Open the UI
open http://localhost:3000The system seeds 500 customers, 100 products, and 2000 orders automatically on first boot. To use your own database instead, add a connection through the UI's Connections panel.
# 1. Make sure MySQL 8.0 is running locally
# 2. Set up the database
python setup_local.py --mysql-root-password YOUR_ROOT_PASSWORD
# 3. Start backend
cd backend
python -m uvicorn main:app --host 0.0.0.0 --port 8000 --reload
# 4. Start frontend (separate terminal)
cd frontend
npm install
npm run devmake up # start all services
make down # stop all services
make seed # re-seed the demo database
make eval # run the 50-query evaluation suite
make test # run pytest unit tests
make logs # tail all service logsAll three providers expose an OpenAI-compatible API, so the same client code works with all of them — only the base_url and api_key change.
| Provider | Base URL | Default Model | Cost |
|---|---|---|---|
| OpenRouter | https://openrouter.ai/api/v1 |
deepseek/deepseek-chat-v3-0324:free |
Free |
| NVIDIA NIM | https://integrate.api.nvidia.com/v1 |
meta/llama-3.3-70b-instruct |
Free credits |
| DeepSeek | https://api.deepseek.com |
deepseek-chat |
~$0.001/1K tokens |
Fallback chain: if the primary provider fails (rate limit, timeout, error), the client automatically retries with the next provider in the chain. Up to 3 attempts per provider with exponential backoff.
Embeddings run locally using sentence-transformers with all-MiniLM-L6-v2. This model is ~80MB, runs on CPU, and produces embeddings in under 100ms. No API calls needed for embedding.
- OpenRouter (recommended): Sign up at openrouter.ai → free tier with DeepSeek, Llama, Gemma, Qwen models. Add a minimum balance ($0.01) for free model access.
- NVIDIA NIM: Sign up at build.nvidia.com → free API credits
- DeepSeek: Sign up at platform.deepseek.com → near-free pricing
Every SQL query passes through all rules in order. The first failure stops execution.
| # | Rule | What it checks | Action |
|---|---|---|---|
| 1 | DDL block | CREATE, ALTER, DROP, TRUNCATE |
Block + log to guardrail_log |
| 2 | DML block | INSERT, UPDATE, DELETE, MERGE, REPLACE |
Block + log |
| 3 | Multi-statement | More than one SQL statement (; separator) |
Block + log |
| 4 | Dangerous functions | LOAD_FILE, INTO OUTFILE, SLEEP, BENCHMARK, GET_LOCK, sys_exec, xp_cmdshell |
Block + log |
| 5 | Subquery depth | More than 3 levels of nested SELECT |
Block + log |
| 6 | Auto-LIMIT | No LIMIT clause present |
Inject LIMIT 1000 + warn |
| 7 | Comment strip | --, /* */, and # SQL comments |
Strip silently |
Rules 1–5 block execution entirely. Rule 6 modifies the query. Rule 7 sanitizes before any other check.
Five signals, each weighted independently:
| Signal | Weight | How measured |
|---|---|---|
| SQL syntax validity | 20% | sqlparse — valid or not |
| LLM self-reported confidence | 20% | Raw score from generation response |
| Back-translation alignment | 25% | Cosine similarity, original vs back-translated question |
| Result sanity check | 20% | Pass/fail with partial credit for minor issues |
| Multi-query agreement | 15% | Comparison of two independently generated queries |
Score interpretation:
- 75–100% → green, high confidence, results displayed normally
- 40–74% → yellow, moderate confidence, warning displayed
- 0–39% → red, low confidence, recommend rephrasing or manual review
Back-translation check: sends the generated SQL back to the LLM and asks "what question does this query answer?" Embeds both the original and back-translated question using cosine similarity. Below 0.6 = flagged hallucination.
Sanity check: validates result plausibility — COUNT > 1 row, dates within expected range, no negative revenue, NULL-heavy columns suggesting bad JOINs.
Multi-query validation: generates a second SQL using a different approach (subquery vs JOIN), runs both, compares results. Row count difference > 10% = low agreement.
Three layers of defense:
| Layer | Mechanism | What it prevents |
|---|---|---|
| 1. Guardrails | Parse and check SQL before execution | DDL, DML, injections, dangerous functions |
| 2. DB user permissions | nlquery_readonly has SELECT only |
Any write/drop even if guardrails miss it |
| 3. Read-only transaction | SET TRANSACTION READ ONLY per session |
Any accidental mutation |
Credential encryption: Dynamic database connection passwords are encrypted at rest using cryptography.fernet. The encryption key lives in the environment (ENCRYPTION_KEY), never in the database. Passwords are never returned in API responses.
SSRF protection: Connection manager blocks localhost, 127.0.0.1, and private IP ranges. Connection attempts time out after 5 seconds.
Users can connect NLQuery to any MySQL database through the UI — no code changes required.
Flow:
- Open the Connections panel and click "Add Connection"
- Fill in: name, host, port, database, username, password
- Click "Test Connection" — lightweight
SELECT 1with a 5-second timeout - On success, credentials are encrypted and stored in
database_connections - The connection appears in a dropdown in the main query UI
- All subsequent queries and schema introspection use the selected connection
Security:
- Connections to
localhost,127.0.0.1, and private IP ranges are blocked - Connection attempts time out after 5 seconds
- Passwords encrypted with Fernet before storage, never returned in API responses
- Each connection is scoped to a
session_id— users cannot see each other's connections
| Method | Endpoint | Description |
|---|---|---|
| POST | /v1/query |
Submit natural language query → get SQL + results |
| GET | /v1/schema |
Get database schema with types and relationships |
| GET | /v1/history |
Get query history for session |
| POST | /v1/feedback |
Submit correctness feedback on a query |
| POST | /v1/connections |
Save a new database connection |
| GET | /v1/connections |
List saved connections |
| DELETE | /v1/connections/{id} |
Delete a saved connection |
| POST | /v1/connections/{id}/test |
Test a saved connection |
| POST | /v1/connections/{id}/default |
Set connection as default |
| GET | /health |
Health check with DB and provider status |
| GET | /docs |
Interactive Swagger API documentation |
# Run the 50-query golden set evaluation
make eval
# Or manually:
docker-compose exec backend python -m eval.run_evalsThe evaluation tests:
- Simple SELECT with WHERE (10 cases)
- Multi-table JOINs (10 cases)
- GROUP BY aggregations (10 cases)
- Date range filters (5 cases)
- Ambiguous questions → clarification trigger (5 cases)
- Unanswerable questions → graceful failure (5 cases)
- Dangerous queries → guardrail block (5 cases)
Metrics: execution match %, guardrail block rate, clarification rate, hallucination flag rate, average confidence score, average latency.
# MySQL — application database
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DATABASE=nlquery
MYSQL_USER=nlquery_app
MYSQL_PASSWORD=
MYSQL_ROOT_PASSWORD=
MYSQL_READONLY_USER=nlquery_readonly
MYSQL_READONLY_PASSWORD=
# LLM providers (only OPENROUTER_API_KEY is required for basic use)
OPENROUTER_API_KEY= # free at openrouter.ai (add $0.01 balance)
NVIDIA_API_KEY= # free credits at build.nvidia.com
DEEPSEEK_API_KEY= # near-free at platform.deepseek.com
# LLM defaults
DEFAULT_PROVIDER=openrouter
DEFAULT_MODEL=deepseek/deepseek-chat-v3-0324:free
# Embedding model (downloaded automatically on first boot, ~80MB)
EMBEDDING_MODEL=all-MiniLM-L6-v2
EMBEDDING_CACHE_DIR=./cache/embeddings
# Safety limits
MAX_ROWS=1000
QUERY_TIMEOUT_SECONDS=30
# Credential encryption
# Generate with: python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"
ENCRYPTION_KEY=
# Frontend
NEXT_PUBLIC_API_URL=http://localhost:8000| Layer | Technology |
|---|---|
| Language | Python 3.11+ |
| Backend framework | FastAPI |
| Database driver | SQLAlchemy 2.0 + PyMySQL |
| Database | MySQL 8.0 |
| LLM structured output | instructor |
| SQL parsing | sqlparse |
| Embeddings | sentence-transformers (all-MiniLM-L6-v2, local) |
| Encryption | cryptography (Fernet) |
| Frontend | Next.js 16, React 19, Tailwind CSS v4 |
| API client | TanStack React Query |
| Containerization | Docker + docker-compose |
| Config | pydantic-settings + .env |
| Testing | pytest |
nlquery/
├── docker-compose.yml
├── .env.example
├── Makefile
├── setup_local.py # Local setup script (no Docker)
├── backend/
│ ├── Dockerfile
│ ├── requirements.txt
│ ├── main.py # FastAPI app entry point
│ ├── config.py # pydantic-settings config
│ ├── database/
│ │ ├── connection.py # SQLAlchemy engine + sessions
│ │ ├── schema_extractor.py # Auto-introspect + embeddings
│ │ ├── encryption.py # Fernet credential encryption
│ │ ├── connection_manager.py # Dynamic DB connection security
│ │ └── seed.sql # Demo data (500/100/2000 rows)
│ ├── llm/
│ │ ├── client.py # Unified multi-provider LLM client
│ │ ├── providers.py # OpenRouter, NVIDIA, DeepSeek configs
│ │ └── prompts.py # All prompt templates
│ ├── sql/
│ │ ├── generator.py # NL→SQL with structured output
│ │ ├── guardrails.py # 7-rule safety middleware
│ │ ├── executor.py # Read-only query execution
│ │ └── validator.py # Hallucination detection
│ ├── api/
│ │ ├── routes/ # query, schema, history, feedback, connections
│ │ └── models.py # Pydantic request/response models
│ ├── eval/
│ │ ├── golden_queries.json # 50 test cases
│ │ └── run_evals.py # Evaluation runner
│ └── tests/
│ └── test_guardrails.py # Pytest tests for all 7 rules
└── frontend/
├── Dockerfile
├── next.config.mjs
├── app/page.tsx # Main page with full API wiring
├── components/ # V2 components with real data
└── lib/api.ts # TypeScript API client
MIT