README.md

---

language:
- en
license: apache-2.0
base_model: Qwen/Qwen2.5-Coder-1.5B-Instruct
tags:
- text-to-sql
- pipe-sql
- sqlglot
- tool-calling
- qwen2
datasets:
- spider
pipeline_tag: text-generation
model-index:
- name: pipe-sql-1.5b
  results:
  - task:
      type: text-to-sql
      name: Text-to-SQL
    dataset:
      type: spider
      name: Spider 1.0 Dev
    metrics:
    - type: execution_accuracy
      value: 60.66
      name: Execution Accuracy
---


# Pipe SQL 1.5B

A fine-tuned [Qwen2.5-Coder-1.5B-Instruct](https://huggingface.co/Qwen/Qwen2.5-Coder-1.5B-Instruct) model for generating **Pipe SQL** through multi-turn tool-calling conversations.

**GitHub**: [nittygritty-zzy/sqlglot](https://github.com/nittygritty-zzy/sqlglot)

## What is Pipe SQL?

Pipe SQL is a more readable SQL syntax that uses the `|>` (pipe) operator to chain operations in a linear, top-to-bottom flow:

```sql

FROM employees

|> WHERE department = 'Engineering'

|> AGGREGATE AVG(salary) AS avg_salary GROUP BY level

|> ORDER BY avg_salary DESC

```

This is transpiled to standard SQL via [sqlglot](https://github.com/tobymao/sqlglot), an open-source SQL parser and transpiler.

## Model Details

| Property | Value |
|----------|-------|
| **Base Model** | Qwen2.5-Coder-1.5B-Instruct |
| **Architecture** | Qwen2ForCausalLM |
| **Parameters** | 1.5B |
| **Hidden Size** | 1536 |
| **Layers** | 28 |
| **Attention Heads** | 12 (2 KV heads) |
| **Context Length** | 2048 tokens (training) |

## Design Documents

The full design and methodology behind this project is documented in the following design docs (also available in [docs/design/](https://github.com/nittygritty-zzy/sqlglot/tree/main/docs/design) on GitHub):

| Document | Description |
|----------|-------------|
| [Fine-Tuning Design Doc](docs/pipe-sql-fine-tuning-design-doc.md) | End-to-end system design for incremental pipe SQL synthesis and specialized fine-tuning of 1.5B-7B models |
| [Decompiler Design Doc](docs/pipe-sql-decompiler-design-doc.md) | Standard SQL to pipe SQL decompiler — the deterministic data generation component |
| [Validation Loop Design Doc](docs/pipe-sql-validation-loop-design-doc.md) | SQLite round-trip validation and feedback loop to ensure semantic correctness |
| [Training Reproduction Guide](docs/pipe-sql-training-reproduction-guide.md) | Step-by-step guide to reproduce the full training pipeline from scratch |

## Training

The model was fine-tuned using **QLoRA** on multi-turn tool-calling conversations for text-to-SQL generation.

### Training Data

Conversations were generated from the [Spider 1.0](https://yale-lily.github.io/spider) training set, where each conversation follows an agentic workflow:
1. **Explore** the database schema using `list_tables`, `describe_table`, and `sample_data` tools
2. **Write** pipe SQL queries using `execute_pipe_sql` and `validate_pipe_sql` tools
3. **Iterate** based on execution results until the query is correct

### Hyperparameters

| Parameter | Value |
|-----------|-------|
| **Method** | QLoRA (4-bit NF4) |
| **LoRA rank** | 16 |
| **LoRA alpha** | 32 |
| **LoRA dropout** | 0.05 |
| **Target modules** | q_proj, k_proj, v_proj, o_proj, gate_proj, up_proj, down_proj |

| **Epochs** | 3 |

| **Learning rate** | 2e-4 |

| **LR scheduler** | Cosine |

| **Warmup ratio** | 0.05 |

| **Batch size** | 2 (per device) |

| **Gradient accumulation** | 8 steps |

| **Weight decay** | 0.01 |

| **Loss** | Assistant-only (tool responses masked) |



## Evaluation Results



Evaluated on the **Spider 1.0 dev set** (1,034 questions) using an agentic benchmark pipeline. The agent autonomously explores database schemas via tool calls, writes pipe SQL, and iterates until correct — matching the training workflow.



### Execution Accuracy



| Metric | Value |

|--------|-------|

| **Execution Accuracy** | **60.66%** (626 / 1,032) |

| **Prediction Rate** | 99.7% (1,031 / 1,034) |

| **Total Questions** | 1,034 |

| **Gold Errors Excluded** | 2 |



### Context: Spider 1.0 Dev Set SOTA



| Model | Size | EX (Dev) | Method |

|-------|-----:|----------|--------|

| MiniSeek | — | 91.2% | Proprietary |

| DAIL-SQL + GPT-4 + SC | — | 86.6% | In-context learning |

| DIN-SQL + GPT-4 | — | 85.3% | In-context learning |

| SFT CodeS-7B | 7B | 85.4% | Fine-tuned |

| SFT CodeS-3B | 3B | 83.3% | Fine-tuned |

| SFT CodeS-1B | 1B | 77.9% | Fine-tuned |

| **Pipe SQL 1.5B (ours)** | **1.5B** | **60.7%** | **Fine-tuned, agentic tool-calling** |



Our model trails CodeS-1B by ~17 points. Key differences: (1) Pipe SQL generates a novel SQL dialect (pipe syntax) rather than standard SQL, adding a transpilation step; (2) the agentic tool-calling interface adds overhead vs. direct SQL generation; (3) our focus is on demonstrating the pipe SQL paradigm, not maximizing Spider accuracy. Sources: [Spider leaderboard](https://yale-lily.github.io/spider), [CodeS (Li et al., 2024)](https://arxiv.org/abs/2402.16347).



### Detailed Breakdown



| Status | Count | % of Total | Description |

|--------|------:|------------|-------------|

| **Match** | 626 | 60.5% | Predicted SQL produces identical results to gold SQL |

| **Mismatch** | 209 | 20.2% | SQL executes but results differ from gold |

| **Execution Error** | 170 | 16.4% | Transpiled SQL fails to execute against SQLite |

| **Transpile Error** | 24 | 2.3% | Pipe SQL cannot be transpiled to standard SQL |

| **No Prediction** | 3 | 0.3% | Agent did not produce a pipe SQL query |

| **Gold Error** | 2 | 0.2% | Reference gold SQL fails (excluded from denominator) |



### Evaluation Methodology



1. The TypeScript agent runs each question through a multi-turn tool-calling loop (max 10 turns, 120s timeout)

2. The agent's final `execute_pipe_sql` call is extracted as the predicted pipe SQL

3. Predicted pipe SQL is transpiled to standard SQL using `sqlglot.transpile()`

4. Both predicted and gold SQL are executed against the Spider SQLite databases

5. Result sets are compared using order-insensitive set comparison with numeric tolerance



> **Note**: This is an **in-distribution** evaluation — the model was trained on Spider training data, and the dev set uses the same 20 databases.



## Tools



The model was trained to use 5 tools in a multi-turn conversation:



| Tool | Description |

|------|-------------|

| `list_tables` | List all tables in a database |
| `describe_table` | Get column names, types, and constraints for a table |
| `sample_data` | Retrieve sample rows from a table |
| `execute_pipe_sql` | Execute a pipe SQL query against the database |
| `validate_pipe_sql` | Validate pipe SQL syntax without executing |

## Usage

### Chat Template

The model uses a custom chat template with `<tool_call>` tags for tool invocations:

```

<|im_start|>assistant

Let me explore the database first.

<tool_call>

list_tables({"db_id": "concert_singer"})

</tool_call><|im_end|>

```

Tool responses are formatted as:

```

<|im_start|>user

<tool_response>

Tables in database 'concert_singer':

- stadium

- singer

- concert

- singer_in_concert

</tool_response><|im_end|>

```

### Inference

For inference with the correct chat template, see the [evaluation server code](https://github.com/nittygritty-zzy/sqlglot/tree/main/pipe_sql/evaluation/server) on GitHub.

## Reproducing the Benchmark

### Prerequisites

- **GPU**: NVIDIA GPU with >= 6 GB VRAM (model runs in float16)
- **Python**: 3.11+ with pip/uv
- **Node.js**: 18+ with npm
- **Disk**: ~1 GB for Spider databases, ~3 GB for model weights

### Step 1: Clone the Repository

```bash

git clone https://github.com/nittygritty-zzy/sqlglot.git

cd sqlglot

```

### Step 2: Set Up Python Environment

```bash

# Create virtual environment

uv venv .venv --python 3.11

source .venv/bin/activate       # Linux/macOS

# source .venv/Scripts/activate # Windows (Git Bash)



# Install sqlglot (editable)

uv pip install -e .



# Install evaluation server dependencies

uv pip install fastapi uvicorn pydantic



# Install PyTorch with CUDA support

uv pip install torch --index-url https://download.pytorch.org/whl/cu126



# Install model loading dependencies

uv pip install transformers accelerate

```

Verify CUDA:
```bash

python -c "import torch; print(torch.cuda.is_available(), torch.cuda.get_device_name(0))"

# Expected: True NVIDIA GeForce RTX ...

```

### Step 3: Download Spider 1.0 Dataset

The benchmark uses Spider 1.0 dev set (1,034 questions across 20 SQLite databases).

```bash

# Install gdown for Google Drive downloads

uv pip install gdown



# Download and extract Spider 1.0 (~1 GB)

bash scripts/setup_data.sh

```

Verify:
```bash

ls data/spider/dev.json           # 1,034 questions

ls data/spider/database/ | wc -l  # ~166 databases (20 used by dev set)

```

### Step 4: Download the Model

```bash

# Option A: Use huggingface_hub (recommended)

pip install huggingface_hub

python -c "

from huggingface_hub import snapshot_download

snapshot_download('nittygritty-zzy/pipe-sql-1.5b', local_dir='pipe_sql/finetuning_output/merged')

"



# Option B: Use git-lfs

git lfs install

git clone https://huggingface.co/nittygritty-zzy/pipe-sql-1.5b pipe_sql/finetuning_output/merged

```

### Step 5: Install Node.js Agent Dependencies

```bash

cd pipe_sql/evaluation/agent

npm install

cd ../../..

```

### Step 6: Run the Benchmark

#### Option A: Full Pipeline (Recommended)

```bash

# Run all 1,034 questions (takes ~2 hours on RTX 4080)

bash pipe_sql/evaluation/run_all.sh



# Smoke test with 5 questions first

bash pipe_sql/evaluation/run_all.sh --limit 5

```

This script:
1. Starts the Python evaluation server (model inference + tool execution)
2. Waits for the server to be ready
3. Runs the TypeScript agent benchmark
4. Evaluates results and prints execution accuracy

#### Option B: Run Components Separately

**Start the evaluation server:**
```bash

# Default: loads model from pipe_sql/finetuning_output/merged/

python -m pipe_sql.evaluation.server.app



# Custom model path:

MODEL_PATH=path/to/model python -m pipe_sql.evaluation.server.app

```

Wait for `Server ready` in the logs, then in a separate terminal:

**Run the agent benchmark:**
```bash

cd pipe_sql/evaluation/agent

npx tsx src/main.ts --benchmark           # All 1,034 questions

npx tsx src/main.ts --benchmark --limit 5 # Smoke test

```

**Run single question interactively:**
```bash

cd pipe_sql/evaluation/agent

npx tsx src/main.ts "How many singers do we have?" concert_singer

```

**Evaluate results:**
```bash

python pipe_sql/evaluation/evaluate.py --results pipe_sql/output/results.json

```

### Step 7: Review Results

Results are saved to `pipe_sql/output/`:

| File | Description |
|------|-------------|
| `results.json` | Agent predictions with conversation traces |
| `eval_results.json` | Per-question evaluation details (match/mismatch/error) |
| `eval_summary.json` | Aggregate metrics |

### Configuration

| Environment Variable | Default | Description |
|---------------------|---------|-------------|
| `MODEL_PATH` | `pipe_sql/finetuning_output/merged` | Path to merged model directory |
| `SPIDER_DB_DIR` | `data/spider/database` | Spider database directory |
| `SPIDER_DIR` | `data/spider` | Spider data directory (contains dev.json) |
| `PORT` | `8000` | Evaluation server port |
| `SERVER_URL` | `http://localhost:8000` | Agent to server connection URL |
| `OUTPUT_DIR` | `pipe_sql/output` | Agent output directory |

### Troubleshooting

**Server fails to load model**: Ensure `pipe_sql/finetuning_output/merged/` contains `config.json`, `model.safetensors`, and `tokenizer.json`. If using a different path, set `MODEL_PATH`.

**CUDA out of memory**: The 1.5B model needs ~3 GB VRAM in float16. Close other GPU processes or use `CUDA_VISIBLE_DEVICES=0` to select a specific GPU.

**Agent produces garbled tool calls**: The 1.5B model sometimes generates garbled special tokens instead of proper `<tool_call>` tags. The inference server includes fallback parsing for bare function calls — this is handled automatically.

**Spider databases not found**: Run `bash scripts/setup_data.sh` to download Spider 1.0. The script downloads from Google Drive via `gdown`.

## Limitations

- Trained and evaluated only on Spider 1.0 (SQLite databases)
- Context window limited to 2,048 tokens during training
- The 1.5B model may generate garbled special tokens instead of proper `<tool_call>` tags — the inference server includes fallback parsing for bare function calls
- Performance on out-of-distribution databases (different schemas/domains) has not been extensively tested
- This is an in-distribution evaluation; real-world performance on unseen databases will likely be lower

## License

This model is released under the [Apache 2.0 License](https://www.apache.org/licenses/LICENSE-2.0), consistent with the base Qwen2.5-Coder model license.