Implementing views and download counts by PlanetScale and Prisma in NextJS

When working in a large project (ex. blog and journal) the real time data count gives more flexible to the users and the site administrator to analyze the article interactions.

In this contrast, developers usually use main database fields to post the data. But sometimes serverless database may need. In this post I am sharing to do an article views and pdf download counts by the world’s most advanced serverless MySQL platform “PlanetScale” and the data access layer ORM “Prisma”. I’ll use NextJS 13 for this application.

Firstly create an account in the PlanetScale and start a project. Remember the id and password for connecting this in the CLI. Every project will gives an database url that what we’ll use for post and get data.

PlanetScale with Next.js

Right now, PlanetScale do not offering ui to do everything. Mostly have to do by command line interface. To get started make sure you have MySql globally installed (This is maybe only for linux users).

Setting up Prisma

Open a terminal in your project directory and run the following command to generate a Prisma folder as well as a .env file: npx prisma init

DATABASE_URL="mysql://root@127.0.0.1:3309/YOUR-DB-NAME-HERE"

Defining the schema

Now in your schema.prisma file, update your data source and client to the following:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
  relationMode = "prisma"
}

In the same file create the views and download models

model views{
  slug String @id @db.VarChar(128)
  count BigInt @default(1)
}

model downloads{
  url String @id @db.VarChar(128)
  count BigInt @default(1)
}

Tip: Install the Prisma VS Code extension to get syntax highlighting and autocompletion for your schema file.

Running the Database Locally

Once you have defined the database model, run the command to connect the database

pscale connect YOUR-DB-NAME-HERE main --port 3309

In this step, make sure that the main branch hasn’t been promoted to the production, because after deploy to production you can’t make the prisma schema. If you want to change the schema then you may change by creating a branch.

Now run the command to sync the prisma.schema with the PlanetScale schema

px prisma db push

You should see a success message!

Then, to verify the database is in sync with the schema use Prisma Playground by running the command

Tip: If you do not have the playground installed then install it first.

pscale shell prisma-playground main

Run this line and replace Inquiry with an entity you defined in your schema.:

describe views; //Don’t forget the semicolon here.

Now promote the main databse branch to production

pscale branch promote YOUR-DB-NAME-HERE main

Creating NextJS API route for Views

Now create a api route in the nextjs application “pages/api/views.js”

// api/views/[slug].js

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default async function handler(req, res) {
  // console.log("API route hit");
  // console.log("Slug: ", req.query.slug);

  const { slug } = req.query;

  if (req.method === "GET") {
    const view = await prisma.views.findUnique({
      where: {
        slug,
      },
    });

    if (view) {
      // Convert BigInt to regular number
      const count = Number(view.count);
      return res.status(200).json({ count });
    }

    return res.status(200).json({ count: 0 });
  }

  if (req.method === "POST") {
    const view = await prisma.views.upsert({
      where: {
        slug,
      },
      update: {
        count: {
          increment: 1,
        },
      },
      create: {
        slug,
        count: 1,
      },
    });

    // Convert BigInt to regular number
    const count = Number(view.count);
    return res.status(200).json({ count });
  }

  return res.status(405).end();
}

Now create a viewcounter component to get and post the data in the database

// Viewcounter.js

import { useEffect } from "react";
import useSWR from "swr";

const fetcher = (url) => fetch(url).then((res) => res.json());

export default function ViewCounter({ slug }) {
  const { data: { count = 0 } = {}, error } = useSWR(
    `/api/views/${slug}`,
    fetcher
  );

  // console.log(count);

  useEffect(() => {
    const registerView = async () => {
      await fetch(`/api/views/${slug}`, {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
        },
      });
    };

    registerView();
  }, [slug]);

  if (error) return <div>Failed to load views</div>;

  return (
    <div>
      <p className="text-3xl text-[#132F4C] font-semibold">{count}</p>
    </div>
  );
}

Now use the component in the post page

<ViewCounter slug={post.slug} />

Creating NextJS API route for Downloads

// api/downloads/[url].js

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

export default async function handler(req, res) {
  // console.log("API route hit");
  // console.log("Slug: ", req.query.url);

  const { url } = req.query;

  if (req.method === "GET") {
    const download = await prisma.downloads.findUnique({
      where: {
        url,
      },
    });

    if (download) {
      // Convert BigInt to regular number
      const count = Number(download.count);
      return res.status(200).json({ count });
    }

    return res.status(200).json({ count: 0 });
  }

  if (req.method === "POST") {
    const download = await prisma.downloads.upsert({
      where: {
        url,
      },
      update: {
        count: {
          increment: 1,
        },
      },
      create: {
        url,
        count: 1,
      },
    });

    // Convert BigInt to regular number
    const count = Number(download.count);
    return res.status(200).json({ count });
  }

  return res.status(405).end();
}

Create a component of DownloadCounter.js

// DownloadCounter.js
import useSWR, { mutate } from "swr";

const fetcher = (url) => fetch(url).then((res) => res.json());

export default function DownloadCounter({ url, slug }) {
  const { data } = useSWR(`/api/downloads/${slug}`, fetcher);
  const downloads = data?.count || 0;

  // console.log(downloads);

  const handleDownload = async () => {
    const response = await fetch(`/api/downloads/${slug}`, {
      method: "POST",
    });
    const data = await response.json();
    mutate(data, false);
    const updatedData = { ...data, count: data.count + 1 };
    mutate(updatedData, true);
  };

  return (
    <div className="">
      <button
        className="bg-green-500 p-1 rounded my-4"
        onClick={handleDownload}
      >
        <a
          href={url}
          className="border border-orange-800 text-orange-800 rounded px-4 py-1 font-[Inter] text-base font-medium"
        >
          {downloads}
        </a>
      </button>
    </div>
  );
}

Now use this in the article page

 <ViewCounter url={post.pdf_file} />

Follow this post for more details How to set up Next.js with Prisma and PlanetScale