Displaying data collected from an external Database


#21

Displaying dynamic data from a NoSQL database using Node.js

Hello everyone,
in this tutorial, I will show you how you can use OpenHybrid to continuously display data you get from a MongoDB database. To achieve this, we will be hosting a webservice which gets data from the database through simple http requests, parses this data to a table and displays it, updating changing elements on-the-fly.
We will be using different modules and frameworks for Node.js which I will start by explaining.

Before you start though, note that I did not (and do not plan to) add any fancy CSS or anything to my html elements, so they kinda look old and uncool. I really didn’t focus on that though, so if you have any good stylings you can contact me and show me :smiley:

The 3 different frameworks

First of all, as you may have guessed, we will be using MongoDB as our background database. MongoDB is a document-type, NoSQL database. This means that data is not stored in classic tables and we will not be using SQL queries to get that data. But the cool thing about MongoDB is what? Well it saves the data as JSON objects, which we can use directly inside our JavaScript code. Isn’t that cool? Yeah you guessed it, it is :grinning:
If you want to know more about MongoDB, go here to their official website.

Next thing we will use is going to be the Express module for Node.js. Express is a web framework which will allow us to easily create a webpage to display our data, and further to define custom urls for GET, POST etc calls. Express makes it also possible to use dynamic routing, but we will not be using this for now. Maybe I will have implemented that in a further version of this tutorial.

Finally, we will be using AngularJS to combine our data with the rendering, using databinding to update our html elements without the need to refresh our page. AngularJS is a web framework developed by Google which uses the MVC (Model, View, Control) concept to manage and display data. This permits us to efficiently combine rendering data and making changes to said data. Later on, I will explain more in detail how this works.

Getting started with our project

Ok, so before we start off with the programming, you’ll have to install some stuff. If you have not yet installed node.js, then do it now. I’d recommend you follow this guide so that you get the right Node.js version to also be able to turn your PC into a hybrid object. All of the functionality in this tutorial has been tested using that node.js version so you’re safe to install it.

Ok, so you installed node.js and subsequently npm as well. The next step would be to install the packages and dependencies that you need. First, we will install AngularJS. You can install it locally or globally, that’s your choice. I’ve installed it globally though, but it really shouldnt make much of a difference. So to install it locallyor globally, type

npm install angular or npm install angular -g

in your terminal.
Now, create a folder where you want on your computer, inside which we will keep all of our files. This folder will be designated as root from now on. Now, get the package.json file which I have provided for you (@valentin how do I upload files here?) and copy it into your root folder.

If you want to create it yourself well here is the code for it:

{
  "name": "mean-openhybrid",
  "version": "1.0.0",
  "description": "Utility to create a dynamic website with MongoDB access and render a table with the database entries",
  "main": "testServer.js",
  "dependencies": {
    "express": "^4.13.4",
    "mongodb": "^2.1.16",
    "body-parser": "^1.15.0",
    "ejs": "^2.4.1"
  },
  "devDependencies": {},
  "author": "Alexander Peitz",
  "license": "ISC"
}

The package.json is basically a file where metadata concerning the node.js project is located (name, dependencies etc…). Now, navigate to your root folder using your favorite terminal and type in

npm install

This will install all required modules in a folder called node_modules. In detail, the installed modules are Express and the MongoDB Node.js library mongodb. Ok, now we have installed everything we need. Pretty neat, huh?

Starting with the webservice

Ok, so the first thing we will implement is going to be our webservice that will fetch our data from our database. So using your favorite IDE, or the windows textpad or whatever you want (I’m not going to judge you about that :stuck_out_tongue:), create a javascript (.js) file in your root directory. I’ve called it testServer.js, but it doesn’t matter what you name it.

Step 1: Initializing the service

Firstly, we will include the modules that we need. So start by typing the following lines of code

var express = require('express');
var MongoClient = require('mongodb').MongoClient;
var Db = require('mongodb').Db;
var Server = require('mongodb').Server;
var assert = require('assert');

