cockroach demo

On this page

Warning:

CockroachDB v20.2 is no longer supported as of May 10, 2022. For more details, refer to the Release Support Policy.

The cockroach demo command starts a temporary, in-memory CockroachDB cluster of one or more nodes, with or without a preloaded dataset, and opens an interactive SQL shell to the cluster.

All SQL shell commands, client-side options, help, and shortcuts supported by the cockroach sql command are also supported by cockroach demo.
The in-memory cluster persists only as long as the SQL shell is open. As soon as the shell is exited, the cluster and all its data are permanently destroyed. This command is therefore recommended only as an easy way to experiment with the CockroachDB SQL dialect.
By default, cockroach demo starts in secure mode using TLS certificates to encrypt network communication. It also serves a local DB Console that does not use TLS encryption.
Each instance of cockroach demo loads a temporary Enterprise license that expires after 24 hours. To prevent the loading of a temporary license, set the --disable-demo-license flag.

Synopsis

View help for cockroach demo:

$ cockroach demo --help

Start a single-node demo cluster with the movr dataset pre-loaded:

$ cockroach demo <flags>

Load a different dataset into a demo cluster:

$ cockroach demo <dataset> <flags>

Run the movr workload against a demo cluster:

$ cockroach demo --with-load <other flags>

Execute SQL from the command line against a demo cluster

$ cockroach demo --execute="<sql statement>;<sql statement>" --execute="<sql-statement>" <other flags>

Start a multi-node demo cluster:

$ cockroach demo --nodes=<number of nodes> <other flags>

Start a multi-region demo cluster with automatic geo-partitioning

$ cockroach demo --geo-partitioned-replicas <other flags>

Start a multi-region demo cluster with manually defined localities

$ cockroach demo --nodes=<number of nodes> --demo-locality=<key:value pair per node> <other flags>

Stop a demo cluster:

> \q

> quit

> exit

ctrl-d

Datasets

Tip:

By default, the movr dataset is pre-loaded into a demo cluster. To load a different dataset, use cockroach demo <dataset>. To start a demo cluster without a pre-loaded dataset, pass the --empty flag.

Workload	Description
`bank`	A `bank` database, with one `bank` table containing account details.
`intro`	An `intro` database, with one table, `mytable`, with a hidden message.
`kv`	A `kv` database, with one key-value-style table.
`movr`	A `movr` database, with several tables of data for the MovR example application. By default, `cockroach demo` loads the `movr` database as the current database, with sample region (`region`) and availability zone (`az`) replica localities for each node specified with the `--nodes` flag.
`startrek`	A `startrek` database, with two tables, `episodes` and `quotes`.
`tpcc`	A `tpcc` database, with a rich schema of multiple tables.
`ycsb`	A `ycsb` database, with a `usertable` from the Yahoo! Cloud Serving Benchmark.

Flags

General

The demo command supports the following general-use flags.

