Server-side operations with Node.js
Learn how to perform server-side operations using Node.js with a complete reference implementation that uses the MySQL database.
This guide is intended as a starting point when learning how to use the Server-side Row Model, as it provides a simple grid implementation that uses limited set of features and grid configurations.
The sample Olympic Medals application is developed using a Node.js server that connects to a MySQL database and will demonstrate how data can be lazy-loaded as required, even when performing group, filter and sort operations when working with large datasets.
The following screenshot shows what the finished application looks like:
The source code can be found here: https://github.com/ag-grid/ag-grid-server-side-nodejs-example.
Overview
In this Olympic Medals application, the server endpoint will be hosted using a web server comprised of Node.js running Express.js, that connects to a single MySQL datasource.
An overview of technologies used in this guide is illustrated in the diagram below:
We will now proceed and to install and run the application before going through the implementation details.
Download and Install
Clone the example project using:
Database Setup
Download and install the database as per the MySql Download documentation.
Create a database with the name 'sample_data'. Then run the following script to create the table
olympic_winners
and populate it with data via the mysql command line:
That's it. We are now ready to run and explore the application.
Running the application
To run the application execute the following from the command line:
Then point your browser to http://localhost:4000/
Client Configuration
In order to keep this sample application as simple as possible, the grid configurations are kept to a minimum. The
gridOptions
required for our grid are shown below:
In the code snippet above, the grid is configured to use the Server-side Row Model by setting: gridOptions.rowModelType = 'serverSide'
.
Sorting is enabled via defaultColDef.sortable = true
property. A simple number filter is also added
to the 'year' column. The example has filterParams.newRowsAction = 'keep'
set to ensure that as new data is loaded
the applied filters are kept - however since v21 of ag-Grid, newRowsAction defaults to 'keep' for Server Side Row Model
so this property no longer needs to be set.
To demonstrate Row Grouping, the 'country' and 'sport'
columns have been configured with rowGroup = true
. Finally, to ensure the medal values are aggregated
up the group hierarchy, the value columns have been set up with an aggregation function: aggFunc='sum'
.
Server-side Datasource
In order to fetch data for the Server-side Row Model we must implement the IServerSideDatasource
,
which contains a single method getRows(params)
which accepts request params from the grid.
Successful responses are then passed back to the grid via the params.successCallback(rows, lastRow)
as shown below:
Server Endpoint
Hosting our server endpoint /olympicWinners
which accepts json requests is done with the help of
the express
and
body-parser
npm packages.
Request are delegated to the OlympicWinnersService
which contains all the server side application
logic. The getData()
method queries the MySQL database using the mysql
npm package with
the SQL returned by buildSql()
.
The buildSql()
method uses a number of helper methods to build up sql fragments used in the
combined SQL which is returned. The implementation details of these helper methods are be omitted from this
guide but can be examined in the project repository.
Conclusion
In this guide we presented a reference implementation for integrating the Server-side Row Model with a Node.js server connected to a MySQL database. This included all necessary configuration and install instructions.
A high level overview was given to illustrate the problem this approach solves before providing details of how to achieve the following server-side operations:
- Sorting
- Filtering
- Grouping
- Aggregation