CockroachDB TIL: Volume 1
Read this initial post in a new series of articles covering short "Today I Learned" topics as I peel the layers of CockroachDB.
This is a new series of articles covering short "Today I Learned" topics as I peel the layers of CockroachDB. This is meant to resemble release notes. I decided to mix it up with the format for these posts as they may not justify an entire blog. Link here for past articles.
Topic 1: Unit Testing With CockroachDB
I get this question a lot: "What is the best way to unit test CockroachDB as part of application development?" Depending on the use case, there are several approaches we can take. There is a venerable [cockroach demo](https://www.cockroachlabs.com/docs/stable/cockroach-demo.html), which has been my trusty go-to tool when I needed something in a hurry. It comes with a one-hour license for enterprise features, which you can also unit test, but it also comes with certain limitations. For example, there is reported latency at table creation, ports until recently were random and not configurable, and by default it loads data which you can also overcome with a flag. The goal for
cockroach demo is to quickly spin up a working environment for demo purposes and not necessarily something designed for unit testing.
cockroach demo --no-example-database --sql-port 26257
Another option that is being actively documented is
--store=type=mem, which is an in-memory version of CockroachDB you can spin up locally. It does not come with enterprise features turned on by default. For that, you would have to provide a valid license. This option feels and behaves like a real cluster and has been blessed by our engineering for unit testing. It is meant for application development testing and will be the direction we take going forward. One caveat is that you have to specify size of the store when you start cockroach.
cockroach start-single-node --store=type=mem,size=5GB --background --insecure --http-addr=127.0.0.1:8080 --advertise-addr=127.0.0.1:26257
I've noticed a few changes in behavior, which I've itemized below:
- Creates a
- Does not create a
cockroach-datadirectory to persist data
- Logs output to the console by default
- Requires an explicit shutdown command to terminate
A lot of the findings will likely change as we iterate and address customer feedback.
Lastly, I'd like to mention that Docker is still a viable option: I write a lot of my test cases in Docker Compose. There's also a promising project called TestContainers which I have yet to explore. It is sadly limited to Java language exclusively.
Topic 2: Override CockroachDB Cluster ID
I just learned that cluster-ID, usually a random UUID that appears on the DBConsole, can be overridden with a customer-provided ID. You can do that using
--cluster-name=<id> flag. This is useful in environments with many clusters and arbitrary ID does not make it easy to remember which cluster is which.
cockroach start-single-node \ --background \ --insecure \ --http-addr=127.0.0.1:8080 \ --advertise-addr=127.0.0.1:26257 \ --cluster-name=customname
Topic 3: Sleep Command in CockroachDB and PG
select pg_sleep(seconds); will do exactly what you think it will do: sleep for the predetermined period of time in the SQL console.
firstname.lastname@example.org:26257/defaultdb> select pg_sleep(10); pg_sleep ------------ true (1 row) Time: 10.002s total (execution 10.002s / network 0.000s)
Topic 4: Set Timezone on SQL Client
You can set timezone on client side using the following syntax:
show timezone; timezone ------------ UTC
Setting a custom timezone:
show timezone; timezone -------------------- America/New_York
This works fine if you are inside the interactive SQL shell, but if you need to have timezone set upon SQL client initialization, you have to pass timezone as a parameter to the connection string:
cockroach sql --url "postgresql://email@example.com:26257?sslmode=disable&timezone=America/New_York"
# Welcome to the CockroachDB SQL shell. ... show timezone; timezone -------------------- America/New_York
Notice in the former case, we had to wrap the custom timezone in double quotes, but not in the case of connection string.