Reformat SQL Queries for Enhanced Clarity

On this page Carat arrow pointing down
Warning:
As of April 30, 2020, CockroachDB v2.1 is no longer supported. For more details, refer to the Release Support Policy.

New in v2.1: 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:

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

  3. Run the query:

    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:

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:

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:

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:

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
On this page

Yes No