Ok, now we will initialize the express app and define variables for connecting to our database and for our server:

var app = express();

var expressPort = 3000,
    MongoPort = 27017,
    MongoIP = 'your MongoDB IP here',
    dbname = 'your database name here',
    collectionName = 'Your collection name here';

app.use(express.static(__dirname));
app.set('view engine', 'html');

The first line here initializes express, then we set our variables. Our webservice will be running on port 3000, we use the standard port for MongoDB (27017), so if you changed this, put your MongoDB port here.

The last 2 lines set the view engine and the directory for the static fileserving. Later on, we will create a html document to serve as the webpage. This document will need to be in the same directory as indicated here, but I’ll remind you of that later :grin:

Finally, we have to open our server, making it available onto a port. At the end of your file, write

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

Now, you can open up a terminal, go to your root directory and hit node ‘yourServer.js’ (without the ‘’), open up your favorite browser and go to localhost:3000. It should open a blank page.

Step 2: Extracting the data


All of the following code has to be inserted before the app.listen() command!

Ok, so we got all the initializing done. Now we have to actually extract our data from our database. So, first of all, we will have to connect to our database. We do this with the following line of code:

var db = new Db(dbname, new Server(MongoIP, MongoPort), { safe: false });

Ok, so now we are connected to our database. What we need to do now is extract the data we want from it. We will implement a custom route that will respond with the data we want to have. Type the following lines of code in the file:

app.get('/getData', function (req, res) {
    db.open(function (err, db) {
        assert.equal(null, err);
        var cursor = db.collection(collectionName).find().limit(1).sort({ $natural: -1 });
        cursor.each(function (err, doc) {
            assert.equal(err, null);
            if (doc != null) {
                res.status(200).json(doc);
                return;
            }
        });
    });
});

Ok, so what does this code actually do? app.get(’/url’) defines a method that will be executed when we do a GET call to that given url. the req object contains our request parameters, the res object is used to send our response. For further information about these (for example if you want to use parameters or anything), please see the express documentation or various other tutorials on the internet.

After that, we open our database, select our collection that we want to search in, and here we search for the last element that got added. The limit(1) call limits our response to one object, the {$natural: -1} parameter indicates that we want the reverse adding order, so from last to first. We then iterate through our cursor, checking for errors and when we found our document with doc != null we respond with the 200 status (which means OK in http standard) and send our document back.

Creating the html page

Ok so I already mentioned above that we were going to create a html page that will get served up to the webserver. This page will be very simple, actually containing only 1 main element. Start off by creating a .html document in the folder you indicated before using the app.use(express.static(“yourfolder”)); function call. In my case, this was __dirname, meaning it is the root folder.

Call this html document index.html. Then, start by adding the following lines of code:

<!DOCTYPE html>

<html>
    <head>
        <meta charset="utf-8" />
        <title>TestApp</title>
    </head>
    <body ng-app="test" ng-controller="mainCtrl">
        <render-dynamic-table/>
        <script src="angular.min.js"></script>
        <script src="testScript.js"></script>
    </body>
</html>

Ok what are the important parts here? It starts with the ng-app command inside the body tag. This indicates that every element inside the body tag has to be handled by the AngularJS compiler. This part is called bootstrapping.

Furthermore, we have a ng-controller parameter inside our body tag. This is also an AngularJS parameter, or directive. This indicates that we are going to use the mainCtrl controller to connect our model and our view inside the body tag. The mainCtrl controller will fill our scope with the required variables, but more of that later.

Next, you see a strange kind of element called render-dynamic-table. This doesnt look like an HTML nor an AngularJS standard element. Well you’re right, it isn’t. That’s what’s called an AngularJS directive. Almost everything in AngularJS is a directive, for example the ng-repeat we will use later on is also a directive. AngularJS permits us to write our own directives, making it possible to template whole new custom elements, and that’s what we’re doing here. I’ll explain more of this later on.

