Use the Built-in SQL Client

CockroachDB comes with a built-in client for executing SQL statements from an interactive shell or directly from the command line. To use this client, run the cockroach sql command as described below.

To exit the interactive shell, use \q, quit, exit, or ctrl-d.

Tip:

If you want to experiment with CockroachDB SQL but do not have a cluster already running, you can use the cockroach demo command to open a shell to a temporary, in-memory cluster.

Synopsis

# Start the interactive SQL shell:
$ cockroach sql <flags>
# Execute SQL from the command line:
$ cockroach sql --execute="<sql statement>;<sql statement>" --execute="<sql-statement>" <flags>
$ echo "<sql statement>;<sql statement>" | cockroach sql <flags>
$ cockroach sql <flags> < file-containing-statements.sql
# Exit the interactive SQL shell:
$ \q
$ quit
$ exit
ctrl-d
# View help:
$ cockroach sql --help

Flags

The sql command supports the following types of flags:

• General Use
• Client Connection
• Logging

General

• To start an interactive SQL shell, run cockroach sql with all appropriate connection flags or use just the —url flag, which includes connection details.
• To execute SQL statements from the command line, use the —execute flag.

  Flag	Description
  —database-d	A database name to use as current database in the newly created session.
  —echo-sql	Reveal the SQL statements sent implicitly by the command-line utility. For a demonstration, see the example below.This can also be enabled within the interactive SQL shell via the \set echo shell command.
  —execute-e	Execute SQL statements directly from the command line, without opening a shell. This flag can be set multiple times, and each instance can contain one or more statements separated by semi-colons. If an error occurs in any statement, the command exits with a non-zero status code and further statements are not executed. The results of each statement are printed to the standard output (see —format for formatting options).For a demonstration of this and other ways to execute SQL from the command line, see the example below.
  —format	How to display table rows printed to the standard output. Possible values: tsv, csv, table, raw, records, sql, html.Default: table for sessions that output on a terminal; tsv otherwiseThis flag corresponds to the display_format client-side option.
  —safe-updates	Disallow potentially unsafe SQL statements, including DELETE without a WHERE clause, UPDATE without a WHERE clause, and ALTER TABLE … DROP COLUMN.Default: true for interactive sessions; false otherwisePotentially unsafe SQL statements can also be allowed/disallowed for an entire session via the sql_safe_updates session variable.
  —set	Set a client-side option before starting the SQL shell or executing SQL statements from the command line via —execute. This flag may be specified multiple times, once per option.After starting the SQL shell, the \set and unset commands can be use to enable and disable client-side options as well.

Client connection

See Client Connection Parameters for more details.

Logging

By default, the sql command logs errors to stderr.

If you need to troubleshoot this command's behavior, you can change its logging behavior.

Session and output types

cockroach sql exhibits different behaviors depending on whether or not the session is interactive and/or whether or not the session outputs on a terminal.

• A session is interactive when cockroach sql is invoked without the —execute flag and input is not redirected from a file. In such cases:
  • The errexit option defaults to false.
  • The check_syntax option defaults to true if supported by the CockroachDB server (this is checked when the shell starts up).
  • Ctrl+C at the prompt will only terminate the shell if no other input was entered on the same line already.
  • The shell will attempt to set the safe_updates session variable to true on the server.
• A session outputs on a terminal when output is not redirected to a file. In such cases:
  • The —format flag and its corresponding display_format option default to table. These default to tsv otherwise.
  • The show_times option defaults to true.
    When a session is both interactive and outputs on a terminal, cockroach sql also activates the interactive prompt with a line editor that can be used to modify the current line of input. Also, command history becomes active.

SQL shell

Welcome message

When the SQL shell connects (or reconnects) to a CockroachDB node, it prints a welcome text with some tips and CockroachDB version and cluster details:

# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CCL v19.1.0 (darwin amd64, built 2017/07/13 11:43:06, go1.10.1) (same version as client)
# Cluster ID: 7fb9f5b4-a801-4851-92e9-c0db292d03f1
#
# Enter \? for a brief introduction.
#
>

