Datatables Server Side Processing Using NodeJS & MySQL

Datatables Server Side Processing Using NodeJS & MySQL

Recently I was doing a NodeJS project while working on it, on one page I had to handle the data in the tabular format. To represent the data in the tabular format only one plugin came to my mind that was none other than Datatables.

Datatables make our life easy by doing all the heavy lifting. So I started to search online what are the options available for server-side processing using NodeJS with MySQL & to my surprise I did not find any relevant article or package for that.

We already know that server-side processing script for PHP exists & I also have written an extensive article on it – Learn Datatable with Codeigniter : The Ultimate Tutorial Part 1 . So I took the inspiration from that PHP script & decided to build my own package for server-side processing using NodeJS & MySQL.

Prerequisite.

In this tutorial, we will learn how we can achieve the server side processing in Datatables using NodeJS & MySQL. Since you are reading this article so I can safely assume that you know NodeJS.

We are also going to use the express package to handle all the incoming request to our app. So you need to be familiar with this to follow along.

For accessing & executing SQL queries I am going to use mysql package. This is very important as the package I have developed also need this package instance to execute queries under the hood.

For the frontend part, I am using Bootstrap 4, to quickly style our table.

If you don’t know how to add datatables scripts in your application then I highly recommend you to go through the above-mentioned tutorial.

Installations.

We are going to install express, mysql, nodetable  packages, So open up your terminal inside your project directory & fir this command,

>npm install express mysql nodetable

NOTE: nodetable is the package I have developed, you can check it here,

NPM – //www.npmjs.com/package/nodetable

GitHub – //github.com/ropali/nodetable

Step 1: Creating a Database & Setting Up Tables

I have created a database named fake_db using phpmyadmin & in that created a table called users, So our table schema is as follows,

