SQL formatter

What this tool does

Paste an SQL query, or open a .sql file, and the tool lays it out so a person can read it: one clause per line, one column or condition per line, sub-queries and CASE expressions indented, reserved words in the case you choose. It works on one statement or a whole script, and everything happens in your browser. Queries often contain table names, customer ids and literals that should not be pasted into a website that uploads them; here nothing is sent anywhere.

How to use it

1. Paste or type SQL, press "Open a file", or drop a file on the page.
2. Pick the dialect: generic SQL, PostgreSQL, MySQL or MariaDB, or SQLite. The dialect only changes how the text is read, for example which quotes make a name and which make a string.
3. Choose the indentation (two spaces, four spaces or a tab), the case of reserved words, and whether commas go at the end of the line or at the start of the next one.
4. Copy the result or download it as formatted.sql. "Use as input" feeds the result back in.

What the layout looks like

Take select a,b from t left join u on t.id=u.t_id and u.x=1 where a=1 and (b=2 or c=3). The tool writes:

SELECT
  a,
  b
FROM t
LEFT JOIN u
  ON t.id = u.t_id
  AND u.x = 1
WHERE
  a = 1
  AND (b = 2 OR c = 3)

A clause with a single short item stays on the keyword's line (FROM t); a clause with a list or several conditions breaks them one per line. A BETWEEN 1 AND 2 is not split at its AND, a CASE expression is written as CASE, WHEN, ELSE and END lines, and a sub-query or a CTE is indented inside its parentheses. INSERT lists its value tuples one per line, and a CREATE TABLE puts one column per line.

It never changes what the query means

The formatter reads the query into tokens: words, quoted names, strings, numbers, operators, comments. It then writes the same tokens in the same order. It cannot reorder, drop or merge anything. Only two things change: the whitespace between tokens, and the case of reserved words. Names are never touched, so a column called Name stays Name, and a word is treated as reserved only when it cannot be a name, which is why words such as name, date, type, key and status are left as you wrote them. Strings and quoted names are copied exactly, including 'it''s', PostgreSQL dollar-quoted strings and MySQL backticks. The tests check this on dozens of queries and on random mixtures: remove whitespace, comments and the case of reserved words from the input and from the output and the token streams are identical, and formatting the output again gives the same text.

Comments

Comments are kept. A comment at the end of a line stays at the end of that line, a comment on its own line stays on its own line, and a block comment in the middle of an expression stays there. A line comment is always followed by a line break so that it can never swallow the code after it.

Dialects

• PostgreSQL: $$ ... $$ and $tag$ ... $tag$ strings, E'...' strings with backslash escapes, nested /* /* */ */ comments, :: casts and the JSON operators ->, ->>, #>>.
• MySQL and MariaDB: backtick names, double quotes as strings, # comments, and a -- comment only when a space follows it, so 1--1 is arithmetic.
• SQLite: [name], backtick and double-quote names, ?1, :name and $name parameters.

Good to know

This is a formatter, not a parser or validator: it does not check that a query is valid or that the tables exist, and it says so only for the things it can see, an unclosed string or comment and unbalanced parentheses. It does not wrap long lines, so a long IN list stays on one line, and it lays out procedural code such as BEGIN blocks as plain statements. Data types and function names are left as written. For other formats see the JSON formatter and the XML formatter, and to see what a reformat changed use the diff checker. Size limit: one million characters.