Using jQuery Datatables with NodeJS and MongoDb using mongoose-datatables

Web Development

So what exactly is this long name all about?  Well let’s just dive right into it. jQuery Datatable is an awesome tool to use as a table for your website.  It offers some nice features right off the bat such as sorting, and searching mechanisms.  So using it can save you a lot of development time. I realized rather quickly though when implementing this tool in my website that using the jQuery datatable with Node and MongoDb was a little difficult, mainly because of the lack of documentation.  That’s why I decided to write a blog post about how to create a datatable with NodeJS as the server, and MongoDb as the back end database.  Keep in mind, there are a lot of details that I will not go into that have to do with NodeJS.  So if you’re unfamiliar with Node you may want to look into it first.   So here we go! So we’re going to have four files.

  • index.html – The web page itself
  • mongo.js – This will handle all our database manipulations using mongoose
  • node.js – This will be where we write our Node server
  • main.js – This is where we handle all the client side javascript.

The example that I have here is using a document that I created entitled reservations with four fields.  The Json looks like this.

reservation : {
   name : String,
   startDate : String,
   endDate : String,
   confirmationCode : String
}

The first thing you need to do is create a table with the columns already specified.  This is an example of what you can do.  Keep in mind though that this table will not be too pretty, I’m not showing any fancy design with this.

index.html

<!--We need these two js files included. You need jquery 1.7.2 because for some reason the newer versions have issues with datatables. The second will be the js file for the datatable itself.-->
<script type="text/javascript" src="path/to/1.7.2.jquery.min.js"></script>
<script type="text/javascript" src="path/to/jquery.dataTables.js"></script>
<table id="example" cellpadding="0" cellspacing="0" border="0" >
<thead>
  <tr>
    <th>Name</th>
    <th>Start Date</th>
    <th>End Date</th>
    <th>Confirmation #</th>
  </tr>
 </thead>
 <tbody>
   <tr></tr>
 </tbody>
</table>

So this is just a simple table, with column names:

  • Name
  • Start Date
  • End Date
  • Confirmation #

Now here’s what you need in your Node Javascript file.  I always name mine node.js.

node.js

function start ()
 {
   var http = require("http");
   var fs = require("fs");
   var express = require('express');
   var app = express();
   var mongo = require("./mongo"); 
   app.configure(function () {
     app.use(express.bodyParser());
     app.use(app.router); app.all('/', function(req, res, next) {
       res.header("Access-Control-Allow-Origin", "*");
       res.header("Access-Control-Allow-Headers", "Content-Type");
       next();
     });
  }); 

  app.get('/mongo/get/datatable', mongo.getDataForDataTable) //Here's the line that we're looking at specifically
  app.use(express.static(__dirname));
  app.listen(process.env.PORT || 8080);
}; 

exports.start = start;

Here’s the line that we really need to look at.

app.get('/mongo/get/datatable', mongo.getDataForDataTable)

So here is our GET request. What this line does, if you’re not really familiar with Node, is it will route a GET request to the method getDataForDataTable in my mongo.js file. Now lets move onto our mongo.js file.

mongo.js

First off, you want these variables to be global.

var mongoose = require('mongoose');
var DataTable = require('mongoose-datatable');
var MyModel;

Configuring is optional, but if you do, make sure you configure the datatable and initialize it on start up.

DataTable.configure({ verbose: false, debug : false });
mongoose.plugin(DataTable.init); var dbURI = 'mongodb://localhost/test';
mongoose.connect(dbURI);

Once again, I set these variables you see below as global, but they don’t have to be, just make sure they can be reached in the db.once method, which we will be writing below.

//Global
var db = mongoose.connection;
var reservations;
var dates; var reservationsSchema;
var dateSchema;

db.on('error', console.error.bind(console, 'connection error:'));
db.once('open', function callback ()
{
  console.log('Connection has succesfully opened');
  var Schema = mongoose.Schema;
  reservationsSchema = new Schema(
  {
    reservation :
    {
       name : String,
       startDate : String,
       endDate : String,
       confirmationCode : String
    }
 });
 reservations = mongoose.model('reservations', reservationsSchema);
 MyModel = require('mongoose').model('reservations');
});

So here what we’re doing is creating the Schema (reservationsSchema) which is necessary in order to create the model (MyModel) object. The following line retrieves the model.

reservations = mongoose.model('reservations', reservationsSchema);

And in the line below we set the model MyModel, which will be using later.

MyModel = require('mongoose').model('reservations');

So so far we’ve:

  1. Created a small table with the columns specified.
  2. Wrote a router which will route our get request to the proper method in the mongo.js file.
  3. Created our model and schema which both are necessary in order to retrieve the documents from the Mongo database.

Now here is our code to get the data from the Mongo database.

exports.getDataForDataTable = function getData (request, response) {
  //"type.typeName" : "Trolley"
  //console.log("Get Request for Data Table made with data: ", request.query);
  MyModel.dataTable(request.query, function (err, data) {
    response.send(data);
  });
};

So what did I just do? We

  1. Called the dataTable method on the MyModel object, and sent request.query (JSON) as the query for what data we want to receive.  For now, we aren’t going to mess with that query at all, so that will be the default setting.
  2. Returned the data object which contains our documents that we just searched for.

