Skip to content

Latest commit

 

History

History
220 lines (148 loc) · 12.4 KB

cockroach-sql-binary.md

File metadata and controls

220 lines (148 loc) · 12.4 KB
title summary toc docs_area
cockroach-sql
cockroach-sql is a client for executing SQL statements from an interactive shell or directly from the command line.
true
reference.cli

The cockroach-sql command is a client for executing SQL statements from an interactive shell or directly from the command line. To use this client, run cockroach-sql as described below.

{{site.data.alerts.callout_info}} cockroach-sql is functionally equivalent to the cockroach sql command. {{site.data.alerts.end}}

To exit the interactive shell, enter \q, quit, exit, or Ctrl+D.

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.

Install cockroach-sql

Linux Mac Windows

Download the binary and copy it into your PATH.

{% include_cached copy-clipboard.html %}

curl https://binaries.cockroachdb.com/cockroach-sql-{{ page.release_info.version }}.linux-amd64.tgz | tar -xz && sudo cp -i cockroach-sql-{{ page.release_info.version }}.linux-amd64/cockroach-sql /usr/local/bin/ && if [ ! -f /usr/local/bin/cockroach ]; then sudo ln -s /usr/local/bin/cockroach-sql /usr/local/bin/cockroach; fi

If you don't have an existing cockroach binary in /usr/local/bin this will create a symbolic link to cockroach so you can use the cockroach sql command.

{% include_cached copy-clipboard.html %}

curl https://binaries.cockroachdb.com/cockroach-sql-{{ page.release_info.version }}.darwin-10.9-amd64.tgz | tar -xz && sudo cp -i cockroach-sql-{{ page.release_info.version }}.darwin-10.9-amd64/cockroach-sql /usr/local/bin && if [ ! -f /usr/local/bin/cockroach ]; then sudo ln -s /usr/local/bin/cockroach-sql /usr/local/bin/cockroach; fi

If you don't have an existing cockroach binary in /usr/local/bin this will create a symbolic link to cockroach so you can use the cockroach sql command.

Open a PowerShell terminal as an Administrator, then run the following command:

{% include_cached copy-clipboard.html %}

$ErrorActionPreference = "Stop"; [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12;$ProgressPreference = 'SilentlyContinue'; $null = New-Item -Type Directory -Force $env:appdata/cockroach; Invoke-WebRequest -Uri https://binaries.cockroachdb.com/cockroach-sql-{{ page.release_info.version }}.windows-6.2-amd64.zip -OutFile cockroach-sql.zip; Expand-Archive -Force -Path cockroach-sql.zip; Copy-Item -Force "cockroach-sql/cockroach-sql-{{ page.release_info.version }}.windows-6.2-amd64/cockroach-sql.exe" -Destination $env:appdata/cockroach; $Env:PATH += ";$env:appdata/cockroach"; if (!(Test-Path "$env:appdata/cockroach/cockroach.exe")) { New-Item -ItemType SymbolicLink -Path $env:appdata/cockroach/cockroach.exe -Target $env:appdata/cockroach/cockroach-sql.exe }

If you don't have an existing cockroach binary in $env:appdata/cockroach/ this will create a symbolic link to cockroach so you can use the cockroach sql command.

Or you can download the [binary from the releases page](../releases/{{ page.version.version }}.html) and install it manually.

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 option), 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:

{% include_cached copy-clipboard.html %}

$ cockroach-sql -e="<sql statement>;<sql statement>" -e="<sql-statement>" <flags>

{% include_cached copy-clipboard.html %}

$ echo "<sql statement>;<sql statement>" | cockroach-sql <flags>

{% include_cached copy-clipboard.html %}

$ cockroach-sql <flags> -f file-containing-statements.sql

Exit the interactive SQL shell:

> \q

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 otherwise

This 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 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 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

{% include {{ page.version.version }}/sql/connection-parameters.md %}

See Client Connection Parameters for more details.

Logging

{% include {{ page.version.version }}/misc/logging-defaults.md %}

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 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.
    • 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:

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 {{page.release_info.version}} (x86_64-apple-darwin17.7.0, built {{page.release_info.build_time}}) (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 earlier 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

{% include {{ page.version.version }}/sql/shell-commands.md %}

Client-side options

{% include {{ page.version.version }}/sql/shell-options.md %}

Help

{% include {{ page.version.version }}/sql/shell-help.md %}

Shortcuts

{% include {{ page.version.version }}/sql/shell-shortcuts.md %}

macOS terminal configuration

{% include {{ page.version.version }}/sql/macos-terminal-configuration.md %}

Error messages and SQLSTATE codes

{% include {{ page.version.version }}/sql/sql-errors.md %}

Examples

{% include {{ page.version.version }}/sql/sql-examples.md %}

See also