DOCSEARCHbyAlgolia
Submit Docs

syntaqlite

repository·main·Indexed 19 days ago

https://github.com/lalitmaganti/syntaqlite

A high-fidelity SQLite SQL parser, formatter, and validator built on SQLite's Lemon-generated grammar and tokenizer. It provides exact parity with SQLite behavior, including support for specific versions and compile-time flags. Features include an LSP for editors (VS Code, Claude Code), a Python library for AST parsing and schema analysis, and a CLI for deterministic formatting and validation.

Tokens
105.1K
Snippets
338
Records
466
Agent score
69%

What's inside syntaqlite

  1. What is syntaqlite?

    main

    syntaqlite is a parser, formatter, validator, and language server for SQLite SQL. It is built using SQLite's own grammar and tokenizer to ensure high fidelity.

    Key capabilities include:

    • Validation: Ensures SQL is valid according to specific SQLite versions and compile-time flags.
    • Formatting: Provides SQL formatting services.
    • Version Awareness: Can reject syntax that a specific target SQLite version does not support.
    • Flag Awareness: Can enable or disable optional syntax based on compile-time flags.
    • Cross-language Validation: (Experimental) Ability to validate SQL embedded within other programming languages.

    Note: The project is currently in version 0.x. APIs and CLI flags are subject to change before the 1.0 release.

  2. How syntaqlite compares to other SQL tools

    main

    Syntaqlite is designed specifically for SQLite-specific SQL, focusing on syntax that often trips up generic SQL parsers (e.g., UPSERT, RETURNING, STRICT tables, window frames with EXCLUDE, and recursive CTEs).

    It is benchmarked against other tools in three main categories:

    1. Parsing: Accuracy in handling advanced SQLite syntax and speed of parsing.
    2. Formatting: Ability to perform round-trip semantic preservation (ensuring formatted SQL produces the exact same EXPLAIN bytecode as the original).
    3. Analysis: Static semantic analysis to catch errors like unknown tables, bad column references, or function arity mismatches without requiring a live database connection.

  3. How macros are handled during formatting

    main

    Syntaqlite uses a dual-track system for macros: the parser sees expanded SQL, but the formatter reconstructs the macro syntax.

    Macro Definitions

    When you define a macro (e.g., CREATE MACRO foo(x) AS SELECT $x), the system uses probe-based parsing to infer the body type. It tries different synthetic wrappers (like SELECT {body} or {body}) to determine if the body is a statement, expression, or table expression. This allows the formatter to apply the correct structural formatting to the macro body.

    Macro Invocations

    During tokenization, macro calls are recorded as MacroRegions. The expanded tokens retain source positions pointing back to the original macro arguments.

    When formatting, the system classifies macro arguments into two types:

    1. Structural: If the expanded tokens for an argument form a single continuous AST subtree, the formatter treats it as a structural argument and applies standard Wadler-style formatting.
    2. Raw Text: If the expanded tokens span multiple unrelated subtrees, the formatter treats it as unstructured and emits the original source text from that argument to ensure correctness.

    This classification happens automatically at format time based on the AST structure.

  4. Configure keyword casing in the formatter

    main

    The formatter identifies keywords via the parser and applies casing during the rendering phase. Text nodes (such as identifiers, literals, and table names) are never modified.

    • Default: Keywords are cased as upper (e.g., select 1 becomes SELECT 1;).
    • Lower: Use keyword-case = "lower" to render keywords in lowercase (e.g., SELECT 1 becomes select 1;).

  5. Understand SQLite grammar evolution and token classes

    main

    The SQLite grammar (parse.y) has grown significantly (from 326 rules in 3.12.2 to 411 in 3.51.2) but remains semantically additive. Rule removals are typically refactorings (renaming, compression, or restructuring) rather than language narrowing.

    Notable changes to token classes:

    • 3.42: Introduced the idj token class, which merges bare identifiers (id), INDEXED, and JOIN_KW into a single class.

  6. Compare the Library and Amalgamation APIs

    main

    Syntaqlite provides two distinct ways to access parser and tokenizer capabilities, using different symbol names to allow them to coexist in the same binary without collision.

    • Amalgamation (syntaqlite_parser.h): Provides standalone functions with the syntaqlite_parser_* prefix. Use this for a zero-dependency, specialized path.

      • Examples: syntaqlite_parser_new(), syntaqlite_parser_parse().

    • Library (syntaqlite.h): Provides engine-mediated functions with the syntaqlite_engine_* prefix. This is the high-level API intended for most users.

      • Examples: syntaqlite_engine_parse(), syntaqlite_engine_walk_nodes().

  7. Understand syntaqlite parser validation methodology

    main

    Syntaqlite's parser accuracy is validated against sqlite3 using the EXPLAIN command as the ground truth.

    Validation Legend:

    • PASS: Correctly parses valid SQL.
    • FAIL: Rejects valid SQL.
    • FP: Accepts invalid SQL (False Positive).

    Every test statement in the comparison suite is checked to ensure it is compatible with the target SQLite version. For example, the suite includes complex features like UPSERT, Recursive CTEs, STRICT tables, Window functions, and JSON operators.

  8. Define custom grammar and nodes for extensions

    main

    When building an extension (like the Perfetto example), you provide two main components:

    1. Grammar files (.y): Use Lemon-style syntax to define new tokens and production rules. You can use %token to define new keywords and %fallback to handle identifier collisions.
    2. Node definitions (.synq): Define how new syntax elements are represented in the AST. Each node includes its fields and a fmt block that describes how the node should be serialized back to text (the formatter bytecode).

    Example structure:

    • actions/: Contains .y files for grammar rules.
    • nodes/: Contains .synq files for AST node definitions.
    # Example grammar file (perfetto_stmts.y)
