CODEWITHSUNDEEP
reactjsnext.jspromiseprisma
skorpio
skorpio

Reputation: 209

What is the most performant way to do both a DB query and a DB update in a single API call in Next.js with Prisma

I have a API route that returns a blogpost by ID.

I also want to update the viewcount on this blogpost in the same API call that fetches the post.

What is the fastest way to do this? I want to show the blogpost as fast as possible, while also incrementing its counter.

I use Next.js and Prisma ORM with PostgreSQL from supabase.

I was thinking of using Promise.All like this:

export default async function handle(req, res) {
   const { id } = req.query;

   const blogpost = prisma.post.findUnique({
     where: {
       id: id
     });

   const incrementPost = prisma.post.update({
     where: {
      id: id
     },
     data: {
       views: { increment: 1 }
     });
   
  Promise.all([blogpost, incrementPost])
  .then((values) => {
     res.status(200).json({values[0]})
   })
  .catch((error) => {
    res.status(500)
  });
}

Is this a good way to solve this?

User model could be like this:

model User {
 id String @id @default(cuid())
 title String
 body String
 views Int @default(0)
}

Upvotes: 0

Views: 303

Answers (2)

skorpio
skorpio

Reputation: 209

Thanks to Shea for providing the solution that works for me!

I made a @@unique([creatorId, slug]) in my model, and used the new UserWhereUniqueInput in Prisma 4.5.0 to run an update on my Post model.

I only have username and slug to identify the post from my frontend.

let post = await prisma.post.update({
      where: {
        creatorId_slug: {
          creatorId: user.id,
          slug: slug,
        },
        deletedAt: null,
        published: {
          not: null
        }
      },
      data: {
        views: {
          increment: 1
        }
      },
      include: {
         image: true
      }
}

This returns the post and increments the "views" in one go, and solves my problem!

Simplified Model from my real app:

model Post {
  id String @id @default(cuid())

  published DateTime?
  creatorId String

  userId Int
  user   UserInfo @relation("posts", fields: [userId], references: [id], onDelete: Cascade)

  title  String
  body   String
  slug   String

  @@unique([creatorId,slug])
}

Upvotes: 0

Shea Hunter Belsky
Shea Hunter Belsky

Reputation: 3238

If you update something in Prisma, it will return the updated item as part of the update operation. From your code, incrementPost and blogpost will both refer to the same post. So you can just return incrementPost from your API.

Documentation: https://www.prisma.io/docs/concepts/components/prisma-client/crud#update-a-single-record

export default async function handle(req, res) {
    const {
        id
    } = req.query;

    prisma.post.update({
            where: {
                id: id
            },
            data: {
                views: {
                    increment: 1
                }
            })
        .then((updatedpost) => {
            res.status(200).json(updatedpost)
        });
        .catch(() => res.status(500));
    })
}

Upvotes: 1

Related Questions