CockroachDB Basics

Introduction

In this tutorial we’ll show you the basic operations you can perform on a cluster in CockroachDB. We’ll demonstrate the common operations of starting a cluster, adding nodes, monitoring the cluster, and stopping in our local development environment running on Mac OS X.

Prerequisites

  • You should have CockroachDB installed. We have a “How to Install CockroachDB on Mac OS X” article that can help you if don’t already have it installed.
  • We recommend you be familiar with the command line. It’s not required but the commands can be a little intimidating if you’re not.

Starting a Cluster

To create a cluster we start the first node. Let’s look at the command to do so first and then we’ll dissect what’s happening:

cockroach start --insecure --listen-addr=localhost

You should receive a response similar to ours:

$ cockroach start --insecure --listen-addr=localhost
*
* WARNING: RUNNING IN INSECURE MODE!
*
* - Your cluster is open for any client that can access localhost.
* - Any user, even root, can log in without providing a password.
* - Any user, connecting as root, can read or write any data in your cluster.
* - There is no network encryption nor authentication, and thus no confidentiality.
*
* Check out how to secure your cluster: https://www.cockroachlabs.com/docs/v19.1/secure-a-cluster.html
*
CockroachDB node starting at 2019-05-07 23:17:47.188676 +0000 UTC (took 0.6s)
build:               CCL v19.1.0 @ 2019/04/29 18:31:15 (go1.11.6)
webui:               http://localhost:8080
sql:                 postgresql://root@localhost:26257?sslmode=disable
client flags:        cockroach <client cmd> --host=localhost:26257 --insecure
logs:                /Users/alexthompson/Downloads/cockroach-data/logs
temp dir:            /Users/alexthompson/Downloads/cockroach-data/cockroach-temp370964665
external I/O path:   /Users/alexthompson/Downloads/cockroach-data/extern
store[0]:            path=/Users/alexthompson/Downloads/cockroach-data
status:              restarted pre-existing node
clusterID:           b9421d9c-5322-47fe-b184-d547683ba386
nodeID:

This command starts a new node in insecure mode ( specified by the –insecure flag ) which leaves the communication unencrypted. This is for our development so insecure mode is fine.

The --lisen-addr=localhost specifies that the node should only listen on localhost (127.0.0.1) with the default port (26257). The Admin UI by default is available on localhost:8080.

If you look at the output of the command you can can view a lot of helpful information including where the node data is stored store[0]:            path=/Users/alexthompson/Downloads/cockroach-data.

Now that we have successfully started a node in our first cluster let’s move on to the next step of adding another node.

Adding a Node to the Cluster

In production environments you’ll typically have an odd number of three or mored nodes which allows for CockroachDB the ability to perform replication and fault tolerance to your cluster. We’ll demo this by adding two more nodes to our cluster.

Inside a new terminal tab or window you’ll execute the following command:

cockroach start \
--insecure \
--store=node2 \
--listen-addr=localhost:26258 \
--http-addr=localhost:8081 \
--join=localhost:26257

Now we add a third node so we have an odd number of node with a similar command:

cockroach start \
--insecure \
--store=node3 \
--listen-addr=localhost:26259 \
--http-addr=localhost:8082 \
--join=localhost:26257

Each new node is listening on a different localhost port. The first is listening on localhost:26257, the second on localhost:26258, and the third is on localhost:26259. Each Admin UI also listens on a different localhost port. The first is listening on localhost:8080, the second on localhost:8081, and the third is on localhost:8082.

Let’s explain a few of the differences from the command we used to create the first node. In these commands the --join flag is used to connect the nodes of the cluster. Since we’re running on a single machine we use the --store flag to put the nodes data in a subdirectory of the initial node.

Verify the Cluster is Working

To verify the cluster is working we will open the execute some SQL commands. To start the SQL client open the Terminal and execute the following command:

cockroach sql --insecure --host=localhost:26257

You should see a response similar to below letting you know that you’ve entered the cockroach SQL interface:

# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.
#
# Server version: CockroachDB CCL v19.1.0 (x86_64-apple-darwin14, built 2019/04/29 18:31:15, go1.11.6) (same version as client)
# Cluster ID: b9421d9c-5322-47fe-b184-d547683ba386
#
# Enter \? for a brief introduction.
#
root@localhost:26257/defaultdb>

Now let’s make sure we can execute some commands. We’ll start with creating a database:

CREATE DATABASE demo;

Then we’ll create a new table in that database:

CREATE TABLE demo.article (id INT PRIMARY KEY, rating DECIMAL);

And insert a row:

INSERT INTO demo.article VALUES (1, 4.5);

Now let’s verify the data has been stored:

SELECT * FROM demo.article;

Response:

  id | rating  
+----+--------+
   1 |    4.5  
(1 row)

Time: 3.572ms

To exit the SQL shell type in `q’ and then hit enter and you’ll be returned to your normal bash.

We just connected to the first node on port 26257 but let’s try connecting to the second node:

cockroach sql --insecure --host=localhost:26258

And then you can execute the same query to get the data we inserted on node 1:

root@localhost:26258/defaultdb> SELECT * FROM demo.article;
  id | rating  
+----+--------+
   1 |    4.5  
(1 row)

Time: 7.599ms

So we can get the same data from each node in our cluster. Again type \q and hit enter to exit the SQL client.

Visit the Admin UI to Monitor your Cluster

Now you can monitor your cluster from the included Admin UI. Open up your preferred browser to http://localhost:8080 which is the port our first node is listeing for http requests on.

You should see something similar to this: Image from Gyazo

You can find tons of useful information from the this page including a list of your databases, a list of your tables, a history of events, and the number of nodes replicating your data.

Stopping the Cluster

You can stop the cluster by going to the Terminal where your first node is running and pressing CTRL-C. Doing this will exhibit some of the properties of CockroachDB because although you have taken one node offline, the cluster still works because 2 out of 3 ( a majority ) of the nodes are still running. You can verify this by logging into the SQL client through nodes 2 or 3 which are still running and testing the SELECT query from earlier.

cockroach sql --insecure --host=localhost:26258
SELECT * FROM demo.article;

Now to stop the node completely go to each terminal that is still running a cockroachDB node and stopping it CTRL-C.

To remove the data created by this test you can remove the directories with the following remove recursively command:

rm -rf cockroach-data node2 node3

Conclusion

We showed some of the basics of running a CockroachDB cluster including creating a new cluster, adding nodes, creating databases, creating tables, monitoring the Admin UI, and stopping the cluster. We hope you’ll use these basics as a foundation to get more experience with this new technology for creating distributed SQL systems.

If you have any questions about CockroachDB, NewSQL, or distributed SQL systems don’t hesitate to reach out to us.

Pilot the ObjectRocket platform free for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.