Skip to content

Latest commit

 

History

History
158 lines (117 loc) · 3.81 KB

cockroach-sqlfmt.md

File metadata and controls

158 lines (117 loc) · 3.81 KB
Raw
title summary toc key
cockroach sqlfmt
Use cockroach sqlfmt to enhance the text layout of a SQL query.
true
use-the-query-formatter.html

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.

{% include {{ page.version.version }}/misc/experimental-warning.md %}

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:

    {% include_cached copy-clipboard.html %}

    $ cockroach sqlfmt

  2. Press Enter.

  3. Run the query:

    {% include_cached copy-clipboard.html %}

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

{% include_cached copy-clipboard.html %}

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

{% include_cached copy-clipboard.html %}

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

{% include_cached copy-clipboard.html %}

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

Output with no simplification of parentheses:

{% include_cached copy-clipboard.html %}

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

See also