How tobymao/sqlglot works
1. Project shape
- This is a Python library centered on a single core package, sqlglot, with optional compiled acceleration in sqlglotc.
- It is not an application with servers or jobs; the architecture is a reusable SQL processing engine consumed as a library and CLI entry point.
2. Primary pipeline
- The main data flow is: SQL text in → tokenization → parsing → expression tree (AST) → optional optimization/analysis → SQL generation or execution.
- Most features in the repo are built around that pipeline, with dialect-specific behavior injected at parse and generation time.
3. Parsing subsystem
- sqlglot/tokenizer_core.py, tokens.py, and parsers/ implement lexical analysis and syntax parsing.
- parser.py and dialect-specific parser modules translate dialect-specific SQL into a shared internal AST.
- The parser is designed to accept a broad “SQLGlot dialect” plus named source dialects such as Spark, BigQuery, Snowflake, Trino, and others.
4. AST / expression model
- sqlglot/expressions/ defines the internal tree of SQL nodes: queries, DDL/DML, functions, datatypes, temporal expressions, JSON, constraints, etc.
- This AST is the central boundary between all subsystems: parser produces it, optimizer rewrites it, generator renders it, lineage/diff inspect it, and executor interprets it.
5. Dialect layer
- sqlglot/dialects/ contains one dialect class per supported SQL flavor.
- A dialect bundles a tokenizer, parser behavior, and generator behavior so the same AST can be read from one dialect and emitted in another.
- This is the key extensibility boundary; plugin dialects can be registered externally through entry points.
6. SQL generation / transpilation
- generator.py and generators/ turn AST nodes back into SQL text.
- Dialect-specific generator modules control quoting, type names, function mappings, syntax differences, and formatting details.
- Transpilation is simply AST generation followed by rendering into a target dialect.
7. Optimizer subsystem
- sqlglot/optimizer/ contains rule-based query rewrites such as qualification, normalization, predicate pushdown, projection pushdown, subquery/CTE elimination, join optimization, and simplification.
- The optimizer takes an AST plus optional schema metadata and returns a transformed canonical AST.
- This is the main semantic-analysis layer and is used when the library needs more than syntax translation.
8. Analysis and metadata utilities
- lineage.py, diff.py, schema.py, and transforms.py provide higher-level analysis on top of the AST.
- lineage traces how output columns depend on input tables/expressions.
- diff computes semantic differences between SQL trees.
- schema provides table/column type information needed by optimizer rules and type-sensitive transpilation.
9. Execution engine
- sqlglot/executor/ is a lightweight Python SQL execution engine for running parsed SQL against in-memory table data.
- It is intended for correctness, testing, and educational use rather than performance.
- The executor consumes the same AST produced by the parser, which keeps execution aligned with the library’s parsing and rewrite semantics.
10. Supporting utilities
- helper.py, serde.py, jsonpath.py, trie.py, time.py, and typing/ support common concerns like serialization, path parsing, keyword lookup, temporal handling, and dialect-specific type logic.
- errors.py defines the structured error model used by parser and transpiler failures.
11. Optional performance path
- sqlglotc/ packages a compiled variant via mypyc, giving a faster runtime path without changing the public architecture.
- The repository therefore has two execution modes: pure Python and compiled extension-backed, both using the same library surface.
12. Testing architecture
- tests/ is extensive and organized by subsystem: parser, generator, optimizer, executor, lineage, diff, schema, serde, tokens, transforms, and per-dialect behavior.
- Fixtures under tests/fixtures/ drive deterministic coverage for optimizer rewrites, JSONPath, and large SQL corpora.
- There is also a separate sqlglot-integration-tests area and CI workflows for sync and verification.
13. Tooling and release flow
- Packaging is Python-first (pyproject.toml, setup.py, setup.cfg) with CI workflows for package tests, publishing, benchmarks, and integration tests.
- pdoc/ generates API docs, and benchmarks/ measures parser and optimizer performance across representative SQL workloads.
- The repo’s maintenance workflow is code-centric: tests, docs, and benchmark gates validate changes to the core AST/dialect pipeline.
14. Architecture summary
- The essential boundary is the AST: everything funnels through it.
- SQL text is normalized into a dialect-aware tree, manipulated by optimizer/analysis passes, then either rendered back to SQL or executed in-memory.
- Dialects, optimizer rules, and the executor are the three main extension points around that central model.
Main entry points: expressions AST · __init__.py sqlglot · parser.py Parser · generator.py Generator
Components 20
Core library
Primary SQL pipeline centered on AST creation, rewrites, rendering, and analysis.
- sqlglot · Public package surface that ties together parsing, transpiling, optimization, and analysis APIs.
sqlglot/__init__.py - Tokens · Token definitions, keyword lookup, and lexical helpers.
sqlglot/tokens.py - Tokenizer · Converts SQL text into tokens.
sqlglot/tokenizer_core.py - Parser · Shared parser entry point that builds the internal SQL tree.
sqlglot/parser.py - AST · Central SQL expression model used by parsing, rewriting, rendering, and execution.
sqlglot/expressions - Generator · Renders AST nodes back into SQL text.
sqlglot/generator.py - Optimizer · Rule-based AST rewrites for qualification, normalization, pushdown, and simplification.
sqlglot/optimizer - Schema · Table and column metadata used by analysis and optimization.
sqlglot/schema.py - Lineage · Traces output columns back to source expressions and tables.
sqlglot/lineage.py - Diff · Computes semantic differences between SQL trees.
sqlglot/diff.py - Typing · Dialect-specific type logic and annotations.
sqlglot/typing - Helpers · Shared support for errors, serialization, JSONPath, time, tries, and general utilities.
sqlglot/helper.py
Dialect layer
Dialect-specific parsing, generation, and typing behavior.
- Dialect parsers · Per-dialect parsing rules and syntax specialization.
sqlglot/parsers - Dialects · Dialect classes that bundle tokenizer, parser, generator, and type behavior.
sqlglot/dialects - Dialect generators · Per-dialect SQL rendering and syntax mapping.
sqlglot/generators
Runtime paths
In-memory execution and optional compiled acceleration.
Tooling
Docs, benchmarks, packaging, and release support.
- Benchmarks · Parser and optimizer performance measurement harness.
benchmarks - Docs · API docs and explanatory posts for the library architecture.
pdoc
Tests
Subsystem-focused validation and fixture coverage.
- Tests · Subsystem-focused tests and integration coverage.
tests
Connections 28
- sqlglot → Tokenizer: exports · Public API exposes the tokenizer layer.
- sqlglot → Parser: exports · Public API exposes parsing entry points.
- sqlglot → Generator: exports · Public API exposes SQL rendering and transpilation.
- sqlglot → Optimizer: exports · Public API exposes rewrite and canonicalization routines.
- Tokenizer → Tokens: uses · Tokenizer relies on token and keyword definitions.
- Tokenizer → Parser: feeds · Token stream is consumed by the parser.
- Parser → Dialect parsers: extends · Shared parser delegates dialect-specific grammar rules.
- Dialect parsers → AST: builds · Dialect parsing produces the shared AST.
- Dialects → Dialect parsers: configures · Dialect classes select parser behavior and syntax.
- Dialects → Dialect generators: configures · Dialect classes select generator behavior and output rules.
- Dialects → Typing: configures · Dialect classes also control type-specific semantics.
- AST → Generator: renders · AST nodes are rendered back to SQL text.
- Dialect generators → Generator: implements · Per-dialect renderers specialize the shared generator.
- AST → Optimizer: rewritten by · Optimizer transforms the AST into canonical forms.
- Schema → Optimizer: annotates · Metadata informs qualification and type-sensitive rewrites.
- AST → Lineage: inspected by · Lineage walks the AST to trace dependencies.
- AST → Diff: compared by · Diff compares semantic structure between trees.
- AST → Executor: interpreted by · Executor evaluates the parsed tree in memory.
- Helpers → Tokenizer: supports · Helpers support parsing, errors, and lookup utilities.
- Helpers → Generator: supports · Helpers support serialization and formatting concerns.
- sqlglotc → sqlglot: accelerates · Compiled package accelerates the same public library surface.
- Benchmarks → Parser: measures · Benchmarks cover parsing workloads.
- Benchmarks → Optimizer: measures · Benchmarks cover optimization workloads.
- Docs → AST: documents · Documentation explains the AST and API surface.
- Tests → Parser: verifies · Parser tests validate syntax and dialect behavior.
- Tests → Generator: verifies · Generator tests validate SQL output.
- Tests → Optimizer: verifies · Optimizer tests validate rewrite rules.
- Tests → Executor: verifies · Executor tests validate in-memory evaluation.