Finally, we have 2 script calls at the end of our page. Placing your script tags at the end of the page is good practice, because it will not slow down the loading of your elements while the script is loading. The first tab calls the angularjs script that allows us to use all of the standard angular functionality. You can call it locally (download the file here) or you can use the google CDN (Content Delivery Network) to serve the script. You will have to use the following tag in your html instead:

<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.5/angular.min.js"></script>

Beware though that this will only work when you have an active internet connection.

The second script is a custom script we will write in the next step.

That’s it for the html, pretty easy huh?

Writing our script to render the table

Ok, now comes the fun part. Integrating the AngularJS logic into our service. There are 3 main parts to this:

  • Requesting data from the database

  • Formatting it so that we can dispaly our table

  • Creating an appropriate template for the view

We will again start by creating a .js file (I called it testScript :grin:) inside our root folder (or wherever you want really, just make sure to indicate the right path in the html file then). The first thing we need to do here is initialize our AngularJS application. This always has to be the first step when writing javascript code for angularJS. It completes the bootstrapping process. So insert the following lines of code at the very start of your script:

var app = angular.module('test', []);

Note that the first parameter you pass on is the same string you used in your html document calling the ng-app="" directive. The second argument is a list of parameters you can pass, see the angularJS docs for more info about them.
Ok, now we will start with the first main part.

Requesting the data

To get our data from MongoDB, we will use the GET Method we implemented beforehand in our webserver. This will be executed by the controller we already mentioned in our html page. I’ll start by giving you here all the code for the controller, and gradually explain what it does. So, add the following lines to your script:

app.controller('mainCtrl', function ($scope, $http, $interval) {
//Function used to render the table. It helps convert the ng-each loop into a for loop with defined limits and step
    $scope.range = function (min, max, step) {
        step = step || 1;
        var input = [];
        for (var i = min; i <= max; i += step) {
            input.push(i);
        }
        return input;
    };

    //Function to get the data from MongoDB
    $scope.getData = function () {
        //Make a http get request with a defined url (which is defined as an app.get in the server)
        $http({ url: "/getData", method: "GET", params: {} })
        .success(function (data, status, header, config) {
            //on success of said call, initialize scope variables
            $scope.titles = {};
            $scope.values = {};
            $scope.maxrownumber = 1;

            //Call the functions to prepare the table rendering

            parseJSONToTable($scope, data);
            adjustRowSpans($scope, data);
            adjustColSpans($scope, data);

            return;
        })
        .error(function (data, status, headers, config) {
            //on error, one could write an error in the scope for further use
            $scope.error = "error";
            return;
        });
    };
    //Get data from the database by polling every second
    $interval(function () {
        $scope.getData();
    }, 1000); 
});

Before explaining all this code, I’ll tell you what this ominous scope is. The $scope object is the object where you can store your data linking the view and the model. We will be using them when we generate our html template. We can not only store variables and objects inside the scope, we can add whole functions to it as well.

And that’s exactly what we are going to do soon. But before, we define the controller I talked about earlier. Thats what the line

app.controller();

does. The first argument is your controller’s name (make sure it’s the same as in the html) and after that you can pass a whole bunch of AngularJS variables. This is called injection, read about it in the AngularJS docs if you want to know more. The basic principle is that you can then use these variables inside your function. Here, we inject the scope, http and interval variables. The first function

$scope.range()

is used for the templating, we will get there later on. For now, we will just look at the getData function. It uses the AngularJS $http injected object to make a GET request to our webserver, using our custom url we implemented in the webserver. If you remember, this url will call the mongodb database and return the document as a JSON object. This object will, on a successful call, be saved to the data variable.
Furthermore, we create 3 scope variables: titles, values and maxrownumber . These are used later on for parsing our JSON to the table. The function calls after that are also for parsing the data.
If the call is unsuccessful, we can for example write an error to the scope. I won’t be using this error further though. A real engineer doesn’t create errors :wink:

And finally, we call this function every second using the AngularJS injected $interval variable to “continuously” extract our data.

