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 thelimit
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
Top comments (0)