The Version and Cluster ID details are particularly noteworthy:

• When the client and server versions of CockroachDB are the same, the shell prints the Server version followed by (same version as client).
• When the client and server versions are different, the shell prints both the Client version and Server version. In this case, you may want to plan an upgrade of older client or server versions.
• Since every CockroachDB cluster has a unique ID, you can use the Cluster ID field to verify that your client is always connecting to the correct cluster.

Commands

The following commands can be used within the interactive SQL shell:

Client-side options

• To view option descriptions and how they are currently set, use \set without any options.
• To enable or disable an option, use \set <option> <value> or \unset <option> <value>. As of v2.1, you can also use the form <option>=<value>.
• If an option accepts a boolean value:
  • \set <option> without <value> is equivalent to \set <option> true, and \unset <option> without <value> is equivalent to \set <option> false.
  • As of v2.1, on and 0 are aliases for true, and off and 1 are aliases for false.

    Client Options	Description
    auto_trace	For every statement executed, the shell also produces the trace for that statement in a separate result below. A trace is also produced in case the statement produces a SQL error.Default: offTo enable this option, run \set auto_trace on.
    display_format	How to display table rows printed within the interactive SQL shell. Possible values: tsv, csv, table, raw, records, sql, html.Default: table for sessions that output on a terminal; tsv otherwiseTo change this option, run \set display_format <format>. For a demonstration, see the example below.
    echo	Reveal the SQL statements sent implicitly by the SQL shell.Default: falseTo enable this option, run \set echo. For a demonstration, see the example below.
    errexit	Exit the SQL shell upon encountering an error.Default: false for interactive sessions; true otherwiseTo enable this option, run \set errexit.
    check_syntax	Validate SQL syntax. This ensures that a typo or mistake during user entry does not inconveniently abort an ongoing transaction previously started from the interactive shell.Default: true for interactive sessions; false otherwise.To disable this option, run \unset check_syntax.
    show_times	Reveal the time a query takes to complete.Default: trueTo disable this option, run \unset show_times.
    smart_prompt	Query the server for the current transaction status and return it to the prompt. Note that this option is respected only when ECHO is enabled as well.Default: true for interactive sessions; false otherwiseTo disable this option, run \unset smart_prompt.

Help

Within the SQL shell, you can get interactive help about statements and functions:

Examples

> \h UPDATE

Command:     UPDATE
Description: update rows of a table
Category:    data manipulation
Syntax:
UPDATE <tablename> [[AS] <name>] SET ... [WHERE <expr>] [RETURNING <exprs...>]
See also:
  SHOW TABLES
  INSERT
  UPSERT
  DELETE
  https://www.cockroachlabs.com/docs/v2.1/update.html

> \hf uuid_v4

Function:    uuid_v4
Category:    built-in functions
Returns a UUID.
Signature          Category
uuid_v4() -> bytes [ID Generation]
See also:
  https://www.cockroachlabs.com/docs/v2.1/functions-and-operators.html

Shortcuts

The SQL shell supports many shortcuts, such as ctrl-r for searching the shell history. For full details, see this Readline Shortcut reference.

Examples

Start a SQL shell

In these examples, we connect a SQL shell to a secure cluster.

# Using standard connection flags:
$ cockroach sql \
--certs-dir=certs \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

# Using the --url flag:
$ cockroach sql \
--url="postgresql://maxroach@12.345.67.89:26257/critterdb?sslcert=certs/client.maxroach.crt&sslkey=certs/client.maxroach.key&sslmode=verify-full&sslrootcert=certs/ca.crt"

In these examples, we connect a SQL shell to an insecure cluster.

# Using standard connection flags:
$ cockroach sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

# Using the --url flag:
$ cockroach sql \
--url="postgresql://maxroach@12.345.67.89:26257/critterdb?sslmode=disable"

Execute SQL statement within the SQL shell

This example assume that we have already started the SQL shell (see examples above).

> CREATE TABLE animals (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);

> INSERT INTO animals (name) VALUES ('bobcat'), ('? '), ('barn owl');

> SELECT * FROM animals;