Parsing the data to our table

First, I’ll describe the 3 scope variables we added in the getData() method. titles will store all the table headers we want to display. I called it titles because $scope.headers is already defined by AngularJS and overwriting it leads to errors.
$scope.values will contain all of the values we want to display, for example a temperature value, a name etc…
$scope.maxrownumber is a number indicating how many header table rows we need. This depends on how indented our JSON object is.

Ok, so we got the JSON object from our database. Just take the object properties and their values and write them to table headers and table data html objects right? Yeah, well that doesnt always work out, for example when a property is composed of different subproperties. So we’re gonna have to parse that data. For that, we have got 3 functions:

parseJSONToTable($scope, data);
adjustRowSpans($scope, data);
adjustColSpans($scope, data);

Filling the $scope variables

Start by parseJSONToTable(). First again, the code, then the explanation:

function parseJSONToTable($scope, data, numberOfRepeats) {
    numberOfRepeats = typeof numberOfRepeats !== 'undefined' ? numberOfRepeats : 1;
    for (var key in data) {
        var numberOfChildren = Object.size(data[key]);
        if (numberOfChildren >= 1) {
            $scope.titles[key] = { title: key, rownumber: numberOfRepeats };
            if ($scope.maxrownumber <= numberOfRepeats)
                $scope.maxrownumber++;
            numberOfRepeats++;
            parseJSONToTable($scope, data[key], numberOfRepeats);
            numberOfRepeats--;
        }
        else {
            $scope.titles[key] = { title: key, colspan: 1, rownumber: numberOfRepeats };
            $scope.values[key] = data[key];
        }
    }
    return;
}

Ok so the function actually gets 3 arguments passed as you can see (what, you lied to me? Yes I did!). The third argument actually varies, because this function will be called recursively and needs to know how many times it was called beforehand. When calling this function externally, just don’t pass any number, it will be initialized afterwards.

So we start by checking if numberOfRepeats is passed on or not. If it isn’t, we set it to 1 (first call!). This line makes numberOfRepeats act as a default parameter you maybe know from C or Java or wherever.

The we iterate through the properties of the data object. For each object, we check if it has subproperties or not. We use the

Object.size();

function that we will implement ourselves later on.

If subproperties are present, then obviously our considered property does not have a value. We do nonetheless write our property inside the $scope.titles element, because we still want to display it in our table as a header. It gets a title attribute to be displayed and the numberOfRepeats number as a row index. For every subproperty, we will need to generate a new table row and we need to know in which row to write the actual title.

We then increment our maximum row number variable, but only if it is smaller than the number of recursive calls. Then, increment our recursion counter numberOfRepeats and call the parseJSONToTable() function using our subproperty as data. Finally, in the event we step out of these recursive calls, we will decrement the recursion counter.

Ok so as long as we have subproperties, we will always call the same function recursively. But what happens when we don’t have any subproperties anymore? Well that means that we will have a property/value pair. We write that last property into our titles scope variable, passing on the name we want the title to have, the rownumber it will get displayed in and a columnspan of 1 (since it will always correspond to a value and there will need only 1 column). Then we write the data into our $scope.values object and exit the function call.

The Object.size() function

Ok so I’ll just give you a qucik explanation of the object.size function. I did not completely write it myself but basically took it from here and modified it. But here is the code:

Object.size = function (obj) {
    var size = 0, key;
    //Check if the object is a string, because otherwise it will return the number of letters of that string. Return 0 instead
    if (typeof obj == 'string' || obj instanceof String) return 0;
    for (key in obj) {
        if (obj.hasOwnProperty(key)) size++;
    }
    return size;
};

I don’t think this needs much explanation. What should be noted though is that if an object is a string, for some reason that function returns the number of letters inside that string. That’s why we check if it is a string and just return 0 if it is.

Adjusting the Rowspan of our table headers

Ok so now we got all our headers inside our $scope.titles property. To format the table correctly though, we have to know over how many rows our cells have to span. That’s what the function

