Create an API for AG-Grid with Express
AG-Grid is a powerful JavaScript data grid library, ideal for building dynamic, high-performance tables with features like sorting, filtering, and pagination. In this article, we’ll set up an API in Express to support AG-Grid, handling server-side data operations including filtering, sorting, and pagination. By combining AG-Grid with Express, we’ll create a streamlined solution that ensures efficient performance, even with large datasets.
Prerequisites
- Node.js
- MySQL
Setup project
Setting up the Node.js project dependencies.
npm install express mysql2Create 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.htmlProject files
config.js
This file contains the database connection information.
module.exports = {
host: 'localhost',
database: 'example',
user: 'root',
password: ''
}index.js
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 page = req.query.page || 1
let size = parseInt(req.query.size) || 10
let offset = (page - 1) * size
let order = mysql.raw(req.query.order || 'id')
let direction = mysql.raw(req.query.direction || 'asc')
let params = [ order, direction, size, offset ]
let sql = 'select * from product'
let search = req.query.search
if (search) {
search = `%${search}%`
sql = `select * from product where name like ?`
params.unshift(search)
}
let count = (await query(sql.replace('*', 'count(*) as count'), search))[0].count
let data = (await query(`${sql} order by ? ? limit ? offset ?`, params))
res.send({ data, count })
})
app.listen(8000)
The index.js file sets up an Express.js server that connects to a MySQL database to provide an API for product data. It serves static files from a public directory and defines an endpoint (/api/products) that handles GET requests. This endpoint processes query parameters for pagination, sorting, and searching by product name, constructs a SQL query accordingly, and retrieves both the total count of matching products and the paginated data. The server responds with a JSON object containing the product data and total count, and it listens on port 8000.
index.html
<!DOCTYPE html>
<head>
<script src="https://cdn.jsdelivr.net/npm/ag-grid-community@32.0.0/dist/ag-grid-community.min.js"></script>
</head>
<body>
<div id="grid" class="ag-theme-alpine" style="height: 400px; width: 640px; margin: 1em"></div>
<script>
function getQuery(params) {
let query = new URLSearchParams()
let size = params.endRow - params.startRow
let page = Math.floor(params.startRow / size) + 1
query.append('page', page)
query.append('size', size)
if (params.sortModel.length) {
let sort = params.sortModel[0]
query.append('order', sort.colId)
query.append('direction', sort.sort)
}
if (params.filterModel.name) {
query.append('search', params.filterModel.name.filter)
}
return query.toString()
}
let columns = [
{ headerName: 'ID', field: 'id', sortable: true },
{
headerName: 'Name', field: 'name', sortable: true, filter: true,
filterParams: {
filterOptions: ['contains'],
maxNumConditions: 1,
}
},
{ headerName: 'Price', field: 'price', sortable: true }
]
let gridOptions = {
columnDefs: columns,
rowModelType: 'infinite',
pagination: true,
paginationPageSize: 20,
cacheBlockSize: 20,
datasource: {
getRows: function (params) {
let query = getQuery(params)
fetch(`/api/products?${query}`)
.then(response => response.json())
.then(json => {
params.successCallback(json.data, json.count)
})
}
}
}
document.addEventListener('DOMContentLoaded', () => {
agGrid.createGrid(document.querySelector('#grid'), gridOptions)
})
</script>
</body>
</html>
The index.html file sets up a web page that uses the AG-Grid library to display a dynamic data grid for products. It includes a grid styled with the AG-Grid theme and a JavaScript section that constructs query parameters for pagination, sorting, and filtering. The grid is configured with columns for ID, Name, and Price, and it fetches product data from an API endpoint based on user interactions. Upon loading, the grid is initialized, allowing users to view and manipulate the product list effectively.
Run project
node index.jsOpen the web browser and goto http://localhost:8000
You will find this test page.
Testing
Page size test
Change page size by selecting 50 from the "Page Size" drop-down. You will get 50 records per page, and the last page will change from 5 to 2.
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 of the "Name" column, and you will see the filtered result data.
Conclusion
In conclusion, we’ve effectively implemented AG-Grid with Express to create a powerful and efficient data grid solution. By harnessing the capabilities of Express on the backend, we enabled AG-Grid to handle server-side filtering, sorting, and pagination, ensuring smooth performance even with large datasets. This integration not only optimizes data management but also enhances the user experience with dynamic, responsive tables on the frontend. With AG-Grid and Express working seamlessly together, we’ve built a scalable and high-performance grid system that is ready for real-world applications.
Source code: https://github.com/stackpuz/Example-AG-Grid-Express
Create a CRUD Web App in Minutes: https://stackpuz.com
