Guide to CockroachDB in Java

I just announced the new Spring 5 modules in REST With Spring:

1. Introduction

This tutorial is an introductory guide to using CockroachDB with Java.

We’ll explain the key features, how to configure a local cluster and how to monitor it, along with a practical guide on how we can use Java to connect and interact with the server.

Let’s start by first defining what it is.

2. CockroachDB

CockroachDB is a distributed SQL database built on top of a transactional and consistent key-value store.

Written in Go and completely open source, its primary design goals are the support for ACID transactions, horizontal scalability, and survivability. With these design goals, it aims to tolerate everything from a single disk failure to an entire datacenter crash with minimal latency disruption and without manual intervention.

As result, CockroachDB can be considered a well-suited solution for applications that require reliable, available, and correct data regardless of scale. However, it’s not the first choice when very low latency reads and writes are critical.

Automated repair – continue seamlessly as long as a majority of replicas remain available for short-term failures while, for longer-term failures, automatically rebalances replicas from the missing nodes, using the unaffected replicas as sources

3. Configuring CockroachDB

For demo purposes, we’re using the insecure attribute, making the communication unencrypted, without the need to specify the certificates location.

At this point, our local cluster is up and running. With only one single node, we can already connect to it and operate but to better take advantages of CockroachDB’s automatic replication, rebalancing, and fault tolerance, we’ll add two more nodes:

For the two additional nodes, we used the join flag to connect the new nodes to the cluster, specifying the address and port of the first node, in our case localhost:26257. Each node on the local cluster requires unique store, port, and http-port values.

When configuring a distributed cluster of CockroachDB, each node will be on a different machine, and so specifying the port, store and the http-port can be avoided since the defaults values suffice. In addition, the actual IP of the first node should be used when joining the additional nodes to the cluster.

3.1. Configuring Database and User

Once we have our cluster up and running, via the SQL console provided with CockroachDB, we need to create our database and a user.

First of all, let’s start the SQL console:

cockroach sql --insecure;

Now, let’s create our testdb database, create a user and add grants to the user in order to be able to perform CRUD operations:

If we want to verify that the database was created correctly, we can list all the databases created in the current node:

SHOW DATABASES;

Finally, if we want to verify the automatic replication feature of CockroachDB, we can check on one of the two others nodes if the database was created correctly. To do so, we have to express the port flag when we are using the SQL console:

cockroach sql --insecure --port=26258;

4. Monitoring CockroachDB

Now that we have started our local cluster and created the database, we can monitor them using the CockroachDB Admin UI:

This Admin UI, that comes in a bundle with CockroachDB, can be accessed at http://localhost:8080 as soon as the cluster is up and running. In particular, it provides details about cluster and database configuration, and helps us optimize cluster performance by monitoring metrics like:

6. Using CockroachDB

Now that it’s clear what we’re working with and that everything is set up properly, let’s start using it.

Thanks to the PostgreSQL compatibility, it’s either possible to connect directly with JDBC or using an ORM, such as Hibernate (At the time of writing (Jan 2018) both drivers have been tested enough to claim the beta-level support according to the developers). In our case, we’ll use JDBC to interact with the database.

For simplicity, we’ll follow with the basic CRUD operations as they are the best to start with.

Let’s start by connecting to the database.

6.1. Connecting to CockroachDB

To open a connection with the database, we can use the getConnection() method of DriverManager class. This method requires a connection URL String parameter, a username, and a password: