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, .

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

Before you begin

Before reading this page, do the following:

or .
.

Database objects make up the first level of the . To create a database, use a , 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 by executing a USE {databasename}; statement, by passing the --database={databasename} flag to the , or by specifying the database parameter in the 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 : &options=-ccluster=system
Create databases and as a member of (e.g., as the ), and create all other lower-level objects as a , with fewer privileges, following .
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 , in the same database.
Use a or the instead of a to execute .

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 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 .

What’s next?

You might also be interested in the following pages:

Get Started

CockroachDB and AI

Feature Overview

Data Resilience

Connect to an Application

Self-Hosted Deployments

Schema Design

Reads and Writes

Stream Data

Cross-Cluster Replication

Multi-Region Capabilities

Optimize Performance

Integrate

Create a Database

Before you begin