Text-to-SQL LLM

Fine-tune a small LLM on Apple Silicon to translate natural-language questions into executable SQLite queries — and quantify how much LoRA fine-tuning improves execution accuracy over a strong instruction-tuned base model.

Result (held-out test, 128 examples, Qwen3-4B-Instruct):

Metric	Base	Fine-tuned	Δ
SQL validity	100.0%	100.0%	—
Exact match	0.0%	89.1%	+89.1%
Execution accuracy	57.8%	94.5%	+36.7%
Error rate	42.2%	5.5%	−36.7%
Avg latency	0.68 s	0.77 s	+0.09 s

Requirements

• macOS with Apple Silicon
• Python 3.12
• mlx-lm (MLX-native, uses the unified memory / GPU)

python -m venv .venv
source .venv/bin/activate
pip install -e ".[eval,sql]"

The model is loaded from Hugging Face on first use (mlx-community/Qwen3-4B-Instruct-2507-4bit). For offline runs after the first download, set HF_HUB_OFFLINE=1.

Pipeline

1. Generate synthetic dataset — scripts/generate_dataset.py Template-parametrized questions (6 domains: retail, ecommerce, hr, medical, education, finance; 8 SQL patterns) → data/processed/synthetic.
2. Create evaluation databases — scripts/create_databases.py Deterministic SQLite DBs (data/databases/*_001.db), one per domain, with read-only enforcement and query timeouts.
3. Validate — scripts/validate_dataset.py Every gold SQL must execute correctly against its DB. Non-executable examples are dropped.
4. Split — scripts/split_dataset.py Schema-level split so no schema leaks between train/valid/test (data/processed/split/).
5. Baseline (zero-shot) — scripts/run_baseline.py Base model evaluated on the held-out test set → experiments/baseline/.
6. Prepare training data — scripts/prepare_training_data.py Projects split JSONL into mlx-lm chat format (data/processed/mlx/{train,valid,test}.jsonl).
7. Fine-tune (SFT + LoRA) — scripts/train_model.py Wraps mlx_lm.lora with configs/training.yaml. Adapter saved to experiments/run_001/adapters.
8. Evaluate fine-tuned model — scripts/evaluate_model.py Same test set as the baseline → experiments/run_001/evaluation/.
9. Error analysis — evaluation/error_analysis.py Categorizes remaining failures (wrong table / column / filter / …).
10. Ablations — scripts/run_ablations.py Dataset size, LoRA rank, prompt format → experiments/ablations/.

Key commands

# Baseline (base Qwen3-4B, zero-shot)
python scripts/run_baseline.py

# Fine-tune (LoRA rank 8, 1300 iters) — ~25 min on Apple Silicon
scripts/train_model.py  # or:
python -m mlx_lm.lora --config configs/training.yaml

# Evaluate the fine-tuned model
python scripts/evaluate_model.py

# Compare base vs fine-tuned, and run all ablations
python -c "from pathlib import Path; from evaluation.compare import compare, print_table; print_table(compare(Path('experiments/baseline/results.json'), Path('experiments/run_001/evaluation/results.json')))"
python scripts/run_ablations.py --run --evaluate    # trains + evaluates all

Ablation results

All runs use the same 1300-iteration budget and held-out test set; one factor varies per run.

Run	ExecAcc	Exact	ErrRate
baseline (base model)	57.8%	0.0%	42.2%
run_001 (rank 8, 846 ex)	94.5%	89.1%	5.5%
size_025 (211 ex)	87.5%	78.9%	12.5%
size_050 (423 ex)	96.1%	82.0%	3.9%
rank_016 (LoRA rank 16)	95.3%	89.8%	4.7%
prompt_verbose	93.8%	89.1%	6.2%

Findings

• LoRA SFT is the dominant factor: every fine-tuned run ≥ 87.5% execution accuracy vs 57.8% for the base model.
• 1/4 of the data is enough to reach 87.5%; 1/2 matches or beats full data (96.1% vs 94.5%); rank 16 ≈ rank 8; verbose prompt ≈ default. Differences are within ~1-example noise (0.8% on 128 examples).
• The 7 remaining failures on the best run are all valid SQL: 6 are wrong-table choices on underspecified questions (a synthetic-data ambiguity), 1 is a value-format mismatch (sick leave vs sick_leave).

Directory layout

configs/       YAML configs for baseline, training, evaluation, validation
data/          raw + processed datasets, generated SQLite databases
dataset/       synthetic dataset generation & validation code
evaluation/    metrics, SQL executor, baseline, compare, error analysis
scripts/       pipeline entry points (CLI)
training/      train.py / inference.py wrappers around mlx-lm
experiments/   baselines, run_001 (adapters/eval), ablations
api/           optional FastAPI inference service (pyproject: [api])
tests/         pytest suite

See plan.md for the full phase-by-phase build log.