adjustRowSpans()

figures out. By definition (or more precisely MY definition) every element will have a rowspan of 1, except the lowest element which will fill out the remaining space.

Again, this function will be called recursively and therefore takes 3 arguments, only 2 of which are going to be initialized when calling the function externally. Here is the code:

function adjustRowSpans($scope, data, numberOfRepeats)
{
    numberOfRepeats = typeof numberOfRepeats !== 'undefined' ? numberOfRepeats : 0;
    for (var key in data) {
        var numberOfChildren = Object.size(data[key]);
        if (numberOfChildren >= 1) {
            numberOfRepeats++;
            $scope.titles[key].rowspan = 1;
            adjustRowSpans($scope, data[key], numberOfRepeats);
        }
        else {
            $scope.titles[key].rowspan = $scope.maxrownumber - numberOfRepeats;
            continue;
        }
        numberOfRepeats--;
    }
}

Again, we iterate through our properties of the data and check for subproperties. So if we have subproperties, the header will not be the lowest one in our column so it gets a rowspan of 1. Then call the function recursively, just like we did with parseJSONToTable().

When we get to the point where no more subproperties are present, that header will be the lowest for the given parent property. This is where having previously counted our maximum row number comes in handy. To make it fill out the rest of the table, just give it a rowspan corresponding to the maximum rowspan minus the recursive depth we are at now. Then, call continue to take care of eventual other subproperties. Finally, we decrement numberOfRepeats again when quitting the function. That’s all for our rowspans of the table headers.

Adjusting the column spans

We will also need to adjust the column spans of our headers, because they will need to be wide enough to cover ALL of their child properties. For this, we use the

adjustColSpans();

function.

Here is the code you need:

function adjustColSpans($scope, data) {
    for (var key in data) {
        if (Object.size(data[key]) >= 1) {
            adjustColSpans($scope, data[key]);
            var totalColSpan = 0;
            for (var iterator in data[key]) {
                totalColSpan += $scope.titles[iterator].colspan;
            }
            $scope.titles[key].colspan = totalColSpan
        }
        else {
            $scope.titles[key].colspan = 1;
        }
    }
}

As you can see, we don’t even need the recursion counter here. We again check for eventual subproperties and directly go into the recursion. When we are at the point where no more subproperties are present, we set the columnspan of our header to 1 (since the header will be directly over a value). After this, the function call will exit and if it was a subproperty, we will be going back up one level and continue at

var totalColSpan = 0;

This variable will be used to sum up the columnspans of all of the subproperties from our considered property so the header soans over all of them. This summing up is done in a bottom-up fashion for every property until we are back at the top level.

And that’s “it” for parsing the data to the table! We can proceed with the last step now whoop whoop!

Creating the table template for the view

Okay so like I mentioned waaaay before, we will be creating our own directive to render the table. If you want to read more about directives, I suggest the official AngularJS documentation.

Ok so here again is the code for the directive:

app.directive('renderDynamicTable', function () {
    return {
        restrict: 'AE',
        replace: 'true',
        template: '<table border=1><tr ng-repeat="n in range(1, maxrownumber)"><th ng-if="n == obj.rownumber" ng-repeat="obj in titles" colspan="{{obj.colspan}}" rowspan="{{obj.rowspan}}"> {{ obj.title }} </th></tr> <tr><td ng-repeat="key in values">{{ key }}</td></tr></table>'
    };
});

This directive is composed of 3 parameters. The restrict: parameter indicates that it will can only be applied to certain types of elements. ‘AE’ stands for Attribute and Element names. This is actually the standard when you don’t use the restrict attribute explicitely, but I wanted to mention it here anyways.

Replace:true means that our html element where the directive is used will be replaced by the template and not only concatenated with it

And now for the template: property. This is where all the magic is happening! The template actually dynamically creates our html table element and always keeps it up to date with the scope.

