Category Archives: Node.js

Defining Roles-based Security ACLs and Supporting Multitenancy in the Node.js Strongloop Loopback framework.

Strongloop Loopback (https://loopback.io) is a Node.js framework that extends Express.js and makes it easy for developers to create REST-based CRUD APIs in minutes.

In my experience, their proclamations about RAD API development generally holds true, however, their documentation and, in particular, their tutorials, really need a lot of work in terms of organization. While there’s a lot of information that’s presented, it doesn’t really flow to tell the complete story of how you would solve real-world problems and how all of the pieces really fit together.

Hopefully you’ll find that this post helps bridge that gap. I assume that you already have a very basic understanding of the Loopback framework and that you are also a movie nerd.

Let’s say, for example, that you have an e-commerce website that you plan to offer as Software as a Service. It has the following three tables:

  • Stores – a registry containing the stores that are being hosted
  • Users – including username, password, email, and a foreign key store_id that maps back to a record in the Stores table.
  • Orders – Orders placed by a user. Contains a foreign key to the Users table and a foreign key to the Stores table.

We need to create CRUD services for each of these tables, secure them with roles-based security and also support multi-tenancy. For example, an administrator for Store ID #2 should only be able to see Orders and Users that were added store ID #2. A superuser should be able to see everything.

Creating Tables to Support Roles-Based Security

Loopback has connectors for most commonly used databases. Connection data is stored in your project’s server/datasources.json file. In this case, I’ve created a custom datasource named “ecommerce”:

{
  "db": {
    "name": "db",
    "connector": "memory"
  },
  "ecommerce": {
    "host": "[some mysql host on Amazon]",
    "port": 3306,
    "database": "ecommerce",
    "name": "ecommerce",
    "user": "quizartshaderach",
    "password" : "ForH3IsTh3"
    "connector": "mysql"
  }
}

Loopback natively supports roles-based security out of the box. You simply need to have the framework automagically add its tables to your database by placing the following script in your project’s /server/ folder and running it.

var server = require('./server');
var ds = server.dataSources.ecommerce;
var lbTables = [
 'Application','User', 'AccessToken', 'ACL', 'RoleMapping', 'Role'
];
ds.automigrate(lbTables, function(er) {
  if (er) throw er;
  console.log('Loopback tables [' - lbTables - '] created in ', ds.adapter.name);
  ds.disconnect();
});

COOL!

Defining Roles

In the generated Role table, we’re going to add two roles — a “superuser” who is the ultimate supreme being (think Keanu Reeves as John Wick), and a “storeadmin” who manages a specific store or stores in our SaaS system. Loopback has $unauthenticated, $authenticated, and $everyone as built-in, immutable roles. So, we got that going for us:

Roles

Defining Users

You may have noted that Loopback has its own User table definition. You should actually hide this from the API and instead extend its properties into your own custom table as illustrated by the following screenshot:

UsersTableDef

Your corresponding /common/models/users.json file should resemble the following snippet and only list out properties that are *not* baked into the native loopback User model. Also note that I’m already using ACLs to deny general access to unauthenticated users (a built-in immutable loopback role) and grant full access to John Wick, my superuser who can kill five angry customers in a bar with a pencil. A f****ing pencil.


 {
  "name": "users",
  "plural": "users",
  "base": "User",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "firstname": {
      "type": "string"
    },
    "lastname": {
      "type": "string"
    },
    "disabled": {
      "type": "boolean"
    },
    "creationDate": {
      "type": "date"
    },
    "store_id": {
      "type": "number"
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

Creating a Superuser/SuperAdmin Account

Now that the basics of your security framework are in place, you can use Loopback’s APIs to create the superuser account. Loopback automatically generates a Swagger ui for all of its services and will automatically hash the password and store it in your database.

explorer

Note that Loopback will default to using the npm crypto library for hashing passwords. This dependency caused us some issues when deploying on Amazon elastic beanstalk, so we had to use the pure javascript cryptojs library instead, which is also supported by the framework. So you might need to execute the following commands to install the appropriate library for your deployment platform:


npm uninstall crypto
npm install cryptojs

After your superuser account has been added to your users table, you can assign it the superuser role by inserting a record to the RoleMapping table:

addingARoleMapping

Logging In

After you’ve added your account, verify that you can use it to login by executing the users/login service from the Loopback explorer. If your login was successful, the service will return an access token id:

So the following post call: http://localhost:3000/api/v1/users/login?include=user would return something similar to:

{
  "id": "OcCGkGHCKKQrLNr2mS1DskVMXxae7IJlOezkEttVvvYXjk74gwRdpBrW7LEBufG8",
  "ttl": 1209600,
  "created": "2018-09-12T11:17:14.824Z",
  "userId": 1,
  "user": {
    "firstname": "John",
    "lastname": "Wick",
    "disabled": false,
    "creationDate": "2018-09-05T01:47:07.000Z",
    "store_id": 1,
    "realm": null,
    "username": "Administrator",
    "email": "jwick@thecontinentalhotel.com",
    "emailVerified": true,
    "id": 1
  }
}

You can then copy and paste the returned id into Loopback Explorer’s accessToken field. Loopback Explorer will subsequently automatically append a query string variable named access_token to every request.

Securing Services

There are at least 4 ways to secure services:

  1. Statically apply ACLs
  2. Define ACLs in the ACL database table
  3. Programmatically hide public methods
  4. Dynamically verify permissions at runtime with an observer event

Statically applying ACLs

We use static ACLs to restrict methods of our ORDERS table to specific groups. In this particular user story, we want to deny access to anyone who hasn’t logged in, allow full access for anyone in the “superuser” role, and allow read/write access to anyone who’s authenticated:

(common/models/orders.json)

"acls": [
    {
      "accessType": "*",
      "principalType": "ROLE",
      "principalId": "$unauthenticated",
      "permission": "DENY"
    },
    {
      "accessType": "*",
      "principalType": "ROLE",
      "principalId": "superuser",
      "permission": "ALLOW"
    },
    {
      "accessType": "READ",
      "principalType": "ROLE",
      "principalId": "$authenticated",
      "permission": "ALLOW"
    },
    {
      "accessType": "WRITE",
      "principalType": "ROLE",
      "principalId": "$authenticated",
      "permission": "ALLOW"
    }
  ]

Defining ACLs in the Database

Note that you could also implement these rules by adding records to the ACL database table, which would give you a little bit more flexibility for tweaking security without having to modify source code or restart the node service.

dbsecurity

Hiding Public Methods

Our user story dictates that no user should be able to delete an order, so we attack this requirement by stripping the programatically hiding the method in the common/models/orders.js file. We also want to disable a user’s ability to update all records and any other services that we’re not intending to use in our application:

'use strict';

module.exports = function(Orders) {
	
// remove unused methods
Orders.disableRemoteMethodByName('deleteById'); // Removes (DELETE) /products/:id
Orders.disableRemoteMethodByName("prototype.patchAttributes"); // Removes (PATCH) /products/:id
Orders.disableRemoteMethodByName('createChangeStream'); // Removes (GET|POST) /products/change-stream
Orders.disableRemoteMethodByName("updateAll"); // Removes (POST) /products/update
}

Handling Multi-tenancy and Record-Level Permissions with Observers

Loopback includes a series of Operation Hooks that are triggered from all methods that execute a particular high-level create, read, update, or delete operation. For our user story, we want to implement the following business rules:

  1. John Wick can do whatever he needs to do because he’s wearing bulletproof eveningwear.
  2. A store admin can only access records that are related to their specific store Id.
  3. Only a superadmin or a storeadmin can update an Order record.
  4. A user can only only access records that they “own” (user_id foreign key relationship).
  5. Any authenticated user can create a new order.

Each observer gets passed a context (ctx) object. Through the context object, you can access the user’s ID and dynamically modify the SQL WHERE clause before it is passed to the database server. To implement our business rules, however, we also need additional information from the users table for the logged in account. This necessitates adding the following script to the server/boot folder which will lookup additional user info on every request and add it to the context object:

(server/boot/attach-user-info.js)

module.exports = function(app) {
  app.remotes().phases
    .addBefore('invoke', 'options-from-request')
    .use(function(ctx, next) {
      if (!ctx.args.options || !ctx.args.options.accessToken) return next();

      // attach user info to context options
      const User = app.models.users;
      User.findById(ctx.args.options.accessToken.userId, function(err, user) {
        if (err) return next(err);
        ctx.args.options.currentUser = user;
        next();
      });
    });
};

Now that we’ve marshalled all of our user information, dynamically adding where clauses to queries at runtime through observers becomes a relatively trivial process:

(common/models/orders.js)


// handle multitenancy read operations
Orders.observe('access', function limitToTenant(ctx, next) {
  let authorizedRoles = ctx.options.authorizedRoles;
  let userId = ctx.options.accessToken.userId;
  let storeId = ctx.options.currentUser.store_id;

  if (!authorizedRoles.superuser) {
   // non super-duper admins ("storeadmin") can only see orders bound to their "store"
   ctx.query.where = ctx.query.where || {};
   ctx.query.where.store_id = storeId;

   if (!authorizedRoles.storeadmin) {
    // non store admins can only see orders that they "own"
    ctx.query.where = ctx.query.where || {};
    ctx.query.where.user_id = userId;
   }
  }
  next();
});

// only allow admins to update records
Orders.observe('before save', function preventUpdatesFromNonAdmins(ctx, next) {  
  let authorizedRoles = ctx.options.authorizedRoles;
		
  // the presence of ctx.instance indicates an "insert" operation
  // the presence of ctx.instance.id indicates an update
  if (!ctx.instance || ctx.instance.id) { 
    if (!authorizedRoles.superuser && !authorizedRoles.storeadmin) {
     throw new Error("Security Exception - only admins can update an order record.");
    }
  }
  next();
});

And in the end…

John was once an associate of ours. They call him Baba Yaga. Well, John wasn’t exactly the boogeyman. He was the one you sent to kill the f***ing boogeyman!

If you’ve found this post to be helpful in defeating the Strongloop Looopback learning curve boogeyman, please add a comment below.

Happy coding!

ColdFusion and Node.JS – two great tastes that taste great together!

Fig Leaf Software recently delivered a hybrid ColdFusion – Node.js solution to one of our customers.

We decided to use a hybrid architecture for the following reasons:

  1. The customer expressed a preference for using ColdFusion to drive their CMS-based website.
  2. The website has several embedded, small AJAX applications that leverage data from an XML-based API that sits behind the customer’s firewall.
  3. Java-based app servers (ColdFusion) are particularly well-suited for dynamically assembling and serving web pages.
  4. Node.js servers are particularly good at implementing REST APIs and handling large volume, asynchronous data requests for non-cacheable dynamic data.
  5. The website/web apps do not require user authentication, therefore session sharing between different app server technologies was not an issue.
  6. Sharing the workload between CF and Node gives us more flexibility for scaling up the infrastructure in a more cost-effective manner with options for  potentially off-loading the Node service to cloud platforms such as Heroku.

wmata

 

Embracing Open Government with Node.js and Microsoft SQL Server

On May 9, 2013, President Obama signed an executive order that made open and machine-readable data the new default for government information. Making information about government operations more readily available and useful is also core to the promise of a more efficient and transparent government.

Since then, a lot of agency data has been made available on http://www.data.gov. Unfortunately, very little of it is well-suited for web developers who want to immediately start building apps. File formats vary considerably with much of the data being published in difficult to process formats of HTML, CSV, PDF, and Microsoft Excel. Only a very, very small percentage is exposed through a dynamic, queryable standards-based web-services interface.

Recently my company, Fig Leaf Software was tasked by one of our government agency clients with publishing a searchable dataset, currently stored in Microsoft SQL Server, as a series of REST-based web services. The dataset is fairly large and diverse, consisting of 50+ tables. While the customer preferred an Adobe ColdFusion-based solution, we were given the opportunity to select the best technology to deliver the most scalable and cost-efficient solution possible. We also wanted to engineer a solution that could be easily rolled out to other government agencies who were facing the same challenges. By the look of what’s going on at www.data.gov, there’s a whole lot of them.

And for creating RESTful web-services APIs, that technology is Node.JS.

Node.js is a JavaScript runtime built on Chrome’s V8 JavaScript engine. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient. Unlike Java, .NET, or PHP solutions, database requests in Node do not delay thread execution and is therefore much more efficient at handling a high concurrency of requests. The Express framework for Node makes it relatively easy to implement RESTful APIs.

Of course, the real problem at hand was how to expose large, diverse SQL server datasets that would not require tons of hours spent on hand-coding and documenting table and view-specific APIs?

Designing the Request Pattern

We decided to implement an API where each managed table or view could be accessed through the following URL pattern:

/api/entityname?filter=[filters]&sort=[sorters]&start=n&limit=n

  • entityname is the name of the table, view, or stored procedure to access in the database.
  • filter is an array of objects that have the following properties:
    • property : The field to query
    • value: The comparison value
    • operator: The comparison operator
  • sort is an array of objects that have the following properties:
    • property: The field to sort by
    • direction: the sort direction (either ASC or DESC)
  • start is the starting row to return. Defaults to 0.
  • limit is the total number of rows to return (limited to <= 500). Defaults to 25.

Only entityname and sort are required.

The following example url illustrates using multiple filters and sorters to retrieve the first 100 employee records whose last names start with the letter B, placed in sort order by state and city.

/api/employees?filter=[{“property”: “lastname”,”value”:”B”,”operator”:”ge”},{“property”: “lastname”, “value”:”C”, “operator”:”lt”]&sort=[{“property”:”state_code”,”direction” : “asc”},{“property”:”city”,”direction” : “asc”}]&start=0&limit=100

This pattern is similar to the one used by the Sencha Ext JS Javascript framework’s REST Proxy.

The pattern also had to support receiving an authorization code that would be transmitted as part of the HTTP header so that requests could be backtracked to a specific developer and logging could be used to set throttle limits on the number of requests.

Designing the Response Pattern

Output from each of our REST services is a JSON as illustrated by the following snippet:

{
  data: [],
  success: true,
  total: n
}
  • data is an array of javascript objects that represent matching rows from the query result
  • success is a boolean value that indicates whether the query succeeded or not.
  • total is an integer that represents a count of total # of available records that match the filter condition

Defining an Application Config File

The Node.js app has to connect to a SQL database and mail server, both of which require credentials that we wanted to keep separate from our application source code. In addition, we also needed to indicate to our app the specific tables and views that would be queryable, since not every datatable or view in our database was going to have a public API.

To support these requirements, we used the Node.JS config module to read application configuration properties from the static JSON file illustrated below.

{
  "clientName" : "My .gov client",

 "database" : {
    "server": "127.0.0.1",
    "database": "sampledatabase",
    "user": "sdrucker",
    "password": "mypassword",
    "port": 1433,
    "options": {
        "instanceName": "SQLEXPRESS"
    }
 },

  "smtp" : {
    "port": 465,
    "host": "email-smtp.us-east-1.amazonaws.com",
    "auth" : {
      "user" : "[insert credentials here]",
      "pass" : "[insert credentials here]"
    },
    "secure": true,
    "maxConnections": 5,
    "maxMessages": 10,
    "rateLimit": 5
  },

  "entities" : {
    "campgrounds": {
      "type": "view",
      "obj": "campgrounds",
      "label": "Campgrounds",
      "description": "Information about campgrounds"
    },
    "visitor_center": {
      "type": "table",
      "obj": "visitor_center",
      "label": "Visitor Centers",
      "description": "All visitor centers"
    }
  }
}

Note: Storing unencrypted credentials in a text file is definitely not a best practice. I’ll discuss deployment/clustering/security issues with Node.js in my next blog post.

Implementing the Request Pattern in a Node.js/Express.js Route

Express.js routers make it easy to handle dynamic routes and parse arguments from the URL. Our main web service route performs the following tasks:

  • Verifies that the requested table/view is in a list of supported entities.
  • Parses the table/view name from the URL
  • Verifies that a valid authorization code was transmitted over the http header
  • Log the request to a database table
  • Validate and parse the start, limit, filter, and sort url parameters

var express = require('express');
var router = express.Router();
var config = require('config');
var db = require('./../database');

isValidEntity = function (entityName) {
    var entities = config.get('entities');
    if (entities[entityName]) {
        return true;
    } else {
        return false;
    }
}

//
// set tablename parameter and check authorization
//
router.param('entityName', function (req, res, next, id) {
    var isValid = true;

    if (!isValidEntity(req.params.entityName)) {
        isValid = false;
        var err = new Error('Not Found - Invalid Entity Name');
        res.status(404);
        res.render('error', {
            message: 'You did not transmit a valid entity name',
            error: '404-Not Found'
        });
    }

    // check authorization code
    var authorization = req.headers.authorization;
    if (!authorization) {
        isValid = false;
        var err = new Error('Invalid Authorization Key');
        res.status(401);
        res.render('error', {
            message: 'You did not transmit a valid authorization key',
            error: '401-Unauthorized'
        });
    }

    if (isValid) {
        // lookup authorization id
        db.query('api_users', {userid: authorization}, 'userid', 0, 1, function (err, results) {
            if (results.data.length == 1) {
                db.executeStoredProcedure(
                    'api_log_insert',
                    {
                        "userid" : authorization,
                        "request" : decodeURI(req.originalUrl),
                        "ipaddress" : req._remoteAddress
                    } ,
                    function(err,data) {
                        // reserved for future use
                    }
                );
                next();
            } else {
                res.render('error', {
                    message: 'The authorization key that you transmitted is invalid',
                    error: '401-Unauthorized'
                });
            }
        })
    }

});

//
// Parse/validate URL parameters, build the dynamic SQL, and transmit results
// in Javascript Object Notation (JSON) format
//
router.get('/:entityName', function (req, res, next) {

    var entityName = req.params.entityName;
    var params = req.query;
    var startrow, maxrows, orderby;
    var entityDetails = config.get('entities')[entityName];

    // set defaults for pagination
    if (!params.start) {
        startrow = 0;
    } else {
        startrow = parseInt(params.start);
        delete params.start;
    }
    if (!params.limit) {
        maxrows = 20;
    } else {
        maxrows = parseInt(params.limit);
        delete params.limit;
    }

    // set max requested data to 500 rows
    if (maxrows &amp;gt; 500) {
        maxrows = 500;
    }

    if (params.orderby) {
        orderby = params.orderby;
        delete params.orderby;
    }

    if (entityDetails.type == 'table' || entityDetails.type == 'view') {

        if (params.filter) {

            var filter = JSON.parse(params.filter);
            if (params.sort) {
                var sorters = JSON.parse(params.sort);
            } else {
                var sorters = null;
            }

            db.advancedQuery(entityName, filter, sorters, startrow, maxrows, function (err, data) {
                res.json(data);
            });

        } else { // no filter, use "simple" mode which accepts colName=value params for searching

            if (!orderby) {
                res.status(500);
                res.render('error', {
                    message: 'You did not transmit a orderby property.',
                    error: '500-"Simple Mode" requires you to specify an &amp;lt;b&amp;gt;orderby&amp;lt;/b&amp;gt; property'
                });
            } else {
                db.query(entityName, params, orderby, startrow, maxrows, function (err, data) {
                    res.json(data);
                })
            }

        }
    } else {
        // future support for stored procedures
    }

});

module.exports = router;

Creating an Abstraction Layer for Querying Microsoft SQL Server

The next step in building a one-size-fits-all dynamic API was to create a database abstraction class that would simplify using the Node.JS mssql package. This class performs the following tasks:

  • Connects to the SQL Server database using the credentials stored in the .config file
  • Handle stored procedure calls, used in the auto-documentation routine
  • Dynamically generates a SQL prepared statement based on the view/table name, filters, sort, start, and limit input parameters
  • Dynamically generates a second query to get a count of all available records, given the filter conditions specified
  • Executes a multiple SQL Server prepared statements as a single db transaction

 

Abstracting out data requests into a separate class file also provides us with the important benefit of being able to hot-swap database connectivity if our clients need us to support other DBMS like ORACLE or MySQL.

var sql = require('mssql');
var config = require('config');
var dbConfig = config.get('database');

// create connection pool
var dbConn = new sql.Connection(dbConfig);
dbConn.connect(function (err) {
    if (err) {
        console.log('Error connecting to db', err);
    }
});

/**
 * Execute a prepared statement
 * @param ps - the prepared statement
 * @param sqlCmd - the sql command
 * @param values - object - bound values
 * @param callback - callback function
 */

executePreparedStatement = function (ps, sqlCmd, values, callback) {

    ps.prepare(sqlCmd, function (err) {
        if (err) {
            console.log('prepare stmt error', err);
        } else {
            ps.execute(values, function (err, data) {
                if (err) {
                    console.log('sql execution error', err);
                } else {
                    if (callback) {
                        callback(err, data);
                    }
                    ps.unprepare(function (err) {
                        if (err) {
                            console.log('ps unprepare error', err);
                        }
                    });
                }
            });
        }
    });

}

/**
 * Execute a stored procedure
 *
 * @param procedureName
 * @param values - array of name/value pairs to send as arguments to the stored proc
 * @param callback - callback function to receive results from run
 *
*/

executeStoredProcedure = function (procedureName, values, callback) {

    var request = new sql.Request(dbConn);
    for (var colName in values) {
        var value = values[colName];
        switch (typeof value) {
            case 'string':
                request.input(colName, sql.VarChar(), value);
                break;
            case 'number' :
                request.input(colName, sql.Int, value);
                break;
        }
    }
    request.execute(procedureName, function (err, recordsets, returnValue) {
        callback(err, recordsets, returnValue);
    });

}

/**
 * Returns javascript object containing paginated results of query, total # of matching records
 *
 * @param tablename
 * @param values
 * @param orderby
 * @param startrow
 * @param maxrows
 * @param callback
 */

query = function (tablename, values, orderby, startrow, maxrows, callback) {

    var ps = new sql.PreparedStatement(dbConn);
    var filterSql = "";
    var tableName = tablename.split(' ')[0];

    // paginate data
    var sqlCmd = "".concat(
        "select * from ",
        "( select ROW_NUMBER() OVER ( ORDER BY " + orderby.split(' ')[0] + ") as RowNum, *",
        " from " + tableName,
        " where 0=0 "
    );

    for (var colName in values) {
        var value = values[colName];
        if (parseInt(value) == value) {
            value = parseInt(value);
        }
        switch (typeof value) {
            case 'string':
                ps.input(colName, sql.VarChar());
                values[colName] += '%';
                filterSql += ' and ' + colName + ' LIKE @' + colName;
                break;
            case 'number' :
                ps.input(colName, sql.Int);
                filterSql += ' and ' + colName + ' = @' + colName;
                break;
        }
    }
    sqlCmd += filterSql + " ) AS RowConstrainedResult "
    sqlCmd += " WHERE RowNum &gt;= @startrow and RowNum &lt; @endrow";
    sqlCmd += " ORDER by RowNum; ";

    sqlCmd+= ''.concat(
        "select count(*) as thecount ",
        "from " + tableName + " ",
        "where 0=0 " + filterSql
    );

    ps.input('startrow', sql.Int);
    ps.input('endrow', sql.Int);
    values.startrow = startrow + 1;
    values.endrow = values.startrow + maxrows;

    ps.multiple = true; // enable multiple recordsets to be returned

    executePreparedStatement(ps, sqlCmd, values, function(err,results) {

        callback(err, {
            data : results[0],
            success: true,
            total: results[1][0].thecount
        })
    });

}

/**
 * Create a dynamic query against a table or view
 * @param tablename
 * @param filter - array of objects. Each object has the keys property/value/operator
 * @param orderby
 * @param startrow
 * @param maxrows
 * @param callback
 */

advancedQuery = function (tablename, filters, sorters, startrow, maxrows, callback) {

    var ps = new sql.PreparedStatement(dbConn);
    var thisFilter = {};
    var values = {}, colName = '';
    var op = "";
    var isNumber = false;
    var filterSql = "";
    var tableName = tablename.split(' ')[0]; // no sql injection

    // paginate data
    var sqlCmd = "select * from ";
    sqlCmd += "( select ROW_NUMBER() OVER ( ORDER BY " ;

    // handle sorters array
    // sorters are encoded as an array of javascript objects
    // [{property: 'lastname', direction: 'ASC'}, {property: 'firstname', direction: 'ASC'}]

    for (var i=0; i&lt;sorters.length; i++) { if (i &gt; 0) {
            sqlCmd += ", ";
        }
        sqlCmd += sorters[i].property.split(' ')[0] + " ";
        if (!sorters[i].direction) {
            sqlCmd += " ASC ";
        } else {
            sorters[i].direction = sorters[i].direction.toUpperCase();
            if (sorters[i].direction != 'ASC' &amp;&amp; sorters[i].direction != 'DESC') {
                sqlCmd += " ASC "
            } else {
                sqlCmd += " " + sorters[i].direction;
            }
        }
    }

    sqlCmd+= ") as RowNum, *";
    sqlCmd += " from " + tableName + " where 0=0 ";

    // append additional WHERE conditions
    for (var i = 0; i &lt; filters.length; i++) {
        thisFilter = filters[i];
        isNumber = (thisFilter.value.indexOf(' ') == -1 &amp;&amp; !isNaN(parseInt(thisFilter.value)));
        colName = thisFilter.property;
        switch (thisFilter.operator) {
            case 'lt' :
                op = ' &lt; '; break; case 'gt' : op = ' &gt; ';
                break;
            case 'eq' :
                op = ' = ';
                break;
            case 'le' :
                op = ' &lt;= '; break; case 'ge' : op = ' &gt;= ';
                break;
            default :
                if (isNumber) {
                    op = ' = ';
                } else {
                    op = ' like ';
                    thisFilter.value += '%'
                }
                break;
        }
        filterSql += ' and ' + colName + op + '@' + colName;
        if (isNumber) {
            ps.input(colName, sql.Int);
            values[thisFilter.property] = parseInt(thisFilter.value);
        } else {
            ps.input(colName, sql.VarChar());
            values[thisFilter.property] = thisFilter.value;
        }
    }

    sqlCmd += filterSql + " ) AS RowConstrainedResult "
    sqlCmd += " WHERE RowNum &gt;= @startrow and RowNum &lt; @endrow";
    sqlCmd += " ORDER by RowNum; ";

    // handle pagination
    ps.input('startrow', sql.Int);
    ps.input('endrow', sql.Int);
    values.startrow = startrow + 1;
    values.endrow = values.startrow + maxrows;

    // get count of all available records
    sqlCmd+= ''.concat(
        "select count(*) as thecount ",
        "from " + tableName + " ",
        "where 0=0 " + filterSql
    );

    // execute the prepared statement

    ps.multiple = true; // enable multiple recordsets to be returned

    executePreparedStatement(ps, sqlCmd, values, function(err,results) {

        callback(err, {
            data : results[0],
            success: true,
            total: results[1][0].thecount
        })

    });

}

module.exports = {
    advancedQuery: advancedQuery,
    query: query,
    executeStoredProcedure: executeStoredProcedure
};

<h3>Automatically Generating Developer Documentation</h3> Perhaps the most important feature of any API is the documentation that accompanies it. Without adequate docs, developers will struggle with using your system and generate help-desk requests that lead to added support expense. Using Microsoft SQL Server’s system stored procedures we were able to automatically generate developer docs for each entity that is available through our REST API. Our /docs route uses SQL Server’s sp_columns procedure to retrieve information about the columns that are available in the exposed table/view. It also makes a sample call to our API, sending both blocks of information to a Jade template for output:


var express = require('express');
var router = express.Router();
var config = require('config');
var db = require('./../database');

isValidEntity = function (entityName) {
    var entities = config.get('entities');
    if (entities[entityName]) {
        return true;
    } else {
        return false;
    }
}

// set tablename parameter and check authorization

router.param('entityName', function (req, res, next, id) {

    var isValid = true;

    if (!isValidEntity(req.params.entityName)) {
        isValid = false;
        var err = new Error('Not Found - Invalid Entity Name');
        res.status(404);
        res.render('error', {
            message: 'You did not transmit a valid entity name',
            error: '404-Not Found'
        });
    } else {
        next();
    }

});

//
// url: /docs/entityname
//
router.get('/:entityName', function (req, res, next) {

    var entityName = req.params.entityName;
    var entities = config.get('entities');

    if (entities[entityName].type == 'table' || entities[entityName].type=='view') {

        db.executeStoredProcedure('sp_columns', {table_name: entityName}, function (err, data) {

            var sampleCall = "/api/" + entityName + "?orderby=" + data[0][0].COLUMN_NAME + '&amp;start=0&amp;limit=1';

            // run a sample SELECT query, retrieving a single row
            db.query(entityName, {}, data[0][0].COLUMN_NAME, 0, 1, function (err, sampleData) {

                // send the query results and metadata to a Jade template for output.

                res.render('entitydocs', {
                    title: config.get('clientName') + ' API: ' + entityName,
                    sampleCall: sampleCall,
                    columns: data[0],
                    sampleOutput: JSON.stringify(sampleData, null, 4)
                });

            });

        });
    } else {
        // todo: expose stored procedures
    }
});

module.exports = router;

The Jade template gives developers a sample API call and displays sample results from running a query:

Jade API Output

Automatically Generating Unit Tests with POSTMAN

Postman is a free Google Chrome application that enables you to easily test your APIs. Postman saves its URL collection data into an unencrypted .JSON file. This means that you can use Node.js to not only dynamically create your RESTful APIs in minutes, but also use it to automatically generate unit tests and additional developer documentation!
Use POSTMAN to test your APIs

Let Fig Leaf Software Help you Quickly and Painlessly Fulfill the Promise of Open Government

How Many Developers Does It Take to Screw In a Lightbulb? Leveraging the Internet of Things!

The Internet of Things (IoT) refers to the increasing number of devices that are now connected to the Internet and can be programmed remotely (typically from your smartphone).  Everything from from Oral-B toothbrushes to Samsung Washer-Dryers, Nest Thermostats, and Philips Light Bulbs are now connected and have their own programming interfaces. Additional products are coming online that enable you to control virtually any electronic device remotely.

This technology revolution opens the door for software developers to produce some truly innovative and immersive experiences. Easy to use API’s and libraries now exist, such as Cylon.js and the forthcoming “Thunder” IoT platform from Salesforce.com that make it quite simple to control devices in the physical world from the virtual one. Of course, all of this power also opens up a myriad of security concerns as well. While I certainly don’t want my blender getting hacked into and ruining my margarita,  I have a dream where I can install NEST thermostats in my sales team’s houses and turn up the heat (literally) when automatic reporting from Salesforce.com indicates that they’re not making their quotas. Because that’s how I roll.

Enlightening yourself about IoT with Philips Hue Lights

For our first IoT trick, we’re going to use several well-proven technologies to produce a simple app to control a Philips Hue lightbulb. I’ve been using Hue bulbs for a couple of years now and they’re fantastic. They use light-emitting diodes to produce energy-efficient light across the RGB palette. Each bulb contains a wi-fi radio that connects to a bridge.

2015-12-13_09-31-21

Philips Hue Starter Kit with Bridge and 3 Connected Bulbs


The Bridge has its own REST API that enables you to easily get a list of the bulbs that have been named/registered as well as send commands to set the color and brightness of each bulb.

Tools of the Trade

We used the following tools to produce our first IoT app. As a bonus, all of the aforementioned products are free and open-source. They also all use JavaScript as their programming language.

  • Node.JS application server
    Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient. Node.js’ package ecosystem, npm, is the largest ecosystem of open source libraries in the world.
  • Cylon.JS API for Controlling Devices
    Cylon.js is a JavaScript framework for robotics, physical computing, and the Internet of Things. It makes it incredibly easy to command robots and devices.
  • Sencha Ext JS (GPL License) for producing the front-end GUI.
    The most comprehensive JavaScript framework for building feature-rich cross-platform web applications targeting desktop, tablets, and smartphones. Ext JS leverages HTML5 features on modern browsers while maintaining compatibility and functionality for legacy browsers.

Using Node.JS and Cylon.JS – By your Command!

I’ve used Node.JS for a number of projects now and have always been impressed by its simple installation, fast performance, and ease-of-use. In particular, the Express framework for Node makes it ridiculously simple to produce a quick and dirty RESTful api like the one that we produced for this app.

Cylon.JS is an awesome API for connecting to more than 40 different hardware platforms — including Philips Hue bulbs and bridges.

iot2.png

Cylon.JS Supported Devices

To create our Node.JS project, we simply created a new folder (/IoT1/) on our filesystem, opened a command prompt, and issued the following statements which installs the Express framework for Node.JS, creates an express project, installs the Cylon API, and adds-in the cylon-hue library:

npm install express --save
express
npm install cylon --save
npm install cylon-hue --save

Next, we implemented a new route, named hue, by inserting code on lines 10 and 28 of the /IoT1/app.js file as illustrated by the following snippet. In effect, this programs our Node server to respond to HTTP requests to a url of http://serverip/hue


var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');
var hue = require('./routes/hue');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(__dirname + '/public/favicon.ico'));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', routes);
app.use('/users', users);
app.use('/hue', hue);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handlers

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
     message: err.message,
     error: err
    });
  });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500);
  res.render('error', {
  message: err.message,
  error: {}
 });
});

