Install and Run the CockroachDB Client - Part 2

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This article is the second installment of an article series that shows how to install CockroachDB and set up a cluster in order to access its CockroachDB client interface. In the previous installment, we focused on the CockroachDB installation process. Now, we’ll pick up where we left off and review the basics of setting up a cluster, as well as accessing and using the CockroachDB CLI.

Start a CockroachDB cluster

Let’s begin our discussion with a look at the basic command used to start a CockroachDB cluster: cockroach start. The start command allows you to specify several options while starting a cluster. For example, the following command will start an insecure cluster that doesn’t use certificates:

1
cockroach start --insecure

NOTE: You can also initialize an insecure cluster with cockroach init --insecure beforehand, but this isn’t necessary in most cases.

Start the CockroachDB cluster on macOS

If you’re running CockroachDB on macOS, you can use the brew services start command to start the cluster:

1
brew services start cockroachdb/tap/cockroach

This command will use the launchd service to run the cluster as a background daemon service whenever you restart your Mac. If you’d prefer to run the service in the terminal as a foreground service, use the cockroach start command instead.

Screenshot of a successful installation of CockroachDB on macOS using Homebrew

CockroachDB certificate directory location

To create a secure CockroachDB cluster, you’ll need to create certificates and set up a directory for them. The following command can be used to get more information on the cockroach cert command:

1
cockroach cert --help

Create a certificate for CockroachDB

You’ll need to create a certificate if you want to secure your cluster. You can use the cockroach cert create-ca command to accomplish this task. The first step is to create two directories on your machine using the mkdir command: one for the certificates and one for the certificate keys.

1
mkdir cockroachdb-certs-dir certs

NOTE: You can name the certificate key directory whatever you’d like; however, you should name the certificate directory itself certs.

1
2
3
cockroach cert create-ca \
--certs-dir=certs \
--ca-key=cockroachdb-certs-dir/ca.key

Next, use the create-client command to create a certificate for a particular user:

1
2
3
4
cockroach cert create-client    \
    macuser                     \
    --certs-dir=certs           \
    --ca-key=cockroachdb-certs-dir/ca.key

NOTE: Be sure to create a certificate for the root user as well.

The command shown above will create a private RSA key for that user.

Screenshot of creating a certificate and key for a CockroachDB cluster with cockroach cert

You can use the pwd command in a UNIX terminal to get the full path for the certificate keys directory. Then, export that path to create a permanent entry for it in your system’s $PATH variable. You can see an example of how to export the path below:

1
export PATH="$PATH:/Users/macuser/Documents/certs"

You can use the cert list command to see the permanent certificates path for CockroachDB:

1
cockroach cert list

If this command doesn’t return information on a certificate file, try the following command instead:

1
cockroach cert list --certs-dir=/Users/macuser/Documents/certs

Once your certificate directory is set up, you can use the --certs-dir option with the cockroach sql command to access your cluster:

1
cockroach sql --certs-dir=/Users/macuser/Documents/certs

You may get an error that says Error: connections with user root must use a client certificate— this just means you haven’t created a certificate for the root user as well.

Start a secure CockroachDB cluster

The last step of the process is to create a node certificate for each node running on the cluster. You’ll need to use the create-node command:

1
2
3
cockroach cert create-node localhost             \
    --certs-dir=/Users/macuser/Documents/certs   \
    --ca-key=/Users/macuser/Documents/cockroachdb-certs-dir/ca.key

After executing that command, you should be able to start the cluster using the node certificate stored in the certificate keys directory:

1
2
3
4
5
cockroach start                                     \
    --certs-dir=/Users/macuser/Documents/certs      \
    --host=node1                                    \
    --listen-addr=localhost:26257                   \
    --http-addr=localhost:8080

The cockroach start command shown above should return something like the following:

1
2
3
4
5
6
7
8
9
10
11
12
CockroachDB node starting at 2020-02-05 10:30:34.570684 +0000 UTC (took 0.6s)
build:               CCL v19.2.2 @ 2019/12/11 01:27:47 (go1.12.12)
webui:               https://localhost:8080
sql:                 postgresql://root@localhost:26257?sslcert=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fclient.root.crt&sslkey=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2FUsers%2Fmacuser%2FDocuments%2Fcerts%2Fca.crt
RPC client flags:    cockroach <client cmd> --host=localhost:26257 --certs-dir=/Users/macuser/Documents/certs
logs:                /Users/macuser/Downloads/cockroach-data/logs
temp dir:            /Users/macuser/Downloads/cockroach-data/cockroach-temp950761167
external I/O path:   /Users/macuser/Downloads/cockroach-data/extern
store[0]:            path=/Users/macuser/Downloads/cockroach-data
status:              initialized new cluster
clusterID:           c7c15226-0efe-4902-99eb-f751b3a808b8
nodeID:              1

Access the CockroachDB CLI

Once your cluster is running, you can access the built-in CockroachDB CLI. This allows you to execute SQL statements, much like PostgreSQL or MySQL. You can use these statements to retrieve and modify CockroachDB data.

‘cockroach sql’ command

You can access the client interface by executing the cockroach sql command in a terminal or command prompt window.

Screenshot of the cockroach sql command for the CockroachDB client refusing to connect

If you’re using a secure cluster and you get a certificate error that says “problem with CA certificate: not found”, just use the following command instead to connect to the CockroachDB CLI:

1
cockroach sql --certs-dir=/Users/macuser/Documents/certs

Once you’re inside the CockroachDB client interface, you can use the \l command or the SHOW DATABASES; SQL statement to list all of the databases stored on the cluster.

Let’s try to create a new database.

Create a database for CockroachDB

We’ll use the CREATE DATABASE SQL keyword to create a new database for the cluster:

1
CREATE DATABASE my_db;

If the operation was successful, it should return a response of CREATE DATABASE along with the time it took to execute the statement. Try the \l command again to verify that the database was created.

Use Cockroachdb to connect to a database

If you’d like to connect to a specific database, just use the USE SQL keyword followed by a database name. The SQL statement shown below will connect to the defaultdb database:

1
USE defaultdb;

Tis statement should return a response that looks like the following:

1
2
SET
Time: 7.912ms

Once you’re finished you can just type \q to exit out of the CockroachDB client interface for your cluster.

Screenshot of the CockroachDB client interface for a localhost cluster

Conclusion

Over the course of our two-part article series on using the built-in, SQL-based CockroachDB client interface, we’ve covered quite a bit of ground. After walking you through the CockroachDB installation process in the first article, we turned our focus to the setup and configuration process in this second article. In addition, we showed you how to access and use the CockroachDB client to execute SQL statements. With these step-by-step instructions, you’ll be able to get the client interface set up and working for your own CockroachDB installation.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

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.