Create a Database

This page provides best-practice guidance on creating databases, with a couple examples based on Cockroach Labs’s fictional vehicle-sharing company, MovR.

For reference documentation on the CREATE DATABASE statement, including additional examples, see the CREATE DATABASE syntax page.

Before you begin

Before reading this page, do the following:

Database objects make up the first level of the CockroachDB naming hierarchy. To create a database, use a CREATE DATABASE statement, following the database best practices. After reviewing the best practices, see the examples we provide below.

Cockroach Labs recommends against starting a database name with the string cluster:. Refer to Database Best Practices for more information.

Database best practices

Here are some best practices to follow when creating and using databases:

Do not use the preloaded defaultdb database. Instead, create your own database with a CREATE DATABASE statement, and change it to the SQL session’s current database by executing a USE {databasename}; statement, by passing the --database={databasename} flag to the cockroach sql command, or by specifying the database parameter in the connection string passed to your database schema migration tool.
Do not begin your database name with the string cluster:. If your database name begins with this string, you must append the following to the URI connection string to connect to the cluster: &options=-ccluster=system
Create databases and user-defined schemas as a member of the admin role (e.g., as the root user), and create all other lower-level objects as a different user, with fewer privileges, following authorization best practices.
Limit the number of databases you create. If you need to create multiple tables with the same name in your cluster, do so in different user-defined schemas, in the same database.
Use a database schema migration tool or the CockroachDB SQL client instead of a client library to execute database schema changes.

Example

Create an empty file with the .sql file extension at the end of the filename. This file will initialize the database that will store all of the data for the MovR application. For example:

$ touch dbinit.sql

Open dbinit.sql in a text editor, and, at the top of the file, add a CREATE DATABASE statement:

CREATE DATABASE IF NOT EXISTS movr;

This statement will create a database named movr, if one does not already exist. To execute the statement in the dbinit.sql file as the root user, run the following command:

$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
-f dbinit.sql

To view the database in the cluster, execute a SHOW DATABASES statement from the command line:

$ cockroach sql \
--certs-dir={certs-directory} \
--user=root \
--execute="SHOW DATABASES;"

  database_name | owner | primary_region | regions | survival_goal
----------------+-------+----------------+---------+----------------
  defaultdb     | root  | NULL           | {}      | NULL
  movr          | root  | NULL           | {}      | NULL
  postgres      | root  | NULL           | {}      | NULL
  system        | node  | NULL           | {}      | NULL
(4 rows)

You’re now ready to start adding user-defined schemas to the movr database. For guidance on creating user-defined schemas, see Create a User-defined Schema.

What’s next?

You might also be interested in the following pages:

​Before you begin