module.exports = app;

The Cylon.js library uses a high-level object named “Cylon” to control supported devices. A Cylon object has three major properties:

  • connections – Defines how to connect to devices and pass credentials
  • devices – Defines the types of devices that you want to control and their corresponding device drivers
  • work – a function that is automatically invoked after the device connection and authentication has occurred

Each device type has a series of methods that are enumerated in the Cylon.js documentation. Our application makes the following calls:

  • bridge.getFullState() – returns all information about the system including lights and color themes
  • bulb.toggle() – toggles the on/off state of the bulb
  • bulb.rgb() – Sets the color of the bulb as a red,green,blue 3-tuple
  • bulb.turnOn() – Turns the bulb on.
  • bulb.turnOff() – Turns the bulb off

Uisng this information, we created a node.js file named /IoT1/routes/hue.js to handle http GET/POST/PUT requests. In this particular case, we somewhat arbitrarily decided the following:

  • GET requests should return information about connected Hue bulbs from the Hue Bridge
  • PUT requests should turn on/off a selected bulb and change the bulb color to Fig Leaf’s green brand color
  • POST requests should activate the “Red Alert” mode, causing the light to blink like in Star Trek because, well… of course!
var express = require('express');
var router = express.Router();
var Cylon = require('cylon');

// see notes about host + username in the blog article
var host = '10.0.1.71';
var username = '215c4296227a3247159acafa1258e75b';

