Geckoboard Developer Community

[Tutorial] How to use Geckoboard Datasets w/ Node, Express & MySQL


#1

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.

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);
});

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);
            });
        });
    };
}

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

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, don't forget to comment this line out.

Here’s a quick reference to 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

Voila! Enjoy! :slight_smile:


#2

Hey Jacob, thanks so much for sharing this - it’s really useful :slight_smile:


#3

Here’s some more Node.js tutorials.


#4

Hello,

It was an nice post to hear from you which is very useful. Thank you for sharing