+--------------------+----------+
|         id         |   name   |
+--------------------+----------+
| 148899952591994881 | bobcat   |
| 148899952592060417 | ?        |
| 148899952592093185 | barn owl |
+--------------------+----------+

Execute SQL statements from the command line

In these examples, we use the —execute flag to execute statements from the command line:

# Statements with a single --execute flag:
$ cockroach sql --insecure \
--execute="CREATE TABLE roaches (name STRING, country STRING); INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States')" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

CREATE TABLE
INSERT 2

# Statements with multiple --execute flags:
$ cockroach sql --insecure \
--execute="CREATE TABLE roaches (name STRING, country STRING)" \
--execute="INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States')" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

CREATE TABLE
INSERT 2

In this example, we use the echo command to execute statements from the command line:

# Statements with the echo command:
$ echo "SHOW TABLES; SELECT * FROM roaches;" | cockroach sql --insecure --user=maxroach --host=12.345.67.89 --database=critterdb

+----------+
|  Table   |
+----------+
| roaches  |
+----------+
+-----------------------+---------------+
|         name          |    country    |
+-----------------------+---------------+
| American Cockroach    | United States |
| Brownbanded Cockroach | United States |
+-----------------------+---------------+

Control how table rows are printed

In these examples, we show tables and special characters printed in various formats.

When the standard output is a terminal, —format defaults to table and tables are printed with ASCII art and special characters are not escaped for easy human consumption:

$ cockroach sql --insecure \
--execute="SELECT '?' AS chick, '?' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

+-------+--------+
| chick | turtle |
+-------+--------+
| ?    | ?     |
+-------+--------+

However, you can explicitly set —format to another format, for example, tsv or html:

$ cockroach sql --insecure \
--format=tsv \
--execute="SELECT '?' AS chick, '?' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

1 row
chick   turtle
?    ?

$ cockroach sql --insecure \
--format=html \
--execute="SELECT '?' AS chick, '?' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

<table>
<thead><tr><th>chick</th><th>turtle</th></tr></head>
<tbody>
<tr><td>?</td><td>?</td></tr>
</tbody>
</table>

When piping output to another command or a file, —format defaults to tsv:

$ cockroach sql --insecure \
--execute="SELECT '?' AS chick, '?' AS turtle" > out.txt \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

$ cat out.txt

1 row
chick   turtle
?    ?

However, you can explicitly set —format to another format, for example, table:

$ cockroach sql --insecure \
--format=table \
--execute="SELECT '?' AS chick, '?' AS turtle" > out.txt \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

$ cat out.txt

+-------+--------+
| chick | turtle |
+-------+--------+
| ?    | ?     |
+-------+--------+
(1 row)

Make the output of SHOW statements selectable

To make it possible to select from the output of SHOW statements, set —format to raw:

$ cockroach sql --insecure \
--format=raw \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb

> SHOW CREATE customers;

# 2 columns
# row 1
## 14
test.customers
## 185
CREATE TABLE customers (
    id INT NOT NULL,
    email STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    UNIQUE INDEX customers_email_key (email ASC),
    FAMILY "primary" (id, email)
)
# 1 row

When —format is not set to raw, you can use the display_format SQL shell option to change the output format within the interactive session:

> \set display_format raw

# 2 columns
# row 1
## 14
test.customers
## 185
CREATE TABLE customers (
  id INT NOT NULL,
  email STRING NULL,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  UNIQUE INDEX customers_email_key (email ASC),
  FAMILY "primary" (id, email)
)
# 1 row

Execute SQL statements from a file

In this example, we show and then execute the contents of a file containing SQL statements.

$ cat statements.sql

CREATE TABLE roaches (name STRING, country STRING);
INSERT INTO roaches VALUES ('American Cockroach', 'United States'), ('Brownbanded Cockroach', 'United States');

$ cockroach sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb \
< statements.sql

CREATE TABLE
INSERT 2

Run external commands from the SQL shell

In this example, we use ! to look at the rows in a CSV file before creating a table and then using | to insert those rows into the table.

Note:
This example works only if the values in the CSV file are numbers. For values in other formats, use an online CSV-to-SQL converter or make your own import program.

