albertkingdom/deepseek-coder-7b-text2sql-magicoder-lora

1---2base_model: deepseek-ai/deepseek-coder-7b-instruct-v1.53library_name: peft4model_name: deepseek-coder-7b-text2sql-magicoder-lora5tags:6- base_model:adapter:deepseek-ai/deepseek-coder-7b-instruct-v1.57- lora8- sft9- text-to-sql10- trl11license: other12license_name: deepseek13license_link: https://github.com/deepseek-ai/DeepSeek-Coder/blob/main/LICENSE-MODEL14pipeline_tag: text-generation15datasets:16- b-mc2/sql-create-context17- ise-uiuc/Magicoder-OSS-Instruct-75K18---19 20# Model Card for deepseek-coder-7b-text2sql-magicoder-lora21 22LoRA adapter fine-tuned from [deepseek-ai/deepseek-coder-7b-instruct-v1.5](https://huggingface.co/deepseek-ai/deepseek-coder-7b-instruct-v1.5) for text-to-SQL generation, trained with [TRL](https://github.com/huggingface/trl) SFTTrainer.23 24## Intended Use & Limitations25 26**Intended use**: generating a single SQL query from a natural-language question given a `CREATE TABLE` schema, in English, for single or lightly-joined relational databases similar in style to Spider / sql-create-context schemas.27 28**Limitations**:29- Not evaluated on production/adversarial inputs, non-English questions, or dialects outside SQLite-compatible syntax.30- Execution accuracy on Spider (60.8%) means roughly 2 in 5 generated queries on unseen schemas are still wrong — **always validate generated SQL before running it against a real database**, especially for destructive statements (this adapter was only trained/evaluated on read (`SELECT`) queries).31- **JOIN over-generation**: the SQL training data is almost entirely single-table, so on multi-table schemas the model tends to join every available table indiscriminately. This is the main source of exact-match errors on Spider.32- Code capability (HumanEval+) is retained better than the pure-SQL version but still ~4pp below the un-finetuned base model — for general-purpose coding tasks unrelated to SQL, the base model remains the stronger choice. See Results below.33 34## What's on `main` vs `pure-sql`35 36- **`main` (this version)** — trained on a 50/50 mix of SQL and general code-instruction data. Better SQL generalization to unseen schemas *and* better retention of general code ability than the pure-SQL version.37- **`pure-sql`** branch — the original version trained on 100% SQL data. Comparable in-distribution SQL accuracy, but noticeably worse code capability retention and worse generalization to unseen database schemas.38 39```python40# to load the pure-SQL version instead:41PeftModel.from_pretrained(model, adapter_id, revision="pure-sql")42```43 44## Why mix in code data45 46The pure-SQL version showed catastrophic forgetting of general code generation ability:47 48| Metric | Base | Pure-SQL SFT | Δ |49|---|---|---|---|50| HumanEval pass@1 | 52.0% | 40.0% | -12.0pp |51| HumanEval+ (999 edge cases) | 46.0% | 34.0% | -12.0pp |52 53Training data was rebalanced to 50% [b-mc2/sql-create-context](https://huggingface.co/datasets/b-mc2/sql-create-context) + 50% [ise-uiuc/Magicoder-OSS-Instruct-75K](https://huggingface.co/datasets/ise-uiuc/Magicoder-OSS-Instruct-75K) (interleaved batch-wise via `datasets.interleave_datasets`, `stopping_strategy="first_exhausted"`), 1 epoch.54 55The two datasets are nearly the same size (~70.7k vs ~75.2k), so a 50/50 ratio consumes essentially all of both — 141,771 training examples, 8,861 optimizer steps.56 57## Results58 59### Code capability retention (n=50, HumanEval/HumanEval+)60 61| Metric | Base | Pure-SQL SFT | **This version (mixed)** |62|---|---|---|---|63| HumanEval pass@1 | 52.0% | 40.0% (-12.0pp) | **44.0% (-8.0pp)** |64| HumanEval+ (plus) | 46.0% | 34.0% (-12.0pp) | **42.0% (-4.0pp)** |65 66### SQL generalization on unseen schemas (Spider 1.0, n=1034, real databases + official eval)67 68| Metric | Base | Pure-SQL SFT | **This version (mixed)** |69|---|---|---|---|70| Official Execution Accuracy | 39.9% | 50.4% | **60.8%** |71| Official Exact Match (structural) | 32.1% | 37.4% | **47.3%** |72 73Compared with the pure-SQL version, this version generalizes substantially better to unseen schemas (+10.4pp execution accuracy) while also retaining more code capability. Mixing in code data appears to act as a regularizer against overfitting to the narrow single-domain SQL distribution.74 75## LoRA configuration76 77```78r: 1679lora_alpha: 3280lora_dropout: 0.0581target_modules: [q_proj, k_proj, v_proj, o_proj]82quantization: 4-bit NF4 (QLoRA), bf16 compute83```84 85## Quick start86 87```python88import torch89from transformers import AutoModelForCausalLM, AutoTokenizer90from peft import PeftModel91 92base_model_id = "deepseek-ai/deepseek-coder-7b-instruct-v1.5"93adapter_id = "albertkingdom/deepseek-coder-7b-text2sql-magicoder-lora"94 95model = AutoModelForCausalLM.from_pretrained(96    base_model_id, torch_dtype=torch.bfloat16, device_map="auto"97)98tokenizer = AutoTokenizer.from_pretrained(base_model_id)99model = PeftModel.from_pretrained(model, adapter_id)  # main = mixed training version100 101messages = [{102    "role": "user",103    "content": """Given the database schema below, write a SQL query that answers the user's question.104Only output the SQL query. Do not add any explanation.105 106### Schema107CREATE TABLE users (id INT, name VARCHAR(100), email VARCHAR(100))108 109### Question110Find all users with gmail addresses"""111}]112 113inputs = tokenizer.apply_chat_template(messages, return_tensors="pt", add_generation_prompt=True).to(model.device)114outputs = model.generate(inputs, max_new_tokens=200, do_sample=False)115print(tokenizer.decode(outputs[0][inputs.shape[-1]:], skip_special_tokens=True))116```117 118The adapter was trained with exactly this instruction + `### Schema` / `### Question` layout wrapped in the DeepSeek chat template. Free-form prompts still work, but accuracy drops.119 120## Training procedure121 122Trained with SFT (TRL `SFTTrainer`) on an interleaved SQL + code instruction dataset, 1 epoch over 141,771 examples (8,861 steps), learning rate 1e-4 with linear decay and 3% warmup, effective batch size 16 (per-device 4 × gradient accumulation 4), max sequence length 1024, `paged_adamw_8bit`, bf16, 4-bit QLoRA, on a rented RTX 5090 (~12h). Final train loss 0.3986, eval loss 0.3437.123 124### Framework versions125 126- PEFT: 0.18.0127- TRL: 0.26.2128- Transformers: 4.57.3129- Datasets: 4.4.2130- PyTorch: 2.9.1+cu128131 132## License133 134This adapter is a derivative of [deepseek-ai/deepseek-coder-7b-instruct-v1.5](https://huggingface.co/deepseek-ai/deepseek-coder-7b-instruct-v1.5), which is released under the [DeepSeek Model License](https://github.com/deepseek-ai/DeepSeek-Coder/blob/main/LICENSE-MODEL) rather than a standard open-source license. Per that license, derivative models must carry forward at least the same use-based restrictions, so this adapter — and any model merged/derived from it — inherits them:135 136- No use for military purposes.137- No use that harms minors.138- No generation of false information intended to harm others.139- No creation of non-consensual personal identifiable information.140- No fully automated decision-making that adversely affects an individual's legal rights.141- No discrimination based on protected characteristics.142- See the [full license text](https://github.com/deepseek-ai/DeepSeek-Coder/blob/main/LICENSE-MODEL) (Attachment A) for the complete list.143 144Commercial use is otherwise permitted, consistent with the base model's license.145 146## Credits & Data Provenance147 148- **Base model**: [deepseek-ai/deepseek-coder-7b-instruct-v1.5](https://huggingface.co/deepseek-ai/deepseek-coder-7b-instruct-v1.5) (DeepSeek Model License)149- **[b-mc2/sql-create-context](https://huggingface.co/datasets/b-mc2/sql-create-context)** (CC-BY-4.0) — itself derived from [WikiSQL](https://github.com/salesforce/WikiSQL) and [Spider](https://yale-lily.github.io/spider); credit to both original sources per CC-BY-4.0 attribution terms.150- **[ise-uiuc/Magicoder-OSS-Instruct-75K](https://huggingface.co/datasets/ise-uiuc/Magicoder-OSS-Instruct-75K)** (MIT) — generated via the OSS-Instruct method using `gpt-3.5-turbo-1106`. Outputs are subject to [OpenAI's usage policies](https://openai.com/policies/usage-policies) in addition to the dataset's own MIT license.151 152## Citations153 154```bibtex155@misc{vonwerra2022trl,156	title        = {{TRL: Transformer Reinforcement Learning}},157	author       = {Leandro von Werra and Younes Belkada and Lewis Tunstall and Edward Beeching and Tristan Thrush and Nathan Lambert and Shengyi Huang and Kashif Rasul and Quentin Gallou{\'e}dec},158	year         = 2020,159	journal      = {GitHub repository},160	publisher    = {GitHub},161	howpublished = {\url{https://github.com/huggingface/trl}}162}163```164 165```bibtex166@article{yu2018spider,167	title   = {Spider: A Large-Scale Human-Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task},168	author  = {Yu, Tao and Zhang, Rui and Yang, Kai and Yasunaga, Michihiro and Wang, Dongxu and Li, Zifan and Ma, James and Li, Irene and Yao, Qingning and Roman, Shanelle and Zhang, Zilin and Radev, Dragomir},169	journal = {arXiv preprint arXiv:1809.08887},170	year    = 2018171}172```173 174```bibtex175@article{wei2023magicoder,176	title   = {Magicoder: Empowering Code Generation with OSS-Instruct},177	author  = {Wei, Yuxiang and Wang, Zhe and Liu, Jiawei and Ding, Yifeng and Zhang, Lingming},178	journal = {arXiv preprint arXiv:2312.02120},179	year    = 2023180}181```182