AGENTS.md · tobymao/sqlglot

Sections

1. About SQLGlot
2. Development Commands
3. Architecture Overview
4. Key Concepts
5. Common Usage Patterns
6. Development Guidelines
7. Important Files
8. Performance Considerations
9. SQLGlot Coding Rules

First lines (shown under the repo's MIT license)

# Contributing to [SQLGlot](https://github.com/tobymao/sqlglot/blob/main/README.md)
## About SQLGlot

SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine written in pure Python. It supports 31+ SQL dialects and can transpile between them while preserving semantics. The codebase is performance-critical despite being pure Python, with an optional mypyc-compiled C extension for speed improvements (`sqlglotc/`).

## Development Commands
Before contributing read CONTRIBUTING.md

### Installation
```bash
# Basic installation
make install

# Development installation (Python only, no C extension)
make install-dev

# Development installation with mypyc C extension
make install-devc

# Install pre-commit hooks
make install-pre-commit

# With uv (faster):
UV=1 make install-dev
```

### Testing
```bash
# Run all tests (pure Python, hides .so files during run)
make test

# Run all tests with mypyc C extension (builds extension first)
make testc

# Run only unit tests (skip integration tests, pure Python)
make unit

# Run only unit tests with C extension
make unitc

# Run specific test file
python -m unittest tests.test_expressions

# Run specific test class
python -m unittest tests.test_expressions.TestExpressions

# Run specific test method
python -m unittest tests.test_expressions.TestExpressions.test_alias
```

### Linting & Type Checking
```bash
# Run linter and formatter only
make style

# Run full checks (style + pure Python tests + C extension tests)
make check
```

### Benchmarks
```bash
# Run parsing benchmark
make bench

# Run optimization benchmark
make bench-optimize
```

## Architecture Overview

SQLGlot follows a classic compiler architecture with three main phases:

### 1. Tokenizer (`tokens.py`)
- Converts SQL strings into a sequence of tokens (lexical analysis)
- Pure Python implementation in `tokens.py`; core logic in `tokenizer_core.py` (mypyc-compiled when using `[c]` extra)
- Maps lexemes to `TokenType` enum values via `KEYWORDS` and `SINGLE_TOKENS` dictionaries
- Dialects can override tokenizer behavior by customizing these mappings

### 2. Parser (`parser.py`)
- Converts tokens into an Abstract Syntax Tree (AST)
- Uses recursive descent parsing approach
- Parsing methods follow `_parse_*` naming convention (e.g., `_parse_create()`, `_parse_select()`)
- Token matching methods: `_match()`, `_match_set()`, `_match_text_seq()`, `_match_texts()`
- Helper methods for common patterns: `_parse_csv()`, `_parse_wrapped()`, `_parse_wrapped_csv()`
- Maintains index/cursor with `_advance()` and `_retreat()` methods
- Falls back to `exp.Command` for unparseable SQL (preserves original text)

### 3. Generator (`generator.py`)
- Converts AST back to SQL strings
- Traverses AST recursively, generating SQL for each expression node
- Two ways to customize generation:
  - `TRANSFORMS` dictionary for single-line generations
  - `<expr_name>_sql()` methods for complex generations
- Helper methods: `expressions()`, `func()`, `rename_func()`
- Use `sep()` and `seg()` for proper whitespace/newline handling in pretty-printed output

### 4. Expressions (`expressions.py`)
- Defines all AST node types as Python classes inheriting from `Expression`
- Each expression represents a semantic SQL concept (e.g., `Select`, `Join`, `Column`)
- Expressions can be traversed using `.find()`, `.find_all()`, `.walk()`, `.transform()`
- Building SQL programmatically: use helper functions like `select()`, `from_()`, `where()`, etc.

### 5. Dialects (`dialects/`)
- 34 dialect implementations in `dialects/<dialect>.py`
- Each dialect subclasses base `Dialect` and can override Tokenizer, Parser, and Generator
- Base "sqlglot" dialect acts as a superset to minimize duplication
- Dialect customization via:
  - Feature flags (e.g., `SUPPORTS_IMPLICIT_UNNEST`)
  - Token sets (e.g., `RESERVED_TOKENS`)
  - `token -> Callable` mappings (e.g., `FUNCTIONS`, `STATEMENTS`)
  - `Expression -> str` mappings in Generator

### 6. Optimizer (`optimizer/`)
- Canonicalizes and optimizes queries while preserving semantics
- Applies sequential optimization rules (order matters!)
- Key rules:
  - `qualify`: Normalizes identifiers and qualifies all tables/columns (most important rule)
  - `annotate_types`: Infers data types throughout the AST
  - `pushdown_predicates`, `pushdown_projections`: Optimization rewrites
  - `simplify`: Simplifies boolean expressions and arithmetic
- Rules depend on schema information for best results
- Optimizer performs logical optimization only (not physical/performance)

### 7. Schema (`schema.py`)
- Represents database structure (tables, columns, types)
- Used by optimizer and lineage analysis
- `MappingSchema` takes nested dict: `{"table": {"col": "type"}}`

### 8. Lineage (`lineage.py`)
- Traces column-level lineage through queries
- Requires target query, upstream queries, and root table schemas
- Builds linked list of `Node` objects representing data flow
- Can visualize with `node.to_html()`

## Key Concepts

### The "sqlglot" Dialect
- Base dialect that accommodates common syntax across all dialects
- All other dialects extend this base
- When adding multi-dialect features, prefer adding to base dialect to avoid duplication
- Only add dialect-specific features to individual dialect classes

### AST-First Approach
- SQLGlot preserves _semantics_ not syntax
- Parse SQL → AST (semantic representation) → Generate SQL in target dialect
- This enables accurate cross-dialect transpilation
- Comments are preserved on best-effort basis
- See `posts/ast_primer.md` for detailed AST tutorial

### Testing Philosophy
- Comprehensive test suite in `tests/` directory
- Dialect-specific tests in `tests/dialects/`
- Tests are critical - "robust test suite" is a core feature
- Use `tests/fixtures/` for test data
- `tests/helpers.py` contains test utilities

### Parser/Generator Symmetry
- Parser: `token -> Callable` mappings (builds AST from tokens)
- Generator: `Expression -> str` mappings (builds SQL from AST)
- Customization follows similar patterns in both