README.md

---
license: bsd-3-clause
library_name: transformers
pipeline_tag: other
base_model: Salesforce/codet5p-770m
language:
  - en
tags:
  - text-to-sql
  - sqlite
  - text2text-generation
  - encoder-decoder
  - codet5
  - codet5-plus
  - peft
  - lora
  - schema-aware
  - spider
  - natural-language-to-sql
model-index:
  - name: Schema-Aware Text-to-SQL CodeT5+ 770M
    results:
      - task:
          type: text2text-generation
          name: Schema-Aware Text-to-SQL
        dataset:
          name: Spider 1.0 and curated portfolio examples
          type: spider
          split: validation
        metrics:
          - type: execution_accuracy
            value: 60.99
            name: Validation Execution Accuracy
          - type: valid_sql_rate
            value: 94.90
            name: Validation Valid-SQL Rate
          - type: exact_match
            value: 42.04
            name: Validation Exact Match
      - task:
          type: text2text-generation
          name: Schema-Aware Text-to-SQL
        dataset:
          name: Held-Out Spider Evaluation
          type: spider
          split: test
        metrics:
          - type: execution_accuracy
            value: 56.92
            name: Held-Out Execution Accuracy
          - type: valid_sql_rate
            value: 92.69
            name: Held-Out Valid-SQL Rate
          - type: exact_match
            value: 38.37
            name: Held-Out Exact Match
---

# Schema-Aware Text-to-SQL CodeT5+ 770M

This repository contains a schema-aware Text-to-SQL encoder-decoder model based
on `Salesforce/codet5p-770m`.

The model converts a natural-language question and a serialized relational
database schema into one read-only SQLite query.

It was fine-tuned using PEFT and LoRA on Spider 1.0 together with a small set of
curated rule-based and synthetic portfolio examples. The final LoRA adapter was
merged into the base model so that the model can be loaded directly with
Hugging Face Transformers without requiring PEFT during inference.

## Model summary

| Property | Value |
|---|---|
| Selected experiment | CodeT5+ 770M LoRA r32 |
| Base model | `Salesforce/codet5p-770m` |
| Architecture | Encoder-decoder Transformer |
| Task | Schema-aware natural-language-to-SQL generation |
| Fine-tuning method | PEFT / LoRA |
| LoRA rank | 32 |
| LoRA alpha | 64 |
| LoRA dropout | 0.05 |
| Training precision | BF16 |
| Training hardware | NVIDIA GeForce RTX 5090 |
| Target SQL dialect | SQLite |
| Output policy | One read-only `SELECT` or `WITH` query |
| Validation examples | 628 |
| Held-out test examples | 1,040 |

## Final evaluation results

### Validation results

The selected model was evaluated on 628 validation examples.

| Metric | Result |
|---|---:|
| Execution accuracy | 60.99% |
| Valid-SQL rate | 94.90% |
| Exact match | 42.04% |
| Schema-linking precision | 88.89% |
| Schema-linking recall | 100.00% |
| Schema-linking F1 | 93.33% |
| Average generation latency | 1,104.62 ms |
| Median generation latency | 850.23 ms |
| P95 generation latency | 2,639.82 ms |

### Held-out test results

The final held-out evaluation used 1,040 examples.

| Metric | Result |
|---|---:|
| Execution accuracy | 56.92% |
| Valid-SQL rate | 92.69% |
| Exact match | 38.37% |
| Schema-linking precision | 96.67% |
| Schema-linking recall | 100.00% |
| Schema-linking F1 | 98.15% |
| Average generation latency | 1,184.16 ms |
| Median generation latency | 932.17 ms |
| P95 generation latency | 2,849.17 ms |

## Portfolio quality gate

The final project quality gate required:

| Requirement | Minimum | Achieved |
|---|---:|---:|
| Held-out execution accuracy | 50.00% | 56.92% |
| Held-out valid-SQL rate | 90.00% | 92.69% |
| Improvement over the base model | 3 percentage points | Passed |
| Held-out evaluation examples | 500 | 1,040 |

The model passed all required portfolio-readiness checks.

## Dataset

The complete training corpus contained 8,070 examples.

| Split | Examples |
|---|---:|
| Training | 6,402 |
| Validation | 628 |
| Held-out test | 1,040 |
| Total | 8,070 |

The corpus included:

- Spider 1.0 examples;
- curated rule-based examples;
- synthetic portfolio examples.

The evaluation process used database-aware splitting and leakage checks.

The completed leakage audit found:

- zero exact-record leakage;
- zero question leakage;
- no detected overlap between the training and held-out evaluation records.

