SQL Formatter

What is a SQL formatter?

A SQL formatter rewrites a query so each clause starts on its own line, which makes long statements from logs, ORMs or one-line scripts readable. This one is a lightweight keyword-based formatter: it puts major clauses like SELECT, FROM, WHERE and ORDER BY on new lines and lists columns one per line, without parsing the SQL.

How to format a SQL query

1. Paste your query into the input box. It can be on one line or spread across several.
2. Click Format.
3. Copy the result back into your editor or database client.

Worked example

This query, copied from an application log:

select id, email, created_at from customers where country = 'UK' and active = 1 order by created_at desc limit 20

is formatted as:

SELECT id,
  email,
  created_at
FROM customers
WHERE country = 'UK'
AND active = 1
ORDER BY created_at desc
LIMIT 20

select, from, where, and, order by and limit were uppercased and moved to new lines. Each comma starts a new line indented by two spaces. desc stayed lowercase because it isn't one of the keywords the formatter looks for.

How it works

The formatter runs three passes over the text:

1. Collapse whitespace. Every run of spaces, tabs and line breaks becomes a single space, so the original layout doesn't matter.
2. Break at keywords. Each keyword from the list below gets a line break before it and is rewritten in uppercase. Matching is case-insensitive and whole-word only, so a column named updated_at or setting isn't touched.
3. Break at commas. Every comma is followed by a line break and two spaces of indentation.

The keywords it breaks on:

Limits to know about

Because it works on keywords instead of understanding the query, a few results look odd or need checking. These were all confirmed by running them through the tool:

• Two-word joins split in two. LEFT JOIN comes out as LEFT on one line and JOIN orders o on the next. The same happens to RIGHT, INNER and OUTER joins, and DELETE FROM becomes DELETE then FROM .... The query still means the same thing.
• Subqueries aren't indented. SELECT a FROM (SELECT a FROM b) x puts the inner SELECT on its own line at the same indentation as the outer one.
• Every comma breaks a line, including commas inside VALUES (1, 'Ada') and function arguments.
• AND inside BETWEEN breaks too. BETWEEN 1 AND 5 is split across two lines.
• String literals aren't protected. WHERE description = 'Ships from Leeds' becomes 'Ships on one line and FROM Leeds' on the next, which changes the value. Commas inside strings are also followed by a line break.
• Comments can swallow code. -- comments run to the end of the line (PostgreSQL, MySQL and SQLite all agree on this). Since the formatter removes line breaks first, code on the line after a comment can be pulled onto it: SELECT price -- in cents followed by * 100 comes out as SELECT price -- in cents * 100, so the multiplication is now commented out. The opposite also happens: a commented-out -- WHERE id = 2 can come back as live code on its own line. Remove -- comments, or convert them to /* */, before formatting.

For quick reading of a logged query these rarely matter. If you plan to run the formatted SQL, and the original had string literals with keywords or commas, or -- comments, compare the two with the code diff tool first.

Formatting conventions

There's no official SQL style, but most teams agree on a few things this tool already does: uppercase keywords, one clause per line, and one column per line in long SELECT lists, which keeps version-control diffs small when a column is added. Leading-comma style (, email) and right-aligned keywords are matters of taste the tool doesn't offer.

Related pages

• SQL editor: write queries with syntax highlighting.
• SQL cheat sheet: syntax for joins, grouping and common functions.
• Learn SQL: a structured introduction, from SELECT to joins.
• Code beautifier: indentation for HTML, CSS and JavaScript.