Reformat SQL Queries for Enhanced Clarity

Reformat SQL Queries for Enhanced Clarity

The cockroach sqlfmtcommand changes the textual formatting ofone or more SQL queries. It recognizes all SQL extensions supported byCockroachDB.

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):

Start the interactive query formatter:

$ cockroach sqlfmt

Press Enter.
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
)

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:

$ 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:

$ 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

cockroach sqlfmt