Go and simplifying Scylla with Gocqlx

Published

Cql (Cassandra Query Language) is similar to SQL, but it is not SQL. And while Gocql brought a rich driver to Go developers, it was missing something. Now, inspired by sqlx, the Scylla developers have brought us Gosqlx to make life easier.

Using Go and Scylla with the Gocql driver could be frustratingly error-prone exercise. Gocql is a relatively low-level driver and as such offers no help building queries and no assistance binding Go structures to database tables. Without a query builder, your code becomes intermingled with blocks of CQL strings which obscure your business logic. And with no binding, you spend your time marshaling and unmarshalling data between rows and structures.

And this is where Gocqlx comes in. Gocqlx sets out to do for CQL what the sqlx package does for SQL in Go; make it a lot more manageable. We recently released the Scylla/Go example in our Grand Tour examples and it's there that we'll go for some simple comparisons of code.

Reading data

In the example, we have to retrieve all the rows to send to the user. With plain gocql, we make a slice of item structures, then write in full the command for a query and then send the query to the database to get an iterator. We then have to create a set of temporary variables ready to iterate through the results. As we do that we read each part of the row into the temporary variables. Then we create a new item with those variable values and append the new item to our current list of items.

import (  
    "github.com/gocql/gocql"
)

...

type item struct {  
    ID         string `json:"_id,omitempty"`
    Word       string `json:"word"`
    Definition string `json:"definition"`
}

...

        items := make([]item, 0)
        iter := session.Query("SELECT my_table_id,word,definition FROM examples.words").Iter()
        var tmpid, tmpword, tmpdef string
        for iter.Scan(&tmpid, &tmpword, &tmpdef) {
            newitem := item{ID: tmpid, Word: tmpword, Definition: tmpdef}
            items = append(items, newitem)
        }
        err := iter.Close()
        if err != nil {
            log.Fatal(err)
        }

It's all functional code but... first, there's a string with a CQL query, field, and table names embedded. And there's the process of extracting the data where you need to make sure you synchronize that query with the order of your fields being extracted. Well, it's a future minefield, functional but fragile.

Let's look at the same code using Gocqlx:

import (  
    "github.com/gocql/gocql"
    "github.com/scylladb/gocqlx"
    "github.com/scylladb/gocqlx/qb"
)

...

type item struct {  
    ID         gocql.UUID `json:"_id,omitempty" db:"my_table_id"`   
    Word       string     `json:"word"`
    Definition string     `json:"definition"`
}

...

    stmt, _ := qb.Select("examples.words").ToCql()
        var items []*item
        err := gocqlx.Select(&items, session.Query(stmt))
        if err != nil {
            log.Fatal(err)
        }

It's a more compact bit of code, but more importantly, it's also less fragile. Let's start with qb.Select. This starts building a query that does a select on the named table. We don't have to specify fields here, we can just let it select "*" - you'll see why in a moment. There's a lot of other methods you can use to compose a query here but, whatever you decide to use, you finish with a toCql() which builds the statement.

Moving on, we create the slice to keep our items in and then we invoke gocqlx.Select, giving it the slice we want to store the results in the first parameter and a gocql.Query built with the generated statement as the second parameter.

Behind the scenes gocqlx.Select is doing all the unmarshalling using the information from the item type. It does this using the same mechanisms (and even the same package, ReflectX) as the sqlx package - unless tagged with a "db" tag, struct fields map to the lower-case version of the field name. If tagged, as the ID field is, then it uses that name my_table_id.

And that is practically it. Your data is retrieved and ready to go.

Writing data

The other part of the example is inserting new definitions. This is already pretty compact in the gocql version of the code:

    r.ParseForm()
        uuid, _ := gocql.RandomUUID()
        err := session.Query("INSERT INTO examples.words(my_table_id, word, definition) VALUES(?,?,?)", uuid, r.Form.Get("word"), r.Form.Get("definition")).Exec()
        if err != nil {
            log.Fatal(err)
        }

A random UUID is created and then a query is constructed, variables passed in from the form and executed. There's a lot of parts in that session.Query call. Let's look at a Gocqlx version.

    stmt, names := qb.Insert("examples.words").Columns("my_table_id", "word", "definition").ToCql()

        uuid, _ := gocql.RandomUUID()

        newitem := &item{ uuid,
            r.Form.Get("word"),
            r.Form.Get("definition"),
        }

        q := gocqlx.Query(session.Query(stmt), names).BindStruct(newitem)

        err := q.ExecRelease() 
        if err != nil {
            log.Fatal(err)
        }

What's happened here is that Gocqlx has helped us separate the moving parts. The code opens creating a statement and list of names for parameters using the query builder. The columns are explicitly listed - we've yet to find a quick way to turn a type into a column name list.

The uuid is created as before, but then we create an item which holds all our new values. In more realistic code, this could well just be the data's in-memory representation being presented. As an aside, there's also an Update builder function too, where the pattern of only updating specific fields makes more sense.

Anyway, once the item is created, it's time to write it to the database. This is done with the gocqlx.Query method which takes a gocql Query, created from out statement and a list of names for the various values the statement will need filling in. The BindStruct() function will do that filling in, mapping the item's fields to the statements fields. Once that's done, the query is fully populated and ready to be run.

That running is handled by calling ExecRelease() on the created query; it'll execute the query, return nothing and release the query so it can't be reused. And yes, there is an Exec() which can be called which allows query reuse but we didn't need that here. And so we've done our insert.

Epilogue

These are trivial cases and as such makes a less than great example, but it does illustrate the unwinding of the query building from the data binding and database writing. The Gocqlx package makes it a lot easier to do without building your own abstract layer over your database code and you can still go straight down to gocql calls if you want. If you are working with Scylla (or Cassandra) from Go, add it to your code armory today.

Further Reading

Read more articles about Compose databases - use our Curated Collections Guide for articles on each database type. If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

attribution Stefan Kunze

Dj Walker-Morgan
Dj Walker-Morgan is Compose's resident Content Curator, and has been both a developer and writer since Apples came in II flavors and Commodores had Pets. Love this article? Head over to Dj Walker-Morgan’s author page to keep reading.

Conquer the Data Layer

Spend your time developing apps, not managing databases.