Create Database in CockroachDB

Introduction

If you’re getting started with CockroachDB, one of the first things you’ll need to do is set up a database that will eventually contain tables to store your data. Fortunately, it’s easy to accomplish this task in the CockroachDB SQL client interface. In this tutorial, we’ll show you how to create a database in CockroachDB and also demonstrate how to drop it.

Prerequisites

Before proceeding with the steps outlined in this tutorial, you’ll need to make sure one important prerequisite is in place: You must have a CockroachDB cluster running on at least one node on your server. If you’re running macOS X, you can install CockroachDB with Homebrew using the following brew command:

1
brew install cockroach

For Linux servers and machines, you’ll need to follow the necessary steps to install CockroachDB and run the cluster. You can also download the binary using cURL and install CockroachDB from scratch.

Run CockroachDB using Docker

Once CockroachDB is installed, you can run it in Docker with the following command:

1
2
3
4
5
docker run -d                                     \
    -p 26257:26257 -p 8080:8080                   \
    -v "data:/cockroach/cockroach-data"           \
    cockroachdb/cockroach:latest start            \
    --insecure

Access the SQL client for CockroachDB

You’ll need SSH access to your server in order to access the SQL client for CockroachDB. No matter what operating system you’re using to run CockroachDB, you should be able to use the following command to access the SQL client:

1
cockroach sql --insecure

Access CockroachDB running on a Docker container

If you’re running a CockroachDB cluster node in a Docker container, you’ll need to first use docker exec in order to access the container:

1
docker exec -it {CONTAINER_NAME or ID} /bin/bash

Once you’ve successfully accessed the container, use the shell command shown below to enter the CockroachDB SQL client:

1
./cockroach sql --insecure

Troubleshooting a CockroachDB cluster

If there’s problem with your cluster, or if you encounter a cannot dial server error, you can try running CockroachDB with the following command to get detailed feedback about the errors:

1
2
3
cockroach start-single-node    \
    --insecure                 \
    --logtostderr

Show the CockroachDB databases

If you’re not sure which databases currently reside on your CockroachDB cluster, you can use the SHOW DATABASES; or \l command to list all of your existing databases.

Both of these commands should return a response that looks like the following:

1
2
3
4
5
6
7
8
9
  database_name  
+---------------+
  defaultdb      
  postgres      
  some_db        
  system
(4 ROWS)

TIME: 3.947886ms

Screenshot of \l and SHOW DATABASES SQL commands for CockroachDB

If the database you’re looking for doesn’t yet exist on your cluster, it’s easy to create it using the CREATE DATABASE SQL statement.

Create a database in CockroachDB

In this example, we’ll use the CREATE DATABASE SQL statement in the CockroachDB client interface to create a new database. The following SQL statement creates a new Cockroach database called roaches_db:

1
CREATE DATABASE roaches_db;

NOTE: CockroachDB follows standard SQL naming conventions for database names, avoiding the use of uppercase letters and spaces in favor of lowercase words separated by underscores.

The command shown above should return the following response from the SQL client interface:

1
2
3
CREATE DATABASE

TIME: 10.654829ms

Set a CockroachDB database

We’ll have to SET our new database before we can create a table for it. We can use the SQL SET keyword followed by the database = identifier to set the name:

1
SET DATABASE = roaches_db;

NOTE: Spaces and quotation marks around the database name are optional, but CockroachDB will raise a "pq" driver error if the specified database doesn’t exist.

CockroachDB also allows for other SQL statements and commands to be used as aliases; this can be helpful for users who may be more accustomed to PostgreSQL or MySQL. For example, you can also use the USE statement to set the database as seen below:

1
USE roaches_db;

Drop a CockroachDB database

Let’s imagine that we don’t like the name roaches_db, and we want to delete or DROP the database. We can use the following command to drop the same database that we created earlier:

1
DROP DATABASE roaches_db;

DROP DATABASE on the current CockroachDB database

The command shown above should return a pq: rejected: DROP DATABASE on current database response since we had set it as the current database. To get around this, we can use the following command to set the database to the pre-fabricated defaultdb:

1
SET DATABASE = defaultdb;

Once we receive a SET response from this command, we can then try the DROP DATABASE statement again:

1
DROP DATABASE roaches_db;

Now it should return a response that looks something like the following:

1
2
3
DROP DATABASE

TIME: 10.488788ms

Screenshot of DROP DATABASE in SQL client for CockroachDB current db

Conclusion

Being able to create and drop databases are important skills to have when you’re using CockroachDB to store and manage your data. It’s easy to perform both of these database operations using the CREATE DATABASE and DROP DATABASE statements in the CockroachDB SQL client interface. In this table, we showed you how to create a database in CockroachDB and also how to drop one. With these examples to guide you, you’ll have no problem creating new databases in your own CockroachDB environment.

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.