DEV Community

loading...

How to create server side pagination REST api endpoint using nodejs, prisma with Postgresql?

Subbusainath
Gentle Giant who knows how to write code
・4 min read

We know how prisma works well with GraphQL.But, Most of the programmers still use RESTful API over GraphQL. So here i am , Going to create the REST Api endpoint for pagination from server side with the help of well renown NodeJS with quality ORM for Postgresql (RDBMS).

Why nodejs ?

NodeJS is primarily used in backend developement since it is an single-threaded nature.And it is designed with real-time,push-based architecture. Most important thing of nodejs is IT IS AN OPENNNNNNNNNNNNNNNNN SOURCEEEEEEEEEEEEEEEEEEEEE🔥!

Tools used to start!

  1. NodeJS V_12.X to latest
  2. Prisma -version 2.16.1 @prisma/client, prisma-studio
  3. PostgerSQL -version 9.3.1
  4. Nodemon -2.0.7 (Looking for changes in file and automatically restart the server)
  5. Visual Studio Code-Insider (probably for Mac M1 chip)
  6. Dotenv - 8.2.0 for environment variable
  7. Insomnia - 2021.2.2 for testing your API endpoints (You can use Postman)

Lets Start to Develope

  1. Create a folder with the name you desire . In my case , I named my folder as "Server-side-Pagination"

  2. Open your vscode with the folder

  3. Run npm init to create package.json file

npm init
Enter fullscreen mode Exit fullscreen mode
  1. Your package.json file will be created with default structure and add dependencies like the snapshot which is mentioned below

Package.json

  1. Project Structure

Project Structure

  1. Prisma schema setup for database models to create tables to store data
generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-1.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model forensicDetails {
  id          Int     @id @default(autoincrement())
  first_name  String?
  last_name   String?
  Criminal     String?
  Blood_group String?
  height    Int?
  weight    Int?
  Evidence  String?
}
Enter fullscreen mode Exit fullscreen mode
  1. Run the following command
 dotenv -e .env npm run prisma:generate
 dotenv -e .env npm run prisma:migrate

Enter fullscreen mode Exit fullscreen mode

first command is for generating prisma client

second command is for migrate it to local databse

Here comes the major part of this blog

In this blog, i am gonna use offset limit pagination which is good but not good as cursor pagination where it takes Primary Keys as a identifier for processing search.

Actually, How does offset works in prisma?

take: limit(data to retrieve from the table)
skip: offset(data to be skipped from the table)
Enter fullscreen mode Exit fullscreen mode

offset based pagination is a popular technique where the client makes a request with parameters for limit(number of results) and offset(number of records to skip).

pagination.js

Folowing code snippet is for write a data into the table called forensicDetails.

const express = require("express");
const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();
const app = express();
const port = process.env.PORT || 5000;
app.use(express.json());
app.listen(port, () => {
  console.log(`Server is Running on the ${port}`);
});
app.post("/forensic", async (req, res) => {
  const {
    first_name,
    last_name,
    Criminal,
    Blood_group,
    height,
    weight,
    Evidence,
  } = req.body;
  return await prisma.forensicDetails
    .create({
      data: {
        first_name,
        last_name,
        Criminal,
        Blood_group,
        height,
        weight,
        Evidence,
      },
    })
    .then((data) => {
      return res.status(201).json(data);
    })
    .catch((err) => {
      return res.status(500).json(err);
    });
});
Enter fullscreen mode Exit fullscreen mode

As we use LIMIT and OFFSET condition for selecting limited data from table. As i said earlier, we are going to implement this method use PRISMA ORM.

app.get("/forensic/page", async (req, res) => {
  const query = req.query;
  const page = parseInt(query.page) || 1;
  const limit = parseInt(query.limit) || 2;
  const last_page = req.query.last_page;
  const startIndex = (page - 1) * limit;
  const endIndex = page * limit;
  const result = {};
  const totalCount = await prisma.forensicDetails.count();
  const totalPage = Math.ceil(totalCount / limit);
  const currentPage = page || 0;
  try {
    if (page < 0) {
      return res.status(400).json("Page value should not be negative");
    } else if (page === 1 && !last_page) {
      result.totalCount = totalCount;
      result.totalPage = totalPage;
      result.currentPage = currentPage;
      result.next = {
        page: page + 1,
        limit: limit,
      };
      result.paginateData = await prisma.forensicDetails.findMany({
        take: limit,
        skip: startIndex,
        orderBy: {
          id: "desc",
        },
      });
      res.paginatedResult = result;
      result.currentCountPerPage = Object.keys(result.paginateData).length;
      result.range = currentPage * limit;
      return res.status(200).json(result);
    } else if (endIndex < totalCount && !last_page) {
      result.totalCount = totalCount;
      result.totalPage = totalPage;
      result.currentPage = currentPage;
      result.next = {
        page: page + 1,
        limit: limit,
      };
      result.paginateData = await prisma.forensicDetails.findMany({
        take: limit,
        skip: startIndex,
        orderBy: {
          id: "desc",
        },
      });
      res.paginatedResult = result;
      result.currentCountPerPage = Object.keys(result.paginateData).length;
      result.range = currentPage * limit;
      return res.status(200).json(result);
    } else if (startIndex > 0 && !last_page) {
      result.totalCount = totalCount;
      result.totalPage = totalPage;
      result.currentPage = currentPage;
      result.previous = {
        page: page - 1,
        limit: limit,
      };
      result.paginateData = await prisma.forensicDetails.findMany({
        take: limit,
        skip: startIndex,
        orderBy: {
          id: "desc",
        },
      });
      res.paginatedResult = result;
      result.currentCountPerPage = Object.keys(result.paginateData).length;
      result.range = currentPage * limit;
      return res.status(200).json(result);
    } else if (last_page === "true" && page === totalPage) {
      result.totalCount = totalCount;
      result.totalPage = totalPage;
      result.currentPage = totalPage;
      result.last = {
        page: totalPage,
        limit: limit,
      };
      result.paginateData = await prisma.forensicDetails.findMany({
        take: limit,
        skip: startIndex,
        orderBy: {
          id: "desc",
        },
      });
      res.paginatedResult = result;
      result.currentCountPerPage = Object.keys(result.paginateData).length;
      result.range = totalCount;
      return res.status(200).json(result);
    } else {
      return res.status(404).json({ error: "Resource not found" });
    }
  } catch (err) {
    console.error("error", err);
    return res.status(500).json(err);
  }
});
Enter fullscreen mode Exit fullscreen mode

here the logic i used is User is able to adjust there LIMITs what ever they like except negative value.

Every page will be displayed with default LIMIT of 2. I stored the values in Objects.

Format for the api end point is consists of totalCount,totalPage,CurrentPage,next or previous or last ,paginatedData as array of objects, currentCountPerPage and range. This endpoint consists all the data preferable by front end developers.

Below mentioned screenshot , this is how it would look like in api endpoint testing tool

here i used insomnia,

Insomnia

Conclusion

This is how we able to create rest api endpoint for server side pagination using node js , Postgresql with Prisma. We can tweak this code based on the requirement by your friend developers.

Follow me for more technical blogs on Twitter.
Twitter
you can find complete code here Github link:
Blog_code

Discussion (2)

Collapse
ankitaabad profile image
ankitaabad

Really helpful ...Thanks for posting

Collapse
subbusainath profile image
Subbusainath Author

Thank you !