Golang and ScyllaDB Part 3 – GoCQLX

In a previous lesson, we saw how to use the GoCQL driver to interact with a ScyllaDB cluster and perform some basic queries. In this lesson, we’ll introduce the GoCQLX package and a hands-on example to see how it works in practice.

GoCQLX is an extension to the GoCQL driver. It improves developer productivity while not sacrificing any performance.

It’s inspired by Sqlx, a tool for working with SQL databases, but it goes beyond what Sqlx provides. For example:

• Builders for creating queries
• Support for named parameters in queries
• Support for binding parameters from struct fields, maps, or both
• Scanning query results into structs based on field names
• Convenient functions for common tasks such as loading a single row into a struct or all rows into a slice (list) of structs

GoCQLX is fast. Its performance is comparable to GoCQL. You can find some benchmarks here. 

Creating a Sample GoCQLX Application

The sample application that we will go over is very similar to the one we saw in the lesson Golang and ScyllaDB Part 1. It will connect to a ScyllaDB cluster, display the contents of the Mutant Catalog table, insert and delete data, and show the contents of the table after each action. We will first go through each section of the code that will be used and then explain how to run the code in a Docker container that will access the ScyllaDB Mutant Monitoring cluster. 

For this application, the main class is called main, and the code is stored in a file called main.go. It’s located here: scylla-code-samples/mms/go/cmd/gocqlxapp/main.go

We’ll start by going over the code. In the application, we first need to import the GoCQLX package:

The createStatements() functions simply sets up the different queries we want to use later in the program.

We can set up the different static queries for later use, as they do not change. This also means that they don’t have to be computed every time. We use the “gcqlx/table” package to create a representation of the tables and as a result, the queries we need are created. Beyond the initial configuration, we don’t have to worry about CQL syntax or getting variable names correct. 

The query and statements structs are convenient constructs to allow us to access the different queries in a modular and unified way. Although not required, you can group them to reduce clutter in the code.

The deleteQuery function performs a delete in the database. It uses a Record struct to identify which row to delete. Gocqlx takes the values in the struct and sends them on as parameters to the underlying prepared statement that is sent to the database. 

The function insertQuery works in the same way as the deleteQuery function. It uses a Record instance as data for the insert query using the BindStruct function.

Note the similarity between the different queries. They are almost identical and the only difference is the amount of data in the insert Record instance.

The function selectQuery reads all the available records into the local variable “rs”. Note that this query loads  the entire dataset into memory and if you know that you have too many rows for this then you should use an iterator based approach instead.

The “Record” struct is a convenient example that illustrates some data that you might have in a database. Please take note of the annotations that names the database column names. They are here to ensure that the correct column is used for the given field.

The main function is the entry point of every Go program. Here we first connect to the database by using the two functions scylla.CreateCluster and gocql.NewSession. This is the code for the scylla.CreateCluster function (located here: mms/go/internal/scylla/cluster.go):

The scylla.CreateCluster is a little helper function to get the driver configuration out of the way. gocql.NewSession connects to the database and sets up a session that can be used to issue queries against the database.

Starting the ScyllaDB Cluster

follow this procedure to remove previous clusters and set up a new cluster.

Next, create the keyspace catalog and the table mutant_data

docker exec -it scylla-node1 cqlsh

CREATE KEYSPACE catalog WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy','DC1' : 3};

use catalog;

CREATE TABLE mutant_data (
   first_name text,
   last_name text,
   address text,
   picture_location text,
   PRIMARY KEY((first_name, last_name)));

Now let’s add a few mutants to the catalog with the following statements:

insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Loblaw','1313 Mockingbird Lane', 'http://www.facebook.com/bobloblaw');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Bob','Zemuda','1202 Coffman Lane', 'http://www.facebook.com/bzemuda');
insert into mutant_data ("first_name","last_name","address","picture_location") VALUES ('Jim','Jeffries','1211 Hollywood Lane', 'http://www.facebook.com/jeffries');

The Golang sample application that we are using was modified from the Datastax Blob example located on the Cassandra Driver’s GitHub page.

Building the Golang Example

If you previously built the Golang  Docker, you can skip directly to “Running the Golang Example.”

Otherwise, to build the application in Docker, change into the “mms/go” subdirectory in scylla-code-samples:

exit

cd scylla-code-samples/mms/go

Now we can build and run the container:

docker build -t go-app .

To run the container and connect to the shell, run the following command:

docker run -d --net=mms_web --name some-go-app go-app

To connect to the shell of the container, run the following command:

docker exec -it some-go-app sh

Running the Golang Example

Finally, the sample Golang application can be run:

gocqlxapp

The output of the application will be:

Conclusion

In this lesson, we explained how to create a sample Go application that executes a few basic CQL statements with a ScyllaDB cluster using the GoCQLX package. The lesson covers only the basics. The following video, from ScyllaDB Summit, goes into more details: