Crafting paginated API with NodeJS and MSSQL

When the data is in huge numbers and a single API call to the backend is taking ages, then the need of paginated API arises. A paginated API focuses on bringing data not as a whole, but in chunks or page wise. The client can request the data based on page numbers. This may sound simple, but there are more to this requirement.

With a data grid/table the user must be in a position to sort the order of data in which they appear, filter out based on search criteria and limit the number of records they want to view. Need to keep all this in mind while creating a paginated API is the real challenge. Let us list down the objectives to better understand:

• Paging (page wise data)
• Sorting (ordered data either ascending or descending)
• Filtering (search option)

In this reading we will focus on creating a NodeJS API which can achieve all of the above mentioned objectives without any hurdles. We are considering MS SQL Server as our data source; the approach will be similar for any other relation or non-relational databases only with syntactical and coding differences.

We have an article written about establishing a basic connection with MSSQL with NodeJS which you can checkout from here Teaming up with NodeJS and SQL Server. In addition to that a more detailed article about the working with MSSQL's stored procedures with NodeJS is written here Calling Stored Procedure with NodeJS and MSSQL; these readings will form a good base for understanding the pagination concept.

We are considering the same Employee table, which we have seen in above mentioned articles (additionally the database scripts are provided with the project's repository in scripts folder).

Creating Paginated Stored Procedure

In order to get a limited number of records based on page number we will make use of OFFSET of MSSQL which follows skip and take fashion; with OFFSET we can specify the number of rows to skip and number of rows to fetch. We can understand this with a cute little SQL script given below:

SELECT	* 
FROM	[dbo].[Employee] 
ORDER BY ID DESC
OFFSET	(0) ROWS FETCH NEXT (5) ROWS ONLY

The above query will return a total of 5 records with order descending by ID, we have mentioned skip 0 records while fetch total of 5 records. Like wise, in order to fetch next 5 records we can set the offset skip value to 5 while keeping the number of records same as 5. Check out the following examples:

OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY	// skipped 0 records [page 1]
OFFSET (5) ROWS FETCH NEXT (5) ROWS ONLY	// skipped 5 records [page 2]
OFFSET (10) ROWS FETCH NEXT (5) ROWS ONLY	// skipped 10 records [page 3]

The front-end or the UI is not concerned about the skip and take logic instead we will get page numbers from the UI as an input to our paginated API. So we need to construct a simple formula on the basis of which we can decide the number of rows to skip for a particular page number depending on given size (fear the Math).

skip = (size * page) - size

page = 1, size = 5, skip = (5 * 1) - 5 = 5 - 5 = 0
page = 2, size = 5, skip = (5 * 2) - 5 = 10 - 5 = 5
page = 3, size = 5, skip = (5 * 3) - 5 = 15 - 5 = 10
...
page = 7, size = 5, skip = (5 * 7) - 5 = 35 - 5 = 30
page = 8, size = 5, skip = (5 * 8) - 5 = 40 - 5 = 35
page = 9, size = 5, skip = (5 * 9) - 5 = 45 - 5 = 40

In addition to the above paginated query we optionally have a filter query in order to implement filtering functionality. The filter logic will be added to query in case the API receives any search parameter. Nothing fancy, we will simply make use of SQL's LIKE operator to perform a search across columns.

Lastly, our proc will provide the count of total number records present in the table and the filtered count obtained after applying the filtered condition. The total count will let the user know how much total records present in the table while the pages will be constructed based on filtered count.

Let us summarize the input and output to and from the paginated stored proc:

Input:
	1. page
	2. size
	3. search
	4. orderBy
	5. orderDir
	
Output Result Sets:
	1. Records
	2. Record Counts
		- Filtered Count
		- Total Count

On the basis of above theory, we will create our paginated stored proc (drums roll please):

CREATE PROCEDURE [dbo].[usp_EmployeePagination]
	@page			INT,
	@size			INT,
	@search			VARCHAR(MAX) = '',
	@orderBy		VARCHAR(MAX) = 'ID',
	@orderDir		VARCHAR(MAX) = 'DESC'
AS
BEGIN
	DECLARE @condition	VARCHAR(MAX);
	DECLARE @skip		INT;

	SET @skip	= (@size * @page) - @size;
	SET @search = LOWER(@search);

	IF @search <> ''
		SET @condition = '
			WHERE	LOWER([Name])		LIKE ''%'' + ' + @search + ' + ''%'' OR
					LOWER([Job])		LIKE ''%'' + ' + @search + ' + ''%'' OR
					LOWER([Salary])		LIKE ''%'' + ' + @search + ' + ''%'' OR
					LOWER([Department]) LIKE ''%'' + ' + @search + ' + ''%''
		';

	EXEC('
		SELECT	* 
		FROM	[dbo].[Employee] 
		' + @condition + '
		ORDER BY ' + @orderBy + ' ' + @orderDir + '
		OFFSET	(' + @skip + ') ROWS FETCH NEXT (' + @size + ') ROWS ONLY		
		
		SELECT 	
			(SELECT COUNT(*) FROM [dbo].[Employee] ' + @condition + ') AS [Filtered],
			(SELECT COUNT(*) FROM [dbo].[Employee]) AS [Total] 
	');
END
GO

We are creating a dynamic query which we will execute using the EXEC function of SQL. We have created the skip variable and calculated its value using our formula. After that the filtering condition is constructed if the search parameter is supplied with values; and then finally the paginated query is placed at the end.

Now let us see our heroic paginated stored proc in action:

EXEC [usp_EmployeePagination] @page = 1, @size = 5
EXEC [usp_EmployeePagination] @page = 1, @size = 5, @search = 'pa'
EXEC [usp_EmployeePagination] @page = 1, @size = 5, @search = 'pa', @orderBy = 'Name', @orderDir = 'ASC'
EXEC [usp_EmployeePagination] @page = 1, @size = 5, @search = '', @orderBy = 'Name', @orderDir = 'ASC'

Creating Paginated API

Let us jump into creating our paginated API in NodeJS and call our newly created paginated stored procedure. Here we will directly check out our stored procedure call and the paginated API end point code logic:

router.get('/', async (req, res) => {
    try {
        await pool.connect();
        const result = await pool.request()
            .input('page', req.query.page || 1)
            .input('size', req.query.size || 5)
            .input('search', req.query.search || '')
            .input('orderBy', req.query.orderBy || 'Id')
            .input('orderDir', req.query.orderDir || 'DESC')
            .execute(`usp_EmployeePagination`);
        const count = result.recordsets[1][0];
        const employees = { 
            records: result.recordsets[0],
            filtered: count.Filtered,
            total: count.Total,
        };

        res.json(employees);
    } catch (error) {
        res.status(500).json(error);
    }
});

We have provided default values to the procedure's parameters in case we don't receive any of it from the query string of the request. After that we have called the execute method of MSSQL pool request to run the procedure. Finally, we are returning the employee's paginated data which consists of an array named records along with filtered and total count. Time to see the API in action.

Testing Paginated API

We will make a rest call to our paginated API end point in the definitions/employees.rest file as follows:

#### Get Employees with page 1, size 5, search 'pa', orderBy 'Name' and orderDir 'ASC' 
GET http://localhost:3000/api/employees?page=1&size=5&search=al&orderBy=Name&orderDir=ASC

We will obtain following result form our API call:

{
  "records": [
    {
      "Id": 47,
      "Code": "CT7317",
      "Name": "Alice Howard",
      "Job": "Analyst",
      "Salary": 50000,
      "Department": "Operations"
    },
    {
      "Id": 3,
      "Code": "CT7202",
      "Name": "Allen Green",
      "Job": "Salesman",
      "Salary": 15000,
      "Department": "Sales"
    },
    {
      "Id": 28,
      "Code": "CT8100",
      "Name": "Bruce Banner",
      "Job": "Salesman",
      "Salary": 20000,
      "Department": "Sales"
    },
    {
      "Id": 33,
      "Code": "CT8100",
      "Name": "Bruce Banner",
      "Job": "Salesman",
      "Salary": 20000,
      "Department": "Sales"
    },
    {
      "Id": 13,
      "Code": "CT7213",
      "Name": "Catherine Foster",
      "Job": "Salesman",
      "Salary": 15000,
      "Department": "Sales"
    }
  ],
  "filtered": 17,
  "total": 39
}

Additionally, we can try some of the variations to our API call as below:

#### Get Employees with page 1 and default size 5 
GET http://localhost:3000/api/employees

#### Get Employees with page 1 and size 10
GET http://localhost:3000/api/employees?page=1&size=10

#### Get Employees with page 1, size 5 and search 'pa'
GET http://localhost:3000/api/employees?page=1&size=5&search=pa