Spider database files and private database files are not redistributed with this
model repository.

## Input format

The model expects one prompt containing:

- a task instruction;
- the database schema;
- table names;
- column names and data types;
- primary-key indicators;
- foreign-key relationships;
- the natural-language question;
- read-only SQL generation rules.

Example input:

```text
Task:
Generate one valid SQLite query for the given business question.

Database schema:
Table: sales

Columns:
- sale_id INTEGER PRIMARY KEY
- region TEXT
- sale_date TEXT
- sales_amount REAL

Question:
What is the total sales amount for each region?

Rules:
- Use only tables and columns present in the schema.
- Generate exactly one read-only SELECT or WITH query.
- Return SQL only.
```

## Output format

The expected output is SQL text without an explanation:

```sql
SELECT region,
       SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region;
```

## Ablation study

The final ablation study was performed on the complete 1,040-example held-out
test set.

| Condition | Exact match | Valid SQL | Execution accuracy |
|---|---:|---:|---:|
| Base model without schema | 0.00% | 0.29% | 0.00% |
| Base model with schema | 0.00% | 2.98% | 0.00% |
| Fine-tuned model with schema | 38.37% | 92.69% | 56.92% |
| Fine-tuned model with schema and conservative repair | 38.37% | 92.69% | 56.92% |

The experiment demonstrates that both domain fine-tuning and structured schema
conditioning were necessary for reliable Text-to-SQL generation.

## Repair-layer interpretation

The conservative repair condition produced the same results as the condition
without repair:

- repair attempt rate: 0.00%;
- repair success rate: 0.00%;
- execution-accuracy improvement: 0 percentage points.

The repair layer should therefore be described as a conservative SQL formatting
and safety mechanism rather than as an accuracy-improvement component.

No repair-related performance gain is claimed for this experiment.

## Basic usage

```python
from __future__ import annotations

import torch
from transformers import (
    AutoModelForSeq2SeqLM,
    AutoTokenizer,
)

MODEL_ID = (
    "anmol-unitmole/"
    "schema-aware-text-to-sql-codet5p-770m"
)

tokenizer = AutoTokenizer.from_pretrained(
    MODEL_ID,
    use_fast=True,
)

dtype = (
    torch.bfloat16
    if torch.cuda.is_available()
    and torch.cuda.is_bf16_supported()
    else torch.float32
)

model = AutoModelForSeq2SeqLM.from_pretrained(
    MODEL_ID,
    torch_dtype=dtype,
)

device = torch.device(
    "cuda"
    if torch.cuda.is_available()
    else "cpu"
)

model = model.to(device)
model.eval()

prompt = """
Task:
Generate one valid SQLite query for the given business question.

Database schema:
Table: sales

Columns:
- sale_id INTEGER PRIMARY KEY
- region TEXT
- sale_date TEXT
- sales_amount REAL

Question:
What is the total sales amount for each region?

Rules:
- Use only tables and columns present in the schema.
- Generate exactly one read-only SELECT or WITH query.
- Return SQL only.
""".strip()

inputs = tokenizer(
    prompt,
    return_tensors="pt",
    truncation=True,
    max_length=768,
)

inputs = {
    key: value.to(device)
    for key, value in inputs.items()
}

with torch.no_grad():
    generated = model.generate(
        **inputs,
        max_new_tokens=256,
        num_beams=4,
        do_sample=False,
        early_stopping=True,
    )

sql = tokenizer.decode(
    generated[0],
    skip_special_tokens=True,
)

print(sql)
```

## CPU usage

The model can be loaded on CPU, but inference will be significantly slower:

```python
import torch
from transformers import (
    AutoModelForSeq2SeqLM,
    AutoTokenizer,
)

MODEL_ID = (
    "anmol-unitmole/"
    "schema-aware-text-to-sql-codet5p-770m"
)

tokenizer = AutoTokenizer.from_pretrained(
    MODEL_ID
)

model = AutoModelForSeq2SeqLM.from_pretrained(
    MODEL_ID,
    torch_dtype=torch.float32,
)

model = model.to("cpu")
model.eval()
```

For interactive inference, a CUDA-capable GPU is recommended.

## SQL validation and execution safety

The model itself generates text and does not independently guarantee safe SQL.

The accompanying project applies a separate SQL safety layer that includes:

- destructive-keyword rejection;
- read-only query enforcement;
- single-statement enforcement;
- SQL comment rejection;
- schema-aware table validation;
- schema-aware column validation;
- handling of quoted SQLite string literals;
- read-only SQLite execution;
- query timeouts;
- output-row limits;
- structured execution-error reporting.

