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
or ctrl-d
.
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
# View help:
$ cockroach sql --help
Flags
The sql
command supports the following types of flags:
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 |
New in v1.1: 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 , pretty , raw , records , sql , html .Default: pretty for interactive sessions, tsv for non-interactive sessionsCorresponds to the display_format SQL shell option for use in interactive sessions. |
--safe-updates |
Changed in v2.0: 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 otherwise.Potentially unsafe SQL statements can also be allowed/disallowed for an entire session via the sql_safe_updates session variable. |
Client Connection
Flag | Description |
---|---|
--host |
The server host to connect to. This can be the address of any node in the cluster. Env Variable: COCKROACH_HOST Default: localhost |
--port -p |
The server port to connect to. Env Variable: COCKROACH_PORT Default: 26257 |
--user -u |
The SQL user that will own the client session. Env Variable: COCKROACH_USER Default: root |
--insecure |
Use an insecure connection. Env Variable: COCKROACH_INSECURE Default: false |
--certs-dir |
The path to the certificate directory containing the CA and client certificates and client key. Env Variable: COCKROACH_CERTS_DIR Default: ${HOME}/.cockroach-certs/ |
--url |
A connection URL to use instead of the other arguments. Env Variable: COCKROACH_URL Default: no URL |
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.
SQL Shell Welcome
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 v2.0.7 (darwin amd64, built 2017/07/13 11:43:06, go1.8) (same version as client)
# Cluster ID: 7fb9f5b4-a801-4851-92e9-c0db292d03f1
#
# Enter \? for a brief introduction.
#
>
New in v1.1: 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
andServer 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.
Interactive Sessions
cockroach sql
distinguishes between an interactive session and other sessions that generate terminal output. An interactive session is one where there's a user manually entering queries and looking at the results. This is detected using the following criteria:
- The
cockroach sql
command is used - Standard input is a terminal, not redirected
- The
--execute
flag is not used
When an interactive session is detected, the following options have their defaults changed:
- The
--format
flag (and its correspondingdisplay_format
option) default topretty
. - The
errexit
option defaults tofalse
. - The
check_syntax
option defaults totrue
. - The
smart_prompt
option defaults totrue
.
SQL Shell Commands
The following commands can be used within the interactive SQL shell:
Command | Usage |
---|---|
\q ctrl-d |
Exit the shell. When no text follows the prompt, ctrl-c exits the shell as well; otherwise, ctrl-c clears the line. |
\! |
Run an external command and print its results to stdout . See the example below. |
\| |
Run the output of an external command as SQL statements. See the example below. |
\set <option> |
Enable a client-side option. For available options, see SQL Shell Options. To view help and the current settings, use \set without any options. |
\unset <option> |
Disable a client-side option. For available options, see SQL Shell Options. |
\? help |
View this help within the shell. |
\h <statement> \hf <function> |
New in v1.1: View help for specific SQL statements or functions. See SQL Shell Help for more details. |
SQL Shell Options
To view help and the current settings, use \set
without any options. To enable or disable a shell option, use \set <option>
or \unset <option>
.
Client Options | Description |
---|---|
display_format |
Updated in v2.0: How to display table rows printed within the interactive SQL shell. Possible values: tsv , csv , pretty , raw , records , sql , html .Default: pretty when the output goes to a terminal and the --format flag is not passed at startup, tsv otherwise.To change this option, run \set display_format <format> . For a demonstration, see the example below. |
echo |
New in v1.1: Reveal the SQL statements sent implicitly by the SQL shell. Default: false . To enable it, run \set echo . For a demonstration, see the example below. |
errexit |
Updated in v2.0: Exit the SQL shell upon encountering an error. Default: false for interactive sessions, true otherwise. To enable it, 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 it, run \unset check_syntax . |
show_times |
New in v1.1: Reveal the time a query takes to complete. Default: true . To disable it, run \unset show_times . |
smart_prompt |
New in v1.1: Query the server for the current transaction status and return it to the prompt. Default: true for interactive sessions, false otherwise. However, it is respected only when ECHO is enabled as well. To disable this option, run \unset smart_prompt . |
SQL Shell Help
Within the SQL shell, you can get interactive help about statements and functions:
Command | Usage |
---|---|
\h ?? |
List all available SQL statements, by category. |
\hf |
List all available SQL functions, in alphabetical order. |
\h <statement> or <statement> ? |
View help for a specific SQL statement. |
\hf <function> or <function> ? |
View help for a specific SQL function. |
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/v1.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/v1.1/functions-and-operators.html
SQL Shell 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 \
--port=26257 \
--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 \
--port=26257 \
--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 assumes 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 \
--port=26257 \
--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 \
--port=26257 \
--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 --port=26257 --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 pretty
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 \
--port=26257 \
--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 \
--port=26257 \
--database=critterdb
1 row
chick turtle
🐥 🐢
$ cockroach sql --insecure \
--format=html \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--port=26257 \
--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 \
--port=26257 \
--database=critterdb
$ cat out.txt
1 row
chick turtle
🐥 🐢
However, you can explicitly set --format
to another format, for example, pretty
:
$ cockroach sql --insecure \
--format=pretty \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" > out.txt \
--user=maxroach \
--host=12.345.67.89 \
--port=26257 \
--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 \
--port=26257 \
--database=critterdb
> SHOW CREATE TABLE 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 \
--port=26257 \
--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.
> \! 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 |
+---+
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
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 \
--port=26257 \
--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 \
--port=26257 \
--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