DEV Community

Devanshu Biswas
Devanshu Biswas

Posted on

I built a SQL formatter from scratch — no library, just a tokenizer

#webdev #programming #sql #beginners

Every developer has pasted a wall-of-text query into some online "SQL beautifier" and gotten back something readable. I always assumed those tools were doing something clever with the query. Turns out they mostly aren't — and the one genuinely hard part isn't the part you'd expect. So for Day 31 of my SolveFromZero series I built one by hand, in vanilla JS, no library, and it changed how I think about the whole category of "formatter" tools.

Here's the punchline up front: a formatter doesn't understand your SQL. It re-spaces a list of tokens. The database doesn't care whether you write SELECT id FROM t on one line or across ten — they run identically. Formatting is purely for humans: readability, code review, and — the underrated one — clean git diffs. When every column sits on its own line, changing one column is a one-line diff instead of a rewritten blob.

The trap I walked into first

My first instinct was the obvious one: run some regexes. Uppercase the keywords, add a newline before FROM and WHERE, done.

That falls apart in about thirty seconds. Consider this:

SELECT id, 'please select an option' AS hint FROM menu WHERE note LIKE '%group by%'
Enter fullscreen mode Exit fullscreen mode

A naive replace(/select/gi, 'SELECT') corrupts the string literal. A newline-before-group by rule wrecks the LIKE pattern. And what about a column literally named `order`? Reserved words make perfectly legal quoted identifiers.

So the real lesson: before you move a single character, you have to split the text into tokens, and some of those tokens must be treated as opaque and untouchable — string literals, comments (-- line and /* block */), and quoted identifiers ("name", `name`).

The tokenizer is the whole game

The tokenizer scans character by character. When it hits an opening quote or a comment marker, it consumes greedily until the close and emits one token for the entire span. That's it — that single decision is what makes everything downstream safe. Once a whole string is one token, no layout rule can ever reach inside it. The word select in a literal is invisible to the formatter, by construction.

if (c === "'"){                 // consume the whole string literal
  let a = i; i++;
  while (i < n){
    if (s[i] === "'"){ if (s[i+1] === "'"){ i += 2; continue; } i++; break; }
    i++;
  }
  toks.push({ type: "string", val: s.slice(a, i) });   // opaque
}
Enter fullscreen mode Exit fullscreen mode

Everything after that is comparatively boring: runs of letters are words (keyword or identifier — decided by an uppercase lookup in a keyword set), digits are numbers, and , ( ) ; . get their own types because layout depends on them.

Printing is just three rules

With a clean token stream, the pretty-printer is almost anticlimactic. It keeps a current line and an indent level, and applies:

  1. Major clauses break the line. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT — each flushes the current line and starts a new one at the base indent. That's the stepped shape.
  2. Parens carry two meanings. count(*) stays inline; (SELECT ...) opens a subquery. You tell them apart by peeking at the next token — if it's SELECT, push a deeper frame onto a stack and indent. The stack makes nesting fall out for free, and each ) pops back to its opener's column.
  3. Commas and AND/OR align. Inside SELECT, each column goes on its own line (trailing- or leading-comma, your pick). Inside WHERE, each AND/OR drops one level so conditions stack up neatly.

Minifying is the same stream with the break rules switched off: drop comments, join tokens with single spaces (none around . ( ) or before commas), and you're guaranteed one line. It doubles as a correctness test — minify(format(sql)) should equal minify(sql), proving you only moved whitespace.

Where hand-rolling stops

My version handles ordinary queries well, but SQL is a family of dialects. MySQL uses # comments, SQL Server uses [bracketed] identifiers, Postgres has $$ function bodies, and some words are keywords in one position and column names in another. Getting all that right means actually parsing a specific grammar — which is exactly what a library like sql-formatter does. Roll your own to understand the mechanics; reach for the dialect-aware library when correctness across engines matters.

Play with the live version (tokenizer viewer, casing, indent, comma style, minify) here:

https://dev48v.infy.uk/solve/day31-sql-formatter.html

The tokenizer viewer is my favorite part — you can literally see the string literal light up as one green block, which is the entire reason the thing is safe.

Top comments (0)