Node.js SQL Server: Pagination

Summary: in this tutorial, you will learn how to perform pagination using the OFFSET and FETCH clauses in Node.js.

This tutorial begins where the Selecting data from a table in Node.js tutorial left off.

Pagination is a technique for breaking up a large dataset into smaller, more manageable chunks (pages) for display purposes.

In SQL Server, you can use the OFFSET and FETCH clauses together to implement pagination.

Pagination using OFFSET and FETCH clauses

We’ll retrieve data from the Authors table using the OFFSET and FETCH clauses.

Step 1. Modify the select.js file and define a new function findAuthors that retrieves the authors using the OFFSET and FETCH clauses:

async function findAuthors(limit = 5, offset = 0) {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Execute a query
    const result = await pool
      .request()
      .input('offset', sql.Int, offset)
      .input('limit', sql.Int, limit)
      .query(
        `SELECT * FROM Authors 
         ORDER BY FirstName
         OFFSET @offset ROWS 
         FETCH FIRST @limit ROWS ONLY`
      );

    return result.recordset;
  } catch (err) {
    console.error('Error finding authors:', err);
  }
}Code language: JavaScript (javascript)

How it works.

First, connect to the SQL Server:

const pool = await sql.connect(config);Code language: JavaScript (javascript)

Second, retrieve the authors using the OFFSET and FETCH clauses sorted by the FirstName in ascending order:

const result = await pool
  .request()
  .input('offset', sql.Int, offset)
  .input('limit', sql.Int, limit)
  .query(
    `SELECT * FROM Authors 
         ORDER BY FirstName
         OFFSET @offset ROWS 
         FETCH FIRST @limit ROWS ONLY`
  );Code language: JavaScript (javascript)

In this statement:

pool.request() creates a new Request instance that represents a query to execute against the database.
.input(name, type, value) defines input parameters for the query. In this case, we use it to define three parameters offset and limit.
.query(sqlQuery) executes the SQL query using input parameters offset, limit, and sort.

Third, return the record set:

return result.recordset;Code language: JavaScript (javascript)

Step 2. Export the findAuthors function with other functions:

export { findAuthorById, findAuthorsByLastName, findAuthors };Code language: JavaScript (javascript)

Step 3. Modify the index.js to use the findAuthors function:

import { findAuthors } from './select.js';

const authors = await findAuthors(5);

for (const { AuthorID, FirstName, LastName } of authors) {
  console.log(`${AuthorID} - ${FirstName} ${LastName}`);
}Code language: JavaScript (javascript)

Output:

2 - Agatha Christie
5 - Agatha Christie
9 - Charles Dickens
22 - Emily Bronte
11 - Ernest HemingwayCode language: JavaScript (javascript)

How it works.

First, import the findAuthors from the select.js module:

import { findAuthors } from './select.js';Code language: JavaScript (javascript)

Second, call findAuthors function to retrieve the first 5 authors sorted by their first names:

const authors = await findAuthors(5);Code language: JavaScript (javascript)

Third, display the author ID, first name, and last name on the console:

for (const { AuthorID, FirstName, LastName } of authors) {
  console.log(`${AuthorID} - ${FirstName} ${LastName}`);
}Code language: JavaScript (javascript)

To get the next 5 authors, you can pass the second argument like this:

const authors = await findAuthors(5, 5);Code language: JavaScript (javascript)

It would return the following records:

10 - F. Scott Fitzgerald
7 - George Orwell
1 - George Orwell
18 - H.G. Wells
17 - Harper LeeCode language: JavaScript (javascript)

Download the project source code

Summary

Use OFFSET and FETCH clauses to implement a pagination feature when selecting data.

Was this tutorial helpful?