var redAlert = null; // red alert interval timer

/*
poll the bridge and return info about the lights
*/
router.get('/', function(req, res, next) {

 Cylon.robot({
  connections: {
  hue: {
   adaptor: 'hue',
   host: host,
   username: username
  }
 },

 devices: {
   bridge: {
    driver: 'hue-bridge'
   }
 },

 work: function(my) {
  
  my.bridge.getFullState(function(err, config) {
 
    if (err) {
     var result = {
      success: false,
      error: err
     }
    } else {
     var lights = [];
     for (var i in config.lights) {
       lights.push({
        'id': i,
        'state': config.lights[i].state.on,
        'reachable': config.lights[i].state.reachable,
        'name': config.lights[i].name
       });
     }
     var result = {
      success: true,
      lights: lights,
      config: config.config
     }
    }
    res.writeHead(200, {
     "Content-Type": "application/json"
    });
    res.write(JSON.stringify(result));
    res.end();
   });
  }
 }).start();

});

/*
* toggle bulb on/off
*/
router.put('/', function(req, res) {

var lightId = req.body.id;
var turnOn = (req.body.turnOn == 'true');

Cylon.robot({
 connections: {
  hue: {
   adaptor: 'hue',
   host: host,
   username: username
  }
 },

 devices: {
  bulb: {
  driver: 'hue-light',
  lightId: lightId
 }
},

 work: function(my) {
   if (turnOn) {
    my.bulb.turnOn();
    my.bulb.rgb(0, 102, 51); // fig leaf green
   } else {
    if (global.redAlert) {
     clearInterval(global.redAlert);
     global.redAlert = null;
    }
    my.bulb.turnOff();
   }

   var result = {
    success: true
   };
   res.writeHead(200, {
     "Content-Type": "application/json"
   });
   res.write(JSON.stringify(result));
   res.end();
   }
  }).start();
});

