In this tutorial I explained how to create a simple Express based API. The goal of this tutorial is to:

  • Create routes with multiple parameters
  • Use the MySQL npm [github] using MySQL strings for arguments

I will be using the same dataset from earlier. Bureau of Transportation Statistics on airline cancellation of flights. You can download the dataset here.

As a simple example, what if you wanted to find the number of cancellations, and the reasoning, behind all flights departing Los Angeles International Airport and an optional argument for a destination, say John F. Kennedy International Airport. So, we would be structuring the API call as GET route/:origin/:dest or, in this example, GET route/lax/jfk.

We will need to declare the variables, in this case, origin and dest. In this example, I wanted to be able to ask for the origin airport but not necessarily the destination airport. To do so, let's start with:

var origin = req.params.origin;
var dest = req.params.dest || '%';

If you don't care to have the variable dest be to % by default then you can just delete || '%';.

Previously our model of the connection query was:

con.query(query, function(err, result) {

We now need to add the argument to the connection query. A simple change to this line becomes:

con.query(query, [origin, dest], function(err, result) {

So, when you have multiple arguments, you will want to pass them in an array. If you were to make a route that only had one argument, then, it would be passed along by itself, i.e.

con.query(query, origin, function(err, result) {

We should also add to the line below that, an if statement to have it provide an error:

if (err) {
    throw err;
    }

Now, for our query it has now become:

SELECT    CONCAT_WS(' - ', origin, dest) AS route, 
          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 
AND       origin LIKE ?
AND       dest LIKE ?
GROUP BY  concat_ws(' - ', origin, dest), 
          dc.description

Quick side note, I had heard of the CONCAST_WS function in MySQL before. Pretty nifty I found out. Here's the MySQL reference.

We need to make sure that the value is escaped for the LIKE section of the query. To do so, we need to copy the below for each escaped variable.

" + "?" +"

If you do not do it that way (or, ?? for that matter) then it will try to query

SELECT    CONCAT_WS(' - ', origin, dest) AS route, 
          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 
AND       origin LIKE req.param.origin
AND       dest LIKE req.param.dest
GROUP BY  concat_ws(' - ', origin, dest), 
          dc.description

The completed model is below:

routecancellations.js

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

module.exports = function(req, res) {
	this.get = function(req, res, next) {
		var origin = req.params.origin;
		var dest = req.params.dest || '%';

		connection.acquire(function(err, con) {
			var query = "SELECT CONCAT_WS(' - ', origin, dest) AS route, \
			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 \
			AND  origin LIKE " + "?" +" \
			AND dest LIKE " + "?" + " \
			GROUP BY CONCAT_WS(' - ', origin, dest), dc.description";
			console.log(query, origin, dest)
			con.query(query, [origin, dest], function(err, result, fields) {
				if (err) {
					console.log('error');
					throw err;
				}
				con.release();
				res.send(result);
			});
		});
	};
}

Now, moving onto routes.js. This is super simple. Like any other route with Express, define the variables.

var route  = require('./models/routecancellations')
var route = new route();

So, after declaring the variable, we can make the route:

app.get('/route/:origin/:dest?', function(req, res, next) {
route.get(req, res, next);
});

It truly is that simple! :)

routes.js

var route  = require('./models/routecancellations')
var route = new route();

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

Voila, now if you query GET /route/lax/jfk you'll receive:

[
  {
    "route": "LAX - JFK",
    "description": "National Air System",
    "count": 12
  },
  {
    "route": "LAX - JFK",
    "description": "Weather",
    "count": 19
  }
]

Or, if you query GET /route/lax/

[
  {
    "route": "LAX - ABQ",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - ATL",
    "description": "Weather",
    "count": 5
  },
  {
    "route": "LAX - AUS",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - BNA",
    "description": "Weather",
    "count": 5
  },
  {
    "route": "LAX - BOS",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - BWI",
    "description": "Weather",
    "count": 12
  },
  {
    "route": "LAX - CLT",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - DAL",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - DAL",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - DCA",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - DEN",
    "description": "Carrier",
    "count": 3
  },
  {
    "route": "LAX - DEN",
    "description": "National Air System",
    "count": 2
  },
  {
    "route": "LAX - DEN",
    "description": "Weather",
    "count": 4
  },
  {
    "route": "LAX - DFW",
    "description": "Carrier",
    "count": 4
  },
  {
    "route": "LAX - ELP",
    "description": "Carrier",
    "count": 2
  },
  {
    "route": "LAX - EWR",
    "description": "National Air System",
    "count": 5
  },
  {
    "route": "LAX - EWR",
    "description": "Weather",
    "count": 27
  },
  {
    "route": "LAX - FLL",
    "description": "Carrier",
    "count": 2
  },
  {
    "route": "LAX - GEG",
    "description": "Weather",
    "count": 1
  },
  {
    "route": "LAX - HNL",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - HOU",
    "description": "Weather",
    "count": 4
  },
  {
    "route": "LAX - IAD",
    "description": "National Air System",
    "count": 4
  },
  {
    "route": "LAX - IAD",
    "description": "Weather",
    "count": 22
  },
  {
    "route": "LAX - IAH",
    "description": "Carrier",
    "count": 4
  },
  {
    "route": "LAX - IND",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - IND",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - JFK",
    "description": "National Air System",
    "count": 12
  },
  {
    "route": "LAX - JFK",
    "description": "Weather",
    "count": 19
  },
  {
    "route": "LAX - LAS",
    "description": "Carrier",
    "count": 12
  },
  {
    "route": "LAX - LAS",
    "description": "National Air System",
    "count": 4
  },
  {
    "route": "LAX - LAS",
    "description": "Weather",
    "count": 5
  },
  {
    "route": "LAX - MCI",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - MDW",
    "description": "Carrier",
    "count": 3
  },
  {
    "route": "LAX - MDW",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - MKE",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - MSY",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - OAK",
    "description": "Carrier",
    "count": 16
  },
  {
    "route": "LAX - OAK",
    "description": "National Air System",
    "count": 3
  },
  {
    "route": "LAX - OAK",
    "description": "Weather",
    "count": 5
  },
  {
    "route": "LAX - OGG",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - ORD",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - ORD",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - PDX",
    "description": "Carrier",
    "count": 2
  },
  {
    "route": "LAX - PDX",
    "description": "Weather",
    "count": 1
  },
  {
    "route": "LAX - PHL",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - PHX",
    "description": "Carrier",
    "count": 8
  },
  {
    "route": "LAX - PHX",
    "description": "National Air System",
    "count": 1
  },
  {
    "route": "LAX - PHX",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - RNO",
    "description": "Weather",
    "count": 2
  },
  {
    "route": "LAX - SAT",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - SBP",
    "description": "National Air System",
    "count": 6
  },
  {
    "route": "LAX - SBP",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - SEA",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - SFO",
    "description": "Carrier",
    "count": 16
  },
  {
    "route": "LAX - SFO",
    "description": "National Air System",
    "count": 18
  },
  {
    "route": "LAX - SFO",
    "description": "Weather",
    "count": 6
  },
  {
    "route": "LAX - SJC",
    "description": "Carrier",
    "count": 10
  },
  {
    "route": "LAX - SJC",
    "description": "National Air System",
    "count": 1
  },
  {
    "route": "LAX - SJC",
    "description": "Weather",
    "count": 9
  },
  {
    "route": "LAX - SLC",
    "description": "Weather",
    "count": 3
  },
  {
    "route": "LAX - SMF",
    "description": "Carrier",
    "count": 1
  },
  {
    "route": "LAX - SMF",
    "description": "Weather",
    "count": 4
  },
  {
    "route": "LAX - STL",
    "description": "Weather",
    "count": 1
  },
  {
    "route": "LAX - TUS",
    "description": "Weather",
    "count": 1
  }
]