Go and Compose - etcd v3, Scylla, and MySQL

Published

How to connect to every Compose database using Go: This is the third and final stop on the Go section of Compose Grand Tour which shows you the drivers, code and everything you need to get going with Go and Compose etcd v3, Scylla, and MySQL.

Compose's Grand Tour project sets out to provide an example of connecting to all of the databases of Compose. This includes using TLS/SSL, self-signed certificates, and any other appropriate good practice to get a more secure connection. In this third part, we'll look at etcd v3, Scylla and MySQL.

In the previous parts, we looked at Go with MongoDB, Elasticsearch, and PostgreSQL, Go with Redis, RethinkDB, and RabbitMQ and at "The Example", a simple web application that this, and all other Grand Tour examples are based on.

etcd v3

The Code: You'll find the code for the etcd v3 example in the example-etcdv3 directory of the Grand Tour - Go repository. This example, as the name suggests, is for the newer etcd v3 API and etcd 3.x versions of the database.

The Driver: The driver we use is the official Go driver from etcd; the database is built with Go and the driver is distributed as part of the database package. To import it into our application, we use this:

import (  
    "github.com/coreos/etcd/clientv3"
)

The etcd v3 example stands out currently because where most other databases pass a single connection string from the Compose overview, with credentials, here we have three environment variables to set. The COMPOSE_ETCD_ENDPOINTS variable should be a list of endpoints that the database can be contacted on. You'll find this in this overview.

Connection Strings and Command line screen shot

The simplest place to copy the endpoints list from is the --endpoints parameter in the command line. Here that gives us

https://portal1324-25.dazzling-etcd-63.compose-3.composedb.com:23930,https://portal2349-4.dazzling-etcd-63.compose-3.composedb.com:23930  

The COMPOSE_ETCD_USER and COMPOSE_ETCD_PASS more obviously come from the credentials section of the Overview. With that covered, let's get connecting.

The Connection: First we gather the three environment variables we just mentioned.