/*
* "Red Alert" mode - red blinking light
*/

router.post('/', function(req, res) {
  
  var lightId = req.body.id;
  var turnOn = (req.body.turnOn == 'true');

  Cylon.robot({
   connections: {
    hue: {
     adaptor: 'hue',
     host: host,
     username: username
    }
   },

   devices: {
    bulb: {
     driver: 'hue-light',
     lightId: lightId
    }
   },

   work: function(my) {
    
    if (!global.redAlert) {
      my.bulb.turnOn();
      my.bulb.rgb(255, 0, 0);
      global.redAlert = every((0.8).second(), function() {
       my.bulb.toggle();
      });
    }

    var result = { success: true };

    res.writeHead(200, {
      "Content-Type" : "application/json"
    });
    res.write(JSON.stringify(result));
    res.end();
   }
  }).start();
});

module.exports = router;

Building out the Web Client with Sencha Ext JS

On the client-side, we used Ext JS to produce a simple GUI illustrated in the figure below. Left-clicking on the bulb toggles its on/off state. Right-clicking on the bulb puts it into “Red Alert” mode.

Bulb Off Ext JS Bulb Activation UI

The View, which outputs the image of the bulb and the combo box light selector is fairly straightforward:


Ext.define('MyApp.view.Lightbulb', {
    extend: 'Ext.window.Window',
    alias: 'widget.Lightbulb',

    requires: [
        'MyApp.view.LightbulbViewModel',
        'MyApp.view.LightbulbViewController',
        'Ext.Img',
        'Ext.form.field.ComboBox'
    ],

    controller: 'lightbulb',
    viewModel: {
        type: 'lightbulb'
    },
    constrain: true,
    height: 358,
    width: 425,
    bodyPadding: 10,
    title: 'Fig Leaf Software IoT Demo 1',

    layout: {
        type: 'vbox',
        align: 'center',
        pack: 'center'
    },
    items: [
        {
            xtype: 'image',
            reference: 'lightbulbImg',
            disabled: true,
            height: 256,
            width: 256,
            bind: {
                src: '{lightbulbUrl}'
            }
        },
        {
            xtype: 'combobox',
            reference: 'lightscombo',
            width: 200,
            fieldLabel: '',
            displayField: 'name',
            forceSelection: true,
            valueField: 'id',
            bind: {
                store: '{Lights}'
            },
            listeners: {
                change: 'onComboboxChange'
            }
        }
    ],
    listeners: {
        afterrender: 'onWindowAfterRender'
    }

});