%token PERFETTO MACRO INCLUDE MODULE RETURNS FUNCTION DELEGATES.
%fallback ID FUNCTION MODULE PERFETTO.

cmd(A) ::= CREATE or_replace(R) PERFETTO FUNCTION ID(N) LP ... { ... }
    // Example node definition (perfetto_stmts.synq)
node PerfettoFunctionStmt {
    func_name: inline TextSpan
    body: index Stmt
    is_replace: inline Bool
    fmt {
        Text("CREATE")
        IfSet(is_replace) { Text("OR REPLACE") }
        Text("PERFETTO FUNCTION")
        Span(func_name)
        ...
    }
}

  9. How Column Inference works for `define_table` with `select`

    main

    When using CREATE TABLE foo AS SELECT ... (where columns is not explicitly provided), the engine performs a forward-pass accumulation to infer the columns for foo by visiting the SELECT subtree.

    Inference Rules:

    1. Explicit Aliases: If a result column has an alias, that alias becomes the column name.
    2. Bare Column Refs: If a result column is a column_ref without an alias, the original column name is used.
    3. Star Expansion: For SELECT * or SELECT t.*, the engine expands the columns using the known map (relations accumulated in the document) or the external DatabaseCatalog.
    4. Expressions without Aliases: Result columns that are expressions without aliases (e.g., SELECT 1 or SELECT a + b) produce no column entry and cannot be addressed by name.

  10. Use the `syntaqlite-lsp` crate for language server logic

    main

    The syntaqlite-lsp crate provides a pure Rust, computation-only engine for language server capabilities. It is designed to be used without IO or transport concerns, making it suitable for both native LSP servers (via syntaqlite-cli) and WASM-based browser integrations (via syntaqlite-wasm).

    Key responsibilities include:

    • Computing diagnostics (errors, warnings, etc.)
    • Formatting SQL code
    • Managing document state and lifecycle
    • Providing an AmbientContext for schema-aware analysis.
    /* The crate re-exports these core types: */
// AnalysisHost, FormatError, AmbientContext, Diagnostic, Severity

  11. Define catalog effects using Catalog roles

    main

    Catalog roles (which replace session_schema) declare what a DDL statement contributes to the database catalog.

    Key roles include:

    • define_table(name: ..., columns: ..., select: ...): Declares a table. If both columns and select are provided, the explicit columns take precedence. If only select is provided (e.g., CREATE TABLE ... AS SELECT), the engine infers columns from the SELECT result.
    • define_view(name: ..., select: ...): Declares a view.
    • define_function(name: ..., args: ...): Declares a function.
    • import(module: ...): Declares a module import. This is treated as a catalog mutation (like CREATE TABLE) rather than a compile-time scope directive. It relies on a pluggable module resolver provided by the caller.

    To handle heterogeneous column definitions (e.g., different node types for SQLite vs. Perfetto), individual column nodes should carry their own column_def role, which the engine collects when processing a define_table call.

    node CreateTableStmt {
  ... 
  semantic { define_table(name: table_name, columns: columns, select: as_select) }
}

node ColumnDef {
  column_name: index Name
  type_name: inline SyntaqliteTextSpan
  ...
  semantic { column_def(name: column_name, type: type_name, constraints: constraints) }
}

  12. How Catalog resolution works for columns and functions

    main

    The Catalog uses specific logic to resolve identifiers based on available metadata:

    Column Resolution

    • Qualified (table is Some(tbl)): If the table exists and its columns are known (Some(cols)), it checks if the column is present. If the table exists but columns are unknown (None), it returns Found conservatively to avoid false errors.
    • Unqualified (table is None): Scans all layers. If any table in scope has unknown columns (None), it returns Found conservatively. If all tables have known columns and the column is missing from all of them, it returns NotFound.

    Function Resolution

    Functions are checked for existence and arity (number of arguments). An overload matches if:

    • Exact(n): arg_count == n
    • AtLeast(min): arg_count >= min
    • Any: Always matches.

    If no overload matches, the result is WrongArity containing the expected fixed arities.