func main() {  
    var err error

    endpointlist := os.Getenv("COMPOSE_ETCD_ENDPOINTS")
    username := os.Getenv("COMPOSE_ETCD_USER")
    password := os.Getenv("COMPOSE_ETCD_PASS")

We can now create the basic Config type which passes connection information to the driver.

endpoints := strings.Split(endpointlist, ",")

    cfg := clientv3.Config{
        Endpoints:   endpoints,
        Username:    username,
        Password:    password,
        DialTimeout: 5 * time.Second,
    }

        etcdclient, err = clientv3.New(cfg)

The Endpoints field takes an array of endpoints, so we simply Split() our comma-delimited endpoint string. Once we have our type populated, we create out clientv3.New() connection.

The Write: For this example, we are going to write keys which contain our word and set the value to their definition. All the words will be prefixed with /grand_tour/words/ to group them in the key namespace. That means that this code is all we need to put a new word/definition into the database.

        _, err := etcdclient.Put(context.TODO(), "/grand_tour/words/"+r.Form.Get("word"), r.Form.Get("definition"))

Simply call the Put function with a context (we've left that as TODO()), a key and a value. When you do start defining a context, remember a call to etcdv3 can return errors with the underlying gRPC calls in the err return value or return exceeded deadlines or cancellations in the context. See the etcdv3 godocs for examples of handling that.

The Read: The reading of the data is complicated by the fact that we embedded the word in the key. The first thing we do is retrieve all the relevant keys.

        resp, err := etcdclient.Get(context.TODO(), 
                                "/grand_tour/words/", 
                                clientv3.WithPrefix())

The Get will retrieve key/value pairs for us. Skipping past the context, you'll see the second parameter is /grand_tour/words/. Unmodified, the Get would try and retrieve just that key but the third parameter is the first (and here only) option and that sets the WithPrefix flag so that second parameter is now a prefix and the call will return any keys with that prefix.

The response isn't quite the returned values we'd expect but is actually a GetResponse which is, in turn, a RangeResponse which wraps headers, a more flag, a count and the key/values in one structure. Our next task is to decant that structure into an array of items.

        var items []item

        for _, ev := range resp.Kvs {
            _, word := path.Split(string(ev.Key))
            items = append(items, item{Word: word, Definition: string(ev.Value)})
        }

resp.KVs is the array of KeyValue which we step through. The Key is a byte array and includes the whole key including the prefix. We coerce it to a string and then, as it looks file-system-ish, we use the standard path.Split() function to extract the word. We then append a new item to our items using the word we just extracted and the Value byte array, also coerced as a string, to initialize it.

And that wraps it up for etcd v3. The client library can seem initially a little overwhelming, but just remember that it follows its gRPC foundations to provide an API for all its functionality.

Scylla

The Code: As with all the examples, you can find the code for the Scylla example in the Grand Tour - Go repository. In this case, look in the example-scylla.

The Driver: In the example-scylla.go code, we use the gocql driver. It runs with Scylla and, of course, with Cassandra and we import it like so:

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

Note that we are also pulling in composeaddresstranslator which is a package which handles the mapping from IP addresses inside the Scylla cluster to outside addresses on the net. This map comes from the Compose Overview and we set it, as is, in the environment variable COMPOSE_SCYLLA_MAPS.

export COMPOSE_SCYLLA_MAPS='{  
                  "10.153.168.133:9042": "portal1122-5.regal-scylla-68.compose-3.composedb.com:20598",
                  "10.153.168.134:9042": "portal1085-4.regal-scylla-68.compose-3.composedb.com:20598",
                  "10.153.168.135:9042": "portal1130-0.regal-scylla-68.compose-3.composedb.com:20598"
                }'

Note the surrounding single quotes around the value. The other variable that needs to be set is the COMPOSE_SCYLLA_URLS which is a comma delimited string made up of the three connection strings, with credentials, that are displayed in the Compose Overview too.

export COMPOSE_SCYLLA_URLS="https://scylla:password@portal1122-5.regal-scylla-68.compose-3.composedb.com:20598,https://scylla:password@portal1085-4.regal-scylla-68.compose-3.composedb.com:20598,https://scylla:password@portal1130-0.regal-scylla-68.compose-3.composedb.com:20598"  

With these set, we can look at the connection code.

The Connection: The first part of setting up the connection is gathering the various bits of connection data from the environment.

     urlstring := os.Getenv("COMPOSE_SCYLLA_URLS")
     mapstring := os.Getenv("COMPOSE_SCYLLA_MAPS")
     urls := strings.Split(urlstring, ",")
     parseurl, err := url.Parse(urls[0])
     user := parseurl.User
     username := user.Username()
     password, isset := user.Password()

To get the username and password, we split up the list in COMPOSE_SCYLLA_URLS and then parse the first URL we find. In this example, that'll be the last we refer to those URLs because of what happens next. We create a composeaddresstranslator which can consume the mapstring as JSON.

    addresstranslator, err = composeaddresstranslator.NewFromJSONString(mapstring)

The Compose translator has an additional trick up its sleeve; it can return an array of ContactPoints which can be used to seed a new cluster connection.

    cluster = gocql.NewCluster(addresstranslator.ContactPoints()...)

That's the start of creating a new cluster connection though. Now we have to populate various settings for the connection. In this case we're going to ask for Quorum consistency from the nodes and turn on SSL by setting the SslOpts to "not nil".

    cluster.Consistency = gocql.Quorum
    cluster.SslOpts = &gocql.SslOptions{} 

Now we need to set up a password authenticator for the connection and seed it with the username and password we extracted earlier. Then we need to tell the connection to use addresstranslator we created so it can do all the needed mapping and to ignore peer addresses so it doesn't try and discover other node addresses.

    cluster.Authenticator = gocql.PasswordAuthenticator{
        Username: username,
        Password: password,
    }
    cluster.AddressTranslator = addresstranslator
    cluster.IgnorePeerAddr = true

With that created and initialized we can now create a session with the database.

    session, err = cluster.CreateSession()
    defer session.Close()

Our final step in setting up the connection is to make sure we have a keyspace and table to write in.

    err = session.Query("CREATE KEYSPACE IF NOT EXISTS examples WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '3' }").Exec()
    err = session.Query("CREATE TABLE IF NOT EXISTS examples.words (my_table_id uuid, word text, definition text, PRIMARY KEY(my_table_id))").Exec()

The created keyspace is set to be simply replicated over the three nodes for reliability. The table is a simple UUID and two text fields. Both are created using IF NOT EXISTS to ensure they don't overwrite previously. Now we can get to reading and writing.

The Read: This time around we'll start with the reading of the data. This begins with a query being run on the database.

      iter := session.Query("SELECT my_table_id,word,definition FROM examples.words").Iter()

This runs a CQL SELECT for all our words, returning an Iter which is practically the same as a cursor; we can increment over it and read a row at a time from the database. Now we need to do that reading. First, we'll create an empty array and then define some variables to temporarily stow read row data into.

        items := make([]item, 0)
        var tmpid, tmpword, tmpdef string

For each row, we call iter.Scan() with a list of variables which match the order of fields we specified in the CQL SELECT command. Then we take those results, package them into an item and append that to the items array.

        for iter.Scan(&tmpid, &tmpword, &tmpdef) {
            newitem := item{ID: tmpid, Word: tmpword, Definition: tmpdef}
            items = append(items, newitem)
        }
        err := iter.Close()

When we are done, we close the Iter.

The Write: The write operation is somewhat simpler and very similar to SQL. This time we create a Query on the session to insert our values, retrieved from the form in the request r. The UUID we obtain from Gocql's own UUID generator.

    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()

The .Exec() tells the query to run, only returning errors if they come up.

That's pretty much it for the Scylla connection, apart from the fact that there's a supplementary library, gocqlx, which makes reading much easier and writing much more reusable. The example is in the example-gocqlx directory and there's another article, Go and simplifying Scylla with Gocqlx which looks at that option in detail.

MySQL

The Code: Our final stop is the code for the MySQL example in the example-mysql directory of the Grand Tour - Go repository.

The Driver: This, like PostgreSQL, leverages the built-in SQL framework of Go. That means that we simply import an appropriate driver, in this case go-sql-driver/mysql, by doing this.

import (  
    _ "github.com/go-sql-driver/mysql"
)

We'll be using the Compose connection string for MySQL to configure the connection.

The Connection: This is where the MySQL driver differs in that it uses DSNs, Data Source Names. That means we have to start by reformatting and creating a DSN based on the URL.

  connectionString := os.Getenv("COMPOSE_MYSQL_URL")
    url, err := url.Parse(connectionString)
    passwd, _ := url.User.Password()
    dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s",
        url.User.Username(),
        passwd,
        url.Host,
        url.Path[1:])

