Server-side processing for JQuery Datatable plug-in
Node.js implementation of a server-side processor for the JQuery Datatable plug-in.
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: truemData: "cn" bSearchable: truemData: "support";var queryBuilder = tableDefinition ;// requestQuery is normally provided by the datatable ajax callvar requestQuery =iDisplayStart: 0iDisplayLength: 5;// Build an array of SQL query statementsvar queries = queryBuilderbuildQuery 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 =myDbObjectquery queriesvar result = queryBuilderparseResponseresultArray;resjsonresult;
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
aoColumnDefs- An array of objects each containing
bSearchableproperties. The default value for
sCountColumnNameFor 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
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- If set then this is used as the FROM section for the SELECT statement. If not set then
sTableNameis 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
dateTois 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 callback that takes the parameters ( row, aoColumnDefs, params ). Row is an object containing a row of returned data, columns is aoColumnDefs (see above), and params is the object set for oRowFormatterParams. There is more documentation below on this function.
oRowFormatterParams- Optional params passed to fnRowFormatter.
The query builder object.
var queryBuilder =sTableName: 'user'aoColumnDefs:mData: 'username' bSearchable: truemData: 'email' bSearchable: true;
Builds an array containing between two and four SQL statements, in the following order:
- (Optional, if
sDatabaseis set) A USE statement that specifies which database to use.
- (Optional, if
requestQuery.sSearchis set) A SELECT statement that counts the number of filtered entries. This is used to calculate the
- A SELECT statement that counts the total number of unfiltered entries in the database. This is used to calculate
- 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
requestQuery: An object containing the properties set by the client-side datatable library as defined in Parameters sent to the server.
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
var queries = queryBuilderbuildQuery 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 = queryBuilderparseResponse queryResponseArray ;resjsonresult;
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.mimeType are reduced into one column, as are the
var tzoffset = params ? paramstzOffset : undefined;var result = rowusername dateutiltoSortableStringrowtimestamptzoffset ;resultpush urlTypeMaprowurlTyperowmimeType ;var url = rowurl ? decodeURIrowurlreplace /https?:\/\//i '' : "";if rowcodeurl += "</br>" + rowcode + ": " + rowdescription;resultpush url ;return result;
In the above example, params is used to pass the timezone offset. This illustrates a use where the timezone offset originates in the browser and is used when outputting the date.
- 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).
- A more thorough SQL injection security review (volunteers?).