Install and Run the CockroachDB Client - Part 2

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:

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:

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.

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:

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.

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

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

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.

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:

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

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

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

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:

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

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

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.

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:

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:

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:

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

Once you’re finished you can just type \q to exit out of the CockroachDB client interface for your 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.