We parse the connection string as given and format it as a TCP based connection. With a DSN created, we can go on to create the connection to the database using sql.Open and naming the driver we want to use. With the SQL framework, this is not yet a working connection. To ensure it is alive, we use db.Ping() and then check the error state for failure.

  db, err = sql.Open("mysql", dsn)
  defer db.Close()

  err = db.Ping()
  if err != nil {
    log.Fatal(err)
  }

  _, err = db.Exec("CREATE TABLE IF NOT EXISTS words (id int auto_increment primary key, word varchar(256) NOT NULL, definition varchar(256) NOT NULL)")

We finish the connection by creating a table for the words. Note the unique primary key is required on Compose's MySQL configurations as it plays an essential part in group replication.

The Read: With our db available, we can issue an SQL query to the database.

        rows, err := db.Query("SELECT id,word,definition FROM words ORDER BY word ASC")
        defer rows.Close()

This returns a cursor to us, so we defer closing the cursor to tidy up automatically after using it. Now we get to build up our items array after creating it.

        items := make([]*item, 0)

Now, calling up rows.Next() will move onto the next available row. We can have this in a loop; it will exit when there are no more.

    for rows.Next() {
        myitem := new(item)
        err := rows.Scan(&myitem.ID, &myitem.Word, &myitem.Definition)
        items = append(items, myitem)
    }

For each row, we create a new item, use rows.Scan to scan the fields into the item variables and then append the new item to the items. The code continues after this marshaling the items as JSON and sending that on to

The Write: The write is another example of the SQL framework making it simple to send ad-hoc statements to the database. It's simply an INSERT.

    _, err := db.Exec("INSERT INTO words (word, definition) VALUES(?,?)", r.Form.Get("word"), r.Form.Get("definition"))

We drop the form values for the word and definition, retrieved from the response, into the INSERT command and that's it. The id will be autogenerated. That, apart from some error-checking, is it.

Like PostgreSQL and other SQL framework driver databases, the sqlx package can make life considerably simpler when working with SQL – You'll find an example in the example-sqlx directory of using it.

Wrapping up

That brings us to the end of our tour of Compose databases with Go. The eagle-eyed may have noticed that JanusGraph isn't in the current batch of examples. That's down to the lack of a Go driver for WebSocket connections, something we are working on. We will, as with all the Grand Tour code, be maintaining the examples as time moves on. We are also happy to receive your pull requests if you spot anything that can be done more effectively or clearly.


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 Bryan Minear

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.