bonsai-1.7b-text2sql-onnx

ONNX export of Bonsai-1.7B fine-tuned for text-to-SQL (SQLite + DuckDB). Give it a database schema in the system message and it returns a single SQL query.

This is the ONNX sibling of the GGUF/Ollama release oamazonasgabriel/bonsai-1.7b-text2sql. It targets onnxruntime (CPU/GPU), browser/edge runtimes and non-Ollama servers.

Variants

File	Precision	Size	Notes
onnx/model.onnx	fp32	7.6 GB	reference export (text-generation-with-past, KV cache)
onnx/model_q4.onnx	int4	2.2 GB	recommended — weight-only MatMulNBits, correct + fastest on CPU
onnx/model_q8.onnx	int8	2.9 GB	weight-only int8; correct but larger and slower than int4

All graphs expose the KV cache (past_key_values.* -> present.*) and take input_ids, attention_mask and position_ids.

Do not use dynamic int8 (onnxruntime.quantization.quantize_dynamic): it quantizes activations and collapses this decoder to gibberish (verified). Use the weight-only int4 graph.

1-bit is not available for ONNX. ONNX Runtime's MatMulNBits supports 4/8-bit on CPU and 2/4-bit on WebGPU — never 1-bit — and Bonsai's GGUF Q1_0 format has no ONNX kernel. The official onnx-community/Bonsai-1.7B-ONNX model_q1.onnx is actually 2-bit (WebGPU). Even if 1-bit existed, merging the fine-tuned LoRA into 1-bit loses the fine-tune (ONNX has no ADAPTER directive). The int4 graph is the smallest supported artifact that keeps the fine-tune.

Usage

onnxruntime (Python)

from transformers import AutoTokenizer
import numpy as np
import onnxruntime as ort

repo = "impacte/bonsai-1.7b-text2sql-onnx"
tok = AutoTokenizer.from_pretrained(repo)
sess = ort.InferenceSession(f"{repo}/onnx/model_q4.onnx", providers=["CPUExecutionProvider"])

schema = 'CREATE TABLE "Payments" ("Payment_Method_Code" TEXT, "Amount" REAL);'
messages = [
    {"role": "system", "content":
        "You are an expert SQLite data analyst. Given a database schema, write a single "
        "valid SQLite query that answers the user's question.\n\n### Database schema\n" + schema},
    {"role": "user", "content": "What is the payment method that were used the least often?"},
]
prompt = tok.apply_chat_template(messages, tokenize=False, add_generation_prompt=True)
ids = tok(prompt, return_tensors="np")["input_ids"].astype(np.int64)
# ... greedy decode with the KV cache; see serve_onnx.py in the training repo.

The training repo ships a ready-made decoder:

PYTHONPATH=src python -m bonsai_sql.serve_onnx \
  --model <downloaded-repo>/onnx/model_q4.onnx \
  --schema 'CREATE TABLE t (a INT)' --question 'how many rows?'

transformers.js / browser

The graphs are standard ONNX; point transformers.js at onnx/model_q4.onnx (WebGPU) or onnx/model_q8.onnx (WASM). The tokenizer files are at the repo root.

Training

Base	prism-ml/Bonsai-1.7B-unpacked (Qwen3ForCausalLM, ChatML, Apache-2.0)
Method	LoRA (r=16, α=32) on attention + MLP projections; 17.4M trainable (1.0%)
Data	Spider (Yale / XLang NLP Lab, 8,025 rows) + MotherDuck duckdb-text2sql-25k (22,378 rows)
Steps	3,556 (2 epochs), train_loss 0.445, eval_loss 0.372
License	Apache-2.0 (model); datasets CC BY-SA 4.0

Evaluation

Greedy decoding, exact string match after normalisation, on the 1,519-example held-out split:

Model	Size	Overall	Spider	MotherDuck
Base Bonsai-1.7B	248 MB	3.4%	8.7%	1.5%
GGUF :q1_0 (Ollama)	318 MB	26.5%	39.9%	21.7%
GGUF :f16 (Ollama)	3.4 GB	28.8%	45.1%	22.9%
ONNX int4	2.2 GB	~22.5%*	45.5%*	13.8%*

* measured on a 40-example subset (CPU); Spider matches the F16 GGUF, the MotherDuck number is noisy at that sample size. Exact-match is strict — many "misses" are semantically correct (e.g. START WITH 1 vs START 1), so execution accuracy is higher.

Prompt format

system
You are an expert {dialect} data analyst. Given a database schema, write a single valid
{dialect} query that answers the user's question.
Rules:
- Use only tables and columns that appear in the schema.
- Match identifiers exactly as written in the schema.
- Return ONLY the SQL query, with no markdown fences and no explanation.

### Database schema
{schema}
user
{question}
assistant
{sql}

Limitations

• Trained on Spider (SQLite) and MotherDuck (DuckDB); other dialects are out of distribution.
• 1.7B parameters — complex multi-join / nested queries can still fail.
• The schema must be supplied by the caller; the model has no database access.
• Training data is CC BY-SA 4.0, so treat derived weights as share-alike.

Attribution

• Yu et al., Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task, EMNLP 2018.
• MotherDuck, duckdb-text2sql-25k.
• Prism ML, Bonsai-1.7B.