We recently published a new lesson in ScyllaDB University, using GoCQLX to interact with a ScyllaDB cluster. This is a summary of the lesson.
The GoCQLX package is an extension to the GoCQL driver. It improves developer productivity without 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. This blog post goes through each section of the code that will be used. To see more details and run the code check out the full lesson.
For this application, the main class is called main, and the code is stored in a file called main.go.
In the application, we first need to import the GoCQLX package:
import (
"goapp/internal/log"
"goapp/internal/scylla"
"github.com/gocql/gocql"
"github.com/scylladb/gocqlx"
"github.com/scylladb/gocqlx/qb"
"github.com/scylladb/gocqlx/table"
"go.uber.org/zap"
)The createStatements() function simply sets up the different queries we want to use later in the program.
var stmts = createStatements()
func createStatements() *statements {
m := table.Metadata{
Name: "mutant_data",
Columns: []string{"first_name", "last_name", "address", "picture_location"},
PartKey: []string{"first_name", "last_name"},
}
tbl := table.New(m)
deleteStmt, deleteNames := tbl.Delete()
insertStmt, insertNames := tbl.Insert()
// Normally a select statement such as this would use `tbl.Select()` to select by
// primary key but now we just want to display all the records...
selectStmt, selectNames := qb.Select(m.Name).Columns(m.Columns...).ToCql()
return &statements{
del: query{
stmt: deleteStmt,
names: deleteNames,
},
ins: query{
stmt: insertStmt,
names: insertNames,
},
sel: query{
stmt: selectStmt,
names: selectNames,
},
}
}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.
type query struct {
stmt string
names []string
}
type statements struct {
del query
ins query
sel query
}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.
func deleteQuery(session *gocql.Session, firstName string, lastName string, logger *zap.Logger) {
logger.Info("Deleting " + firstName + "......")
r := Record{
FirstName: firstName,
LastName: lastName,
}
err := gocqlx.Query(session.Query(stmts.del.stmt), stmts.del.names).BindStruct(r).ExecRelease()
if err != nil {
logger.Error("delete catalog.mutant_data", zap.Error(err))
}
}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.
func insertQuery(session *gocql.Session, firstName, lastName, address, pictureLocation string, logger *zap.Logger) {
logger.Info("Inserting " + firstName + "......")
r := Record{
FirstName: firstName,
LastName: lastName,
Address: address,
PictureLocation: pictureLocation,
}
err := gocqlx.Query(session.Query(stmts.ins.stmt),
stmts.ins.names).BindStruct(r).ExecRelease()
if err != nil {
logger.Error("insert catalog.mutant_data", zap.Error(err))
}
}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.
func selectQuery(session *gocql.Session, logger *zap.Logger) {
logger.Info("Displaying Results:")
var rs []Record
err := gocqlx.Query(session.Query(stmts.sel.stmt), stmts.sel.names).SelectRelease(&rs)
if err != nil {
logger.Warn("select catalog.mutant", zap.Error(err))
return
}
for _, r := range rs {
logger.Info("\t" + r.FirstName + " " + r.LastName + ", " + r.Address + ", " + r.PictureLocation)
}
}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.
type Record struct {
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
Address string `db:"address"`
PictureLocation string `db:"picture_location"`
}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.
func main() {
logger := log.CreateLogger("info")
cluster := scylla.CreateCluster(gocql.Quorum, "catalog", "scylla-node1", "scylla-node2", "scylla-node3")
session, err := gocql.NewSession(*cluster)
if err != nil {
logger.Fatal("unable to connect to scylla", zap.Error(err))
}
defer session.Close()
selectQuery(session, logger)
insertQuery(session, "Mike", "Tyson", "12345 Foo Lane", "http://www.facebook.com/mtyson", logger)
insertQuery(session, "Alex", "Jones", "56789 Hickory St", "http://www.facebook.com/ajones", logger)
selectQuery(session, logger)
deleteQuery(session, "Mike", "Tyson", logger)
selectQuery(session, logger)
deleteQuery(session, "Alex", "Jones", logger)
selectQuery(session, logger)
}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):
func CreateCluster(consistency gocql.Consistency, keyspace string, hosts ...string) *gocql.ClusterConfig {
retryPolicy := &gocql.ExponentialBackoffRetryPolicy{
Min: time.Second,
Max: 10 * time.Second,
NumRetries: 5,
}
cluster := gocql.NewCluster(hosts...)
cluster.Keyspace = keyspace
cluster.Timeout = 5 * time.Second
cluster.RetryPolicy = retryPolicy
cluster.Consistency = consistency
cluster.PoolConfig.HostSelectionPolicy = gocql.TokenAwareHostPolicy(gocql.RoundRobinHostPolicy())
return cluster
}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.
Conclusion
This blog explains how to create a sample Go application that executes a few basic CQL statements with a ScyllaDB cluster using the GoCQLX package. It’s only an overview. For more details, including the code, the instructions for running it, check out the full lesson at ScyllaDB University.
The course Using ScyllaDB Drivers covers drivers in different languages such as Python, Node.js, Golang and Java and how to use them for application development. If you haven’t done so yet, register as a user for ScyllaDB University and start learning. It’s free!
