# rehanali1092/nl-2-sql-compiler architecture

> Architecture diagram of the GitHub repository rehanali1092/nl-2-sql-compiler, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-30). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/rehanali1092/nl-2-sql-compiler
- Repository: https://github.com/rehanali1092/nl-2-sql-compiler

## Overview

This repository is a small, pipeline-driven compiler that translates natural-language database requests into SQL, executes them against SQLite, and returns results. The architecture is layered like a classic compiler: input normalization, parsing, semantic validation, optimization, SQL generation, execution, and history/logging.

1. **Entry point / orchestration**
   - `main.py` is the command-line front end.
   - It takes a user query, drives it through the compiler stages, and presents either generated SQL or execution results.
   - This is the main control boundary between the user and the compiler pipeline.

2. **Lexical analysis**
   - `lexer/tokenizer.py` converts free-form text into tokens.
   - `lexer/synonyms.py` supports normalization of natural-language variants into canonical terms, which is important because users may express the same intent in different wording.
   - This stage is where the project begins mapping “natural language” into a structured query language domain.

3. **Parsing / AST construction**
   - `parser/grammar.py` defines the supported query grammar.
   - `parser/parse_tree.py` holds the parse tree / AST representation used by later phases.
   - The parser is the main structural boundary: it turns token sequences into a query object with explicit clauses like SELECT, WHERE, ORDER BY, INSERT, UPDATE, and DELETE.

4. **Semantic analysis**
   - `semantic/analyzer.py` validates the parsed query against schema knowledge.
   - `semantic/symbol_table.py` provides the schema-aware symbol lookup used for checking table and column references.
   - `schema.json` is the key external schema source, so semantic validation depends on predefined database structure rather than discovering schema dynamically.

5. **Optimization**
   - `optimizer/optimizer.py` performs lightweight query simplification.
   - From the README and file naming, this is mainly aimed at removing redundant conditions and simplifying simple `AND`-based filters.
   - This stage sits after semantic validation, so it assumes the query is already structurally and schema-valid.

6. **SQL generation**
   - `codegen/sql_generator.py` translates the optimized internal representation into concrete SQL strings.
   - This is the compiler’s code generation boundary: everything before it is internal representation, and everything after it is executable SQL.

7. **Execution layer**
   - `executor/` is the runtime boundary that sends generated SQL to SQLite and handles results.
   - The primary database artifact is `student_data.db`, which is the target datastore for generated statements.
   - This layer is separate from SQL generation, so the system cleanly distinguishes “produce SQL” from “run SQL.”

8. **History / persistence of past queries**
   - `history/history_manager.py` and `query_history.db` suggest a lightweight persistence layer for recording query history.
   - This is not part of the compilation pipeline itself, but it is an important side subsystem for traceability and user session continuity.

9. **Error handling**
   - `utils/errors.py` defines compiler/runtime error types.
   - `utils/error_reporter.py` centralizes how failures are surfaced to the user.
   - This gives the pipeline a consistent failure path for lexical, parse, semantic, optimization, and execution errors.

10. **Validation and test boundary**
   - `tests/test_integration.py` indicates end-to-end coverage of the full NL → SQL → SQLite flow.
   - Because the system is a compiler pipeline, integration tests are especially important: they validate that all stages agree on the same internal contract.

11. **Data flow summary**
   - User text enters via `main.py`.
   - It is tokenized, parsed into a query structure, semantically checked against `schema.json` / symbol table, optionally optimized, converted to SQL, then executed on `student_data.db`.
   - Results and/or failures are reported back through the CLI, while query history can be persisted separately in `query_history.db`.

12. **Key architectural boundary**
   - The most important boundary in the repository is between **natural-language interpretation** and **database execution**.
   - Everything before SQL generation is compiler logic; everything after that is runtime/database interaction.
   - For an architecture diagram, these should be drawn as distinct layers with `schema.json` feeding semantic analysis and SQLite databases feeding execution/history.