As a base element, we are creating a table with a border of 1. Next, we use the built-in foreach loop ng-repeat to generate as many table rows for the headers as we need. For that, we use the $scope.range() function we defined earlier inside our controller. I’ll post the code here again but you should already have it inside your controller.

$scope.range = function (min, max, step) {
    step = step || 1;
    var input = [];
    for (var i = min; i <= max; i += step) {
        input.push(i);
    }
    return input;
};

This range function in combination with the ng-repear directive will basically create a for loop in angularJS that repeats between 2 fixed numbers being incremented by step. Here again, I didn’t write that myself but took it from here so check it out if you want to know more.

Then we will create our table headers row by row. That’s why we saved the rownumber in our scope. We iterate through the titles element of our scope and check if the rownumber is the same as the tr iteration number n. If it is, the header will be created and we set its text, columnspan and rowspan we calculated and saved earlier.

When finished with the headers, we will then create a new table row and just iterate through the $scope.values and create a td (tableData) element for every value.

Congratulations, you are now finished and can dynamically render a table based on a JSON object you got from a MongoDB Database! Whoop whoop!

Ok so this is cool and all, but how do I display it using the Reality Editor you’re going to ask. Well that’s really easy now.

Displaying the table in the Reality Editor

So first of all, create your Hybrid Object. Then, all you are going to do is edit the index.html file inside your object and add an iframe element. An iframe just dispalys another website! So you will link it to the IP and Port on which your webservice you created above is running on, and BAM you’re finished. It looks something like this:

<iframe id="myIFrame" src="http://<IP>:<ExpressPort>/" frameborder="0" />

That’s it, you’re done. To test your program, you can now use your terminal to navigate to your root folder, type in node <yourServerFile.js>, start up your hybrid object and point the Reality Editor at the target image. You will then see the changing data in a (ok, pretty ugly) HTML Table if your database is running and being updated!

Thanks for reading all of this!



@valentin just let me know what you think about this tutorial and what could be done better. I’m thinking of maybe adding a section of how you can create a mongodb database and update it so that you can actually see something while coding my stuff, and I want to upload some screenshots of how these Tables I’m talking about look.


#22

@Alex_Peitz This tutorial is really helpful. Thank you.

I do have one very naïve question though. I am very new to Angular. So this tutorial is only to display the last entry added to Mongodb? How can I display my entire mongo data in a tabular format?


#23

Hey @yatish0833,

I’m very glad my tutorial was helpful for you! You are right, my tutorial covers only how to display the latest entry from MongoDB. If you wanted to display your entire mongo data in tabular format, there are different ways I can think of:

  1. Always get the whole Dataset from your Database. You would then create your header structure with one of the elements (the first or last for example) and you would need to extract only the values then from every entry inside your parseJSONToTable() function. Your $scope.values would then have to be some sort of array or JSON containing your values such as they were before.
    Inside your template for the table you would then have to add a ng-repeat statement such that you have <tr ng-repeat="number in values"><td ng-repeat= "key in number">...</td></tr> so you have a double for loop iterating once over all the values and then as you are used to over the individual values.

  2. At first initialization, pull all the Data inside the Database. Then, pull the Database inside a setInterval function for te latest element, the interval depending on how much data you write to the DB. This would be my favored method I think, because it reduces unnecessary network traffic.

So you would have to at first realize something similar to 1., and checking for a first initialization of your $scope.values or maybe $scope.titles or whatever. Then you get all the data and save it just as you would have in 1. You need a new function for that in the Server.js getData() method. There, you would have once the one you have now which only gets the latest dataset from the Database, and one get Method which gets all current Data from the DB. You would use

db.collection(collectionName).find().sort({ $natural: -1 }); for that.

When you did the first table initialization with all the current Data, you would then have to just get your latest element, extract only the values like you did in the current parseJSONToTable and write them at the end of your array or JSON $scope.values. That variable and the template has to be formatted just like in the first method.

I hope I could help you here, consider that the lines of code written here are not tested :wink: But that’s the general Idea! Let me know if you have been succesful :smiley: