Setting up a SQL GUI with CockroachDB

This is a setup guide to walk you through the steps of setting up a Graphical User Interface (GUI), in particular DBeaver, to communicate with a free cluster offered by CockroachDB.

Create a free cluster on cockroachlabs.cloud

Navigate to https://cockroachlabs.cloud/ and create a free cluster. You can change the name of the cluster, the cloud provider, and the region. I stick with the default settings and change the given name of “tall-mole” to “python-bootcamp” for the rest of this guide.

After your cluster is created, you are shown the following window:

Take note of the cert web address from step 2 and the connection string from step 3. The cert web address looks like this:

https://cockroachlabs.cloud/clusters/1f2936ba-9ec7-4b55-9f57-61a6c3fe9254/cert

And the connection string looks like this:

'postgresql://paul:<REVEAL_PASSWORD>@free-tier.gcp-us-central1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster%3Dpython-bootcamp-2788'

When copied the connection string will include your real password, not the string “<REVEAL_PASSWORD>”.

Copy the cert web address into your browser’s URL bar. Navigate to that address (click enter) to trigger your browser to download the cert file to your local machine. Move this file a convenient location on your machine. I’ve placed it in my home folder (ie. /Users/paul/root.crt).

Next, lets break apart the connection string. It has several parts that take the shape:

'scheme://username:password@host:port/database_name?config_params'

These are the values for each part:

• Username: paul
• Password: g4wH8APMDV
• Host: free-tier.gcp-us-central1.cockroachlabs.cloud
• Port: 26257
• Database: defaultdb
• Configuration Parameters: sslmode=verify-full&sslrootcert=’$HOME’/.postgresql/root.crt&options= — cluster%3Dpython-bootcamp-2788

We’ll further break down the configuration parameters like so:

• sslmode: verify-full
• sslrootcert: ’$HOME’/.postgresql/root.crt
• options:--cluster%3Dpython-bootcamp-2788

Lastly, we’ll change the value of sslrootcert to the location of the cert file on our local file system and replace %3D with “=” in the options string.

• sslrootcert: /Users/paul/root.crt
• options: --cluster=python-bootcamp-2788

We now have our cert and all the values we need to create a connection with DBeaver.

Download and install DBeaver

Download and install DBeaver from https://dbeaver.io/download/. The GUI has download options for several platforms and the Community Edition is free. This guide uses version Community Edition 21.1.3 for a Mac.

After installing DBeaver, open it and click on the plug with a plus icon in the upper left hand corner. This will open a window like the following:

Select “All” from the tabs to the left and type “CockroachDB” to narrow the selection down the correct database type. Click “Next” and you’ll be shown the following window for entering settings. If you are prompted to download drivers for CockroachDB, do so.

Use the values from above for Host, Database, Port, Username, and Password. Then navigate to the “Driver Properties” tab. You’ll need to update the sslmode to be “verify-full”, sslrootcert to be the full path to your cert file, and the options value to be something like “ — cluster=python-bootcamp-2788”. Your options value will differ.

Then click the “Test Connection” button. You should get a window like this:

At this point the connection settings are correct and you’re ready to write some SQL! Click OK until you are back to a screen like this:

Click on the “defaultdb” row in the Database Navigator and then on the SQL button in the upper left corner and a new window should appear. In this window you should be able to run SQL queries. Click the play button on the left and get results.

Enjoy!