Etcd and Compose for MySQL - The last leg of the NodeJS Grand Tour

Published

The Grand Tour is Compose's epic journey to write about how to connect to every Compose database with as many languages as possible. Here, we are on the last leg of the NodeJS journey stopping at etcd and MySQL.

The Node.JS segment of the Grand Tour comes to an end with etcd and Compose for MySQL. Two very different databases, but, as with all of the Compose Grand Tour articles a common example which shows how to connect to all the databases of Compose. In previous tours, we've covered Go and Python and this is the final stage of the Node.JS/JavaScript tour.

In the first stage of the Node.JS tour we covered MongoDB, Elasticsearch, and PostgreSQL. We then moved on to Redis and RabbitMQ. In the third leg of the journey we took on the Cassandra-compatible Scylla and RethinkDB. Skip back to the first stage if you want to know more about the common example; it's a simple Express web application which takes words and definitions, saves them and displays the current collection back. It's enough to show how to connect, write and read the database.

Now we're onto looking at etcd and MySQL so let's get going.

etcd

We'll be looking at the current etcd V3 gRPC-based API. The code for this example is in node/example-etcd3 in the Compose Grand Tour Node repository. There is, for the curious, an example for the previous V2 REST API in node/example-etcd but as that's the old API, we won't be covering it here.

The etcd Driver

For this example, we're going to use node-etcd3 as the driver to connect to the etcd database. It's a package that's written in TypeScript and if you use that you'll benefit from its type information. If you don't you can treat it like any other JavaScript package.

The etcd Connection

Because etcd supports multiple endpoints, the first thing we need to pass over is a list of https endpoints in an environment variable we like to call COMPOSE_ETCD_ENDPOINTS. You can find that information for a Compose deployment in the console overview's connection strings. Note that for these endpoints, don't include the username and password. Look to the command line example and it's --endpoints value for an example. It should look something like this:

export COMPOSE_ETCD_ENDPOINTS=https://portal1833-16.illustrious-etcd-60.compose-3.composedb.com:16217,https://portal1841-15.illustrious-etcd-60.compose-3.composedb.com:16217  

As for the username and password, they should be set in the environment variables COMPOSE_ETCD_USER and COMPOSE_ETCD_PASS like so:

export COMPOSE_ETCD_USER=root  
export COMPOSE_ETCD_PASS=EVERYBODYKNOWS  

With these three environment variables in place, we can move onto the code.

Preparing to Connect

The first step to connecting is to bring in the node-etcd package and import the various environment variables.

const { Etcd3 } = require("etcd3");

let endpoints = process.env.COMPOSE_ETCD_ENDPOINTS;

if (endpoints === undefined) {  
  console.error("Please set the COMPOSE_ETCD_ENDPOINTS environment variable");
  process.exit(1);
}

let envuser = process.env.COMPOSE_ETCD_USER;  
let envpass = process.env.COMPOSE_ETCD_PASS;  

This information will be passed to the Etcd3 constructor through an options object. We'll create that options object next:

let opts = {  
  hosts: endpoints.split(","),
  auth: {
    username: envuser,
    password: envpass
  }
};

The hosts field takes an array of endpoints so we split() that string up into an array.

Time to connect

With the options created, we can create our Etcd instance.

var etcd = new Etcd3(opts).namespace("/grand_tour/words/");  

The interesting part of this command is the .namespace() option. This means that any keys we use with this connection will be prefixed, automatically with "/grand_tour/words/". This allows us to use the database without clashing with any other application's keys.

The etcd Read

Reading our keys and values out of the database is fairly simple, but we do have to reformat the results once we have them. The first stage involves building a command; etcd.getAll().strings()..... It does what it says, it get's all keys and values in the namespace and then formats the returned data as strings.

