English | 繁體中文
Prepare database schemas, relevant columns, and actual data values as prompts that help large language models translate natural-language questions into SQL.
AAP-SQL implements the master's thesis An Efficient and Low-Cost Adaptive Augmented Prompting Framework for Text-to-SQL Generation. The repository brings together the research method, experiment tables, model training, relation discovery, BIRD evaluation, and a reusable Python core.
| Research dataset | SQL generation model | Thesis-reported Execution Accuracy |
|---|---|---|
BIRD Dev dev_20240627, 1,534 questions across 11 SQLite databases |
Gemini 2.5 Flash-Lite | 62.58% (960 / 1,534) |
Research · Quick start · Core API · Reproduction · Models · Citation
Text-to-SQL models need to know how tables connect, which columns relate to the question, and how filter values are actually stored. Including every column description and large amounts of data in a prompt increases token usage and can obscure the relevant information.
AAP-SQL preserves the complete schema structure while adding semantic descriptions and data values for relevant columns. For example, alice in a question can be aligned with the stored value Alice. Queries spanning multiple tables can use declared foreign keys and previously collected relationship cues.
| Technique | Purpose |
|---|---|
| Two-stage column retrieval | A fine-tuned retriever recalls 50 candidates; a reranker selects 10 core columns |
| Active value probing | Supplies actual values from relevant columns to help the model interpret query conditions |
| Literal Alignment | Aligns names and literals in the question with stored values, preserving their spelling and case |
| Relationship cues | Combines declared foreign keys with an offline graph of undeclared relationships to guide table joins |
---
config:
htmlLabels: false
---
flowchart TD
Q["Question<br/>+ Evidence"] --> R["Column retrieval: Top 50<br/>Reranking: Top 10"]
DB["Database<br/>SQLite / PostgreSQL"] --> S["Complete schema<br/>+ Foreign keys"]
DB --> V["Actual values<br/>from relevant columns"]
Q --> L["Literal Alignment"]
DB --> L
G["Offline graph<br/>Undeclared relations"] --> P["Build<br/>AAP-SQL prompt"]
R --> P
S --> P
V --> P
L --> P
Q --> P
P --> M["SQL generator<br/>supplied by the caller"]
M --> SQL["SQL output"]
The core prepares database context and calls the generator. The caller supplies the models, index, and LLM provider. The application or research runner validates and executes the resulting SQL.
Requires Python 3.12+. Install the package and run the offline example to see the core workflow:
git clone https://github.com/Tommyweige/AAP-SQL.git
cd AAP-SQL
python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install -e '.[test]'
python -m aap_sql.smoke
pytest -qWindows PowerShell commands
git clone https://github.com/Tommyweige/AAP-SQL.git
Set-Location AAP-SQL
py -3.12 -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -e ".[test]"
python -m aap_sql.smoke
pytest -qThe example uses a temporary SQLite database and synthetic model/generator adapters to demonstrate retrieval, value probing, literal alignment, and SQL generation. It prints the assembled prompt and Offline smoke passed on success.
AAPSQLPipeline provides two entry points:
| Method | Returns |
|---|---|
prepare(question, db_id, evidence="") |
The assembled prompt, selected columns, literal hints, and retrieval metadata |
generate(question, db_id, generator, evidence="") |
A cleaned SQL string produced by the supplied generator |
Place SQLite databases at databases/<db_id>/<db_id>.sqlite and specify the database root through DatabaseConfig. You can also supply BIRD-format column-description metadata through dev_tables_path.
For PostgreSQL, install .[postgres] and supply a PostgreSQLBackend through
DatabaseConfig(backend=backend, dev_db_path=None). The backend integration guide
covers read-only access, table allowlists, context hooks, error handling, and validation.
Python integration example and adapter interfaces
Pass your application's models, index, column mapping, and generator into the pipeline:
from pathlib import Path
from aap_sql import AAPSQLPipeline, DatabaseConfig
pipeline = AAPSQLPipeline(
database=DatabaseConfig(dev_db_path=Path("databases")),
model=column_retriever, # encode(texts, **kwargs)
index=column_index, # search(vectors, k) → distances, indices
mapping=column_mapping, # db_id, full_name, description
reranker=candidate_reranker, # predict(question/description pairs, **kwargs)
normalize=normalize_vectors, # Normalize vectors in place, e.g. faiss.normalize_L2
relation_path=None, # Optional: path to the offline relationship graph JSON
)
question = 'Find the person named "Alice".'
prepared = pipeline.prepare(question, "demo")
sql = pipeline.generate(question, "demo", generator=my_generator)The prompt retains the research version's ordering:
Schema → Foreign Keys → Columns → Probed Values
→ Literal Alignment → Evidence → Question → SQL
The application manages database paths, model loading, and LLM clients. When sharing a model across threads, serialize inference according to the model's concurrency requirements.
The thesis compares all methods on BIRD Dev dev_20240627 with Gemini 2.5 Flash-Lite and Evidence (Table 4-3):
| Method | Execution Accuracy | Total tokens |
|---|---|---|
| AAP-SQL | 62.58% | 3,320,035 |
| DIN-SQL, thesis reproduction | 61.08% | 47,320,913 |
| CHESS, thesis reproduction | 61.21% | 42,428,725 |
| Baseline + Evidence | 57.76% | 2,188,308 |
AAP-SQL uses 92.98% fewer tokens than DIN-SQL and 92.18% fewer than CHESS in this setting. It improves execution accuracy by 4.82 percentage points over Baseline + Evidence. The research overview includes training metrics, retrieval quality, generator comparisons, ablations, and per-question error transitions; structured results preserve the source tables.
Thesis decoding settings and local runner defaults
| Setting | Value |
|---|---|
| Provider / region | Vertex AI / global |
| Generator | Gemini 2.5 Flash-Lite |
| Temperature / max output | 0.0 / 2048 tokens |
| Local inference / validation workers | 24 / 4 (operational settings) |
| SQL timeout | 60 seconds |
To reproduce the experiment, prepare BIRD dev_20240627, the fine-tuned retriever and reranker, an offline relation graph, and Vertex AI Application Default Credentials (ADC). The full run generates SQL for 1,534 questions through Vertex AI, with usage billed by the provider.
The research reproduction guide covers training, graph generation, paths, and execution parameters. The method and validation record documents the implementation choices, including reconstructed numeric relation rules.
Fine-tuned weights are available on Hugging Face:
| Path | Contents |
|---|---|
src/aap_sql/ |
Reusable pipeline, prompt assembly, retrieval, and SQLite/PostgreSQL backends |
scripts/research/ |
BIRD batch generation, caching, parallel validation, and research configuration |
resources/bird_dev_20240627/ |
Two 798-column mappings, thesis results and relation-validation evidence |
tests/ |
Core, backend, research utility, and local training tests |
docs/ |
Research, reproduction, implementation, backend and engineering guides |
| Read this to… | Document |
|---|---|
| Understand the method and complete results | Research overview |
| Prepare data/models and run experiments | Reproduction guide |
| Trace thesis sections to code and current choices | Implementation notes |
| Inspect numeric mining assumptions and evidence | Relation audit |
| Integrate SQLite/PostgreSQL and context hooks | Backend guide |
Tung-Wei Pan. An Efficient and Low-Cost Adaptive Augmented Prompting Framework for Text-to-SQL Generation. Master's thesis, National Dong Hwa University, June 2026.
CITATION.cff provides GitHub citation metadata; a BibTeX entry is in the research overview.
See CONTRIBUTING.md for development checks and reporting experiments.
Dataset, mappings and model sources are described in the research asset guide. Downloaded data, weights and generated experiment outputs are managed locally.