> \! cat test.csv

12, 13, 14
10, 20, 30

> CREATE TABLE csv (x INT, y INT, z INT);

> \| IFS=","; while read a b c; do echo "insert into csv values ($a, $b, $c);"; done < test.csv;

> SELECT * FROM csv;

+----+----+----+
| x  | y  | z  |
+----+----+----+
| 12 | 13 | 14 |
| 10 | 20 | 30 |
+----+----+----+

In this example, we create a table and then use | to programmatically insert values.

> CREATE TABLE for_loop (x INT);

> \| for ((i=0;i<10;++i)); do echo "INSERT INTO for_loop VALUES ($i);"; done

> SELECT * FROM for_loop;

+---+
| x |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

Edit SQL statements in an external editor

In applications that use GNU Readline (such as bash), you can edit a long line in your preferred editor by typing Ctrl-x Ctrl-e. However, CockroachDB uses the BSD-licensed libedit, which does not include this functionality.

If you would like to be able to edit the current line in an external editor by typing C-x C-e as in bash, do the following:

• Install the vipe program (from the moreutils suite of tools).
• Edit your ~/.editrc to add the following line, which takes advantage of the SQL client's ability to run external commands:

cockroach:bind -s ^X^E '^A^K\\\| echo \"^Y\" | vipe\r'

This tells libedit to translate C-x C-e into the following commands:

• Move to the beginning of the current line.
• Cut the whole line.
• Paste the line into your editor via vipe.
• Pass the edited file back to the SQL client when vipe exits.

Note:

Future versions of the SQL client may opt to use a different back-end for reading input, in which case please refer to this page for additional updates.

Allow potentially unsafe SQL statements

The —safe-updates flag defaults to true. This prevents SQL statements that may have broad, undesired side-effects. For example, by default, we cannot use DELETE without a WHERE clause to delete all rows from a table:

$ cockroach sql --insecure --execute="SELECT * FROM db1.t1"

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
|  7 | g    |
|  8 | h    |
|  9 | i    |
| 10 | j    |
+----+------+
(10 rows)

$ cockroach sql --insecure --execute="DELETE FROM db1.t1"

Error: pq: rejected: DELETE without WHERE clause (sql_safe_updates = true)
Failed running "sql"

However, to allow an "unsafe" statement, you can set —safe-updates=false:

$ cockroach sql --insecure --safe-updates=false --execute="DELETE FROM db1.t1"

DELETE 10

Note:
Potentially unsafe SQL statements can also be allowed/disallowed for an entire session via the sql_safe_updates session variable.

Reveal the SQL statements sent implicitly by the command-line utility

In this example, we use the —execute flag to execute statements from the command line and the —echo-sql flag to reveal SQL statements sent implicitly:

$ cockroach sql --insecure \
--execute="CREATE TABLE t1 (id INT PRIMARY KEY, name STRING)" \
--execute="INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c')" \
--user=maxroach \
--host=12.345.67.89 \
--database=db1
--echo-sql

# Server version: CockroachDB CCL f8f3c9317 (darwin amd64, built 2017/09/13 15:05:35, go1.8) (same version as client)
# Cluster ID: 847a4ba5-c78a-465a-b1a0-59fae3aab520
> SET sql_safe_updates = TRUE
> CREATE TABLE t1 (id INT PRIMARY KEY, name STRING)
CREATE TABLE
> INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c')
INSERT 3

In this example, we start the interactive SQL shell and enable the echo shell option to reveal SQL statements sent implicitly:

$ cockroach sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=db1

> \set echo

> INSERT INTO db1.t1 VALUES (4, 'd'), (5, 'e'), (6, 'f');

> INSERT INTO db1.t1 VALUES (4, 'd'), (5, 'e'), (6, 'f');
INSERT 3
Time: 2.426534ms
> SHOW TRANSACTION STATUS
> SHOW DATABASE

See also

• Client Connection Parameters
• cockroach demo
• Other Cockroach Commands
• SQL Statements
• Learn CockroachDB SQL

Was this page helpful?
YesNo