Counting the number of hits on a page seems like an easy enough task, but it requires a single source of truth that can be updated and retrieved from at any given moment. This is a challenge that calls for some sort of organized persistant storage service. This kind of service is also known as a database.

For the purpose of this website/blog, I chose to use a SQL Database, and more specifically PostgreSQL (a flavour of SQL) for a number of reasons.

• This database application is very simple, we're only storing the number of views/hits for every blog post on the website. This means the database paradigm that we choose isn't that important.
• I'm already a bit familiar with the SQL paradigm.
• I needed a database service that's easy to work with and connect to without being a local file.

• Vercel, the front-end hosting platform that I use doesn't support file system persistant storage because it is a serverless service. This means SQLite isn't adequete.

The Assumed Tools

• React (My front-end framework of choice)
• Next.js (A React full-stack framework that I use for my blog)
• PostgreSQL (Chosen flavour of SQL)
• Prisma (A modern JavaScript ORM (object relational mapper) which makes working with databases in JS, and especially TypeScript, painless)

1. The Setup

PostgreSQL Database

First, we need a PostgreSQL database server running to interact with. For development, we can host a PSQL db locally. Here are instructions for doing this on Windows/macOS/Linux.

On macOS, you can also use Postgres.app, a PostgreSQL installation packaged as a macOS app.

To connect to the database in our application, we need a connection string, which should be in the following format:

postgresql://<username>:<password>@<hostname>:<port>/<database>

We'll need this string later.

Prisma

Prisma is an ORM for Node.js and TypeScript (use TS, it enables cool things like autocompletion). It makes interacting with a database incredibly simple.

Install Prisma using your package manager of choice:

$  npm install prisma

Now we can invoke the Prisma CLI with the prefix npx. Run the initial command:

$  npx prisma init

This should

• create a new prisma directory with the schema.prisma file.
• create the .env file in the root directory.

Every database needs some sort of blueprint that describes how the data organization. This is called the schema.

This differs from the Prisma schema file, which is a config file for Prisma. It consists of 3 parts:

1. Data Sources ~ Database and connection details.
2. Generators ~ Specifies what clients should be generated based on the data model.
3. Data Model ~ specifies the shape of the data.

Here's my prisma.schema file:

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  slug       String   @id
  created_at DateTime @default(now())
  views      Int      @default(0)
}

Notice how url is set using an environment variable? This ensures privacy and portability. We can have a different database for development and production.

In .env, define the variable like this:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"

The model Post defines the shape of a record holding the viewcount for a specific blog post. Each Prisma Model maps to a PostgreSQL Table.

Each Post has a unique String field slug which we use to identify it, which is why it has the @id attribute.

The field views is the integer of hits a post has. The @default attribute defines the default value as 0, since the view count starts at 0 when a post is first recorded in the database.

Now, we can map the data model to the database schema, we use prisma migrate .

$  npx prisma migrate dev --name init

This creates an SQL migration file, and runs it against the database.

We're ready to interact with the database in our app 🙌.

2. Interacting with the Database

Prisma Client allows us to write queries to read and write data to our database. Every query using Prisma Client has the following boilerplate:

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

const prisma = new PrismaClient();