Flag	Description
`--cache`	For each demo node, the total size for caches. This can be a percentage (notated as a decimal or with `%`) or any bytes-based unit, for example: `--cache=.25` `--cache=25%` `--cache=1000000000 ----> 1000000000 bytes` `--cache=1GB ----> 1000000000 bytes` `--cache=1GiB ----> 1073741824 bytes` Default: `64MiB`
`--demo-locality`	Specify locality information for each demo node. The input is a colon-separated list of key-value pairs, where the i^th pair is the locality setting for the i^th demo cockroach node. For example, the following option assigns node 1's region to `us-east1` and availability zone to `1`, node 2's region to `us-east2` and availability zone to `2`, and node 3's region to `us-east3` and availability zone to `3`: `--demo-locality=region=us-east1,az=1:region=us-east1,az=2:region=us-east1,az=3` By default, `cockroach demo` uses sample region (`region`) and availability zone (`az`) replica localities for each node specified with the `--nodes` flag.
`--disable-demo-license`	Start the demo cluster without loading a temporary Enterprise license that expires after 24 hours. Setting the `COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING` environment variable will also prevent the loading of a temporary license, along with preventing the sharing of anonymized diagnostic details with Cockroach Labs.
`--echo-sql`	Reveal the SQL statements sent implicitly by the command-line utility. This can also be enabled within the interactive SQL shell via the `\set echo` shell command.
`--empty`	Start the demo cluster without a pre-loaded dataset.
`--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).
`--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 for use in interactive sessions.
`--geo-partitioned-replicas`	Start a 9-node demo cluster with the Geo-Partitioned Replicas topology pattern applied to the `movr` database.
`--insecure`	Include this to start the demo cluster in insecure mode. Env Variable: `COCKROACH_INSECURE`
`--max-sql-memory`	For each demo node, the maximum in-memory storage capacity for temporary SQL data, including prepared queries and intermediate data rows during query execution. This can be a percentage (notated as a decimal or with `%`) or any bytes-based unit, for example: `--max-sql-memory=.25` `--max-sql-memory=25%` `--max-sql-memory=10000000000 ----> 1000000000 bytes` `--max-sql-memory=1GB ----> 1000000000 bytes` `--max-sql-memory=1GiB ----> 1073741824 bytes` Default: `128MiB`
`--nodes`	Specify the number of in-memory nodes to create for the demo. Default: 1
`--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.
`--with-load`	Run a demo `movr` workload against the preloaded `movr` database. When running a multi-node demo cluster, load is balanced across all nodes.

Logging

By default, the demo command logs errors to stderr.

If you need to troubleshoot this command's behavior, you can change its logging behavior.

SQL shell

Welcome text

When the SQL shell connects to the demo cluster at startup, it prints a welcome text with some tips and cluster details. Most of these details resemble the welcome text that is printed when connecting cockroach sql to a permanent cluster. cockroach demo also includes some connection parameters for connecting to the DB Console or for connecting another SQL client to the demo cluster.

#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 1 node.
#
# This demo session will attempt to enable Enterprise features
# by acquiring a temporary license from Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Beginning initialization of the movr dataset, please wait...
#
# The cluster has been preloaded with the "movr" dataset
# (MovR is a fictional vehicle sharing company).
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# Connection parameters:
#   (console) http://127.0.0.1:59403
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo635924269&port=26257
#   (sql/tcp) postgres://root:admin@127.0.0.1:59405?sslmode=require
#
#
# The user "root" with password "admin" has been created. Use it to access the Web UI!
#
# Server version: CockroachDB CCL v20.2.0 (x86_64-apple-darwin19.6.0, built , go1.14.4) (same version as client)
# Cluster ID: d4055073-8b30-490b-97bf-39ced0cd6471
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
##

Connection parameters

The SQL shell welcome text includes connection parameters for accessing the DB Console and for connecting other SQL clients to the demo cluster:

# Connection parameters:
#   (console) http://127.0.0.1:50037
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo294284060&port=26257
#   (sql/tcp) postgres://root:admin@127.0.0.1:50039?sslmode=require

Parameter	Description
`console`	Use this link to access a local DB Console. To login, use the `root` user with password `admin`.
`sql`	Use this connection URL to establish a Unix domain socket connection with a client that is installed on the same machine.
`sql/tcp`	Use this connection URL for standard sql/tcp connections from other SQL clients such as `cockroach sql`.

Note:

You do not need to create or specify node and client certificates in sql or sql/tcp connection URLs.

When running a multi-node demo cluster, use the \demo ls shell command to list the connection parameters for all nodes:

> \demo ls

node 1:
  (console) http://127.0.0.1:50037
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo294284060&port=26257
  (sql/tcp) postgres://root:admin@127.0.0.1:50039?sslmode=require

node 2:
  (console) http://127.0.0.1:50040
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo294284060&port=26258
  (sql/tcp) postgres://root:admin@127.0.0.1:50042?sslmode=require

node 3:
  (console) http://127.0.0.1:50048
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo294284060&port=26259
  (sql/tcp) postgres://root:admin@127.0.0.1:50050?sslmode=require

General

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.
`\show`	During a multi-line statement or transaction, show the SQL entered so far.
`\h <statement>` `\hf <function>`	View help for specific SQL statements or functions. See SQL shell help for more details.
`\l`	List all databases in the CockroachDB cluster. This command is equivalent to `SHOW DATABASES`.
`\dt` `d`	Show the tables of the current schema in the current database. These commands are equivalent to `SHOW TABLES`.
`\dT`	New in v20.2: Show the user-defined types in the current database. This command is equivalent to `SHOW TYPES`.
`\du`	List the users for all databases. This command is equivalent to `SHOW USERS`.
`\d <table>`	Show details about columns in the specified table. This command is equivalent to `SHOW COLUMNS`.

Demo-specific

cockroach demo offers the following additional shell commands. Note that these commands are experimental and their interface and output are subject to change.

Command	Usage
`\demo ls`	List the demo nodes and their connection URLs.
`\demo shutdown <node number>`	Shuts down a node in a multi-node demo cluster. This command simulates stopping a node that can be restarted. See an example.
`\demo restart <node number>`	Restarts a node in a multi-node demo cluster. See an example.
`\demo decommission <node number>`	Decommissions a node in a multi-node demo cluster. This command simulates decommissioning a node.
`\demo recommission <node number>`	Recommissions a decommissioned node in a multi-node demo cluster.

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 and 1 are aliases for true, and off and 0 are aliases for false.

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`.
`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` otherwise To 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` otherwise To enable this option, run `\set errexit`.
`show_times`	Reveal the time a query takes to complete. Possible values: `execution` time refers to the time taken by the SQL execution engine to execute the query. `network` time refers to the network latency between the server and the SQL client command. `other` time refers to all other forms of latency affecting the total query completion time, including query planning. Default: `true` To disable this option, run `\unset show_times`.

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/v2.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/v2.1/functions-and-operators.html

Shortcuts

The SQL shell supports many shortcuts, such as ctrl-r for searching the shell history. For full details, see this Readline Shortcut reference.

Diagnostics reporting

By default, cockroach demo shares anonymous usage details with Cockroach Labs. To opt out, set the diagnostics.reporting.enabled cluster setting to false. You can also opt out by setting the COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING environment variable to false before running cockroach demo.

Examples

In these examples, we demonstrate how to start a shell with cockroach demo. For more SQL shell features, see the cockroach sql examples.

Start a single-node demo cluster

$ cockroach demo

By default, cockroach demo loads the movr dataset in to the demo cluster:

> SHOW TABLES;

  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)

You can query the pre-loaded data:

> SELECT name FROM users LIMIT 10;

         name
-----------------------
  Tyler Dalton
  Dillon Martin
  Deborah Carson
  David Stanton
  Maria Weber
  Brian Campbell
  Carl Mcguire
  Jennifer Sanders
  Cindy Medina
  Daniel Hernandez MD
(10 rows)

You can also create and query new tables:

> CREATE TABLE drivers (
    id UUID DEFAULT gen_random_uuid(),
    city STRING NOT NULL,
    name STRING,
    dl STRING UNIQUE,
    address STRING,
    CONSTRAINT primary_key PRIMARY KEY (city ASC, id ASC)
);

> INSERT INTO drivers (city, name) VALUES ('new york', 'Catherine Nelson');

> SELECT * FROM drivers;

                   id                  |   city   |       name       |  dl  | address
+--------------------------------------+----------+------------------+------+---------+
  df3dc272-b572-4ca4-88c8-e9974dbd381a | new york | Catherine Nelson | NULL | NULL
(1 row)

Start a multi-node demo cluster

$ cockroach demo --nodes=3

Load a sample dataset into a demo cluster

By default, cockroach demo loads the movr dataset in to the demo cluster. To pre-load any of the other available datasets using cockroach demo <dataset>. For example, to load the ycsb dataset:

$ cockroach demo ycsb

> SHOW TABLES;

  schema_name | table_name | type  | owner | estimated_row_count
--------------+------------+-------+-------+----------------------
  public      | usertable  | table | demo  |                   0
(1 row)

Run load against a demo cluster

$ cockroach demo --with-load

This command starts a demo cluster with the movr database preloaded and then inserts rows into each table in the movr database. You can monitor the workload progress on the DB Console.

When running a multi-node demo cluster, load is balanced across all nodes.

Execute SQL from the command-line against a demo cluster

$ cockroach demo \
--execute="CREATE TABLE drivers (
    id UUID DEFAULT gen_random_uuid(),
    city STRING NOT NULL,
    name STRING,
    dl STRING UNIQUE,
    address STRING,
    CONSTRAINT primary_key PRIMARY KEY (city ASC, id ASC)
);" \
--execute="INSERT INTO drivers (city, name) VALUES ('new york', 'Catherine Nelson');" \
--execute="SELECT * FROM drivers;"

CREATE TABLE
INSERT 1
                   id                  |   city   |       name       |  dl  | address
+--------------------------------------+----------+------------------+------+---------+
  df3dc272-b572-4ca4-88c8-e9974dbd381a | new york | Catherine Nelson | NULL | NULL
(1 row)

Connect an additional SQL client to the demo cluster

In addition to the interactive SQL shell that opens when you run cockroach demo, you can use the connection parameters in the welcome text to connect additional SQL clients to the cluster.

First, use \demo ls to list the connection parameters for each node in the demo cluster:

> \demo ls

node 1:
  (console) http://127.0.0.1:54880
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo200406637&port=26257
  (sql/tcp) postgres://root:admin@127.0.0.1:54882?sslmode=require

node 2:
  (console) http://127.0.0.1:54883
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo200406637&port=26258
  (sql/tcp) postgres://root:admin@127.0.0.1:54885?sslmode=require

node 3:
  (console) http://127.0.0.1:54891
  (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo200406637&port=26259
  (sql/tcp) postgres://root:admin@127.0.0.1:54893?sslmode=require

Then open a new terminal and run cockroach sql with the --url flag set to the sql/tcp connection URL of the node to which you want to connect:

$ cockroach sql --url='postgres://root:admin@127.0.0.1:54885?sslmode=require'

You can also use this URL to connect an application to the demo cluster.

Start a multi-region demo cluster with automatic geo-partitioning

$ cockroach demo --geo-partitioned-replicas

This command starts a 9-node demo cluster with the movr database preloaded, and partitions and zone constraints applied to the primary and secondary indexes. For more information, see the Geo-Partitioned Replicas topology pattern.

Shut down and restart nodes in a multi-node demo cluster

In a multi-node demo cluster, you can use \demo shell commands to shut down, restart, decommission, and recommission individual nodes.

Warning:

This is an experimental feature. The interface and output are subject to change.

$ cockroach demo --nodes=3

You can shutdown the 3rd node and then restart it:

> \demo shutdown 3

node 3 has been shutdown

> \demo restart 3

node 3 has been restarted

You can also decommission the 3rd node and then recommission it:

> \demo decommission 3

node 3 has been decommissioned

> \demo recommission 3

node 3 has been recommissioned

Try your own scenario

In addition to using one of the pre-loaded dataset, you can create your own database (e.g., CREATE DATABASE <yourdb>;), or use the empty defaultdb database (e.g., SET DATABASE defaultdb;) to test our your own scenario involving any CockroachDB SQL features you are interested in.

Cockroach
University

Docs Hub

cockroach demo

Synopsis

Datasets

Flags

General

Logging

SQL shell

Welcome text

Connection parameters

Commands

General

Demo-specific

Client-side options

Help

Examples

Shortcuts

Diagnostics reporting

Examples

Start a single-node demo cluster

Start a multi-node demo cluster

Load a sample dataset into a demo cluster

Run load against a demo cluster

Execute SQL from the command-line against a demo cluster

Connect an additional SQL client to the demo cluster

Start a multi-region demo cluster with automatic geo-partitioning

Shut down and restart nodes in a multi-node demo cluster

Try your own scenario

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

cockroach demo

Synopsis

Datasets

Flags

General

Logging

SQL shell

Welcome text

Connection parameters

Commands

General

Demo-specific

Client-side options

Help

Examples

Shortcuts

Diagnostics reporting

Examples

Start a single-node demo cluster

Start a multi-node demo cluster

Load a sample dataset into a demo cluster

Run load against a demo cluster

Execute SQL from the command-line against a demo cluster

Connect an additional SQL client to the demo cluster

Start a multi-region demo cluster with automatic geo-partitioning

Shut down and restart nodes in a multi-node demo cluster

Try your own scenario

See also

Cockroach
University

Cockroach
University