How stickman1257/nl2sql_1jo works
1. Repository purpose
- This is a Python-based NL2SQL research workspace, organized as a template for building and comparing systems that convert natural language questions into SQL.
- The code is split into reusable package modules under src/nl2sql_agent/, with surrounding folders for datasets, prompts, experiments, docs, and outputs.
2. Top-level architectural boundaries
- The repo separates code (src/), runtime inputs (data/, configs/, prompts/), evaluation and research artifacts (experiments/, notebooks/), and documentation/media (docs/, assets/).
- That split is important because most runtime behavior is driven by external configuration and prompt files rather than hard-coded values.
3. Data layer
- data/raw/ holds benchmark datasets such as Spider and BIRD SQL.
- data/samples/ contains a sample SQLite database and NL2SQL logs for local testing.
- data/schema/ stores structured schema knowledge, including ecommerce_kb.json, which appears to be a knowledge-base representation of database schema metadata.
4. Schema knowledge subsystem
- src/nl2sql_agent/schema/ contains the schema knowledge base layer:
- kb_store.py for persistence/access,
- schema_kb.py for KB structure and retrieval,
- diagnosis.py for schema-related analysis.
- This subsystem is a central boundary between raw database schemas and the downstream SQL-generation logic.
5. Schema enricher pipeline
- src/nl2sql_agent/schema_enricher/ is a separate pipeline that inspects databases, selects informative columns, calls an LLM, filters logs, and updates the schema KB.
- Key roles:
- db_explorer.py discovers schema/content signals from a database,
- column_selector.py chooses columns to enrich,
- llm_caller.py invokes the model,
- kb_updater.py writes back enriched knowledge,
- pipeline.py orchestrates the flow.
- The presence of configs/dataset/enricher_config*.yaml and scripts/run_enricher.py indicates this is an executable preprocessing/enrichment step, not just library code.
6. SQL generation agent
- src/nl2sql_agent/generate_sql/ is the main NL2SQL inference path.
- It includes:
- agent.py for orchestration,
- db_search_tool.py for schema/database lookup,
- sql_writing_tool.py for SQL construction,
- llm_client.py for model access,
- bird.py and evaluate_bird.py for benchmark-specific handling/evaluation.
- This module likely consumes schema KB context and a user question, then produces SQL and benchmark outputs.
7. Answer-generation / execution-facing path
- src/nl2sql_agent/generate_answer/ suggests a second stage that turns SQL or intermediate reasoning into final answers, likely including database execution and response formatting.
- Files like data_sql.py, llm_io.py, qwen_model.py, and run_answer_agent.py indicate a model-facing runtime that may be distinct from pure SQL synthesis.
- This creates a boundary between “generate SQL” and “generate answer from SQL/results.”
8. Decomposition and query understanding
- src/nl2sql_agent/decomposition/ and the prompt folder prompts/subquery_decomposition/ indicate a decomposition-oriented architecture for breaking complex questions into subqueries or reasoning steps.
- src/nl2sql_agent/query_classifier/ contains training and feature extraction scripts, suggesting a separate analysis/training component for query-type classification or routing.
9. Prompt and config management
- prompts/ is organized by function: system, schema_refinement, subquery_decomposition, and answer_generation.
- configs/ separates dataset, model, and prompt configuration, which makes the system behavior reproducible across experiments and backends.
- The repo includes both CUDA and MLX enricher configs, so the architecture supports multiple local inference runtimes.
10. Evaluation and experimentation
- src/nl2sql_agent/evaluation/ plus experiments/ show that the repo is designed for systematic comparison of methods.
- experiments/baseline/ and experiments/ablation/ track variants such as:
- few-shot prompting,
- chain-of-thought prompting,
- removing decomposition,
- removing execution feedback,
- removing schema refinement.
- experiments/results/ stores benchmark outcomes for Spider and BIRD SQL.
11. Documentation and research workflow
- docs/ contains flow descriptions and integration notes, including schema KB feedback and local MLX validation.
- assets/ stores diagrams, figures, and presentation materials, which reinforces that this repo is both a working codebase and a research/reporting workspace.
12. Tests and quality boundary
- tests/test_kb_store.py and tests/test_schema_refinement.py show that coverage focuses on the schema KB and refinement pipeline, which are core architectural seams.
- This implies the most critical correctness risks are in schema persistence and schema-to-prompt refinement logic, not just SQL string generation.
13. Likely end-to-end data flow
- Raw benchmark DBs and sample DBs are enriched into schema KB artifacts.
- The NL2SQL agent reads the KB plus prompt/config context, optionally decomposes the query, searches relevant schema pieces, and generates SQL.
- SQL may then be executed or transformed into a final answer, with logs/results feeding evaluation and ablation experiments.
Main entry points: generate_sql SQL agent · schema_enricher Enricher · schema Schema KB · prompts Prompts
Components 20
Runtime inputs
Datasets, schemas, prompts, and configs that steer the system at runtime.
- Raw data · Benchmark corpora such as Spider and BIRD SQL.
data/raw - Sample DBs · Local SQLite database and NL2SQL logs for quick validation.
data/samples - Schema KB · Persisted schema knowledge base, including ecommerce_kb.json.
data/schema - Configs · Dataset, model, and prompt configuration surfaces.
configs - Prompts · System, decomposition, schema refinement, and answer-generation prompts.
prompts
Core code
Reusable Python package modules implementing enrichment, generation, and evaluation.
- Enricher · Database inspection and KB update pipeline.
src/nl2sql_agent/schema_enricher - Schema KB · KB storage, retrieval, and diagnosis boundary.
src/nl2sql_agent/schema - SQL agent · Main path for question-to-SQL generation.
src/nl2sql_agent/generate_sql - Answer agent · Executes or post-processes SQL/results into final answers.
src/nl2sql_agent/generate_answer - Decomposition · Breaks complex questions into subqueries or steps.
src/nl2sql_agent/decomposition - Query classifier · Training and feature extraction for query-type routing.
src/nl2sql_agent/query_classifier - Evaluation · Benchmark evaluation helpers and reporting hooks.
src/nl2sql_agent/evaluation - Run agent · CLI entrypoint for SQL generation workflows.
src/nl2sql_agent/run_generate_sql.py - Run enricher · CLI entrypoint for schema enrichment.
scripts/run_enricher.py - Tests · Focused tests for KB storage and schema refinement.
tests
Research workflow
Experiments, notebooks, and outputs used to compare variants and record results.
Connections 22
- Raw data → Enricher: schemas to enrich · Raw benchmark databases are inspected to build richer schema knowledge.
- Sample DBs → Enricher: local validation · Sample DBs and logs support local enrichment and debugging.
- Enricher → Schema KB: writes KB · Enrichment updates the persisted schema knowledge base artifacts.
- Configs → Enricher: runtime config · Dataset/runtime options control enrichment behavior.
- Prompts → Enricher: prompting · Prompt templates guide model-based schema enrichment.
- Schema KB → Schema KB: loads KB · The code layer reads and interprets persisted schema knowledge.
- Schema KB → SQL agent: schema context · Generated SQL relies on KB retrieval and schema diagnosis.
- Decomposition → SQL agent: subqueries · Decomposed questions can feed the SQL synthesis path.
- Query classifier → SQL agent: routes · Classification can steer the generation strategy or prompt path.
- Prompts → SQL agent: prompting · System and schema-refinement prompts shape SQL generation.
- Configs → SQL agent: model config · Model and dataset settings parameterize inference.
- SQL agent → Evaluation: benchmarks · Produced SQL is scored against benchmark suites.
- SQL agent → Answer agent: SQL/results · SQL output or intermediate reasoning can feed answer generation.
- Prompts → Answer agent: answer prompts · Answer-generation prompts shape the response stage.
- Tests → Schema KB: validates · Tests focus on KB storage and schema refinement correctness.
- Baselines → Results: compares · Baseline prompt strategies are recorded in benchmark results.
- Ablations → Results: compares · Ablation variants are tracked against benchmark outputs.
- Docs → Enricher: setup notes · Documentation captures enrichment setup and validation flows.
- Docs → Schema KB: integration notes · Docs explain schema KB feedback and integration details.
- Assets → Docs: figures · Assets supply diagrams and presentation media used by docs.
- Run agent → SQL agent: starts · CLI entrypoint launches the SQL generation workflow.
- Run enricher → Enricher: starts · CLI entrypoint launches schema enrichment.