Create a Database

On this page

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

Before you begin

Before reading this page, do the following:

• Create a CockroachDB Standard cluster or start a local cluster.
• Review the database schema objects.

Create a database

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.

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?

• Create a User-defined Schema
• Create a Table

You might also be interested in the following pages:

• CREATE DATABASE
• Schema Design Overview
• cockroach Commands Overview
• CockroachDB naming hierarchy

Was this helpful?