CockroachDB CLI

CockroachDB is a cloud-native, resilient, and scalable SQL database designed to handle the demands of modern applications. It is engineered to be distributed and includes tools to ensure data consistency, making it an ideal choice for applications needing high levels of availability and conformance to ACID principles.

One indispensable tool you'll be using quite frequently with CockroachDB is its command-line interface (CLI) called cockroach. In this chapter, we'll artfully describe the usage of the CockroachDB CLI using a hypothetical movie database as a case study.

Let's imagine we're creating a database to manage a lot of information about movies, including film details, characters, production companies, directors, and several other features. We'll call our database 'CineWorld'. To make things interesting, let's place a particular focus on the James Bond franchise.

First, we'll explore the process of starting a local, single-node CockroachDB cluster. To begin with, we open a terminal session, navigate to our preferred directory, and then execute the following command.

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

In the command above, cockroach start --insecure starts a new instance of CockroachDB, and --store=CineWorld names the local storage area for the nodes' data. The --listen-addr=localhost ensures that our instance only listens for connections coming from our machine.

Next, we open another terminal tab or window and initialize the cluster.

cockroach init --insecure --host=localhost

This command initializes the cluster and readies it for use. Remember the --insecure flag - it is indicating that we're running a test scenario. In production situations, you'll want to remove the insecure flag and correctly configure secure client-to-node and inter-node communication.

With our cluster initialized, we can interact with the 'CineWorld' cluster using the CockroachDB SQL shell. You can access the interactive SQL shell by issuing the following command:

cockroach sql --insecure --host=localhost

Now you're in the SQL shell; you're free to input any SQL command. For instance, let's create our movies database.

CREATE DATABASE movies;

Every movie belongs to a genre. How about we create a genre table?

CREATE TABLE genres (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL
);

The 'genres' table has two columns: 'id' and 'name'. 'id' is a randomly generated Universally Unique Identifier (UUID), and ‘name’ is a string that cannot be null.

Now, let's create a table for our James Bond movie database.

CREATE TABLE james_bond_movies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title STRING NOT NULL,
    release_year INT,
    director STRING,
    genre_id UUID REFERENCES genres(id)
);

The 'james_bond_movies' table maintains details about James Bond movies. It has a 'title', 'release_year', and 'director'. Also, we have a 'genre_id' that links a movie to its genre using a 'FOREIGN KEY'.

With your tables set, you can insert some data. For instance, let's insert some genres:

INSERT INTO genres (name) VALUES ('Action'), ('Adventure'), ('Thriller');

Similarly, you can insert values into the 'james_bond_movies' table.

INSERT INTO james_bond_movies (title, release_year, director, genre_id) VALUES 
('Skyfall', 2012, 'Sam Mendes', (SELECT id FROM genres WHERE name='Action'));

Interacting with the data is straightforward too. To get all the movies that belong to the 'Action' genre, you can query:

SELECT title, release_year FROM james_bond_movies WHERE genre_id = (SELECT id FROM genres WHERE name='Action');

And voilà, you have just made use of the Cockroach SQL.

Running backups of your data is a best practice. If something were to happen to CineWorld’s precious James Bond database, we’d want to restore it quickly.

cockroach sql --host=localhost \
  --execute="BACKUP TO 'gs://acme-co-backup/test-cluster' WITH revision_history"

Lastly, you should always remember to stop your cluster when you finish your local testing.

cockroach quit --insecure --host=localhost

And that's it for now. We've gone through the basics of CockroachDB CLI, including starting, accessing, and stopping a local cluster, creating databases and tables, and manipulating data. Try these commands and create some tables or records related to our movie database scenario or a scenario of your own choice.

Remember, practice makes perfect - the more you experiment with the CockroachDB CLI, the more comfortable it will become. Don't be scared to make mistakes; they're the stepping stones to becoming a CockroachDB expert.