> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# cockroach sql
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
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` or `SQLLOGIN`, which are granted by default. If the user has been set to use the `NOLOGIN` role or the `NOSQLLOGIN` system privilege (or the legacy `NOSQLLOGIN` 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](#macos-terminal-configuration) below for details.
## Synopsis
Start the interactive SQL shell:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql
```
Execute SQL from the command line:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --execute="
```
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ echo "
```
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --file file-containing-statements.sql
```
Exit the interactive SQL shell:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> \q
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> quit
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> exit
```
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> Ctrl+D
```
View help:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --help
```
## Flags
The `sql` command supports the following types of flags:
* [General Use](#general)
* [Client Connection](#client-connection)
* [Logging](#logging)
### General
* To start an interactive SQL shell, run `cockroach sql` with all appropriate connection flags or use just the [`--url` flag](#sql-flag-url), which includes connection details.
* To execute SQL statements from the command line, use the [`--execute` flag](#sql-flag-execute).
| Flag | Description |
| ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `--database`
`-d` | A database name to use as current database in the newly created session. |
| `--embedded` | Minimizes the SQL shell [welcome text](#welcome-message) 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](#reveal-the-sql-statements-sent-implicitly-by-the-command-line-utility) below.
This can also be enabled within the interactive SQL shell via the `\set echo` [shell command](#commands). |
| `--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](#execute-sql-statements-from-the-command-line) below. |
| `--file `
`-f ` | Read SQL statements from ` `--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](#session-and-output-types); `tsv` otherwise
This flag corresponds to the `display_format` [client-side option](#client-side-options). |
| `--read-only` | Sets the `default_transaction_read_only` session variable to `on` upon connecting. |
| `--safe-updates` | Disallow potentially unsafe SQL statements. For the complete list of disallowed statements, refer to the documentation for the `sql_safe_updates` session variable.
**Default:** `true` for [interactive sessions](#session-and-output-types); `false` otherwise
Potentially 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](#client-side-options) 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](#repeat-a-sql-statement) 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.
`-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 `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](#sql-option-errexit) defaults to `false`.
* The [`check_syntax` option](#sql-option-check-syntax) 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.
* The shell continues to read input after the last command entered.
* A session **outputs on a terminal** when output is not redirected to a file. In such cases:
* The [`--format` flag](#sql-flag-format) and its corresponding [`display_format` option](#sql-option-display-format) 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:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL (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` 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.
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 the `ccloud` command, refer to Get Started with the `ccloud` 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