Skip to main content

CockroachDB

Overview​

CockroachDB is a distributed SQL database developed by Cockroach Labs. It is wire-protocol compatible with Postgres. It supports array, JSONB, and JSON data types and has several supporting query functions and query syntax extensions. Qarbine uses the Postgres node.js driver for interacting with the CockroachDB database.

Cockroach Configuration Information​

From CockroachDB you need to determine your endpoint information which includes

  • URL,
  • port number,
  • account name, and
  • account password.

The Cockroach web console is at https://cockroachlabs.cloud/

View your clusters at https://cockroachlabs.cloud/clusters

View the deployment’s accounts at

  

Many cloud services have options limiting the IP addresses which can connect to services. Verify the Qarbine host has access to the MySQL endpoint. A check for this is also part of the instructions below. In the CockroachDB console this information is at

  

A convenient way to obtain the necessary information is by navigating to the cluster and clicking

  

Set your connection options

  

Copy the values from the bottom of the dialog.

`  

Close the dialog by clicking

  

Qarbine Configuration​

Compute Node Preparation​

Determine which compute node service endpoint you want to run this data access from. That URL will go into the Data Service’s Compute URL field. Its form is “https://domain:port/dispatch”. A sample is shown below.

  

The port number corresponds to a named service endpoint configured on the given target host. For example, the primary compute node usually is set to have a ‘main’ service. That service’s configuration is defined in the ˜./qarbine.service/config/service.main.json file. Inside that file the following driver entry is required when using the MySQL interaction. 

"drivers" :[
 . . .
"./driver/cockroachDbDriver.js"
]

The relevant configuration file name for non primary (main) Qarbine compute nodes is service.NAME.json. Remember to have well formed JSON syntax or a startup error is likely to occur. If you end up adding that entry then restart the service via the general command line syntax

pm2 restart <service>

For example,

pm2 restart main

or simply

pm2 restart all

Data Service Definition​

Open the Administration Tool.

Navigate to the Data Services tab.

  

A data service defines on what compute node a query will run by default along with the means to reach to target data. The latter includes which native driver to use along with settings corresponding to that driver. Multiple Data Sources can reference a single Data Service. The details of any one Data Service are thus maintained in one spot and not spread out all over the place in each Data Source. The latter is a maintenance and support nightmare.

To begin adding a data service click

  

On the right hand side enter a name and optionally a description.

  

Set the Compute URL field based on the identified compute node above. Its form is “https://domain:port/dispatch”. A sample is shown below.

  

Choose the driver and set the values as shown below.

  

Enter the values for the server template

  

Enter account information as shown below

  

If your CockroachDB endpoint requires SLL certificates, then see the section below on how to set those parameters.

Enter the database information into the field shown below.

  

You can reference environment variables using the syntax %NAME%. Any strings should be quoted and the key\value pairs separated by commas.

Test your settings by clicking on the toolbar image highlighted below.

  

The result should be something similar to the following

  

Save the Data Service by clicking on the image highlighted below.

  

The data service will be known at the next log on time.

Using SSL Certificates​

Overview​

Access to CockroachDB may require SSL certification. The steps to create such files are at
https://www.cockroachlabs.com/docs/v24.2/create-security-certificates-openssl

If no SSL parameters are provided the CockroachDB server may respond with

  

If you require certificate parameters then there are several options.

Obtaining the Certificate​

See the description on this topic at
https://www.cockroachlabs.com/docs/cockroachcloud/connect-to-a-serverless-cluster?filters=connection-string

Defaults​

Use the SSL defaults by specifying

ssl.ca = null,

Qarbine configContent​

Use the following pattern which requires the files to be in the ˜/qarbine.service/config folder and the file names starting with "cockroach".

ssl.rejectUnauthorized = true,
ssl.ca = "configContent:cockroach-ca.crt",
ssl.key = "configContent:cockroach-client.key",
ssl.cert = "configContent:cockroach-client.crt",

Qarbine resourceContent​

Depending on the Qarbine feature level an alternative is to use Qarbine resources and the pattern

ssl.rejectUnauthorized = true,
ssl.ca = "resourceContent:cockroach-ca.crt",
ssl.key = "resourceContent:cockroach-client.key",
ssl.cert = "resourceContent:cockroach-client.crt",

The value after the colon is the Qarbine resource name. The service.NAME.json file must also have an entry in the “supportResources:[ ]” list for the resource name. Access to Qarbine’s resource management feature varies by edition and other factors.

Troubleshooting​

The following is shown when the certificate options are invalid.

  

Check the log files for additional messages regarding any referenced SSL parameters. A sample message is 

Missing referenced ./config folder content configContent:cockroach-client.crt

You can review the log files from the Administration tool or by SSH’ing into the Qarbine host and reviewing the files at ˜/pm2/logs.