SQL Formatter

What the SQL formatter does

The formatter lays out SQL the way most style guides do: every clause on a line of its own, what belongs to it indented below, and keywords in one case. It first reads the query with the rules of the dialect you choose, from Standard SQL to PostgreSQL, MySQL, SQL Server, Oracle, BigQuery and fourteen more, so their quotes, placeholders and comments are understood rather than broken apart. Only whitespace and letter case change. If the text cannot be read, every problem points to its line and column, and nothing is ever run or sent anywhere.

How to format SQL

Examples

select id,name from users where active=1 order by name

SELECT
  id,
  name
FROM
  users
WHERE
  active = 1
ORDER BY
  name

select * from orders where customer_id = $1 -- by customer

SELECT
  *
FROM
  orders
WHERE
  customer_id = $1 -- by customer

select 'abc from t

Line 1, column 8: This string is never closed with '. Add ' where the string ends. A ' inside the string is written twice: ''. (SQL_PARSE_ERROR)

Choosing the dialect

SQL is a standard, but every database adds its own small print, and a formatter has to get it right before it can lay out anything: how names and strings are quoted, what a placeholder looks like, and which characters start a comment. The ones you meet most:

• PostgreSQL quotes names in "double quotes", numbers its placeholders $1, $2, and has $$dollar-quoted$$ strings for function bodies (lexical structure (opens in a new tab)).
• MySQL and MariaDB quote names in `backticks`, use ? placeholders, accept \' inside strings and start a comment with # too (identifiers (opens in a new tab)).
• SQL Server quotes names in [brackets] or double quotes, and names its parameters @id (identifiers (opens in a new tab)).
• Oracle binds parameters as :name and has q'[…]' strings that need no doubled quotes inside (names (opens in a new tab)).
• BigQuery writes table paths in backticks, like `project.dataset.table`, takes @name and ? parameters, and escapes a quote in a string only with a backslash (lexical structure (opens in a new tab)).
• SQLite accepts names in double quotes, backticks or brackets, and parameters as ?, ?1, :name, @name or $name (keywords and quoting (opens in a new tab)).

Standard SQL reads most portable queries. When the formatter stops at something your database accepts, such as $1, [name] or #, the dialect is the likely cause. Try other dialects then formats the query with PostgreSQL, MySQL, SQL Server, BigQuery and SQLite, and offers each one that reads it. Often several can, since formatting checks so little; choose the database the query actually runs on.

How the layout reads

Clause keywords such as SELECT, FROM, WHERE and GROUP BY start a line, and each column, table and condition under them gets a line of its own, so a diff shows exactly which one changed. Keyword case UPPER makes keywords stand out from names; lower suits teams that write everything in lower case, and As written changes no letter. It covers data types such as VARCHAR and built-in functions such as COUNT as well, while table and column names change only with Identifier case, and never inside quotes.

A bracketed expression, like a function call or an IN list, stays on one line while it fits in the Expression width, and otherwise breaks into one item per line. With AND / OR at the start of the line, a condition and its operator come and go together, which is why it is the default.

Formatting is not validating

The formatter reads a query only as far as laying it out needs: words, quotes, brackets and the order of clauses. It does not know your tables, columns or types, does not check that a function exists, and never runs anything, so a query can format cleanly and still fail in the database. What it does catch are mistakes that make the text unreadable: a string, quoted name or comment that never closes, a bracket or CASE without its end, and characters the dialect has no use for. To read the JSON or timestamps a query returns, the JSON Formatter and the Unix Timestamp Converter take over.

Comments and placeholders

Comments stay where they are, in all their forms: -- to the end of the line, /* … */ blocks, and # lines in MySQL, MariaDB and BigQuery. One thing moves: a comment written after a statement's semicolon belongs to the next statement, so it goes to the line above that one.

Placeholders of prepared statements, such as ?, $1, :name and @name, are never filled in or renamed. A query taken from code keeps its parameters, and the formatted version can go back into the code as it is. Strings stay exactly as written too, even when they hold a document; XML kept in a column is laid out by the XML Formatter.

Options

Common errors and fixes