SQL formatter vs SQL parser: what's the difference

A SQL formatter and a SQL parser both read your query, but to different depths. A formatter needs to know where tokens start and end and roughly which clause it's in. A parser needs the grammar. That difference decides what a formatter can promise you, and when you need something stronger.

The examples use the DevPrix tool, which lets you format SQL in your browser, and every formatted block is its real output. For the implementation, building a SQL tokenizer and formatter from scratch walks through the code.

What a tokenizer sees

The first stage of any SQL tool is a tokenizer, or lexer. It scans characters and groups them into tokens: keywords, identifiers, string and number literals, operators, punctuation and comments. Quoting rules live here: 'it''s' is one string token, -- note runs to the end of the line, [order total] is one SQL Server identifier.

Here is what the DevPrix tokenizer produces for a short query, with whitespace dropped:

select name from users where id >= 42 order by name

keyword      "select"
identifier   "name"
keyword      "from"
identifier   "users"
keyword      "where"
identifier   "id"
operator     ">="
number       "42"
keyword      "order by"
identifier   "name"

order by comes out as one token because the tokenizer looks ahead to glue compound keywords together. The formatter then walks this flat list with a little state (indent depth, which clause it's in, what kind of parenthesis it's inside) and decides where the line breaks go. There is no tree. That's enough to lay out almost any query, including SQL that is half-written or wrong.

What a parser adds

A parser matches the same tokens against a dialect's grammar and builds a parse tree or abstract syntax tree (AST). For the query above, roughly:

SelectStatement
  columns:  [ Column(name) ]
  from:     Table(users)
  where:    Compare(>=)
              left:  Column(id)
              right: Number(42)
  order_by: [ Sort(Column(name), ASC) ]

Because it knows the grammar, a parser can reject input that doesn't fit, and it knows what each token is for. A token stream can't tell you that, and BETWEEN shows the gap clearly:

SELECT
  id
FROM
  orders
WHERE
  total BETWEEN 100
  AND 500
  AND status = 'paid'

The formatter gives every top-level AND in a WHERE clause its own line, and from tokens alone the AND of BETWEEN 100 AND 500 looks like any other. A parser knows it belongs to the BETWEEN predicate. Only whitespace moved, so the query is unchanged, but it reads as three conditions instead of two. The Compact WHERE option keeps them on one line.

What a formatter can't guarantee

A formatter promises one thing: the output has the same tokens as the input, laid out differently. It doesn't promise the query is valid. The input selec id name from from users where and total > has a typo, a doubled FROM and a dangling condition, and it formats without complaint:

selec id name
FROM
FROM
  users
WHERE
  AND total >

selec stays lowercase because it isn't a keyword, which is a hint, not an error. And the missing comma in id name isn't a syntax error at all: it makes name an alias for id. Only a reviewer catches that one.

The DevPrix tool reports no problem with this query either, because its checks are structural, not grammatical. It flags unclosed string literals, unclosed quoted identifiers, unclosed block comments and unbalanced parentheses, with a line and column, and the Format button won't overwrite your input until they're fixed. Those are errors a tokenizer can see. It won't flag an unknown keyword or a missing FROM.

Four layers of "is this query right?"

A formatter lives in the first row and a parser-based linter reaches the second. Even a perfect parser stops there: SELECT * FROM table_that_does_not_exist is grammatically flawless. Checking names and types is semantic analysis, and it needs the schema.

Dialects: where tokens stop being enough

Dialects disagree on details a formatter has to tokenize: identifier quoting ("order" in standard SQL and PostgreSQL, backticks in MySQL, [order] in SQL Server), row limits (LIMIT, TOP, FETCH FIRST 10 ROWS ONLY) and even operators. || concatenates strings in PostgreSQL, Oracle and SQLite, while MySQL treats it as logical OR unless the PIPES_AS_CONCAT SQL mode is on.

In the DevPrix formatter the dialect setting changes which words count as keywords. TOP is a keyword only in the SQL Server dialect: format select top 5 id, name from users as Standard SQL and the first select line is top 5 id,, left as typed; switch to SQL Server and it becomes TOP 5 id,. ILIKE and RETURNING work the same way under PostgreSQL. The setting doesn't translate between dialects; that takes a transpiler.

Dialect-specific operators are where token-based formatters most often go wrong: a formatter that doesn't recognise an operator can put a space inside it, and the database rejects the result. The DevPrix formatter keeps PostgreSQL's :: casts, JSON operators such as ->> and @>, $1 placeholders and $$-quoted bodies in one piece. If your SQL uses other vendor-specific syntax, run the formatted query once before you commit it.

When you need a parser, a linter or the database

Use a formatter to read and review SQL. Reach for something stronger when the question is "is this valid?" or "does this follow our rules?":

• A SQL linter such as SQLFluff. It parses SQL for the dialect you configure, then applies rules that need structure: consistent capitalisation, qualified column references when a query has more than one table, explicit aliases. It can fix many violations itself and understands templated SQL such as Jinja and dbt models, which makes it a good fit for CI.
• A parser library. SQLGlot, a Python library, parses many dialects into an AST and can transpile between them. libpg_query packages PostgreSQL's own parser as a library, so its idea of valid syntax matches PostgreSQL's. Use these when your code needs to inspect or rewrite queries.
• The database itself. It's the only authority on whether tables, columns and types line up. In PostgreSQL, EXPLAIN parses, analyses and plans a statement without running it, so a missing table fails immediately and a valid query shows its plan. MySQL has EXPLAIN, SQLite EXPLAIN QUERY PLAN, Oracle EXPLAIN PLAN FOR, and SQL Server can show an estimated execution plan.

One warning: PostgreSQL's EXPLAIN ANALYZE executes the statement. On an UPDATE or DELETE the rows really change, so wrap it in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE
DELETE FROM sessions WHERE expires_at < now() - interval '30 days';
ROLLBACK;

Why JSON tools can do both

JSON has a tiny grammar, so a JSON formatter can afford to parse the whole document before printing anything. The JSON formatter does exactly that, which is why its errors come with a position. SQL has a large grammar per dialect, and much of the SQL you want to read is a fragment or a draft. A token-based formatter can lay all of it out, at the price of not judging it.

Next steps

Use the SQL formatter to make a query readable, then let the database or a linter decide whether it's correct. For the layout rules themselves, see how to format complex SQL queries.