SQL Formatter

A query copied out of application logs, an ORM or a colleague's message arrives as one long line. It runs perfectly well and it is almost impossible to read: you cannot see where the joins end, which conditions belong to the WHERE and whether that subquery is doing something expensive.

This formatter puts each clause on its own line, indents what belongs under it, and lines up the joins so the shape of the query is visible at a glance. It also goes the other way — minify collapses a formatted query back to one line for a log message, a shell command or a bug report.

Nothing about the query changes except whitespace and, if you ask for it, the case of recognised keywords. Every string, every quoted identifier and every comment is copied out exactly as you wrote it.

Clause keywords — SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and the rest — start a new line at the statement's indent, and everything belonging to that clause is indented one level under it. Commas break the line, so a long select list reads as a column per line rather than a paragraph.

Joins sit one level in from FROM, with the ON condition on the same line as the table it joins, because a join and its condition are one thought. Extra conditions joined by AND or OR hang under it, which is what makes a five-table query readable at all.

A bracket that holds a subquery is opened, indented and closed on its own line. A bracket that holds arguments — a function call, an IN list, a VALUES tuple — stays where it is, since breaking count(*) across three lines helps nobody. CASE expressions get the same treatment, with each WHEN on its own line and END back at the CASE's indent.

Every sequence a pattern-matching formatter would key on also appears inside data that must survive untouched. 'select 1 from t' is a string literal, not a query. The apostrophe in 'it''s' does not end the string, it escapes itself. A column can be named order, and quoting it as "order", [order] or `order` is exactly what people do when it is.

So this tool reads the query character by character and knows, at every point, whether it is inside a string, a quoted identifier, a line comment or a block comment. A keyword is only a keyword when it is found in code. That is also why a -- comment does not swallow the layout of the lines after it: comments are tokens, kept whole and put back where they belong.

Dollar-quoted strings, E'…' escape strings, ANSI, MySQL and SQL Server quoting styles, #temp tables and MySQL's # comments are all recognised. Two things are deliberately not attempted. Nested block comments, which only PostgreSQL supports, end at the first */ — the text still survives, but the layout after it may not. And a backslash inside a string is read as a plain character, following the SQL standard, so MySQL's non-standard backslash escapes are not recognised as escapes.

It is a convention rather than a rule. SQL is case-insensitive for keywords, so SELECT and select run identically. Uppercase keywords have stuck around because they separate the language from your table and column names on sight, which matters most in exactly the queries that are hard to read.

Because unquoted identifiers are also case-insensitive — PostgreSQL folds them to lower case, MySQL and SQL Server compare them without regard to case — changing the case of a recognised keyword cannot change what a query resolves to. Quoted identifiers are never touched at all. If your team writes lowercase keywords, or you would rather nothing was recased, both options are there.

Whitespace collapses to the minimum that keeps the query parsing the same way, which is usually a saving of 20–40% on a formatted query and is mostly useful for fitting a statement onto one line rather than for size.

Line comments have to go: -- runs to the end of the line, so a query on one line with a -- comment in it would have everything after that point commented out. The count of removed comments is shown so you know it happened. Block comments are kept, because /* … */ ends where it says it does and because some of them are load-bearing — optimiser hints and MySQL's /*!40101 … */ version comments both look like comments and both change what the server does.