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:
- Create a CockroachDB Serverless 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.
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 aCREATE DATABASE
statement, and change it to the SQL session's current database by executing aUSE {databasename};
statement, by passing the--database={databasename}
flag to thecockroach sql
command, or by specifying thedatabase
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 theroot
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: