I'm trying to create a paginated table using Nextjs, Prisma and SWR. The table will show a list of invoices ordered by their ID. This is an example of how it would look:
I'm using Prisma to fetch all the data to the api route and SWR to display the data on the UI and revalidate.This is the code for the UI so far:
{InvoiceData.map((invoice, key) => {
return (
<tr key={key}>
<td className="font-medium">
#
{invoice.nro_factura &&
Number(invoice.nro_factura).toString()}
</td>
<td className="whitespace-pre-wrap">
{invoice.fecha_emision &&
moment(invoice.fecha_emision).format("MMM DD, YYYY")}
</td>
<td className="font-medium">
${invoice.total && Number(invoice.total).toLocaleString()}
</td>
<td>
<Badge
status={
invoice.total === invoice.pagos
? 0
: invoice.anulada
? 2
: 1
}
title={
invoice.total === invoice.pagos
? "Pagado"
: invoice.anulada
? "Borrada"
: "Pendiente"
}
/>
</td>
<td className="customer-column">
{CustomersData.map((customer) => {
if (customer.cliente_id === invoice.cliente_id) {
return customer.nombre_clte;
}
return null;
})}
</td>
</tr>
);
})}
This is the code inside the api route:
import { prisma } from "../../../prisma/index.ts";
export default async function getInvoicesData(req, res) {
let InvoiceData = await prisma.factura.findMany({
orderBy: {
nro_factura: "desc",
},
});
return res.status(200).json(InvoiceData);
}
I'm trying to create a paginated table using Nextjs, Prisma and SWR. The table will show a list of invoices ordered by their ID. This is an example of how it would look:
I'm using Prisma to fetch all the data to the api route and SWR to display the data on the UI and revalidate.This is the code for the UI so far:
{InvoiceData.map((invoice, key) => {
return (
<tr key={key}>
<td className="font-medium">
#
{invoice.nro_factura &&
Number(invoice.nro_factura).toString()}
</td>
<td className="whitespace-pre-wrap">
{invoice.fecha_emision &&
moment(invoice.fecha_emision).format("MMM DD, YYYY")}
</td>
<td className="font-medium">
${invoice.total && Number(invoice.total).toLocaleString()}
</td>
<td>
<Badge
status={
invoice.total === invoice.pagos
? 0
: invoice.anulada
? 2
: 1
}
title={
invoice.total === invoice.pagos
? "Pagado"
: invoice.anulada
? "Borrada"
: "Pendiente"
}
/>
</td>
<td className="customer-column">
{CustomersData.map((customer) => {
if (customer.cliente_id === invoice.cliente_id) {
return customer.nombre_clte;
}
return null;
})}
</td>
</tr>
);
})}
This is the code inside the api route:
import { prisma } from "../../../prisma/index.ts";
export default async function getInvoicesData(req, res) {
let InvoiceData = await prisma.factura.findMany({
orderBy: {
nro_factura: "desc",
},
});
return res.status(200).json(InvoiceData);
}
2 Answers
Reset to default 15There are two types of pagination: offset and cursor. Offset pagination works by taking the rows after skipping a certain number. Cursor pagination works by sorting the rows, then taking rows where the cursor property (i.e. ID) is greater than the passed cursor value (i.e. ID of the last row on the frontend side).
Offset
Source: Prisma Docs
There will need to be query parameters sent to the API route, for example page. This parameter can represent the page number at the bottom of the table. Multiply this minus 1 (page - 1) by the number of results per page ((page - 1) * size). Send this via Prisma to the database as skip. This will skip the first X rows of the table. You must also include the take parameter, otherwise you will pull all the results for the table, excluding the rows before the skip.
An example:
const results = await prisma.post.findMany({
skip: 10, // How many rows to skip
take: 10, // Page size
})
Then, your API query may look like: https://example./api/invoices?page=2. If your page size is 10, then page 2 will have rows "number" 11-20 (skip (2 - 1) * 10 = 10 ((page - 1) * size)).
However, his may bee slow with lots of rows. The alternative is cursor pagination (see below)
Cursor
Source: Prisma Docs
This will require a query sorted by ID. You still need to take a certain number of rows (i.e. 10), and pass the ID of the last item as the cursor as such (for pages 2 and above):
const firstQueryResults = await prisma.post.findMany({
take: 10, // Page size
skip: 1, // Skip the cursor row
cursor: {
id: myCursor, // The cursor - only on pages 2 and above
},
orderBy: {
id: 'asc', // Ordering results
},
})
This will take all of the rows where the ID is greater than the cursor (i.e. myCursor)
Use a query parameter such as cursor in the API request to set the cursor.
Cursor pagination is considerably faster, however, requires sorting. It is also possible to sort by a timestamp. Then, you will just need to sort by the timestamp in the query.
Frontend
On the frontend, you can then update the variable storing the results with the data from the API response. I would suggest you include a property on the API response with the number of rows (count from Prisma), so that you can divide by the page number and show the correct number of buttons at the bottom of the table.
For offset pagination, when the buttons are clicked, send the page number as a query parameter to the API.
For cursor pagination, it is easier to simply have a next and back button. For the next button, paginate by sending the cursor. For back, take a negative number of rows (i.e. -10) - this can by done by setting another query parameter such as direction (You will need to implement this logic). It is possible to have page numbers; all you will need to do is use the cursor and skip the difference in number of rows between the pages. (For example, going from page 2 to page 5 skips page 3. Therefore, you need to use the cursor from the last item on page 2, and skip the number of results from pages 3 and 4 (i.e. 20))
As per the SWR docs, you can create a variable that is passed to the API query for the page. This could be modified to be the cursor and direction.
function App () {
const [pageIndex, setPageIndex] = useState(0);
// The API URL includes the page index, which is a React state.
const { data } = useSWR(`/api/data?page=${pageIndex}`, fetcher);
// ... handle loading and error states
return <div>
{data.map(item => <div key={item.id}>{item.name}</div>)}
<button onClick={() => setPageIndex(pageIndex - 1)}>Previous</button>
<button onClick={() => setPageIndex(pageIndex + 1)}>Next</button>
</div>
}
Additional Improvements
You can dynamically set the limit or page size by sending that as a query parameter
export async function GET(request) {
try {
// Extract 'page' and 'limit' query parameters from the URL
const { searchParams } = new URL(request.url);
const page = parseInt(searchParams.get('page') || '1', 10); // Default to page 1 if not provided
const limit = parseInt(searchParams.get('limit') || '10', 10); // Default to 10 items per page if not provided
// Calculate the 'skip' and 'take' values
const skip = (page - 1) * limit;
const take = limit;
// Fetch paginated data from the database
const data = await prisma.photogallery_category.findMany({
orderBy: {
id: "desc",
},
skip,
take,
});
// Get the total count of records to include in the response for pagination metadata
const totalRecords = await prisma.photogallery_category.count();
const totalPages = Math.ceil(totalRecords / limit);
return NextResponse.json(
{
results: data,
totalRecords,
totalPages,
currentPage: page
},
{ status: 200 }
);
} catch (error) {
console.error("Error fetching photos:", error);
return NextResponse.json({ error: "Failed to fetch photos" }, { status: 500 });
} finally {
await prisma.$disconnect(); // Close the Prisma client connection
}
}
To fetch the first page with 10 records per page, you can make a request to:
/api/your-endpoint?page=1&limit=10