Skip to main content
The cockroach sql command opens an interactive SQL shell for executing queries and managing databases.

​
Synopsis

cockroach sql [options]

​
Description

Open an SQL shell running against a CockroachDB database. The shell provides:
  • Interactive query execution
  • Statement history and editing
  • Multi-line statement support
  • Transaction management
  • Output formatting options

​
Connection Flags

​
--host
string
default:"localhost:26257"
Database server host and port to connect to.Can be specified as:
  • Hostname: --host=myserver
  • Hostname with port: --host=myserver:26257
  • IPv6 address: --host=[::1]:26257
Environment variable: COCKROACH_HOST
​
--user
string
default:"root"
Database user for authentication.Environment variable: COCKROACH_USER
​
--database
string
default:"defaultdb"
Database to connect to. Can optionally include virtual cluster:
-d mydb
-d cluster:mycluster/mydb
Environment variable: COCKROACH_DATABASE
​
--url
string
Connection URL (alternative to individual flags).Format: postgresql://[user[:password]@]host[:port]/[database][?options]
--url="postgresql://root@localhost:26257/mydb?sslmode=verify-full"
Environment variable: COCKROACH_URL

​
Security Flags

​
--certs-dir
string
default:"${HOME}/.cockroach-certs"
Path to directory containing client certificates.Required files:
  • ca.crt - CA certificate
  • client.<user>.crt - Client certificate
  • client.<user>.key - Client private key
​
--insecure
boolean
default:"false"
Connect without TLS encryption.
Only use --insecure for local development. Production clusters must use secure connections.

​
Query Execution Flags

​
--execute
string
Execute SQL statement(s) and exit. Multiple statements separated by semicolons.
cockroach sql --execute="SELECT * FROM users; SHOW TABLES;"
​
--file
string
Execute SQL statements from a file.
cockroach sql --file=schema.sql
​
--watch
duration
Repeat the SQL statement(s) at the specified interval.
cockroach sql -e "SELECT count(*) FROM orders" --watch=5s

​
Output Formatting Flags

​
--format
string
default:"table"
Output format for query results.Options:
  • table - ASCII table (default for interactive)
  • tsv - Tab-separated values
  • csv - Comma-separated values
  • sql - SQL statements
  • raw - Raw values without formatting
  • records - One column per line
  • html - HTML table
cockroach sql -e "SELECT * FROM users" --format=table
​
--set
string
Set a client-side option. Can be used multiple times.Common options:
  • display_format - Output format
  • errexit - Exit on error
  • echo - Echo statements before execution
cockroach sql --set=errexit=true --set=echo=true

​
Interactive Shell Commands

When running in interactive mode, these special commands are available:
CommandDescription
\qQuit the shell
\!Execute shell command
\setSet client option
\unsetUnset client option
\showShow current client options
\?Show help on backslash commands
\d [table]Describe table or list all tables
\dtList tables
\dTList types
\lList databases
\c [database]Connect to database
\echoPrint arguments to stdout

​
Examples

​
Connect to Local Cluster

cockroach sql --insecure

​
Connect to Secure Cluster

cockroach sql \
  --host=prod-cluster.example.com:26257 \
  --certs-dir=/path/to/certs \
  --user=admin \
  --database=myapp

​
Execute Single Statement

cockroach sql --insecure -e "CREATE DATABASE mydb;"

​
Execute Multiple Statements

cockroach sql --insecure -e "
  CREATE DATABASE mydb;
  USE mydb;
  CREATE TABLE users (id INT PRIMARY KEY, name STRING);
  INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
"

​
Run SQL File

cockroach sql --insecure --file=schema.sql

​
Connect with Full URL

cockroach sql --url="postgresql://root@localhost:26257/mydb?sslmode=disable"

​
Export Data as CSV

cockroach sql --insecure \
  -e "SELECT * FROM users" \
  --format=csv > users.csv

​
Watch Query Results

cockroach sql --insecure \
  -e "SELECT count(*) FROM active_sessions" \
  --watch=10s

​
Interactive Session Example

# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
root@localhost:26257/defaultdb> CREATE DATABASE myapp;
CREATE DATABASE

root@localhost:26257/defaultdb> \c myapp
root@localhost:26257/myapp> CREATE TABLE products (
                         ->   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                         ->   name STRING NOT NULL,
                         ->   price DECIMAL(10,2)
                         -> );
CREATE TABLE

root@localhost:26257/myapp> INSERT INTO products (name, price) 
                         -> VALUES ('Widget', 9.99), ('Gadget', 19.99);
INSERT 2

root@localhost:26257/myapp> SELECT * FROM products;
                   id                  |  name  | price
---------------------------------------+--------+--------
  7f9c7e8a-1234-5678-9abc-def012345678 | Widget |  9.99
  8a0d8f9b-2345-6789-0bcd-ef1234567890 | Gadget | 19.99
(2 rows)

root@localhost:26257/myapp> \q

​
SQL Shell Configuration

​
Client-Side Options

Set using \set in interactive mode or --set flag:
​
display_format
string
default:"table"
Output format (table, csv, tsv, html, records, sql, raw)
​
errexit
boolean
default:"false"
Exit on first error when executing from file or -e
​
echo
boolean
default:"false"
Echo SQL statements before executing
​
show_times
boolean
default:"true"
Display query execution times

​
Example Configuration

cockroach sql --insecure \
  --set=errexit=true \
  --set=echo=true \
  --set=show_times=false

​
Tips and Best Practices

Interactive Shell Tips:
  1. Use \? to see all available backslash commands
  2. Press Ctrl+C to cancel current statement input
  3. Use Ctrl+R for reverse search through command history
  4. Multi-line statements automatically continue until semicolon
  5. Use \d tablename for quick table schema inspection
Security Reminders:
  • Never hardcode passwords in scripts
  • Use ~/.pgpass file for password management
  • Restrict certificate file permissions: chmod 600 client.*.key
  • Always use TLS in production (--certs-dir, not --insecure)

​
Troubleshooting

​
Connection Refused

  • Verify the server is running: cockroach node status
  • Check hostname and port are correct
  • Ensure firewall allows connections on the SQL port

​
SSL/Certificate Errors

  • Verify certificates exist in --certs-dir
  • Check certificate permissions (keys should be 600)
  • Ensure certificates haven’t expired
  • Verify CA certificate matches server’s CA

​
Authentication Failures

  • Check username is correct
  • Verify user exists: run as root and query system.users
  • Ensure user has appropriate grants

​
See Also

Build docs developers (and LLMs) love

Get started for freeTalk to us