I am a huge believer in leveraging APIs as it allows for the consumption of data in an easier (and, often more sane) means. If you are unfamiliar with APIs, there is a great article of which I recommend you read here. The goal of this tutorial is to have you be able to build your first API and import data into Geckoboard's dataset feature.

Goals:

  • Be able to create a simple API with no additional parameters
  • Connect to MySQL to pull the latest data
  • Have the relevant data sent to Geckoboard in set interval

I will be creating a second tutorial on how to build more parameters into your route (i.e. GET /endpoint/:param1/:param2). I will link the tutorial here once completed.

I will be using information provided by the Bureau of Transportation Statistics on airline on-time performance. You can download the dataset here.

Let's start with the prerequisites:

  • Node 6.6.0
  • NPM 3.10.7
  • All NPM modules (see packages.json dependencies)

package.json

{
  "name": "geckoboard-node",
  "version": "1.0.0",
  "description": "Simple Node application that integrates into Geckoboard datasets leveraging Express + MySQL",
  "main": "app.js",
  "scripts": {
    "start": "nodemon app.js"
  },
  "author": "Jacob Wall",
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.15.2",
    "connect": "^3.5.0",
    "connection": "0.0.0",
    "express": "^4.14.0",
    "geckoboard": "^1.1.0",
    "log-timestamp": "^0.1.2",
    "mysql": "^2.11.1",
    "node-cron": "^1.1.2",
    "nodemon": "^1.11.0",
    "request": "^2.76.0"
  },
  "engines": {
    "node": "6.6.0"
  }
}

If you copy the package.json and execute npm install --production then it will install the required npm dependencies in the node_modules folder. I also highly recommend that you use nodemon which will restart node when a file change is made. Instead of starting the application by writing node app.js you use nodemon app.js.

Now that we have package.json setup, we need to define the application.

app.js

require('log-timestamp'); // Logs timestamps
var express = require('express'); // Express.js Include
var bodyparser = require('body-parser'); // Body parser for fetch posted data
var connection = require('./connection'); // Required to define where express should find the MySQL credentials.
var routes = require('./routes'); // Route handling for express API
var port = process.env.PORT || 8080;

var app = express();
app.use(bodyparser.urlencoded({
    extended: true
}));
app.use(bodyparser.json());

connection.init(); 
routes.configure(app);

var server = app.listen(port, function() {
    console.log('Server listening on port ' + port);
});

The package.json is setup in that if you are using Heroku that the port number is passed as a variable. By defining it as:

var port = process.env.PORT || 8080;

Because Heroku uses a random port, we want the app to use port 8080 unless Heroku passes the variable of the port. So, it is fine to leave that piece there whether or not you are using Heroku.

We need to tell express how to connect to the database, so let's do that:

connection.js

var mysql = require('mysql');

function Connection() {
    this.pool = null;

    this.init = function() {
        this.pool = mysql.createPool({
            connectionLimit: 10,
            host: '127.0.0.1',
            user: 'root',
            password: 'passwd',
            database: 'database',
            ssl: true
        });
    };

    this.acquire = function(callback) {
        this.pool.getConnection(function(err, connection) {
            callback(err, connection);
        });
    };
}

module.exports = new Connection();

We have created the application, connection information but no endpoints or routes yet. So, let's start with creating your first endpoint. Create a folder called models and then inside I will be creating airline_cancellations.js

We will need to start by defining the variable connection.

var connection = require('../connection');

Now that the endpoint knows how to connect to the MySQL database, we can work on the function to query the database.

var connection = require('../connection');

module.exports = function(req, res) {
    this.get = function(res) {
        connection.acquire(function(err, con) {
                    var query = "SELECT dc.description,  \
                                 COUNT(cancellation_code) AS count  \
                    FROM   performance p  \
                                 LEFT JOIN def_cancel dc  \
                                                ON dc.code = p.cancellation_code  \
                    WHERE  dc.description IS NOT NULL  \
                    GROUP  BY dc.description \
                    ";

            con.query(query, function(err, result) {
                con.release();
                                res.send(result);
            });
        });
    };
}

You can also write the MySQL as a single line, but I find with the complex queries I typically am writing it becomes chaotic. Or, as we get more advanced in the routing of Express the statements become confusing for me to keep track of.

            var query = "SELECT dc.description,  \
                   COUNT(cancellation_code) AS count \
            FROM   performance p  \
                   LEFT JOIN def_cancel dc  \
                          ON dc.code = p.cancellation_code  \
            WHERE  dc.description IS NOT NULL  \
            GROUP  BY dc.description \
            ";

