SQL Formatter
Lay out a SQL query by clause, with joins and boolean operators on their own lines. Strings, quoted names and comments are carried through untouched.
SELECT p.ID, p.post_title, COUNT(c.comment_ID) AS comments
FROM wp_posts p
LEFT JOIN wp_comments c
ON c.comment_post_ID = p.ID
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
GROUP BY p.ID
HAVING COUNT(c.comment_ID) > 2
ORDER BY comments DESC
LIMIT 10;
Output is valid and updates as you type.
Fix the highlighted fields to update the output.
Lay out a SQL query so it can be read: one clause per line, joins and boolean operators on their own lines, everything inside a string left exactly as it was.
How to use
- Paste the query. One statement or several; each one starts again at the left margin.
- Choose the keyword case. Uppercase keywords are the convention because they separate the language from your table names at a glance.
- Pick the indent, and turn on leading commas if that is your house style. A comma at the start of the line makes a missing one obvious in a diff.
- Copy the result. The formatter never changes what the query does: it only moves whitespace outside strings and comments.
Example
select p.ID, count(c.comment_ID) as comments from wp_posts p left join wp_comments c on c.comment_post_ID = p.ID where p.post_status='publish' group by p.ID order by comments desc limit 10;becomes
SELECT p.ID, COUNT(c.comment_ID) AS comments
FROM wp_posts p
LEFT JOIN wp_comments c
ON c.comment_post_ID = p.ID
WHERE p.post_status = 'publish'
GROUP BY p.ID
ORDER BY comments DESC
LIMIT 10;COUNT(c.comment_ID) keeps its shape and the ON is indented under its join, which is what makes a five-join query readable.
Pitfalls
- A formatter that works by regular expression breaks the first time a keyword appears inside a string.
where a = 'select from'is one value, and this tool tokenises before it lays anything out. - Formatting is not validating. A query that is wrong is still wrong, and the output will look tidy while doing it.
- SQL dialects disagree about almost everything past the basics. This lays out the clauses every dialect shares and leaves anything it does not recognise as a plain word.
- A doubled quote inside a string is an escaped quote, not the end of it.
'it''s'is one literal, which is why the tokeniser handles it explicitly. - Backticks are MySQL, double quotes are standard SQL identifiers, and square brackets are SQL Server. Quoted names are carried through as they are rather than converted.
- Whitespace inside a string literal is content. Nothing here touches it, including a multi-line string.
- Leading commas and trailing commas are a style argument with one technical point in it: with leading commas, adding a column changes one line instead of two.
- A query long enough to need a formatter is usually a query worth a comment. The formatter keeps comments on their own lines so they stay readable.
Compatibility
The clause and join keywords covered are the ones MySQL, MariaDB, PostgreSQL, SQLite and SQL Server share. Strings use single quotes with doubled-quote and backslash escapes; identifiers may use backticks or double quotes. Line comments start with two dashes and block comments with slash-star. Everything runs in your browser, with no upload, in any browser from 2017 onwards.