Ext JS 5 supports the concept of viewmodels that link data structures to a view. Our viewmodel, as illustrated by the following code snippet, defines lightStatus and lightId properties that indicate the on/off state and the id of the lamp that’s being controlled, respectively.

There’s also a data store (array of records) that makes a GET request to our /hue webservice in Node.JS in order to pull the relevant information about connected bulbs. The bulb listing is bound to the combo box in the view.

The lightbulbUrl formula is used in conjunction with data binding in the view to swap-in the appropriate image on mouseclick.


Ext.define('MyApp.view.LightbulbViewModel', {
    extend: 'Ext.app.ViewModel',
    alias: 'viewmodel.lightbulb',

    requires: [
        'Ext.data.Store',
        'Ext.data.proxy.Ajax',
        'Ext.data.reader.Json',
        'Ext.util.Sorter',
        'Ext.util.Filter',
        'Ext.app.bind.Formula'
    ],

    data: {
        lightStatus: false,
        lightId: 0
    },

    stores: {
        Lights: {
            autoLoad: false,
            model: 'MyApp.model.Light',
            proxy: {
                type: 'ajax',
                url: '/hue',
                reader: {
                    type: 'json',
                    rootProperty: 'lights'
                }
            },
            sorters: {
                property: 'name'
            },
            filters: {
                property: 'reachable',
                value: true
            }
        }
    },
    formulas: {
        lightbulbUrl: function(get) {
            if (get('lightStatus')) {
                return 'resources/images/lightbulb_on.png';
            } else {
                return 'resources/images/lightbulb.png';
            }
        }
    }

});

