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

#postgres #prismaorm #node #backend

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!

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

Lets Start to Develope

Create a folder with the name you desire . In my case , I named my folder as "Server-side-Pagination"
Open your vscode with the folder
Run npm init to create package.json file



npm init

Your package.json file will be created with default structure and add dependencies like the snapshot which is mentioned below

Project Structure

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?
}

Run the following command



❯ dotenv -e .env npm run prisma:generate
❯ dotenv -e .env npm run prisma:migrate

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)

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

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

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,

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