Jim's Guide to CockroachDB Naming Standards

I don't think CockroachDB has an official guide for naming standards, so I thought I would put out a post about what I think are the generally agreed-upon standards and claim them as my own. :)

When I say "naming standards," I'm referring to the general patterns for how we should name databases, tables, columns, etc.

How Case is Handled in Identifiers

Before I get into that, let's take a look at how case gets dealt with in CockroachDB.

If we create a table that has a camel-cased name (i.e., each word is "separated" by a capital letter), it might look something like this:

root@localhost:26257/defaultdb> CREATE TABLE CamelCaseTable
(
  myKeyValue int PRIMARY KEY,
  myField varchar
);

root@localhost:26257/defaultdb> SELECT * FROM CamelCaseTable;
  mykeyvalue | myfield
-------------+----------
(0 rows)

This all seems good so far.

However, if you ask CRDB to show you the table you created, you'll notice that the table definition that was stored does not retain the case specified:

root@localhost:26257/defaultdb> SELECT create_statement FROM [SHOW CREATE TABLE CamelCaseTable];
                         create_statement
-------------------------------------------------------------------
  CREATE TABLE public.camelcasetable (
      mykeyvalue INT8 NOT NULL,
      myfield VARCHAR NULL,
      CONSTRAINT camelcasetable_pkey PRIMARY KEY (mykeyvalue ASC)
  )
(1 row)

Also, if you try to SELECT from the table using a name that is double-quoted, CRDB will report that the table doesn't exist.

root@localhost:26257/defaultdb> SELECT * FROM "CamelCaseTable";
ERROR: relation "CamelCaseTable" does not exist
SQLSTATE: 42P01

This is strange! What's going on here?

Any time you specify an identifier name in DDL or DML, CRDB will convert that name to lowercase. If you want to override this behavior, you must specify the identifier in double quotes.

Here's an example of using double quotes:

CREATE TABLE "CamelCaseTable2" ( "myKeyValue" int PRIMARY KEY, "myField" varchar );

But, note that when you do this, you have to also use the double quotes any time you reference the name.

For instance:

root@localhost:26257/defaultdb> SELECT * FROM CamelCaseTable2;
ERROR: relation "camelcasetable2" does not exist
SQLSTATE: 42P01
root@localhost:26257/defaultdb> SELECT myKeyValue, myField FROM "CamelCaseTable2";
ERROR: column "mykeyvalue" does not exist
SQLSTATE: 42703
root@localhost:26257/defaultdb> SELECT "myKeyValue", "myField" FROM "CamelCaseTable2";
  myKeyValue | myField
-------------+----------
(0 rows)

Snake Case

Because of this "automatic lowercase-ification" of all identifiers, it is much simpler and easier to specify all your names in lower case. For separation of words, use an underscore (btw, a dash is not valid in CRDB identifiers). This is called "snake case."

Here's an example:

root@localhost:26257/defaultdb> CREATE TABLE snake_case_table
(
  my_key_value int PRIMARY KEY,
  my_field varchar
);

root@localhost:26257/defaultdb> SELECT * FROM snake_case_table;
  my_key_value | my_field
---------------+-----------
(0 rows)

root@localhost:26257/defaultdb> SELECT * FROM SNAKE_CASE_TABLE; -- if you want to use caps here, go for it
  my_key_value | my_field
---------------+-----------
(0 rows)

Naming Standards

Because of the handling of case in identifiers, it is much simpler and easier to use snake case for naming things in CRDB. i_hope_you_agree_that_this_is_the_way_to_go