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.
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.
The output of cockroach sql
when used non-interactively is part of a stable interface, and can be used programmatically, with the exception of informational output lines that begin with the hash symbol (#
). Informational output can change from release to release, and should not be used programmatically.
Before you begin
- The role option of the user logging in must be
LOGIN
orSQLLOGIN
, which are granted by default. If the user has been set to use theNOLOGIN
role or theNOSQLLOGIN
system privilege (or the legacyNOSQLLOGIN
role), the user cannot log in using the SQL CLI with any authentication method. - macOS users only: By default, macOS-based terminals do not enable handling of the Alt key modifier. This prevents access to many keyboard shortcuts in the unix shell and
cockroach sql
. See the section macOS terminal configuration below for details.
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 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
- 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. |
--embedded |
Minimizes the SQL shell welcome text to be appropriate for embedding in playground-type environments. Specifically, this flag removes details that users in an embedded environment have no control over (e.g., networking information). |
--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. |
--file <filename> -f <filename> |
Read SQL statements from <filename> . |
--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. |
--read-only |
Sets the default_transaction_read_only session variable to on upon connecting. |
--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. |
--watch |
Repeat the SQL statements specified with --execute or -e until a SQL error occurs or the process is terminated. --watch applies to all --execute or -e flags in use.You must also specify an interval at which to repeat the statement, followed by a time unit. For example, to specify an interval of 5 seconds, use 5s .Note that this flag is intended for simple monitoring scenarios during development and testing. See the example below. |
Client connection
Flag | Description |
---|---|
--url |
A connection URL to use instead of the other arguments. To convert a connection URL to the syntax that works with your client driver, run cockroach convert-url .Env Variable: COCKROACH_URL Default: no URL |
--host |
The server host and port number to connect to. This can be the address of any node in the cluster. Env Variable: COCKROACH_HOST Default: localhost:26257 |
--port -p |
The server port to connect to. Note: The port number can also be specified via --host . 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 |
--cert-principal-map |
A comma-separated list of <cert-principal>:<db-principal> mappings. This allows mapping the principal in a cert to a DB principal such as node or root or any SQL user. This is intended for use in situations where the certificate management system places restrictions on the Subject.CommonName or SubjectAlternateName fields in the certificate (e.g., disallowing a CommonName like node or root ). If multiple mappings are provided for the same <cert-principal> , the last one specified in the list takes precedence. A principal not specified in the map is passed through as-is via the identity function. A cert is allowed to authenticate a DB principal if the DB principal name is contained in the mapped CommonName or DNS-type SubjectAlternateName fields. |
--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/ |
See Client Connection Parameters for more details.
Logging
By default, this command logs messages to stderr
. This includes events with WARNING
severity and higher.
If you need to troubleshoot this command's behavior, you can customize 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-e
or-f
flag, and the input is a terminal. In such cases:- The
errexit
option defaults tofalse
. - The
check_syntax
option defaults totrue
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 totrue
on the server. - The shell continues to read input after the last command entered.
- The
- A session outputs on a terminal when output is not redirected to a file. In such cases:
- The
--format
flag and its correspondingdisplay_format
option default totable
. These default totsv
otherwise. - The
show_times
option defaults totrue
.
- The
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 CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v23.1.24 (x86_64-apple-darwin17.7.0, built 2019/09/13 00:07:19, go1.12.6) (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
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.Note:For clusters deployed in CockroachDB Cloud, do not use the cluster ID printed in the welcome message to verify the cluster your client is connected to. Instead, use the
ccloud cluster list
command to list the ID of each cluster in your CockroachDB Cloud organization to which you have access. To learn more about theccloud
command, refer to Get Started with theccloud
CLI.
Commands
The following commands can be used within the interactive SQL shell:
Command | Usage |
---|---|
\? ,help |
View this help within the shell. |
\q ,quit ,exit ,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 an example. |
\| |
Run the output of an external command as SQL statements. See an example. |
\set <option> ,\unset <option> |
Enable or disable a client-side option. For more details, see Client-side options. You can also use the --set flag to enable or disable client-side options before starting the SQL shell. |
\p ,\show |
During a multi-line statement or transaction, show the SQL that has been entered but not yet executed.\show was deprecated as of v21.1. Use \p instead. |
\h <statement> ,\hf <function> |
View help for specific SQL statements or functions. See SQL shell help for more details. |
\c <option> ,\connect <option> |
Display or change the current connection parameters. Using \c without an argument lists the current connection parameters. To reuse the existing connection and change the current database, use \c <dbname> . This is equivalent to SET <database> and USE <database> . To connect to a cluster using individual connection parameters, use \c <dbname> <user> <host> <port> . Use the dash character (- ) to omit one parameter. To reconnect to the cluster using the current connection parameters enter \c - . When using individual connection parameters, the TLS settings from the original connection are reused. To use different TLS settings, connect using a connection URL. To connect to a cluster using a connection URL use \c <url> |
\l |
List all databases in the CockroachDB cluster. This command is equivalent to SHOW DATABASES . |
\d[S+] [<pattern>] |
New in v23.1: Show details about the relations in the current database. By default this command will show all the user tables, indexes, views, materialized views, and sequences in the current database. Add the S modifier to also show all system objects. If you specify a relation or a pattern, it will show the details of matching relations. Add the + modifier to show additional information. |
\dC[+] [<pattern>] |
New in v23.1: Show the type casts. If you specify a type or a pattern, it will show the details of matching types. Add the + modifier to show additional information. |
\dd[S] [<pattern>] |
New in v23.1: Show the objects of type constraint in the current database. Add the S modifier to also show all system objects. If you specify a type or a pattern, it will show the details of matching objects. |
\df[S+] [<pattern>] |
New in v23.1: Show the user-defined functions of the current database. Add the S modifier to also show all system functions. If you specify a function name or a pattern, it will show the details of matching function. Add the + modifier to show additional information. |
\dg[S+] [<pattern>] |
New in v23.1: Show the roles of the current database. Add the S modifier to also show all system objects. If you specify a role name or a pattern, it will show the details of matching roles. Add the + modifier to show additional information. |
\di[S+] [<pattern>] |
New in v23.1: Show the indexes of the current database. Add the S modifier to also show all system objects. If you specify an index name or a pattern, it will show the details of matching indexes. Add the + modifier to show additional information. |
\dm[S+] [<pattern>] |
New in v23.1: Show the materialized views of the current database. Add the S modifier to also show all system objects. If you specify a materialized view name or a pattern, it will show the details of matching materialized views. Add the + modifier to show additional information. |
\dn[S+] [<pattern>] |
New in v23.1: List all schemas in the current database. Add the S modifier to also show all system schemas. Add the + modifier to show the permissions of each schema. Specify a pattern to limit the output to schemas that match the pattern. These commands are equivalent to SHOW SCHEMAS . |
\ds[S+] [<pattern>] |
New in v23.1: Show the sequences of the current database. Add the S modifier to also show all system objects. If you specify a sequence name or a pattern, it will show the details of matching sequences. Add the + modifier to show additional information. |
\dt[S+] [<pattern>] |
New in v23.1: Show the tables of the current database. Add the S modifier to also show all system objects. If you specify a table name or a pattern, it will show the details of matching tables. Add the + modifier to show additional information. |
\dT[S+] [<pattern>] |
Show the user-defined types in the current database. Add the S modifier to also show all system objects. If you specify a type name or a pattern, it will show the details of matching types. Add the + modifier to show additional information. |
\du[S+] [<pattern>] |
Show the roles of the current database. Add the S modifier to also show all system objects. If you specify a role name or a pattern, it will show the details of matching roles. Add the + modifier to show additional information. |
\dv[S+] [<pattern>] |
New in v23.1: Show the views of the current database. Add the S modifier to also show all system objects. If you specify a view name or a pattern, it will show the details of matching views. Add the + modifier to show additional information. |
\r |
Resets the query input buffer, clearing all SQL statements that have been entered but not yet executed. |
\statement-diag list |
List available diagnostic bundles. |
\statement-diag download <bundle-id> [<filename>] |
Download diagnostic bundle. |
\i <filename> |
Reads and executes input from the file <filename> , in the current working directory. |
\ir <filename> |
Reads and executes input from the file <filename> .When invoked in the interactive shell, \i and \ir behave identically (i.e., CockroachDB looks for <filename> in the current working directory). When invoked from a script, CockroachDB looks for <filename> relative to the directory in which the script is located. |
\echo <arguments> |
Evaluate the <arguments> and print the results to the standard output. |
\x <boolean> |
When true /on /yes /1 , sets the display format to records . When false /off /no /0 , sets the session's format to the default (table /tsv ). |
Patterns
Commands use the SQL LIKE
syntax for string pattern matching, not POSIX regular expressions.
For example to list all schemas that begin with the letter "p" you'd use the following pattern:
\dn p%
List of schemas:
Name | Owner
---------------+--------
pg_catalog | NULL
pg_extension | NULL
public | admin
(3 rows)
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>
. 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
.on
,yes
, and1
are aliases fortrue
, andoff
,no
, and0
are aliases forfalse
.
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: off To enable this option, run \set auto_trace on . |
border |
Display a border around the output of the SQL statement when using the table display format. Set the level of borders using border=<level> to configure how many borders and lines are in the output, where <level> is an integer between 0 and 3. The higher the integer, the more borders and lines are in the output. A level of 0 shows the output with no outer lines and no row line separators. A level of 1 adds row line separators. A level of 2 adds an outside border and no row line separators. A level of 3 adds both an outside border and row line separators. Default: 0 To change this option, run \set border=<level> . See an example. |
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 . |
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> . See an example. |
echo |
Reveal the SQL statements sent implicitly by the SQL shell. Default: false To enable this option, run \set echo . See an example. |
errexit |
Exit the SQL shell upon encountering an error. Default: false for interactive sessions; true otherwiseTo enable this option, run \set errexit . |
prompt1 |
Customize the interactive prompt within the SQL shell. See Customizing the prompt for information on the available prompt variables. |
show_times |
Reveal the time a query takes to complete. Possible values:
Default: true To disable this option, run \unset show_times . |
Customizing the prompt
The \set prompt1
option allows you to customize the interactive prompt in the SQL shell. Use the following prompt variables to set a custom prompt.
Prompt variable | Description |
---|---|
%> |
The port of the node you are connected to. |
%/ |
The current database name. |
%M |
The fully qualified host name and port of the node. |
%m |
The fully qualified host name of the node. |
%n |
The username of the connected SQL user. |
%x |
The transaction status of the current statement. |
For example, to change the prompt to just the user, host, and database:
\set prompt1 %n@%m/%/
maxroach@blue-dog-595.g95.cockroachlabs.cloud/defaultdb>
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> <statement> ? |
View help for a specific SQL statement. |
\hf <function> <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/v23.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/v23.1/functions-and-operators.html
Shortcuts
Note: macOS users may need to manually enable Alt-based shortcuts in their terminal configuration. See the section macOS terminal configuration below for details.
Shortcut | Description |
---|---|
Tab | New in v23.1: Use context-sensitive command completion. |
Ctrl+C | Clear/cancel the input. |
Ctrl+M, Enter | New line/enter. |
Ctrl+O | Force a new line on the current statement, even if the statement has a semicolon. |
Ctrl+F, Right arrow | Forward one character. |
Ctrl+B, Left arrow | Backward one character. |
Alt+F, Ctrl+Right arrow | Forward one word. |
Alt+B, Ctrl+Left arrow | Backward one word. |
Ctrl+L | Refresh the display. |
Delete | Delete the next character. |
Ctrl+H, Backspace | Delete the previous character. |
Ctrl+D | Delete the next character, or terminate the input if the input is currently empty. |
Alt+D, Alt+Delete | Delete next word. |
Ctrl+W, Alt+Backspace | Delete previous word. |
Ctrl+E, End | End of line. |
Alt+>, Ctrl+End | New in v23.1: Move cursor to the end of a multi-line statement. |
Ctrl+A, Home | Move cursor to the beginning of the current line. |
Alt+<, Ctrl+Home | New in v23.1: Move cursor to the beginning of a multi-line statement. |
Ctrl+T | Transpose current and next characters. |
Ctrl+K | Delete from cursor position until end of line. |
Ctrl+U | Delete from beginning of line to cursor position. |
Alt+Q | New in v23.1: Reflow/reformat the current line. |
Alt+Shift+Q, Alt+` | New in v23.1: Reflow/reformat the entire input. |
Alt+L | Convert the current word to lowercase. |
Alt+U | Convert the current word to uppercase. |
Alt+. | New in v23.1: Toggle the visibility of the prompt. |
Alt+2, Alt+F2 | New in v23.1: Invoke external editor on current input. |
Alt+P, Up arrow | Recall previous history entry. |
Alt+N, Down arrow | Recall next history entry. |
Ctrl+R | Start searching through input history. |
When searching for history entries, the following shortcuts are active:
Shortcut | Description |
---|---|
Ctrl+C, Ctrl+G | Cancel the search, return to normal mode. |
Ctrl+R | Recall next entry matching current search pattern. |
Enter | Accept the current recalled entry. |
Backspace | Delete previous character in search pattern. |
Other | Add character to search pattern. |
Tab completion
New in v23.1: The SQL client offers context-sensitive tab completion when entering commands. Use the Tab key on your keyboard when entering a command to initiate the command completion interface. You can then navigate to database objects, keywords, and functions using the arrow keys. Press the Tab key again to select the object, function, or keyword from the command completion interface and return to the console.
macOS terminal configuration
In Apple Terminal:
- Navigate to "Preferences", then "Profiles", then "Keyboard".
- Enable the checkbox "Use Option as Meta Key".
In iTerm2:
- Navigate to "Preferences", then "Profiles", then "Keys".
- Select the radio button "Esc+" for the behavior of the Left Option Key.
Error messages and SQLSTATE
codes
When CockroachDB encounters a SQL error, it returns the following information to the client (whether cockroach-sql
or another client application):
- An error message, prefixed with the "Severity" field of the PostgreSQL wire protocol. For example,
ERROR: insert on table "shipments" violates foreign key constraint "fk_customers"
. - A 5-digit
SQLSTATE
error code as defined by the SQL standard. For example,SQLSTATE: 23503
.
For example, the following query (taken from this example of adding multiple foreign key constraints) results in a SQL error, and returns both an error message and a SQLSTATE
code as described above.
> INSERT INTO shipments (carrier, status, customer_id) VALUES ('DHL', 'At facility', 2000);
ERROR: insert on table "shipments" violates foreign key constraint "fk_customers"
SQLSTATE: 23503
DETAIL: Key (customer_id)=(2000) is not present in table "customers".
The SQLSTATE
code in particular can be helpful in the following ways:
- It is a standard SQL error code that you can look up in documentation and search for on the web. For any given error state, CockroachDB tries to produce the same
SQLSTATE
code as PostgreSQL. - If you are developing automation that uses the CockroachDB SQL shell, it is more reliable to check for
SQLSTATE
values than for error message strings, which are likely to change.
Examples
Start a SQL shell
In these examples, we connect a SQL shell to a secure cluster.
# 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"
# Using standard connection flags:
$ cockroach-sql \
--certs-dir=certs \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb
In these examples, we connect a SQL shell to an insecure cluster.
# Using the --url flag:
$ cockroach-sql \
--url="postgresql://maxroach@12.345.67.89:26257/critterdb?sslmode=disable"
# Using standard connection flags:
$ cockroach-sql --insecure \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb
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
---------------------+-----------
710907071259213825 | bobcat
710907071259279361 | 🐢
710907071259312129 | barn owl
(3 rows)
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
schema_name | table_name | type | owner | estimated_row_count | locality
--------------+------------+-------+-------+---------------------+-----------
public | animals | table | demo | 0 | NULL
public | roaches | table | demo | 0 | NULL
(2 rows)
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 (e.g., tsv
or html
):
$ cockroach-sql --insecure \
--format=tsv \
--execute="SELECT '🐥' AS chick, '🐢' AS turtle" \
--user=maxroach \
--host=12.345.67.89 \
--database=critterdb
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>row</th><th>chick</th><th>turtle</th></tr></thead>
<tbody>
<tr><td>1</td><td>🐥</td><td>🐢</td></tr>
</tbody>
<tfoot><tr><td colspan=3>1 row</td></tr></tfoot></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 (e.g., 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)
Show borders around the statement output within the SQL shell
To display outside and inside borders in the statement output, set the border
SQL shell option to 3
.
\set border=3
SELECT * FROM animals;
+--------------------+----------+
| id | name |
+--------------------+----------+
| 710907071259213825 | bobcat |
+--------------------+----------+
| 710907071259279361 | 🐢 |
+--------------------+----------+
| 710907071259312129 | barn owl |
+--------------------+----------+
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 \
-f 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
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
Repeat a SQL statement
Repeating SQL queries on a table can be useful for monitoring purposes. With the --watch
flag, you can repeat the statements specified with a --execute
or -e
flag periodically, until a SQL error occurs or the process is terminated.
For example, if you want to monitor the number of queries running on the current node, you can use cockroach-sql
with the --watch
flag to query the node's crdb_internal.node_statement_statistics
table for the query count:
$ cockroach-sql --insecure \
--execute="SELECT SUM(count) FROM crdb_internal.node_statement_statistics" \
--watch 1m
sum
-------
926
(1 row)
sum
--------
4227
(1 row)
^C
In this example, the statement is executed every minute. We let the process run for a couple minutes before terminating it with Ctrl+C.
Connect to a cluster listening for Unix domain socket connections
To connect to a cluster that is running on the same machine as your client and is listening for Unix domain socket connections, specify a Unix domain socket URI with the --url
connection parameter.
For example, suppose you start a single-node cluster with the following cockroach start-single-node
command:
$ cockroach start-single-node --insecure --socket-dir=/tmp
CockroachDB node starting at (took 1.3s)
build: CCL v23.1.24 @ 2024-07-18 00:00:00
webui: http://Jesses-MBP-2:8080
sql: postgresql://root@Jesses-MBP-2:26257?sslmode=disable
RPC client flags: ./cockroach <client cmd> --host=Jesses-MBP-2:26257 --insecure
socket: /tmp/.s.PGSQL.26257
logs: /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/logs
temp dir: /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/cockroach-temp805054895
external I/O path: /Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data/extern
store[0]: path=/Users/jesseseldess/Downloads/cockroach-.darwin-10.9-amd64/cockroach-data
storage engine: pebble
status: initialized new cluster
clusterID: 455ad71d-21d4-424a-87ad-8097b6b5b99f
nodeID: 1
To connect to this cluster with a socket:
$ cockroach-sql --url='postgres://root@?host=/tmp&port=26257'