The Ext JS ViewController handles all the events thrown from the view, performing the following functions:

  • On lightbulb click, invoke our Node.JS service, calling its PUT method
  • On lightbulb right-click, invoke our Node.JS service, calling its POST method
  • On combo box selection, set the appropriate values in the viewmodel and display the appropriate bulb image
  • On startup, attach the left-click and right-click event handlers

Ext.define('MyApp.view.LightbulbViewController', {
    extend: 'Ext.app.ViewController',
    alias: 'controller.lightbulb',

    onLightbulbClick: function() {
                var vm = this.getViewModel();
                var lightId = vm.get('lightId');
                if (lightId == 0) {
                    Ext.Msg.alert("Error","You must select a bulb");
                    return;
                }
                var status = vm.get('lightStatus');
                vm.set('lightStatus',!status );
                this.lookupReference('lightscombo').getSelection().set('state', !status );

                Ext.Ajax.request({
                    url: '/hue',
                    method: 'PUT',
                    params: {
                        id: vm.get('lightId'),
                        turnOn: !status
                    },
                    success:function(response) {
                        console.log(response);
                    }
                });
    },

    onLightbulbContextMenuClick: function(e) {
        e.preventDefault();
        var vm = this.getViewModel();
        var lightId = vm.get('lightId');

        if (lightId == 0) {
            Ext.Msg.alert("Error","You must select a bulb");
            return;
        }
        vm.set('lightStatus',true);
        Ext.Ajax.request({
            url: '/hue',
            method: 'POST',
            params: {
                id: vm.get('lightId')
            },
            success:function(response) {
                console.log(response);
            }
        });

    },

    onComboboxChange: function(field, newValue, oldValue, eOpts) {
        var vm = this.getViewModel();
        var selection = field.getSelection();
        vm.set('lightId',selection.get('id'));
        vm.set('lightStatus', selection.get('state'));

    },

    onWindowAfterRender: function(component, eOpts) {
        var l = this.lookupReference('lightbulbImg');
        l.getEl().on('click', this.onLightbulbClick, this);
        l.getEl().on('contextmenu', this.onLightbulbContextMenuClick, this);
    }

});

