SQL Formatter

How this formatter works

The formatter reads your SQL with a small tokenizer, then lays it out. It first separates comments, quoted strings and identifiers, words, and punctuation, so none of those can be mistaken for keywords or break the layout. Common keywords are recased to your choice of upper or lower case: SELECT, FROM, WHERE, AND, OR, ON, AS, JOIN, GROUP BY, ORDER BY, HAVING, LIMIT, OFFSET, INSERT INTO, VALUES, UPDATE, SET, DELETE FROM, CREATE TABLE, UNION, DISTINCT, ASC, DESC, CASE, WHEN, THEN, ELSE, END, IN, NOT, NULL, IS, LIKE, BETWEEN and a few more. A line break goes before each clause keyword and each join, AND and OR get their own indented line, and commas in a list start a new indented line.

Worked example

Input, with the 2-space indent and uppercase options selected:

select id, name, email from users u left outer join orders o on o.user_id = u.id where active = 1 and age between 18 and 30 order by name desc limit 10

Output:

SELECT id,
  name,
  email
FROM users u
LEFT OUTER JOIN orders o ON o.user_id = u.id
WHERE active = 1
  AND age BETWEEN 18 AND 30
ORDER BY name DESC
LIMIT 10

The whole join phrase (INNER, LEFT, RIGHT, FULL, CROSS, NATURAL, with optional OUTER) stays on one line, and the AND inside BETWEEN ... AND is not split like a boolean. Clause keywords start at the left margin, and the 2 or 4 space indent applies to list items and AND or OR lines.

What is left alone

• Comments. A -- note stays at the end of its line, with the next token on a new line, and a /* block */ comment is kept as written.
• Strings and quoted identifiers. Text in 'single', "double" or backtick quotes keeps its case, spacing and commas, so 'on hold' stays lower case and 'a, b' stays on one line. A doubled quote such as 'it''s' is understood.
• Function arguments and lists. Commas inside parentheses, such as coalesce(a, b) or IN (1, 2), stay on the same line. Parenthesised subqueries get their clauses on new lines, indented one level deeper. CREATE TABLE column lists put one column on each line.
• Names after a dot such as u.set are treated as identifiers, not keywords.

Limits

• There is no dialect-specific handling for MySQL, PostgreSQL or SQL Server. Syntax the formatter does not know is passed through as you typed it.
• A column or table that happens to be named like a keyword, for example end or set on its own, is recased as a keyword.
• Backslash-escaped quotes (MySQL style) and dollar-quoted strings are not recognised, only doubled quotes.

The output differs from your input only in whitespace and keyword case, so it should run exactly as before, but compare results on a test database if the query matters. For tidying the JSON your queries return, use the JSON formatter, and to turn result sets into JSON, see CSV to JSON.