Cursor-based pagination for infinite scrolling using Next 13, Tailwind, Postgres and Prisma
In this article, we will look at cursor-based pagination using Postgres and Prisma and consume data from the pagination using Next.js 13, to implement infinite scrolling.
What is Cursor Pagination
Cursors are tokens or values you pass to the database so it knows where to continue serving results. Imagine it like a bookmark in the results, giving the database a hint of which row it last returned.
Cursor-based pagination will allow your application’s users to specify how many items they want to receive, then return both the data and a cursor that you need to specify for the next request, this process repeats itself until no more items are available.
Why use cursors for pagination
- Improved consistency because the cursor is based on an actual value instead of just a number as is in offset-based pagination
- Improved performance/scalability for large databases where a cursor is used since a full dataset scan is not required as in offset-based pagination
Bootstrapping our Next.js 13 project
To bootstrap a Next.js application, run the following command npx create-next-app@latest
The default options from the prompt will suffice for this project. It’s awesome they have support for Tailwind.
To start the project, run the command npm run dev and you should see the page below.
Setting up Prisma
Next, we will set up Prisma and connect it to our PostgreSQL database. Start by installing the Prisma CLI as a development dependency via npm install prisma — save-dev
To initialize Prisma within the project we will run the command npx prisma init which will create a new /prisma directory and inside it, you will find a schema.prisma file. A .env (dotenv) file is also added to the root of the project. Remember to add the .envfile to the .gitignore file.
Open the .env file and replace the dummy connection URL with the connection URL of your PostgreSQL database.
#env
DATABASE_URL="postgresql://postgres:password@localhost:5432/next_cursor?schema=public"Defining the models
Inside the /prisma/schema.prisma file, we will create a user model. Let us update the /prisma/schema.prisma file as follows:
// /prisma/schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt @default(now())
name String
email String @unique
avatar String?
}To create the table in our database, we will npx prisma migrate dev -name init.
This command does the following things:
- Generate a new SQL migration called
init - Apply the migration to the database
- Install Prisma Client if it’s not yet installed
- Generate Prisma Client based on the current schema
Inside the prisma directory, you will notice a new folder called migrations. It should also contain another folder that ends with init and contains a file called migration.sql. The migration.sql file contains the generated SQL.
Single Instance PrismaClient
To create a single PrismaClient instance that can be imported anywhere it’s needed, we create a /lib/prisma.ts file inside the root directory.
Now, let us add the code below to the /lib/prisma.ts file to create the instance.
// /lib/prisma.ts
import { PrismaClient } from "@prisma/client";
declare global {
var cachedPrisma: PrismaClient;
}
let prisma: PrismaClient;
if (process.env.NODE_ENV === "production") {
prisma = new PrismaClient();
} else {
if (!global.cachedPrisma) {
global.cachedPrisma = new PrismaClient();
}
prisma = global.cachedPrisma;
}
export default prisma;Seeding the database
Since the database is currently empty, we want to populate it with data to use on the client. We will use PrismaClient to interact with the database and populate it with dummy data from Faker.js. Let us install Faker.js using the command npm install @faker-js/faker — save-dev .
Let us create a new file /prisma/seed.ts and update it with the following code:
// /prisma/seed.ts
import prisma from "@/lib/prisma";
import { faker } from "@faker-js/faker";
const fakerUser = () => ({
email: faker.internet.email(),
name: faker.person.fullName(),
avatar: faker.image.avatar(),
});
async function main() {
console.log("Seeding...");
const users = Array.from({ length: 400 }, fakerUser);
await prisma.user.createMany({ data: users });
console.log("Seeded!");
}
main().catch((e) => {
console.error(e);
process.exit(1);
}).finally(async () => {
await prisma.$disconnect();
});To easily run the /prisma/seed.ts file, we will instal tsx using the command npm i -D tsx .
Update your package.json file by adding a prisma key with a seed property defining the script for seeding the database:
{
...
"prisma": {
"seed": "tsx ./prisma/seed.ts"
}
}To seed the database run the npx prisma db seed command. If there were no errors, you can run the command npx prisma studio to view the seeded data in the database.
Defining a REST API endpoint to fetch the users
Next, we will implement a REST API endpoint to fetch the users' data and paginate it using cursors. Inside the /app directory, we will create a /api/users/route.ts file. This will create the endpoint http://localhost:3000/api/users
Let us update the /api/users/route.ts file with the following code:
// /app/api/users/route.ts
import prisma from "@/lib/prisma";
export async function GET(req: Request){
try {
// get page and lastCursor from query
const url = new URL(req.url);
const take = url.searchParams.get("take");
const lastCursor = url.searchParams.get("lastCursor");
let result = await prisma.user.findMany({
take: take ? parseInt(take as string) : 10,
...(lastCursor && {
skip: 1, // Do not include the cursor itself in the query result.
cursor: {
id: lastCursor as string,
}
}),
orderBy: {
createdAt: "desc",
}
}
);
if (result.length == 0) {
return new Response(JSON.stringify({
data: [],
metaData: {
lastCursor: null,
hasNextPage: false,
},
}), { status: 200 });
}
const lastPostInResults: any = result[result.length - 1];
const cursor: any = lastPostInResults.id;
const nextPage = await prisma.user.findMany({
// Same as before, limit the number of events returned by this query.
take: take ? parseInt(take as string) : 7,
skip: 1, // Do not include the cursor itself in the query result.
cursor: {
id: cursor,
},
});
const data = {
data: result, metaData: {
lastCursor: cursor,
hasNextPage: nextPage.length > 0,
}
};
return new Response(JSON.stringify(data), { status: 200 });
} catch (error: any) {
return new Response(JSON.stringify(JSON.stringify({ error: error.message })), { status: 403 });
}
}The gif below shows testing the endpoint we just created. We can run the app by using the command npm run dev.
Let's build the frontend
In the previous sections, we focused on the backend. In the following sub-sections, we are going to focus on the frontend.
Installing a few dependencies
Let us run this command npm install @tanstack/react-query react-intersection-observer axios . We will use these dependencies in the frontend to help with the following:
- React Query — manage server state in the frontend and also assist in infinite scrolling
- React Intersection Observer — Helps us to know when a given element is in view
- Axios — Gives us a simple API to get/push data to the server
Preparing the layout
Update the /app/globals.css file as this file here.
React Query needs to be wrapped in a provider for it to work. Let us create a React Query wrapper in the file /app/wrapper/query-wrapper.tsx as follows:
// /app/wrapper/query-wrapper.tsx
"use client";
import { QueryClient, QueryClientProvider } from "@tanstack/react-query";
interface Props {
children: React.ReactNode;
}
const queryClient = new QueryClient();
const QueryWrapper = ({ children }: Props) => {
return (
<QueryClientProvider client={queryClient}>
{children}
</QueryClientProvider>
);
};
export default QueryWrapper;We will use this wrapper inside the /app/layout.tsx file, let us update this file as follows:
// /app/layout.tsx
import "./globals.css";
import { Poppins } from "next/font/google";
import QueryWrapper from "./components/wrapper/query-wrapper";
const poppins = Poppins({ weight: "400", style: "normal", subsets: ["latin"] });
export const metadata = {
title: "Create Next App",
description: "Generated by create next app",
};
export default function RootLayout({
children,
}: {
children: React.ReactNode;
}) {
return (
<html lang="en">
<QueryWrapper>
<body className={poppins.className}>{children}</body>
</QueryWrapper>
</html>
);
}Building the user card
We will use a card to display a user on the UI. Let us make this component inside the /app/components/cards/user-card/index.tsx file as follows:
// /app/components/cards/user-card/index.tsx
import Image from "next/image";
type UserCardProps = {
name: string;
email: string;
avatar: string;
};
const UserCard = ({name, email, avatar}: UserCardProps) => {
return (
<div className="relative flex flex-col min-w-0 break-words bg-white w-full mb-6 shadow-lg rounded-lg p-4">
<div className="flex items-center">
<Image
alt="profile pic"
src={avatar}
className="rounded-full object-cover h-10 w-10 mr-3"
width={40}
height={40}
/>
<div>
<div className="font-medium text-gray-700">{name}</div>
<div className="text-gray-400">{email}</div>
</div>
</div>
</div>
);
};
export default UserCard;Fetching the users
Next, we will fetch the 400 users and paginate the data as one scrolls the page on the browser.
Let us implement this inside the /app/components/users/users.tsx file as follows:
// /app/components/users/users.tsx
"use client";
import { useEffect } from "react";
import axios from "axios";
import { useInfiniteQuery } from "@tanstack/react-query";
import { useInView } from "react-intersection-observer";
import UserCard from "../cards/user-card";
type UserQueryParams = {
take?: number;
lastCursor?: string;
};
const allUsers = async ({ take, lastCursor }: UserQueryParams) => {
const response = await axios.get("/api/users", {
params: { take, lastCursor },
});
return response?.data;
};
type UsersType = {
id: string;
name: string;
email: string;
avatar: string;
};
const Users = () => {
// to know when the last element is in view
const { ref, inView } = useInView();
// useInfiniteQuery is a hook that accepts a queryFn and queryKey and returns the result of the queryFn
const {
data,
error,
isLoading,
hasNextPage,
fetchNextPage,
isSuccess,
isFetchingNextPage,
} = useInfiniteQuery({
queryFn: ({ pageParam = "" }) =>
allUsers({ take: 10, lastCursor: pageParam }),
queryKey: ["users"],
// getNextPageParam is used to get the cursor of the last element in the current page
// which is then used as the pageParam in the queryFn
getNextPageParam: (lastPage) => {
return lastPage?.metaData.lastCursor;
},
});
useEffect(() => {
// if the last element is in view and there is a next page, fetch the next page
if (inView && hasNextPage) {
fetchNextPage();
}
}, [hasNextPage, inView, fetchNextPage]);
if (error as any)
return (
<div className="mt-10">
{"An error has occurred: " + (error as any).message}
</div>
);
// console.log("data:",data);
return (
<div className="mt-10">
{isSuccess &&
data?.pages.map((page) =>
page.data.map((user: UsersType, index: number) => {
// if the last element in the page is in view, add a ref to it
if (page.data.length === index + 1) {
return (
<div ref={ref} key={index}>
<UserCard
key={user.id}
name={user.name}
email={user.email}
avatar={user.avatar}
/>
</div>
);
} else {
return (
<UserCard
key={user.id}
name={user.name}
email={user.email}
avatar={user.avatar}
/>
);
}
})
)}
{(isLoading || isFetchingNextPage) && <p className="mb-4">Loading...</p>}
</div>
);
};
export default Users;Lastly, let us use the component we just created above inside the /app/page.tsx file as follows:
// /app/page.tsx
import Users from "./components/users/users";
export default function Home() {
return (
<section className="pt-10">
<div className="container mx-auto px-2">
<div className="flex content-center items-center justify-center">
<div className="w-full lg:w-6/12 px-4">
<Users />
</div>
</div>
</div>
</section>
);
}We have completed building our application. You can find the Github repo of the application here. Below is a clip of a user scrolling through the data.
Until Next time, peace, love and blessings!