And here’s the final part of our code.

$('#reservationTable').dataTable({
  "bProcessing" : true,
  "bServerSide" : true,
  "sAjaxSource" : '/mongo/get/datatable',
  "aoColumns" : [
    { "mData" : "reservation.name" },
    { "mData" : "reservation.startDate" },
    { "mData" : "reservation.endDate" },
    { "mData" : "reservation.confirmationCode" }
  ]
});

So here’s what we got going on.

  1. bProcessing” : “true” – Enable or disable the display of a ‘processing’ indicator when the table is being processed (e.g. a sort). This is particularly useful for tables with large amounts of data where it can take a noticeable amount of time to sort the entries.
  2. bServerSide” : “true” – Configure DataTables to use server-side processing. Note that the sAjaxSource parameter must also be given in order to give DataTables a source to obtain the required data for each draw.
  3. sAjaxSource” – This is the address of our GET request.
  4. aoColumns” – Used to give DataTables specific instructions for each individual column.
  5. mData” – This property can be used to read data from any JSON data source property, including deeply nested objects / properties.

So basically what I’m doing here is setting the table up to show when it’s processing data, also telling it that we’re going to be doing server side processing, and that the GET request address is /mongo/get/datatable.  Then I’m saying that the column data is going to be taken from the response to the GET request, and we want from the returned JSON, reservation.name, reservation.startDate, reservation.endDate and reservation.confirmationCode. And that’s it, if you run the website now on the server you’ve created, you will see that it’s populated with data.

3 thoughts on “Using jQuery Datatables with NodeJS and MongoDb using mongoose-datatables

  1. Hi Adeiji,

    This is exactly what i was looking for, i am trying to use node, mongoose, mongoose text search and datatables.

    i was able to use mongoose to do text search of the data store in mongo and i want to show the data in datatable on UI, that’s the piece i am missing and i am seeking your help.

    can you help me.
    Here is what i have.

    server.js
    ——————
    var express = require(‘express’), app = express(), port = 2013;
    var mongo = require(“./mongo”);
    var mongoose = require(‘mongoose’);
    var DataTable = require(‘mongoose-datatable’);
    var textSearch = require(‘mongoose-text-search’);

    var lang = require(‘./modules/lang’)(process.argv[2]);

    var pages = require(‘./modules/i18n/’+ lang).pages;
    pages.common.lang = lang;

    var config = require(‘./modules/config’)(app, express);
    DataTable.configure({verbose :false, debug: false});
    mongoose.plugin(DataTable.init);
    mongoose.connect(‘mongodb://localhost/lexicon’);

    var models = {};
    models.term = require(‘./models/term’)(mongoose,textSearch);

    require(‘./controllers/main’)(app, models, pages);
    app.get(‘/mongo/get/datatableongo.getDataForDataTable’)
    app.listen(port);

    console.log(‘[LOG] Server started on port ‘+ port);

    —————–

    Controller/main.js
    ——————–
    module.exports = function(app, models, pages) {
    var limit = 100;
    var options = {
    project: ‘-created’ // do not include the `created` property
    , limit: 100
    }
    var inspect = require(‘util’).inspect;
    app.get(‘/’, function(req, res) {
    // Récupération des visites
    models.term.find().limit(limit).exec(function(err, data) {
    if(err) { throw err; }

    res.render(‘index’, {
    common: pages.common,
    home: pages.home,
    term: data
    });

    });
    })

    app.get(‘/search’, function(req, res) {
    //console.log(inspect(output, { depth: null }));
    console.log(“in the search”+req.query._search);
    models.term.textSearch(req.query._search, options, function(err, data) {
    if(err) { throw err; }
    //console.log(inspect(data,{depth : null}));
    res.render(‘search’, {
    common: pages.common,
    home: pages.home,
    term: data.results
    });
    });
    })

    .use(function(req, res, next) {
    res.render(‘404’, {
    common: pages.common,
    });
    });

    };
    ——————–

    model/term.js
    ——————
    module.exports = function(mongoose,textSearch) {
    var collection = ‘term’;
    var Schema = mongoose.Schema;
    var ObjectId = Schema.ObjectId;

    var schema = new Schema({
    id: ObjectId,
    type: String,
    code: String,
    name: String,
    main_term: String,
    term_level_1: String,
    term_level_2: String,
    term_level_3: String,
    term_level_4: String,
    term_level_5: String,
    term_level_6: String,
    term_level_7: String,
    term_level_8: String,
    term_level_9: String,
    concatenation: String,
    see: String,
    seealso: String,
    createdDate: Date
    });
    // give our schema text search capabilities
    schema.plugin(textSearch);

    // add a text index to the tags array
    schema.index({ concatenation: ‘text’ });

    return mongoose.model(collection, schema);
    };

    —————–

    view/index.html
    ——————–

    <input type="text" id="_search" name="_search" value="” size=”5000″>
    Search Type
    Contains
    Exact Match
    Both

    0) { %>

    ——————–

    view/search.html
    ——————–

    <input type="text" id="_search" name="_search" value="” size=”5000″>
    Search Type
    Contains
    Exact Match
    Both

    0) { %>

    ——————–

    i can see the data returned from mongo using search but i just need to plugin my data into datatable.

    Thanks for your help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s