NodeJS + Datatable + mysql

datatable.js

module.exports = function (db, req, res, data) {

    // const db = require('../db');

    var tableName = data.table;
    var columns = data.columns;
    var modify = data.modifyColumns;
    var actions = data.actionButton;

    var searchValue = req.body['search[value]'];
    var likeSearchValue = '';
    if(searchValue) {
        likeSearchValue = "%"+searchValue+"%";
    } else {
        likeSearchValue = "%%%";
    }

    // generating where statement
    var where = " ";
    columns.forEach(function(column, index) {
        if (index === columns.length - 1){ 
            where = where.concat(" "+column+" like '"+likeSearchValue+"' "); 
        } else {
            where = where.concat(" "+column+" like '"+likeSearchValue+"' or ");
        }
    });

    var start = parseInt(req.body.start);
    var length = parseInt(req.body.length);

    // generating Orderby statement
    var orderby = '';
    if(req.body['order[0][column]']){
        var columnId = req.body['order[0][column]'];
        if(req.body['columns[3][orderable]']){
            if(req.body['columns['+columnId+'][data]'] !== 'actions'){
                orderby = "ORDER BY "+req.body['columns['+columnId+'][data]']+" "+req.body['order[0][dir]']+" ";
            }
        }
    }

    // prepareing query
    var sql1 = "SELECT count(*) as total FROM "+tableName+" where "+where;
    var sql2 = "SELECT * FROM "+tableName+" where "+where+" "+orderby+"  limit "+start+", "+length;


    var totalRow = 0;
    db.query(sql1, function (error, results, fields) {
        if (error) { throw error; }

        if (results.length === 0) {
            totalRow = 0;
        } else {
            totalRow = results[0].total;
            db.query(sql2, function (error, results, fields) {
                if (error) { throw error; }

                var json_data = {
                    draw: req.body.draw,
                    recordsTotal: totalRow,
                    recordsFiltered: totalRow,
                    data: {}
                }

                if (results.length) {

                    var modifiedResults = [];
                    results.forEach(function(result, index) {

                        // modify single row
                        var singleRow = {};
                        columns.forEach(function(column, index) {

                            // modify column
                            var resultColumn = result[column];
                            if(modify){
                                if(modify[column]) {
                                    var modifyColumn = modify[column];
                                    var matches = modifyColumn.split("`");
                                    for(var i = 0; i < matches.length; i++){
                                        if(i%2) {
                                            var matchString = '`'+matches[i]+'`';
                                            var modifyColumn = modifyColumn.replace(matchString, result[matches[i]]);
                                        }
                                    }
    
                                    resultColumn = modifyColumn;
                                    // resultColumn = modifyColumn.replace(/{id}/g , result[column]);
                                }
                            }
                            
                            singleRow[column] = resultColumn;
                        });

                        // Add actions sections to datatable
                        if(actions) {
                            var actionString = actions;
                            var matches = actionString.split("`");
                            for(var i = 0; i < matches.length; i++){
                                if(i%2) {
                                    var matchString = '`'+matches[i]+'`';
                                    var actionString = actionString.replace(matchString, result[matches[i]]);
                                }
                            }

                            singleRow['actions'] = actionString;
                        }

                        modifiedResults.push(singleRow);
                    });

                    json_data.data = modifiedResults;
                }

                res.json(json_data);

            });
        }
    });
};






// in view file declare this code (Just change coloumns)
// $(document).ready(function(){

//     $('#datatable').DataTable( {
//         "processing": true,
//         "serverSide": true,
//         "ajax": {
//             "url": "/dataTableData",
//             "type": "POST"
//         },
//         "columns": [
            //     { "data": "id" },
            //     { "data": "name" },
            //     { "data": "age" },
            //     { "data": "salary" },
            //     { "data": "actions", "orderable": false }
            // ]
//     } );
// }



// Rander datatable
// const db = require('../db');
// const datatable = require('../models/datatable');
// var data = {
//     table: "employee",
//     columns: [ 'id', 'name', 'age', 'salary' ],
//     modifyColumns: {
//         id: "<a href='#' id='`id`'>`id`</a>",
//         name: "<a href='#' id='`id`' data-id='your-id-should-be-here_`id`'>`name`</a>",
//         age: "<a href='#' id='`id`' data-id='your-id-should-be-here_`id`'>`age`</a>"
//     },
//     actionButton: "<a href='/user/`id`' id='`id`' class='btn btn-success'>add</a> <a href='#' id='`id`' class='btn btn-warning'>edit</a> <a href='#' id='`id`' class='btn btn-danger'>delete</a>"
// };
// datatable(db, req, res, data);



// core js code for datatable

// var searchValue = req.body['search[value]'];
// var likeSearchValue = '';
// if(searchValue) {
// 	likeSearchValue = "%"+searchValue+"%";
// } else {
// 	likeSearchValue = "%%%";
// }

// var start = parseInt(req.body.start);
// var length = parseInt(req.body.length);

// // Kill query after 60s
// var totalRow = 0;
// db.query('SELECT count(*) as total FROM employee where name like ? or age like ? or salary like ?', [likeSearchValue, likeSearchValue, likeSearchValue], function (error, results, fields) {
// 	if (error) { throw error; }

// 	if (results.length === 0) {
// 		totalRow = 0;
// 	} else {
// 		totalRow = results[0].total;
// 		var sql = 'SELECT * FROM employee where name like "'+likeSearchValue+'" or age like "'+likeSearchValue+'" or salary like "'+likeSearchValue+'" limit '+start+', '+length;
// 		console.log(sql);
// 		db.query(sql, function (error, results, fields) {
// 			if (error) { throw error; }
    
// 			if (results.length === 0) {
// 				var json_data_not_found = {
// 					draw: req.body.draw,
// 					recordsTotal: totalRow,
// 					recordsFiltered: results.length,
// 					data: {}
// 				}
    
// 				res.json(json_data_not_found);
// 			} else { 
// 				var json_data = {
// 					draw: req.body.draw,
// 					recordsTotal: totalRow,
// 					recordsFiltered: totalRow,
// 					data: results
// 				}
    
// 				res.json(json_data);
// 			}
// 		});
// 	}
// });

Instructions:

create datatable.js model into models folder
require db and datatable.js from route
pass db, req, res and data objects
it will rendar datatable automatically