CREATE TABLE `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `contact` varchar(15) NOT NULL,
 `birthdate` varchar(10) NOT NULL,
 `address` text NOT NULL,
 `active` tinyint(4) NOT NULL DEFAULT '1',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

Now you insert at least 14-15 records in that tables to see the Datatables in action. If you are thinking that it will take time to guess some random data & then insert into the table then don’t worry I got you covered.

In the menu, you can see a link text as FakeFormData, click on the link it will open an application which generates fake random data that you can use while testing any application.

Step 2: Creating Our Front Page

Create one directory in your root directory of the project as views. We will keep all the HTML files there. You can use any approach you want for handling the HTML files. Inside that create an index.html file

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8" />
    <meta
      name="viewport"
      content="width=device-width, initial-scale=1, shrink-to-fit=no"
    />

    <!-- Bootstrap CSS -->
    <link
      rel="stylesheet"
      href="//stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
      integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
      crossorigin="anonymous"
    />

    <title>NodeJS Datatable</title>
  </head>
  <body style="background-color: rgb(230, 228, 228)">
    <div class="container mt-2" style="background-color: rgb(255, 255, 255)">
      <div class="row">
        <div class="col">
          <div class="table-responsive mt-5">
            <table class="table table-bordered table-striped table-hover users-table mb-2">
              <thead>
                <tr>
                  <th>Name</th>
                  <th>Email</th>
                  <th>Contact</th>
                  <th>Birthdate</th>
                  <th>Address</th>
                </tr>
              </thead>
              <tfoot>
                <tr>
                  <th>Name</th>
                  <th>Email</th>
                  <th>Contact</th>
                  <th>Birthdate</th>
                  <th>Address</th>
                </tr>
              </tfoot>
              <tbody></tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    
    <link
      rel="stylesheet"
      type="text/css"
      href="//cdn.datatables.net/v/bs4/jq-3.3.1/dt-1.10.18/datatables.min.css"
    />

    <script
      type="text/javascript"
      src="//cdn.datatables.net/v/bs4/jq-3.3.1/dt-1.10.18/datatables.min.js"
    ></script>
    <script
      src="//stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
      integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
      crossorigin="anonymous"
    ></script>

    <script>
      $(document).ready(function() {
        $(".users-table").DataTable({
          scrollY: 400,
          processing: true,
          serverSide: true,
          ajax: "/data"
        });
      });
    </script>
  </body>
</html>

Step 3: Setting Up Our Express Server.

Let’s quickly set up our express server & create the main route to serve our homepage view.

const express = require("express");
const bodyParser = require("body-parser");
const path = require("path");
const mysql = require("mysql");

const NodeTable = require("nodetable");

const app = express();

const port = 3000;

app.use(bodyParser.urlencoded({ extended: true }));

// Create DB connection
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "fake_db"
});

db.connect();

// Homepage route
app.get("/", (req, res, next) => {
  res.sendFile(path.join(__dirname + "/views/index.html"));
});


app.listen(port, () => {
  console.log("Server started at port " + port);
});

Step 4: Route For Datatable Request

Now we need to specify a route where the Datatables can send the GET request & get the structured data back as the response.

// Datatable route to get the data
app.get("/data", (req, res, next) => {

  // Get the query string paramters sent by Datatable
  const requestQuery = req.query;

  /**
   * This is array of objects which maps 
   * the database columns with the Datatables columns
   * db - represents the exact name of the column in your table
   * dt - represents the order in which you want to display your fetched values
   * If your want any column to display in your datatable then
   * you have to put an enrty in the array , in the specified format
   * carefully setup this structure to avoid any errors
   */
  let columnsMap = [
    {
      db: "name",
      dt: 0
    },
    {
      db: "email",
      dt: 1
    },
    {
      db: "contact",
      dt: 2
    },
    {
      db: "birthdate",
      dt: 3
    },
    {
      db: "address",
      dt: 4
    }
  ];

  // our database table name
  const tableName = "users"

  // NodeTable requires table's primary key to work properly
  const primaryKey = "user_id"
  
  const nodeTable = new NodeTable(requestQuery, db, tableName, primaryKey, columnsMap);
 
  nodeTable.output((err, data)=>{
    if (err) {
      console.log(err);
      return;
    }

    // Directly send this data as output to Datatable
    res.send(data)
  })
  
});

Here we create an object of NodeTabe. NodeTable requires four parameters to be passed in the constructor.

1). requestQuery : This is the query string which is sent by the Datatable. It contains many useful pieces of information to fetch the desired data from the database.
2). db: This is the connection instance of MySQL using which we execute SQL queries under the hood.
tableName: This should be the name of the table from which you want to fetch the records. You can also pass any custom SQL query here instead of the table name.
3). primaryKey: The primary key of the table from which you want to fetch data.
4). columnsMap: This parameter is the heart of NodeTable. This array of objects represents how you will define your structure for the Datatables.

To get the data we call the output method of NodeTable object which takes one parameter as callback with error & data. In this callback, you send the data to the Datatables.

Step 5: Passing The Custom SQL Queries In The NodeTable.

Most of the time you just don’t want to fetch all the data from any tables. Sometimes you may fetch the data based on a certain condition or you may require to join two tables.

In this case, you can define your own SQL query & passed it as tableName parameter. Let’s now fetch all the users from the table whose active column value holds 1 (means fetching only the users who are active).

// Datatable route to get the data
app.get("/data", (req, res, next) => {

  // Get the query string paramters sent by Datatable
  const requestQuery = req.query;

  /**
   * This is array of objects which maps 
   * the database columns with the Datatables columns
   * db - represents the exact name of the column in your table
   * dt - represents the order in which you want to display your fetched values
   * If your want any column to display in your datatable then
   * you have to put an enrty in the array , in the specified format
   * carefully setup this structure to avoid any errors
   */
  let columnsMap = [
    {
      db: "name",
      dt: 0
    },
    {
      db: "email",
      dt: 1
    },
    {
      db: "contact",
      dt: 2
    },
    {
      db: "birthdate",
      dt: 3
    },
    {
      db: "address",
      dt: 4
    }
  ];

  // our database table name
  // const tableName = "users"

  // Custome SQL query
  const query = "SELECT * FROM users WHERE active = 1"

  // NodeTable requires table's primary key to work properly
  const primaryKey = "user_id"
  
  const nodeTable = new NodeTable(requestQuery, db, query, primaryKey, columnsMap);
 
  nodeTable.output((err, data)=>{
    if (err) {
      console.log(err);
      return;
    }

    // Directly send this data as output to Datatable
    res.send(data)
  })
  
});

Step 6: Testing Our Application.

Open your terminal & fire node index.js command to run the application. The application will start listening on port number 3000. Go to the //localhost:3000/  to see your app in action.

nodetable example

nodetable example

 

Conclusion.

You learned how you can use the NodeTable package to handle server-side processing the NodeJS with MySQL. You can see that this package easily handles paginations, searching & sorting functionalities with just a few lines of code. I have made this package open source so you can see the code & modify it as per your need.

I would love to hear your feedback about this post or this package that I have developed in the comments.

Download Complete Project Here

Happy Coding…:)

Ropali Munshi
Ropali Munshi
Ropali Munshi is fullstack PHP Developer. He is passionate developer who loves to learn and expirement with new programming languages , libraries and frameworks. Nowdays he is more into the JavaScript realm.

Leave a Reply

Your email address will not be published. Required fields are marked *

You Don't Want To Miss It!

Please subscribe to our newsletter. Every week I share tips, tricks, tutorials, free books & video course directly in your inbox.