32
How to create server side pagination REST api endpoint using nodejs, prisma with Postgresql?
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).
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🔥!
- 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)
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:migratefirst command is for generating prisma client
second command is for migrate it to local databse
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.
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).
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,
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.
32