Reformat SQL Queries for Enhanced Clarity

On this page

The cockroach sqlfmt command changes the textual formatting of one or more SQL queries. It recognizes all SQL extensions supported by CockroachDB.

Warning:

This is an experimental feature. The interface and output are subject to change.

Synopsis

Use the query formatter interactively:

$ cockroach sqlfmt <flags>
<sql stmt>
CTRL+D

Reformat a SQL query given on the command line:

$ cockroach sqlfmt <flags> -e "<sql stmt>"

Reformat a SQL query already stored in a file:

$ cat query.sql | cockroach sqlfmt <flags>

The sqlfmt command supports the following flags.

Flag	Description	Default value
`--execute` `-e`	Reformat the given SQL query, without reading from standard input.	N/A
`--print-width`	Desired column width of the output.	80
`--tab-width`	Number of spaces occupied by a tab character on the final display device.	4
`--use-spaces`	Always use space characters for formatting; avoid tab characters.	Use tabs.
`--align`	Use vertical alignment during formatting.	Do not align vertically.
`--no-simplify`	Avoid removing optional grouping parentheses during formatting.	Remove unnecessary grouping parentheses.

Using the interactive query formatter, output with the default column width (80 columns):

Run the query:

> CREATE TABLE animals (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);

Press CTRL+D.

CREATE TABLE animals (
        id INT PRIMARY KEY DEFAULT unique_rowid(),
        name STRING
)

Using the command line, output with the column width set to 40:

$ cockroach sqlfmt --print-width 40 -e "CREATE TABLE animals (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);"

CREATE TABLE animals (
        id
                INT
                PRIMARY KEY
                DEFAULT unique_rowid(),
        name STRING
)

Output with the default vertical alignment:

$ cockroach sqlfmt -e "SELECT winner, round(length / (60 * 5)) AS counter FROM players WHERE build = $1 AND (hero = $2 OR region = $3);"

SELECT
winner, round(length / (60 * 5)) AS counter
FROM
players
WHERE
build = $1 AND (hero = $2 OR region = $3)

Output with vertical alignment:

$ cockroach sqlfmt --align -e "SELECT winner, round(length / (60 * 5)) AS counter FROM players WHERE build = $1 AND (hero = $2 OR region = $3);"

SELECT winner, round(length / (60 * 5)) AS counter
  FROM players
 WHERE build = $1 AND (hero = $2 OR region = $3);

Output with the default simplification of parentheses:

$ cockroach sqlfmt -e "SELECT (1 * 2) + 3, (1 + 2) * 3;"

SELECT 1 * 2 + 3, (1 + 2) * 3

Output with no simplification of parentheses:

$ cockroach sqlfmt --no-simplify -e "SELECT (1 * 2) + 3, (1 + 2) * 3;"

SELECT (1 * 2) + 3, (1 + 2) * 3