It is also worth bringing up that if you want to use a single quote that you will need to escape each backtick (') with a \ before each. i.e.

DATE_FORMAT(date,'%Y-%m-%d')

Would become:

DATE_FORMAT(date,\'%Y-%m-%d\')

Now, the fun part - routing!

Let's create routes.js in the project home folder. We will need to provide the variable for the model. In this example, /models/airline_cancellations is shorthanded as ac.

routes.js

var ac = require('./models/airline_cancellations')
var ac = new ac(); 

module.exports = {
    configure: function(app) {
      app.get('/ac/', function(req, res, next) {
          ac.get(res);
      })
    }
};

If you have multiple routes, you will want to change function(req, res) to function(req, res, next).

I use Postman, but you can use your web browser and navigate to http://localhost:8080/ac you can see that your API is working and returning results:

[
  {
    "description": "Carrier",
    "count": 1416
  },
  {
    "description": "National Air System",
    "count": 1186
  },
  {
    "description": "Weather",
    "count": 9025
  }
]

We now can access the data, but we have not sent it to Geckoboard yet. As of today, November 4, 2016, there are the following datatypes:

date

  • Must be formatted as YYYY-MM-DD
"fields":{
  "date":{
    "type": "date",
    "name": "Date"
  }
}

datetime

  • Formatted to ISO 8601 strings. YYYY-MM-DDThh:mm:ssTZD
"fields":{
  "datetime":{
    "type": "datetime",
    "name": "Datetime"
  }
}

number

"fields":{
  "number":{
    "type": "number",
    "name": "Some description here"
  }
}

percentage

  • Values such as 0.35 are interpreted as 35%.
"fields":{
  "percentage":{
    "type": "percentage",
    "name": "Percentage"
  }
}

string

"fields":{
  "string":{
    "type": "string",
    "name": "Description of string"
  }
}

money

"fields":{
  "dollars":{
    "type": "money",
    "name": "Dollars",
    "currency_code": "USD"
  }
}

You can access the most up to date API documentation for Geckoboard here.

Let's create your first node app to send data to Geckoboard. I use node-cron so that it sends the new information every 5 minutes. You can comment out cron.schedule(... and the console.log if you'd prefer if you did not have it automatically sent to Geckoboard.

gecko_ac.js

require('log-timestamp');
var express = require('express');
var request = require('request');
var app = express();
var API_KEY = 'apikeyhere';
var gb = require('geckoboard')(API_KEY);
var cron = require('node-cron');

var datasetName = 'first.geckoboarddataset'

cron.schedule('*/5 * * * *', function(){
  console.log('Sending ' + datasetName + ' dataset to Geckoboard...');

gb.datasets.findOrCreate({
    id: datasetName,
    fields: {
        description: {
            type: 'string',
            name: 'Type of cancellation_code'
        },
        count: {
            type: 'number',
            name: 'Count of cancellations'
        }
    }
}, function(err, dataset) {
    if (err) {
        console.error(err);
        return;
    }

    var request = require('request');
    request('http://localhost:8080/ac/', function(error, response, body) {
        if (!error && response.statusCode == 200) {
            dataset.put(JSON.parse(body), function(err) {
              console.log(datasetName + ' successfully sent to Geckoboard');
            });
        }
    }, function(err) {
        if (err) {
            console.error(err);
            return;
        }
    })
});
}); // If you don't want to use node-cron, comment this line out.

For your reference, here is how crons are formatted:

* * * * * *
| | | | | | 
| | | | | +-- Year              (range: 1900-3000)
| | | | +---- Day of the Week   (range: 1-7, 1 standing for Monday)
| | | +------ Month of the Year (range: 1-12)
| | +-------- Day of the Month  (range: 1-31)
| +---------- Hour              (range: 0-23)
+------------ Minute            (range: 0-59)

Now, if you run node gecko_ac you will get the following output:

[2016-11-04T18:06:50.501Z] first.geckoboarddataset successfully sent to Geckoboard

Do not freak out if you do not see anything at first. Since I use node-cron you will not see the first output for five minutes due to the cron.

Now in Geckoboard you will find your dataset. Congratulations!

geckoboard