Warning:
Cockroach Labs will stop providing Assistance Support for this version on November 1, 2020. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

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

A web interface to this feature is also available.

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>

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.

Examples

Reformat a query with constrained column width

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

  1. Start the interactive query formatter:

    copy
    icon/buttons/copy
    $ cockroach sqlfmt
    
  2. Press Enter.

  3. Run the query:

    copy
    icon/buttons/copy
    > CREATE TABLE animals (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);
    
  4. 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:

copy
icon/buttons/copy
$ 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
)

Reformat a query with vertical alignment

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:

copy
icon/buttons/copy
$ 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);

Reformat a query with simplification of parentheses

Output with the default simplification of parentheses:

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

Output with no simplification of parentheses:

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

See also



Yes No