How to create a paginated table using Nextjs, Prisma and SWR?

Question

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

Answer 1

There 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.com/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 become 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