Adding Metrics to a Next.js Blog Post using Planetscale and Prisma
April 14, 2024: Please be advised that since writing this, Planetscale has shut down its hobby tier 😭
Adding metrics to a post is both a fun coding challenge as well as a nice to have. We are going to utilize a PlanetScale database, which has a generous "Hobby" plan. This plan includes
5 GB of storage
1 billion row reads per month
10 million row writes per month
1 production branch
1 development branch
which is more than enough for our use case! We are also going to use Prisma to define all of our models.
Planetscale's documentation is pretty solid, and they have a quickstart guide.
Follow the instructions for creating a database then promote the default main branch to production. Planetscale follows a git-like branching concept, which your development branch intended for applying new schema changes.
Promoting the branch will protect it from direct schema changes, make it more available, and ensure there are daily scheduled backups.
You will also want to make a new branch for development. Make sure you grab the connection strings to your main branch, and development branch, and store them somewhere safe.
Once your Planetscale database is up and running, we can go ahead and set up Prisma. Prisma is a type-safe database client, where the one of the main selling points is the "human-readable" schema files. We will start by installing Prisma.
then, we will initialize the prisma part of the project using
The above command creates the /prisma directory, with a file called schema.prisma. This is where we will define our schemas. The command also creates a .env file in the project directory.
I keep many of my helper methods in a directory called /lib. There, we are going to create a new file called prisma.ts. Our goal with this
helper is to follow the best Next.js practices according to Prisma.
You may get an error in your IDE saying that it can't find the client. Not to worry! In the project directory run npx prisma db push. This command will push these changes to your database development branch, and generate a typesafe client.
Now that we got the setup out of the way, we can move on to adding the feature -- which is the post metrics! I use the term "backend" loosely here, basically just referring to defining our schema in Prisma and defining the Next.js API routes and React hooks to access the data.
✨ Note: this next part assumes you followed the last part of this
series that set up
content using contentlayer
First, let's begin by adding our views. Breaking this down conceptually is pretty simple... We are going to store views in the database via the slug of post (using the slug as the index on the table).
We are going to open up prisma/schema.prisma and add a table called Stats. Eventually, we can split our schemas into separate files, but for
the time being they won't be too complex and can stay in the same file.
We can create a new table using the model keyword, and then add two columns, slug and views.
After making any schema changes, be sure to run npx prisma db push to update the database tables and regenerate the client.
Now, we can use a Next.js API Route in tandem with the global prisma instance we created to query our database for a post's number of views.
In the /pages directory, create the following structure.
Open /pages/api/views/[slug].ts in your editor of choice, and create the handler function.
We want to handle two cases, the POST and the GET. We also want to make sure we have adequate error handling.
On the POST, we will use the prisma client's upsert method to handle the
request. Basically, we will increment the view field on update, or create a
new record with the slug.
The GET is simpler, we will just find the unique record associated with the
slug.
With the actual "API" route defined, we can go ahead and write the React Hook to use it.
Adding "likes" to our posts is a pretty similar concept, plus a tiny bit of logic. When you're talking about likes, you want users of your site to have the articles they liked to be remembered the next time they visit. So essentially, we don't want the likes to be wiped the next time a user visits the site.
Well, we represent the concept of a Session within our database. We will utilize the user's IP address (hashed for privacy) along with the slug of the article to generate a ID for the row in the database, and each row in the database will represent a like.
Create a folder inside of /pages/api called likes, and then create a file called [slug].ts.
Here, we will handle the GET, POST, and DELETE. We will start out by using crypto to hash the user's IP address.
yarn add crypto
Be sure to add a IP_ADDRESS_SALT to your .env file, and then you can use the createHash method to hash the user's IP address.
We can use this userId to create an ID for our session.
To handle the GET, we will need to return whether the current user (IP address) has previously liked this article, and the number of likes for the article. We can accomplish this using a Promise.all
Then, to handle the POST, we will need to determine whether there exists a row in the Session table that matches the user's IP. If there is a row, there is no change to the number of likes. Otherwise, we create a new row in the Session table and increment the number of likes in the Stats table.
Finally, we want to allow user's to un-like the post (sadly), so we want to handle the DELETE. Here, we will remove the user's IP from the Session table, and decrement the likes column in the Stats table.
The "complicated" part is now out of the way, so we can move on to displaying our metrics. I will start with a simple <span> component that simply displays the numerical value.
We can use that component inside a reusable PostMetrics component that displays both views and likes of a given article.
Before we can do that, we need to introduce the concept of polling into our application. We have data that could (theroretically) be changing any given second, we don't want to use our applications resources to constantly be updating this information. We want to poll this data in intervals.
Create a new file in /lib called usePolling.ts. In this file, we will define a hook to poll on an interval.
We can then use this hook to determine when we should poll for new data within the article metrics display.