Main entry points: [`main.py`](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/main.py) (CLI), [`semantic/analyzer.py`](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/semantic/analyzer.py) (Analyzer), [`lexer/tokenizer.py`](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/lexer/tokenizer.py) (Tokenizer), [`executor`](https://github.com/rehanali1092/nl-2-sql-compiler/tree/main/executor) (Executor)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_group_pipeline["Compiler pipeline"]
  node_main["CLI<br/>entrypoint<br/>[main.py]"]
  node_tokenizer["Tokenizer<br/>lexer<br/>[tokenizer.py]"]
  node_synonyms["Synonyms<br/>normalizer<br/>[synonyms.py]"]
  node_grammar["Grammar<br/>parser rules<br/>[grammar.py]"]
  node_parse_tree(("Parse tree<br/>AST<br/>[parse_tree.py]"))
  node_analyzer["Analyzer<br/>semantic checker<br/>[analyzer.py]"]
  node_symbol_table["Symbols<br/>schema lookup<br/>[symbol_table.py]"]
  node_optimizer["Optimizer<br/>rewriter<br/>[optimizer.py]"]
  node_sql_generator["SQL generator<br/>codegen<br/>[sql_generator.py]"]
end

subgraph group_group_runtime["Runtime and data"]
  node_executor["Executor<br/>runtime"]
  node_student_db[("Student DB<br/>sqlite database<br/>[student_data.db]")]
  node_history_manager["History<br/>persistence<br/>[history_manager.py]"]
  node_query_history_db[("Query log<br/>sqlite database<br/>[query_history.db]")]
end

subgraph group_group_support["Support"]
  node_schema_json["Schema<br/>[schema.json]"]
  node_errors["Errors<br/>error types<br/>[errors.py]"]
  node_error_reporter["Reporter<br/>error handling<br/>[error_reporter.py]"]
  node_integration_tests["Integration tests<br/>test suite"]
end

node_main -->|"input"| node_tokenizer
node_tokenizer -->|"normalize"| node_synonyms
node_synonyms -->|"prepare"| node_grammar
node_grammar -->|"build"| node_parse_tree
node_parse_tree -->|"validate"| node_analyzer
node_symbol_table -->|"resolve"| node_analyzer
node_schema_json -->|"load schema"| node_symbol_table
node_analyzer -->|"simplify"| node_optimizer
node_optimizer -->|"emit sql"| node_sql_generator
node_sql_generator -->|"execute"| node_executor
node_executor -->|"query db"| node_student_db
node_history_manager -->|"persist"| node_query_history_db
node_main -->|"record"| node_history_manager
node_errors -->|"report"| node_error_reporter
node_tokenizer -->|"fail"| node_errors
node_analyzer -->|"fail"| node_errors
node_executor -->|"surface"| node_error_reporter
node_main -->|"output"| node_error_reporter
node_integration_tests -.->|"exercise"| node_main
node_integration_tests -.->|"verify"| node_student_db

click node_main "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/main.py"
click node_tokenizer "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/lexer/tokenizer.py"
click node_synonyms "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/lexer/synonyms.py"
click node_grammar "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/parser/grammar.py"
click node_parse_tree "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/parser/parse_tree.py"
click node_analyzer "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/semantic/analyzer.py"
click node_symbol_table "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/semantic/symbol_table.py"
click node_optimizer "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/optimizer/optimizer.py"
click node_sql_generator "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/codegen/sql_generator.py"
click node_executor "https://github.com/rehanali1092/nl-2-sql-compiler/tree/main/executor"
click node_student_db "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/student_data.db"
click node_history_manager "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/history/history_manager.py"
click node_query_history_db "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/query_history.db"
click node_schema_json "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/schema.json"
click node_errors "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/utils/errors.py"
click node_error_reporter "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/utils/error_reporter.py"
click node_integration_tests "https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/tests/test_integration.py"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_main,node_tokenizer,node_synonyms,node_grammar,node_parse_tree,node_analyzer,node_symbol_table,node_optimizer,node_sql_generator toneBlue
class node_executor,node_student_db,node_history_manager,node_query_history_db toneAmber
class node_schema_json,node_errors,node_error_reporter,node_integration_tests toneMint
```

## Components

### Compiler pipeline

Natural-language query processing stages from input normalization through SQL generation.

- [CLI](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/main.py): entrypoint · Command-line orchestration for the full NL-to-SQL flow. · `main.py`
- [Tokenizer](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/lexer/tokenizer.py): lexer · Splits free-form input into tokens. · `lexer/tokenizer.py`
- [Synonyms](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/lexer/synonyms.py): normalizer · Canonicalizes natural-language variants. · `lexer/synonyms.py`
- [Grammar](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/parser/grammar.py): parser rules · Defines supported query structure and clauses. · `parser/grammar.py`
- [Parse tree](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/parser/parse_tree.py): AST · Internal query representation passed between compiler stages. · `parser/parse_tree.py`
- [Analyzer](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/semantic/analyzer.py): semantic checker · Validates queries against schema-aware rules. · `semantic/analyzer.py`
- [Symbols](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/semantic/symbol_table.py): schema lookup · Resolves table and column names during semantic analysis. · `semantic/symbol_table.py`
- [Optimizer](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/optimizer/optimizer.py): rewriter · Simplifies redundant conditions before SQL generation. · `optimizer/optimizer.py`
- [SQL generator](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/codegen/sql_generator.py): codegen · Converts the optimized internal query into SQL text. · `codegen/sql_generator.py`

### Runtime and data

Database execution, persistence, and external data artifacts.

- [Executor](https://github.com/rehanali1092/nl-2-sql-compiler/tree/main/executor): runtime · Runs generated SQL against SQLite and returns results. · `executor`
- [Student DB](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/student_data.db): sqlite database · Primary target database for generated statements. · `student_data.db`
- [History](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/history/history_manager.py): persistence · Records past queries for traceability and reuse. · `history/history_manager.py`
- [Query log](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/query_history.db): sqlite database · Persistent store for query history. · `query_history.db`

### Support

Errors, validation, and integration coverage that cut across the pipeline.

- [Schema](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/schema.json): schema file · External schema source used by semantic validation. · `schema.json`
- [Errors](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/utils/errors.py): error types · Shared compiler and runtime exception definitions. · `utils/errors.py`
- [Reporter](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/utils/error_reporter.py): error handling · Formats and surfaces failures to the user. · `utils/error_reporter.py`
- [Integration tests](https://github.com/rehanali1092/nl-2-sql-compiler/blob/main/tests/test_integration.py): test suite · End-to-end coverage of the NL-to-SQL pipeline. · `tests/test_integration.py`

## Connections

- CLI → Tokenizer: input · User text enters the compiler pipeline.
- Tokenizer → Synonyms: normalize · Tokens are normalized through synonym mapping.
- Synonyms → Grammar: prepare · Normalized language is interpreted by the grammar.
- Grammar → Parse tree: build · Grammar rules shape the parse tree / AST.
- Parse tree → Analyzer: validate · Parsed queries are checked for semantic correctness.
- Symbols → Analyzer: resolve · Schema-aware symbol lookup supports semantic checks.
- Schema → Symbols: load schema · Schema definitions feed symbol resolution.
- Analyzer → Optimizer: simplify · Validated queries proceed to optimization.
- Optimizer → SQL generator: emit sql · Optimized internal form is translated to SQL.
- SQL generator → Executor: execute · Generated SQL is handed to the runtime layer.
- Executor → Student DB: query db · SQL is executed against the SQLite database.
- History → Query log: persist · Query history is stored in SQLite.
- CLI → History: record · The CLI updates query history after processing.
- Errors → Reporter: report · Error types are surfaced through the reporting layer.
- Tokenizer → Errors: fail · Lexical failures use shared compiler errors.
- Analyzer → Errors: fail · Semantic validation errors use shared compiler errors.
- Executor → Reporter: surface · Runtime/database failures are reported to the user.
- CLI → Reporter: output · CLI presents generated SQL, results, or failures.
- Integration tests → CLI: exercise · End-to-end tests drive the CLI and full pipeline.
- Integration tests → Student DB: verify · Tests validate behavior against the database.