Skip to content


Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: a7b34d3c9c
Fetching contributors…

Cannot retrieve contributors at this time

207 lines (143 sloc) 8.162 kb


Node.js implementation of a server-side processor for the JQuery Datatable plug-in.

You should be careful about relying on this implementation until it has been more thoroughly reviewed, and be aware that the API may change as a result of such review. I will bump the version to 0.1.0 when I have reason to believe the API is stable. That said, please help review this implementation!

The node-datatable module provides backend SQL query generation and result parsing to support datatable server-side processing for SQL databases. This module does not connect nor query a database, instead leaving this task to the calling application. SQL querying has been separated so that the caller can leverage his or her existing module choices for connection pools, database interfaces, and the like. This module has been used with both node-mysql and sequelize.

An incomplete code example:

var QueryBuilder = require('datatable');

var tableDefinition = {
    sTableName: "Orgs",
    aoColumnDefs: [
        { mData: "o", bSearchable: true },
        { mData: "cn", bSearchable: true },
        { mData: "support" }

var queryBuilder = new QueryBuilder( tableDefinition );

// requestQuery is normally provided by the datatable ajax call
var requestQuery = {
    iDisplayStart: 0,
    iDisplayLength: 5

// Build an array of SQL query statements
var queries = queryBuilder.buildQuery( requestQuery );

// Connect with and query the database.
// If you have turned on multipleStatements (e.g. node-mysql) then you may join the queries into one string.
// multipleStatements is normally turned off by default to prevent SQL injections.

var myDbObject = ...
myDbObject.query( queries, function( err, resultArray ) {
    var result = queryBuilder.parseResponse(resultArray);


The source code contains additional comments that will help you understand this module.


Construct a QueryBuilder object.


The node-datatable constructor takes an object parameter that has the following options. In the simplest case only the first two options will be necessary.

  • sTableName - The name of the table in the database where a JOIN is not used. If JOIN is used then set sSelectSql.

  • aoColumnDefs - An array of objects each containing mData and bSearchable properties. The default value for bSearchable is false.

  • sCountColumnName For simple queries this is the name of the column on which to do a SQL COUNT(). Defaults to id. For more complex queries, meaning when sSelectSql is set, * will be used.

  • sDatabase - If set then will add a SQL USE sDatabase statement as the first SQL query string to be returned by buildQuery.

  • aSearchColumns - In database queries where JOIN is used, you may wish to specify an alternate array of column names that the search string will be applied against. Example:

aSearchColumns: [ "table3.username", "table1.timestamp", "table1.urlType", "table1.mimeType", "table1.url", "table2.description" ],
  • sSelectSql - If set then this defines the columns that should be selected, otherwise * is used. This can be used in combination with joins (see sFromSql).

  • sFromSql - If set then this is used as the FROM section for the SELECT statement. If not set then sTableName is used. Use this for more complex queries, for example when using JOIN. Example when using a double JOIN:

"table1 LEFT JOIN table2 ON table1.errorId=table2.errorId LEFT JOIN table3 ON table1.sessionId=table3.sessionId"
  • sWhereAndSql - Use this to specify an arbitrary custom SQL that you wish to AND with the generated WHERE clauses.

  • sDateColumnName - If this property and one of dateFrom or dateTo is set, a date range WHERE clause will be added to the SQL query. This should be set to the name of the datetime column that is to be used in the clause.

  • dateFrom - If set then the query will filter for records greater then or equal to this date.

  • dateTo - If set then the query will filter for records less then or equal to this date.

  • fnRowFormatter - A row formatter function (more documentation below).


The query builder object.


var queryBuilder = new QueryBuilder( {
    sTableName: 'user',
    aoColumnDefs: [
        { mData: 'username', bSearchable: true },
        { mData: 'email', bSearchable: true }


Builds an array containing between two and four SQL statements, in the following order:

  1. (Optional, if sDatabase is set) A USE statement that specifies which database to use.
  2. (Optional, if requestQuery.sSearch is set) A SELECT statement that counts the number of filtered entries. This is used to calculate the iTotalDisplayRecords return value.
  3. A SELECT statement that counts the total number of unfiltered entries in the database. This is used to calculate the iTotalRecords return value.
  4. A SELECT statement that returns the actual filtered records from the database. This will use LIMIT to limit the number of entries returned.

Note that #2, #3 and #4 will include date filtering as well as any other filtering specified in sWhereAndSql.



The resultant array of query strings. The queries should be executed in order, and the result objects collected into an equivalently ordered array that is later passed to the parseReponse function.


var queries = queryBuilder.buildQuery( oRequestQuery );


Parses an array of response objects that were received in response to each of the queries generated by the buildQuery function. The order of responses must correspond with the query order.


  • queryResult: The ordered array of query response objects.


An object containing the properties defined in Reply from the server.


var result = queryBuilder.parseResponse( queryResponseArray );

Database queries involving JOIN

Example using sSelectSql and sFromSql to create a JOIN query.

  sSelectSql: "table3.username,table1.timestamp,urlType,mimeType,table1.table3Id,url,table2.code,table2.description",
  sFromSql: "table1 LEFT JOIN table2 ON table1.errorId=table2.errorId LEFT JOIN table3 ON table1.sessionId=table3.sessionId",

The response of a more complex database queries can result in more columns of data then is displayed in the the browser table. The example below shows how six columns in a row of database response data are reduced to four columns. In this example, the row.urlType and row.mimeType are reduced into one column, as are the row.url, row.code and row.description.

fnRowFormatter: function( row, column ) {
    var result = [ row.username, dateutil.dateToSortableString(row.timestamp) ];
    result.push( urlTypeMap(row.urlType,row.mimeType) );
    var url = row.url ? decodeURI(row.url).replace( /https?:\/\//i, '') : "";
    if( row.code )
        url += "</br>" + row.code + ": " + row.description;
    result.push( url );
    return result;


  1. Add an additional parameter to allow more then the requested number of records to be returned. This can be used to reduce the number of client-server calls (I think).
  2. A more thorough SQL injection security review (volunteers?).




Jump to Line
Something went wrong with that request. Please try again.