function getWords() {  
  return etcd
    .getAll()
    .strings()

This would naturally return a promise with results when resolved but the results would be in the form of "word":"definition" and we need them in the form of an object with a word field and a definition field. We can use a then() to catch those results and return another promise instead, one that reformats the data like so:

function getWords() {  
  return etcd
    .getAll()
    .strings()    
    .then(values => {
      return new Promise((resolve, reject) => {
        let words = [];
        for (const key in values) {
          words.push({ word: key, definition: values[key] });
        }
        resolve(words);
      });
    });
}

This is a handy trick when you are trying to conform to an existing internal API like we are with our Grand Tour examples.

The etcd Write

The write to etcd is a minimal bit of code, even compared to the read:

function addWord(word, definition) {  
  return etcd.put(word).value(definition);
}

We simply put the word, as the key but prefixed by our namespace, into the database and associate it with the definition, as the value. This function returns a promise and we're done; the calling code can determine if the call worked or not.

And that's it for etcd's v3 API. There's a lot of functionality to harness in the API so remember to check out etcd3 package's documentation too.

Compose for MySQL

Before we dive in, the code for the MySQL examples is in node/example-mysql in the Grand Tour Node examples. Remember you can check out the entire collection to get a reference set of examples for almost all the Compose databases.

The MySQL Driver

MySQL is a hugely popular database and has a couple of NodeJS packages; specifically node-mysql and node-mysql2. The former, node-mysql, has a solid following, while the latter is based on node-mysql but is more modern with promises by default and has better performance. So we'll be using node-mysql2.

The MySQL Connection

Compose for MySQL needs a connection string to find the MySQL deployment and, ideally, a self-signed certificate to enable TLS and the verification of the server when using TLS. For the example, we have two environment variables, COMPOSE_MYSQL_URL for the connection string and PATH_TO_MYSQL_CERT for the path to the file which contains the self-signed certificate.

The connection string can be found in the Compose console overview. The self-signed certificate can also be found there - you'll have to copy its contents into a local file to use it. Once you have them, set the values in the environment like this:

export COMPOSE_MYSQL_URL=mysql://admin:EVERYBODYKNOWS@sl-eu-lon-2-portal.0.dblayer.com:17851/compose  
export PATH_TO_MYSQL_CERT=/home/keanu/certs/myownprivatesql.cert  

With that in place, we can move on to the code itself.

Preparing to connect

First things first, we'll want to import the mysql2 package.

const mysql = require("mysql2/promise");  

Notice that we're pulling it in with a suffix of /promise which enables the Promise support. Next, we need to pull in the environment variables; if the connection string isn't set, we'll exit.

let connectionString = process.env.COMPOSE_MYSQL_URL;  
let connectionCertPath = process.env.PATH_TO_MYSQL_CERT;

if (connectionString === undefined) {  
  console.error("Please set the COMPOSE_MYSQL_URL environment variable");
  process.exit(1);
}

We have everything we need to start connecting. The package's createConnection() function takes an object filled with the various settings, host, port, user, password and so on. We need to decompose the connection string into its component parts using URL.

let mysqlurl = new url.URL(connectionString);  
let options = {  
  host: mysqlurl.hostname,
  port: mysqlurl.port,
  user: mysqlurl.username,
  password: mysqlurl.password,
  database: mysqlurl.pathname.split("/")[1]
};

If we have a path to the self-signed certificate, we can read that in and add it to the options. To turn on TLS, we add an object to the options with a key of ssl. Then, to add the certificate we add a ca key to the ssl object. The ca key's value is an array with the certificate contents as a value.

if (connectionCertPath) {  
  var ca = [fs.readFileSync(connectionCertPath)];
  options.ssl = { ca: ca };

MySQL is interesting in that it can fall back to non-TLS/SSL connections unless you tell it to require TLS/SSL. We can do that through the flags field in options:

  options.flags = "--ssl-mode=REQUIRED";
}

Time to connect

We'll create a variable to pass our connection around between various functions. Creating the connection is then simply a matter of calling the createConnection() function with the options.

let connection=null;


mysql  
  .createConnection(options)

But we have things we want to do once the connection is created so, we'll add a .then() which gets the value of the newly created connection. We'll save that connection to the connection variable. Then to test the connection, we'll return a promise from a query. In this case, we'll send a query to create our words table if needed.

  .then(conn => {
    connection = conn;
    return connection.query(
      "CREATE TABLE IF NOT EXISTS words (id int auto_increment primary key, word varchar(256) NOT NULL, definition varchar(256) NOT NULL)"
    );
  })

Some side notes on that SQL Query. We create the table if it doesn't exist using "IF NOT EXISTS", it's still a query executed on the server so it makes for a useful test though if you don't want to create a table as a side effect of checking connectivity, use a "No-op" SQL such as "SELECT 1;". The table itself has an id field as an autoincrementing primary key. This field is essential on Compose's MySQL deployments as they use group replication and that needs a primary key on every table for replication.

Anyway, if that query works, we'll get a result and we can start the local web server. If it doesn't we'll catch the error, write it out and exit.

 .then(result => {
    // Listen for a connection.
    app.listen(port, function() {
      console.log("Server is listening on port " + port);
    });
  })
  .catch(err => {
    console.log(err);
    process.exit(1);
  });

We're now up and running. Time to do some reading and writing.

The MySQL Read

The MySQL read is, as you would expect, an SQL query. Remember in these examples the getWords() and addWord() functions return a promise, so the MySQL version of the read is:

function getWords() {  
  return connection
    .query("SELECT * FROM words ORDER BY word ASC")

But that would return a rather complex resultset where we simply want the rows of words and definitions. For that, we .then() the query promise and take the results as an array made up of a rows object and fields object. In the function, we wrap the rows in a new promise which we then return.

    .then(([rows, fields]) => {
      return new Promise((resolve, reject) => {
        resolve(rows);
      });
    });
}

The MySQL Write

Again, this is a simple SQL query which uses the ability of the driver to safely insert values into a SQL command template:

function addWord(word, definition) {  
  return connection.query("INSERT INTO words( word, definition) VALUES(?, ?)", [
    word,
    definition
  ]);
}

The values for the ? entries come, sequentially from the array that's passed in along with the template. And, the generated promise from this function is returned to the caller who can handle it completing or reporting an error.

And that's it for Compose for MySQL.

Tour's End

We've come to the end of the Compose Grand Tour for NodeJS. Our final destination, knowing how to connect to nine different databases on Compose, if you've read the entire series, of course, using NodeJS. The repository is always open for pull requests and suggestions if you notice something awry or have an improvement to make. The Grand Tour itself will be moving on to another language to follow the Go, Python, and Node. We hope you'll join us on that journey too.


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 Ian Usher

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.