async function main() {
  // ... you will write your Prisma Client queries here
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

A query to read all Post records from the database would look something like this:

async function main() {
  // ... you will write your Prisma Client queries here
  const allPosts = await prisma.post.findMany();

  console.log(allPosts);
}

For our purposes, we have 2 types of interactions with the database:

1. Retrieving the number of views of a specific post.
2. Registering a new view for a specific post.

Retrieving Views: getViews

Let's create a new function called getViews which will

• Ask for a post's slug.
• Tell us the number of views that post has.

We'll use the same query boilerplate as above. We've already used the findMany() query to find all the Post records, but what about only finding a unique post based on the slug? This is where prisma.post.findUnique() query comes in. Read more

async function getViews(slug) {
  const prisma = new PrismaClient()

  async function main() {
    const post = await prisma.post.findUnique({
      where: {
        slug: slug,
      },
    })
    
    const views = post?.views;

    return views;
  }

  const views = main()
    .catch((e) => {
      throw e
    })
    .finally(async () => {
      await prisma.$disconnect()
    })

  return views;
}

Registering a View: registerView

Let's create another function called registerView which will

• Ask for a post's slug.
• Update the post's count on the database.

To update the number of hits for a post, we can use the prisma.post.update() query. Read more.

It's very similar to the findUnique() query, except we also pass in data to the query which specifies how to update the unique post. But how do we increment the view count? We could technically call our getViews() function, and then update views.

Alternatively, we can use Prisma's declarative Atomic number operations, namely the increment operator. Read more.

Here's how that'd look like:

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

export default async function registerView(slug: string) {
    const prisma = new PrismaClient()

    async function main() {
        const updatePost = await prisma.post.update({
            where: {
                slug: slug,
            },
            data: {
                views: {
                    increment: 1
                }
            }
        })
        console.log(updatePost);
    }

    main()
        .catch((e)=> {
            throw e
        })
        .finally(async () => {
            await prisma.$disconnect()
        })
        
}

Awesome! We're ready to make an API endpoint in Next.js.

3. Creating the API

Next.js is a fullstack framework that powers this blog, and provides an easy out of the box way to create a simple RESTful API using API Routes.

What the heck is an API?

An API (Application Programming Interface) acts as an intermediary between two applications or entities in an application.

In our case, we want an easy way to interact with the database from the front-end (our website/client).

We can "hook into" http requests to send back informative responses that can be used in our app.

Any file in the pages/api directory maps to a /api/* endpoint, everything to get this set up is conveniently handled by Next. We can even create dynamic routes, which allows us to create routes like:

GET api/posts/how-to-count-views (which would get the viewcount for this blog post, hopefully it's in the triple digits 😎).

Let's create a dynamic route by creating a new file at pages/api/posts/[slug].js.

There's very little boilerplate needed for defining a route in Next. We define a route by exporting a handler function for it:

export default function handler(req, res) {
  // Some code to find out how many views a post has.

  // Send a response telling the client what the view count is 147.
  res.status(200).json({ count: '147' })
}

The req object contains a query field which holds all query parameters from the request, including the slug.

We want to optionally register a view for the post depending on the request. In HTTP, this kind of request should use the POST method as it updates a resource on the back-end. The req object also has a method field that we can check.

Here's a simple layout for this handler:

import getViews from "../../../helpers/getViews";
import registerView from "../../../helpers/registerView";

export default async function handler(req) {
    const slug = req.query.slug.toString();

    // if no slug is given in the request.
    if (!slug) {
        res.status(400).json({ message: "empty slug" })
    }
    
    const count = await getViews(slug);

    if (req.method == 'POST') {
        await registerView(slug);
    }

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

4. Creating the View Counter

Now that we can request the number of views of a given post, we can make our lives easier by grouping the request logic, as well as the structure and appearance of the counter!

This is where React components can become so POWERFUL, even in a simple blog. We want to be able to declaritively place a view counter in a page of our site like so:

...
<ViewCounter slug={"how-to-count-views"}/>
...

Which should place it on the page like so:

psst... Reload the page to see the view count increment.

React's useEffect hook will allow us to request the views from our backend as soon as the component is mounted into the virtual DOM.

We also need to represent the view count as state of this component. This allows us to have a sort of loading state for the component. Obviously we use React's useState hook for this. We'll intialize the views to 0, but render ––– instead of the views

To make and resolve the request, we use the typical fetch promise chain.

For the eye icon, I used react-feather, a collection of icons as React components based on feather icons.

const ViewCounter = ({slug}: {slug; string}) => {
  const [views, setViews] = useState(0);

  useEffect(() => {
    fetch(`/api/posts/${slug}`)
      .then((response) => response.json())
      .then((data) => {
          console.log(data);
          setViews(data.count);
      })

      const registerView = () => {
          fetch(`/api/posts/${slug}`, {
              method: 'POST',
          });
      }

      registerView();
  }. []);

  return (
    <div className="container">
      <span>{(views ?? 0) ? views.toLocaleString() : "–––"}</span>
      <Eye />
    </div>
  )

}

All we need to do is add some styling using your preferred solution. I used CSS modules.

.container {
  padding: 10px;
  font-size: 1.1em;
  font-weight: 500;
  color: #5D81AB;
  font-family: Fira Code;
  border-radius: 10px;

  /* flexbox was used for positioning */
  display: flex;
  align-items: center;
  justify-content: center;
  gap: 10px;
}

And that's it! We created our very own view counter! 🥳

5. The Deployment Set Up

The only thing we have left to do is to set up our deployment. This seems like quite the challenge. If you've ever looked into hosting a database, then you've probably encountered a couple of database-as-a-service solutions.

The first easy and initially free way to deploy a database was offered by Heroku. It would've been easy to recommend them, but Heroku decided to kill their free tier.

Thankfully, there are so many more options:

I'd recommend Railway, spinning up a PostgreSQL database is super fast and simple! You don't even need to create an account, and the free tier offers 500 hours per month.

Now, in your deployment you just need to create a new env variable called DATABASE_URL and paste the connection URL from Railway.

To do this in Vercel, follow these instructions.