How to create server side pagination REST api endpoint using nodejs, prisma with Postgresql?
Tags: postgres, prismaorm, node, backend
Published: 2021-07-19 17:46:44

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

```jsx
npm init
```

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

![Package.json](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhlilkyki31nrwoqf8qg.png)
 

5. Project Structure 

![Project Structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qs2dcbof3gyhjq5zuql5.png)

6. Prisma schema setup for database models to create tables to store data 

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

7. Run the following command 

```jsx
❯ 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?

```jsx
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.

```jsx
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.** 

```jsx
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,

![Insomnia](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z0ba080bf5v3pbg924l3.png)


### 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](https://twitter.com/SubbuSainath)
you can find complete code here Github link:
[Blog_code](https://github.com/subbusainath/Blog_posts)