Create an API for DataTables with Express
Tags: express, datatables
Published: 2024-07-15 02:36:26

---
title: Create an API for DataTables with Express
published: true
date: 2024-07-12 08:25:00 UTC
tags: Express,DataTables
canonical_url: https://blog.stackpuz.com/create-an-api-for-datatables-with-express/
---

![Express API for DataTables](https://blog.stackpuz.com/media/posts/9/cover.jpg)

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.

```javascript
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. We do not use it, because Spring Boot pagination uses a page index instead. We will write some JavaScript to generate this page index later.
- `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](https://github.com/StackPuz/Example-Datatables-Express/blob/main/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.

```javascript
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.

```javascript
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` and` offset` 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.

```html
<!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.

![test page](https://blog.stackpuz.com/media/posts/9/default.png)

## 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.

![page size test](https://blog.stackpuz.com/media/posts/9/page-size.png)

### Sorting test 

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

![sorting test](https://blog.stackpuz.com/media/posts/9/sort.png)

### Search test 

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

![search test](https://blog.stackpuz.com/media/posts/9/search.png)

## 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](https://github.com/stackpuz/Example-DataTables-Express)

Create a CRUD Web App in Minutes: [https://stackpuz.com](https://stackpuz.com)