How to Perform the CockroachDB Drop Database

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

Introduction

If you’re using CockroachDB to manage your data, there may be times when you need to remove a table or even an entire database from your CockroachDB cluster. The DROP DATABASE command can be used to delete a CockroachDB database and all of the objects that exist in it. In this article, we’ll discuss the CockroachDB DROP DATABASE command and explain the various options that can be used with it.

The DROP DATABASE command in a CockroachDB cluster

In CockroachDB, a DROP statement can be used to remove objects in a database. The object in question can be a whole database or just a single table. In this tutorial, we’ll focus on the CockroachDB DROP DATABASE command, which removes an entire database. The syntax for the DROP DATABASE command in CockroachDB is as follows:

1
DROP DATABASE [(IF EXISTS)] database_name [ CASCADE | RESTRICT ];

Prerequisites

In order to follow along with this tutorial and use the DROP statement, you’ll need to have a CockroachDB cluster running on your machine. The cluster must have one node that can be used to create a sample database and then drop it. You can verify the version number of your CockroachDB installation by using the command cockroach version in your terminal.

Start the local cluster in CockroachDB

If you don’t already have a node cluster set up and ready, you can start an insecure local cluster on your machine. Just use the command with the options shown below:

1
2
3
4
5
cockroach start --insecure \
--store=orkb \
--listen-addr=localhost:26257 \
--http-addr=localhost:8080 \
--background

Access the built-in SQL shell for cockroachdb

CockroachDB provides an interactive shell that can be used when you need to execute SQL queries or commands. The following command is used to run this built-in shell:

1
cockroach SQL --insecure

Create a database in CockroachDB

You’ll need to have a database available before you can remove it from the CockroachDB cluster. If you don’t have an existing database that you don’t mind deleting, you can use the following command to create a sample database:

1
CREATE DATABASE somedb;

You can add the IF NOT EXISTS clause to this statement to prevent an error from being returned if a database with the same name already exists:

1
CREATE DATABASE IF NOT EXISTS somedb;

Display the databases in CockroachDB

We can return a list of databases that exist inside a node of a CockroachDB cluster by using the command shown below:

1
SHOW DATABASES;

The output consists of a simple list of database names:

1
2
3
4
5
6
7
  database_name  
+---------------+
  defaultdb      
  postgres      
  sample        
  somedb        
  system

The CockroachDB DROP DATABASE using CASCADE

When using the DROP DATABASE statement, it automatically applies the default option CASCADE. This option ensures that all objects in the specified database will be removed along with the database itself:

1
DROP DATABASE somedb;

After executing this statement, try to use the database you have removed. You’ll be able to verify that it was deleted:

1
2
USE somedb;
pq: DATABASE "somedb" does NOT exist

The CockroachDB DROP DATABASE using RESTRICT

While the CASCADE option is the default for the DROP DATABASE command, there are times when you may not want the command to behave this way. Using the RESTRICT option will prevent the removal of the database if it contains any objects:

1
DROP DATABASE sample RESTRICT;

If we execute this command and the sample database has objects in it, we’ll receive an error message that says:

1
pq: database "sample" is not empty and RESTRICT was specified

Conclusion

Deleting tables, databases and other objects is a common task in database administration, and it’s important to know how to handle a deletion correctly. In this article, we focused on the process of deleting a database in CockroachDB. We explained how to use the CockroachDB DROP DATABASE command to remove a database along with all of its objects, and we showed some examples of the various options available for this command. With this tutorial to guide you, you’ll be prepared to remove databases in your own CockroachDB cluster.

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.