Where do we go from here?

For my next trick, I’m going to move some bulbs down the basement and put my node.js script on a timer so that they all go into “red alert” mode precisely at 8:30 pm. If that doesn’t get my two boys upstairs for bedtime then nothing will.

You can download the complete sourcecode from github by clicking here.

Check out our Node.JS training, Sencha Ext JS training,  and consulting services!

I hope that you found this article to be illuminating. Have a great holiday, everyone!

Update your Skillz with New Node.JS Courseware!

Learn about Node.JS fast!!I’m proud to announce that I just finished authoring our new 1-day Node.JS Fundamentals course – weighing in at 110 pages with 9 hands-on exercises! This class will get you up-to-speed quickly on creating Node apps using the Express framework, dynamically constructing html output with Jade templates, and interacting with popular databases (MySQL, MongoDB, and CouchDB). It also covers socket I/O, File system access, and other exciting topics!

Check out the course outline and register today at:
http://training.figleaf.com/courses/nodejs100.cfm

For information about licensing this course or for a sample to evaluate, please contact me directly at sdrucker@figleaf.com!

 

Node.JS 101: Querying a Database and Outputting a Dynamic HTML Table with Express and Jade

Last night I gave a presentation about “What’s New in ColdFusion 11” to our ColdFusion meetup in DC. What’s new, of course, is that Adobe has put a lot of effort into making ColdFusion a lot more like JavaScript by significantly enhancing CFScript- an alternative to the ColdFusion Markup Language (CFML) that has syntax similar to JavaScript.

