Create an API for DataTables with Express

DataTables is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will demonstrate how to create an Express API to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables, you need to understand the information that DataTables will send to the API through the query string.

draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false

• draw the request ID that is used to synchronize between the client and server.
• columns[x][data] the column's field name that we define on the client-side.
• order[0] the sorting information.
• start the start index of the record.
• length the length per page (page size).
• search[value] the search value information.

The DataTables expected data will require these information.

• draw DataTables sends this ID to us, and we just send it back.
• recordsTotal Total records number before filtering.
• recordsFiltered Total records number after filtering.
• data The records data.

Prerequisites

• Node.js
• MySQL

Setup project

Setting up the Node.js project dependencies.

npm install express mysql2

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ config.js
├─ index.js
└─ public
   └─ index.html

Project files

config.js

This file contains the database connection information.

module.exports = {
  host: 'localhost',
  database: 'example',
  user: 'root',
  password: ''
}

index.js

This file is the main entry point for the Express application. It will create and setup the Express server. Because this API only has one routing URL, we will include it and the handler function in this file.

const express = require('express')
const mysql = require('mysql')
const util = require('util')
const config = require('./config')

let con = mysql.createConnection({
  host: config.host,
  database: config.database,
  user: config.user,
  password: config.password
})

let query = util.promisify(con.query).bind(con)

let app = express()
app.use(express.static('public'))
app.get('/api/products', async (req, res) => {
  let size = parseInt(req.query.length) || 10
  let start = parseInt(req.query.start)
  let order = mysql.raw((req.query.order && req.query.columns[req.query.order[0].column].data) || 'id')
  let direction = mysql.raw((req.query.order && req.query.order[0].dir) || 'asc')
  let params = [ order, direction, size, start ]
  let search = req.query.search.value
  let sql = 'select * from product'
  if (search) {
    search = `%${search}%`
    sql = 'select * from product where name like ?'
    params.unshift(search)
  }
  let recordsTotal = (await query('select count(*) as count from product'))[0].count
  let recordsFiltered = (await query(sql.replace('*', 'count(*) as count'), search))[0].count
  let data = (await query(`${sql} order by ? ? limit ? offset ?`, params))
  res.send({ draw: req.query.draw, recordsTotal, recordsFiltered, data })
})
app.listen(8000)

• mysql.createConnection() will create the database connection.
• express.static('public') will serve the static resource inside the public folder. (We used to serve index.html as a default page)
• We utilize the query string to get size, start, order, direction information to create the paginated data by using the limit andoffset of the SQL query.
• We return all DataTables required information including: draw, recordsTotal, recordsFiltered, data as object.
  

index.html

This file will be used to setup the DataTables HTML and JavaScript to work with our API.

<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        new DataTable('#table', {
            ajax: '/api/products',
            processing: true,
            serverSide: true,
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'price' }
            ]
        })
    </script>
</body>
</html>

• processing: true show a loading indicator when making the request.
• serverSide: true makes the request to the server (API) for all operations.

Run project

node index.js

Open the web browser and goto http://localhost:8000
You will find this test page.

Testing

Page size test

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

Sorting test

Click on the header of the first column. You will see that the id column will be sorted in descending order.

Search test

Enter "no" in the search text-box, and you will see the filtered result data.

Conclusion

In this article, you have learned how to create an Express API to work with the DataTables. Understand all the DataTables parameters sent to the API and utilize them to produce the appropriate data and send it back. You also learn how to setup the DataTables on the client-side using HTML and JavaScript. I hope this article will help you incorporate them into your next project.

Source code: https://github.com/stackpuz/Example-DataTables-Express

Create a CRUD Web App in Minutes: https://stackpuz.com