Create Database in CockroachDB
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.
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
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:
-p 26257:26257 -p 8080:8080 \
-v "data:/cockroach/cockroach-data" \
cockroachdb/cockroach:latest start \
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:
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:
Once you’ve successfully accessed the container, use the shell command shown below to enter the CockroachDB SQL client:
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:
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:
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
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:
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:
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:
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:
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
Once we receive a
SET response from this command, we can then try the
DROP DATABASE statement again:
Now it should return a response that looks something like the following:
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