Of course, CFScript isn’t JavaScript. It’s not even similar to Microsoft’s jScript (shudder). And switching between CFScript programming and JavaScript programming is a little like switching between playing ping pong and playing tennis. They’re kinda similar, but playing a lot of ping pong is likely to screw up your tennis game, and vice-versa.

The fact of the matter is that if a CF developer really wants to program in Javascript, then they should just learn to stop worrying and take a close look at Node.JS. Node.js is a platform built on Chrome’s JavaScript runtime. It’s ridiculously fast and pretty straightforward to learn – assuming that you’re already familiar with basic JavaScript concepts.

For example, let’s take a look at a bit of Node code that queries a MySQL database table and subsequently transmits the results to a JADE template:


// create the connection 
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: ''
});

var sql = 'SELECT firstName,lastName from NodeJSExamples.Person';
connection.connect();

connection.query(sql, function(err, rows, fields) {
  if (err) throw err;
  res.render('users', { title: 'Users', rows: rows });
});

connection.end();

Pretty straightforward, right?

Now let’s take a look at the code that dumps out the rows into an HTML table via the Jade templating engine:

extends layout

block content
  h1= title
  p #{title} Listing

  table
    thead
      tr
        th First Name
        th Last Name
    tbody
      - each item in rows
        tr
          td= item.firstName
          td= item.lastName

Is this syntax superior (and even less verbose) than CFScript? You be the judge. And if you find yourself intrigued by the possibilities of Node.js, check out our new, no-nonsense, one-day instructor led course – Node.JS Fundamentals!