Generated SQL should always be validated before execution.

## Intended uses

This model is intended for:

- educational demonstrations;
- machine-learning portfolio projects;
- Text-to-SQL research;
- schema-aware generation experiments;
- public and synthetic SQLite databases;
- human-reviewed analytics assistance;
- encoder-decoder model demonstrations;
- LoRA fine-tuning demonstrations.

## Out-of-scope uses

The model is not intended for:

- autonomous execution on production databases;
- unrestricted access to private databases;
- destructive SQL operations;
- unsupervised business-critical analytics;
- financial decision automation;
- medical decision automation;
- legal decision automation;
- compliance or safety-critical applications;
- database administration.

## Limitations

The model can still generate SQL that is:

- syntactically invalid;
- valid but semantically incorrect;
- based on an incorrect table;
- based on an incorrect join relationship;
- missing a required filter;
- using an incorrect aggregation;
- using an incorrect grouping condition;
- using an incorrect ordering or limit;
- incompatible with SQL dialects other than SQLite.

Complex multi-table joins, correlated subqueries, nested aggregations and
semantically ambiguous questions remain challenging.

Exact-match accuracy is lower than execution accuracy because multiple SQL
queries can be textually different while returning equivalent results.

Human review is required before using generated SQL for real decisions.

## Evaluation notes

Execution accuracy compares the shape and normalized returned values of the
generated and reference queries.

Output aliases and SQLite-generated column labels are not required to match when
the returned values are equivalent.

The SQL validator masks quoted string literals before applying schema-aware
identifier checks. This prevents values such as `"JetBlue Airways"` or
`"Presentation"` from being incorrectly classified as column names.

## Training configuration

The selected experiment used approximately the following configuration:

| Parameter | Value |
|---|---:|
| Base model | `Salesforce/codet5p-770m` |
| Fine-tuning mode | LoRA |
| LoRA rank | 32 |
| LoRA alpha | 64 |
| LoRA dropout | 0.05 |
| Source length | 768 tokens |
| Target length | 256 tokens |
| Generation beams | 4 |
| Precision | BF16 |
| TF32 | Enabled |
| Gradient checkpointing | Enabled |
| Optimizer | Fused AdamW |
| Learning-rate schedule | Cosine |
| Evaluation strategy | Per epoch |
| Model-selection metric | Validation exact match |

## Training environment

| Component | Version or value |
|---|---|
| Operating system | Windows 11 |
| Python | 3.12.10 |
| PyTorch | 2.11.0 with CUDA 12.8 |
| Transformers | 4.57.6 |
| GPU | NVIDIA GeForce RTX 5090 |
| GPU memory | Approximately 31.84 GB |
| Compute capability | 12.0 |
| BF16 support | Yes |
| TF32 support | Yes |

## Reproducibility

The complete project includes:

- dataset preparation;
- corpus construction;
- database-aware data splitting;
- leakage auditing;
- schema serialization;
- prompt construction;
- LoRA training;
- full fine-tuning comparison;
- candidate-model evaluation;
- execution-accuracy measurement;
- SQL safety validation;
- ablation studies;
- error analysis;
- reporting;
- model merging;
- deployment preparation.

Source repository:

```text
https://github.com/unit-mole/encoder-decoder-projects
```

Project directory:

```text
01-schema-aware-text-to-sql-encoder-decoder
```

## Model selection

Five final candidates were evaluated:

1. CodeT5+ 770M LoRA rank 32;
2. CodeT5-base full fine-tuning;
3. CodeT5-base LoRA rank 64;
4. CodeT5-base LoRA rank 32;
5. FLAN-T5-base LoRA rank 32.

Candidate ranking used:

1. execution accuracy;
2. valid-SQL rate;
3. exact match;
4. average latency.

CodeT5+ 770M LoRA rank 32 was selected as the final model.

## Base-model attribution

This model is derived from:

```text
Salesforce/codet5p-770m
```

The base model and this merged derivative use the BSD 3-Clause license.

Users should also review the original base-model documentation and comply with
all applicable dataset, model and software licenses.

## Citation

A formal research-paper citation is not currently associated with this
portfolio model.

When referencing the implementation, cite the GitHub repository and this
Hugging Face model page.

## Disclaimer

This model is provided for research, educational and portfolio-demonstration
purposes.

The model authors do not guarantee the correctness, completeness, safety or
business suitability of generated SQL. Users are responsible for validating
queries and protecting all connected databases.