Connecting to cockroachdb

If you've followed Getting cockroachdb running on google cloud platform you'll have a small cockroach implementation of 3 nodes running as Docker containers on a single VM on Google Cloud Platform that can run local SQL queries and which you can monitor through a browser. Note that if you haven't reserved a static ip address (it costs something) it'll change from time to time.

Next, you'll want to connect to it to build an app.

You can find more on this in detail at Cockroachdb site.

Exposing ports

We already exposed port 8080 to be able to use the monitoring tool, but we also need to expose port 26257 so we can connect to the database containers.  Just like before, we need to create a firewall rule for this.

And I'll update the script to make this repeatable. updated
gcloud compute --project=effex-console firewall-rules create cockroach-admin --direction=INGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp:8080 --source-ranges= --target-tags=cockroach-admin

gcloud compute --project=effex-console firewall-rules create cockroach-admin --direction=INGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp:26257 --source-ranges= --target-tags=cockroach-sql

The Docker image already contained a redirect for its ports to the VM's 26257,
docker run -d \
--name=c1 \
--hostname=c1 \
--net=cockroachbridge \
-p 26257:26257 \
-p 8080:8080 \
-v "${PWD}/cockroach-data/c1:/cockroach/cockroach-data" \
cockroachdb/cockroach:v1.1.3 start --insecure

 but we still have to associated this tag, cockroach-sql, with the VM.

I'll update the script to create the instance to include this tag too updated

gcloud beta compute --project "effex-console" instances create "cockroach" --zone "us-central1-c" --machine-type "f1-micro" --subnet "default" --metadata "gce-container-declaration=spec:\u000a containers:\u000a - name: cockroach\u000a image: 'cockroachdb/cockroach:v1.1.3'\u000a stdin: false\u000a tty: false\u000a restartPolicy: Always\u000a" --maintenance-policy "MIGRATE" --service-account "" --scopes "","","","","","" --min-cpu-platform "Automatic" --tags "cockroach-admin,cockroach-sql" --image "cos-stable-63-10032-71-0" --image-project "cos-cloud" --boot-disk-size "10" --boot-disk-type "pd-standard" --boot-disk-device-name "cockroach"

Now we are ready to go, but it's a good idea to check if the port is actually open as expected. for this, which gives  this result.

Creating a user

Next we need to grant access to some user  and allow access to the database, in this case to the locations table. For future automation, I'll put that in a script

docker exec -i c1 ./cockroach user set bruce --insecure 
docker exec -i c1 ./cockroach sql --insecure -e 'GRANT ALL ON TABLE airports.locations TO bruce'

and add that to the overall script
## create network
## start cockroach
## test db
## add a user

Selecting a client

There are a number of clients to choose from, but for Node, I'll be using pg, which you can read about here. It's installable via npm -

npm install pg

Creating an app

This test app on Node will do the same query as I did locally.
const { Pool } = require('pg');

const pool = new Pool({
  host: '',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,

    .then (res=>  pool.query('SELECT name,iata_code,elevation_ft FROM airports.locations ORDER BY elevation_ft DESC'))
    .then (res=> console.log(res.rows))
    .then (()=> pool.end())
    .then (()=>process.exit())
    .catch (err=> console.log ("error",err));

and the result
[ anonymous {
    name: 'Edmonton International Airport',
    iata_code: 'YEG',
    elevation_ft: '2373' },
  anonymous {
    name: 'Halifax / Stanfield International Airport',
    iata_code: 'YHZ',
    elevation_ft: '477' },
  anonymous {
    name: 'Port Moresby Jacksons International Airport',
    iata_code: 'POM',
    elevation_ft: '146' } ]


For more like this, see React, redux, redis, material-UI and firebase Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.