How Our SQL Prettifier Works Under the Hood

Why SQL Formatting Is Hard

SQL looks simple on the surface but is notoriously difficult to format well. Unlike JSON or YAML — which have rigid, unambiguous grammars — SQL has decades of dialects, vendor extensions, and underspecified behaviors. A naive formatter that works on standard SELECT statements will break on window functions, CTEs, lateral joins, or PostgreSQL-specific syntax.

Regex-based approaches are a dead end. They can handle simple cases but collapse under real-world queries. Building a proper SQL formatter requires a real parse step.

The Three-Stage Pipeline

Our SQL formatter operates in three distinct stages:

Lexing (Tokenization)

The raw SQL string is converted into a flat sequence of typed tokens: keywords, identifiers, literals, operators, and punctuation. The lexer handles quoted identifiers, string literals with escaped quotes, comments (both `--` and `/* */`), and dialect-specific tokens.

Parsing (AST Construction)

Tokens are fed into a recursive-descent parser that builds an Abstract Syntax Tree representing the grammatical structure of the query. Each node in the tree corresponds to a SQL construct: SELECT clause, FROM clause, JOIN, WHERE predicate, subquery, window frame, etc.

Pretty-printing (Code Generation)

The AST is traversed depth-first and serialized back to a SQL string with consistent indentation, keyword casing, and line-break rules applied based on the node type and nesting depth.

Handling Dialects

SQL dialects differ in ways that matter for formatting. PostgreSQL uses :: for type casts. MySQL uses backtick-quoted identifiers. BigQuery supports QUALIFY and STRUCT. T-SQL uses TOP instead of LIMIT.

We handle this by parameterizing the lexer and parser with a dialect configuration. The dialect config specifies which tokens are valid keywords in that dialect, which operators are supported, and how identifier quoting works. This allows a single parse pipeline to handle MySQL, PostgreSQL, SQLite, BigQuery, and T-SQL without branching spaghetti in the core logic.

Edge Cases We Had to Get Right

Window functions

SELECT name, salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees

Window functions require the OVER clause and optional PARTITION BY / ORDER BY to be formatted as a coherent nested block.

Common Table Expressions

WITH
  sales AS (
    SELECT ...
  ),
  ranked AS (
    SELECT ...
  )
SELECT * FROM ranked

CTE chains need to be formatted with consistent indentation and trailing comma handling before the main SELECT.

Nested subqueries

SELECT *
FROM (
  SELECT id, MAX(score)
  FROM results
  GROUP BY id
) AS top_scores

Subqueries in FROM, WHERE, and SELECT positions each require their own indentation context.

Error Recovery

Real-world SQL is often syntactically incorrect — queries under development, queries extracted from logs, fragments. A formatter that refuses to format invalid SQL is frustrating.

Our parser implements error recovery: when it encounters an unexpected token, it logs the error, skips tokens until it finds a safe resynchronization point (typically a statement boundary or a known clause keyword), and continues parsing. The resulting AST may be incomplete, but the formatter can still produce useful output for the portions it understood.