# Reformat SQL Queries for Enhanced Clarity

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:

       $ cockroach sqlfmt

2. Press Enter.

3. Run the query:

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

    $ 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

## See also

- Sequel Fumpt
- `cockroach demo`
- `cockroach sql`
- Other Cockroach Commands