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);
  }
}

How it works.

First, connect to the SQL Server:

const pool = await sql.connect(config);

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`
  );

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;

Step 2. Export the findAuthors function with other functions:

export { findAuthorById, findAuthorsByLastName, findAuthors };

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}`);
}

Output:

2 - Agatha Christie
5 - Agatha Christie
9 - Charles Dickens
22 - Emily Bronte
11 - Ernest Hemingway

How it works.

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

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

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

const authors = await findAuthors(5);

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}`);
}

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

const authors = await findAuthors(5, 5);

It would return the following records:

10 - F. Scott Fitzgerald
7 - George Orwell
1 - George Orwell
18 - H.G. Wells
17 - Harper Lee

Download the project source code #

Download the project source code

Summary #

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

Was this tutorial helpful?