Real-time Page Views with Next.js and Supabase
Tags: webdev, react, javascript, nextjs
Published: 2020-12-31 14:27:11

Let's Build our own real-time page view tracker using Next.js as the frontend framework and a Postgres database hosted by Supabase.

One of the best ways to understand how your blog posts are performing is by tracking page views. You can begin to understand what posts and information your readers care more about based on number of views. You can then focus your efforts on the more important content.

Many people use tools like [Google](https://analytics.google.com/) or [Fathom](https://usefathom.com/ref/45XEAJ) Analytics for tracking traffic. With the rise of Ad-Blockers, your traffic collected with these services are not necessarily accurate.

Why not build a page view tracker with your own API routes? The beauty of doing so, ad-blockers will not risk blocking the request without possibly breaking website functionality. This will allow for a more accurate count of page views.

## Tools Used

### Next.js

We will be using [Next.js](https://nextjs.org/) as our frontend framework. It gives us the power of prerendered React.js, serverless API routes, and typescript with minimal configuration.

### Supabase

[Supabase](https://supabase.io/) is an open-source alternative to Firebase. They offer an API wrapped Postgres Database with real-time subscriptions.

## Set up your database hosted by Supabase

Sign in or Sign up for Supabase. Then create a new project. You can either use an existing organization (if you have one) or create a new one.

![Create a new project in Supabase screenshot](https://dev-to-uploads.s3.amazonaws.com/i/you7bo38j3h5wv2w8vla.png)

Enter your project name, add a **strong** password for your database, and select which region you want your database.

![Set the name and password for your database screenshot](https://dev-to-uploads.s3.amazonaws.com/i/nw7xod1f3cw70u878kem.png)

Once the database finishes setting up, we need to create a table to store our pages and the total view count. Navigate to the SQL editor and switch to the `Query-1` tab.

![SQL Editor and Highlighted Query-1 tab screenshot](https://dev-to-uploads.s3.amazonaws.com/i/u6nhj6lkis469qze9u3x.png)

You can then paste the bellow SQL query and run it to create a new table called `pages` with columns `id`, `slug`, `view_count`, and `updated_at`.

```sql
CREATE TABLE pages (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  slug text UNIQUE NOT NULL,
  view_count bigint DEFAULT 1 NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL
);
```

We need to run one more query to add a stored procedure to our database. A stored procedure allows us to add or extend functionality to the database.

Let's first breakdown the query below.

```sql
CREATE OR REPLACE FUNCTION increment_page_view(page_slug TEXT)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT FROM pages WHERE slug=page_slug) THEN
        UPDATE pages
        SET view_count = view_count + 1,
            updated_at = now()
        WHERE slug = page_slug;
    ELSE
        INSERT into pages(slug) VALUES (page_slug);
    END IF;
END;
$$;
```

1. This adds a function called `increment_page_view` that has a parameter of `page_slug`.
2. It sets the language to `plpgsql` which is specific to Postgres.
3. When you trigger the function, it checks to see if a row exists where `slug` equals the parameter `page_slug`.
4. If it **exists**, update the row by adding `1` to `view_count` and setting `updated_at` with `now()`.
5. If it does **not exist**, insert a new row with `slug` equal to `page_slug`.

Now that we know what this function actually does, open a new query tab in the SQL editor. Paste the query in and run it.

Your database should be fully set up to track page views now!

The final thing we need to do is get your API keys located in **API** under **settings**.

![API Keys screenshot](https://dev-to-uploads.s3.amazonaws.com/i/5uzfltc5t7ja65bdf8ua.png)

Now add them to your next.js project's `.env` file.

```bash
NEXT_PUBLIC_SUPABASE_URL=             # URL
NEXT_PUBLIC_SUPABASE_CLIENT_KEY=      # public
SUPABASE_SERVICE_KEY=                 # service_role
```

## Adding the page view feature to Next.js

Let's first install some dependencies we will use:

1. `@supabase/supabase-js`: Client to connect and query your database hosted by Supabase.
2. `swr`: React Hook library for data fetching.

```bash
npm install @supabase/supabase-js swr
```

Now you can create a file `lib/supabase-admin` to initialize your Supabase client.

```ts
import { createClient } from '@supabase/supabase-js';

const supabaseUrl: string = process.env.NEXT_PUBLIC_SUPABASE_URL || '';
const supabaseServerKey: string = process.env.SUPABASE_SERVICE_KEY || '';

const SupabaseAdmin = createClient(supabaseUrl, supabaseServerKey);

export { SupabaseAdmin };
```

> **Important**: Only use your ServerKey on server side logic. You do not want to expose your ServerKey publicly. If you want to use the client on your frontend code, use `NEXT_PUBLIC_SUPABASE_CLIENT_KEY` instead of `SUPABASE_SERVICE_KEY`

We will need to create an API route to increment and fetch the page views. `/pages/api/views/[slug].ts`

```ts
import { NextApiRequest, NextApiResponse } from 'next';
import { SupabaseAdmin } from '@lib/supabase-admin';

export default async (req: NextApiRequest, res: NextApiResponse) => {
  if (req.method === 'POST') {
    // Call our stored procedure with the page_slug set by the request params slug
    await SupabaseAdmin.rpc('increment_page_view', { page_slug: req.query.slug });
    return res.status(200).json({
      message: `Successfully incremented page: ${req.query.slug}`
    });
  }

  if (req.method === 'GET') {
    // Query the pages table in the database where slug equals the request params slug.
    const { data } = await SupabaseAdmin.from('pages').select('view_count').filter('slug', 'eq', req.query.slug);

    if (data) {
      return res.status(200).json({
        total: data[0]?.view_count || null
      });
    }
  }

  return res.status(400).json({
    message: 'Unsupported Request'
  });
};
```

We can now create a component to fetch and display the page view count. `/components/PageViews.tsx`

```tsx
import useSWR from 'swr';
import { FC } from 'react';

interface PageViewsProps {
  slug: string;
}

const fetcher = async (input: RequestInfo) => {
  const res: Response = await fetch(input);
  return await res.json();
};

const PageViews: FC<PageViewsProps> = ({ slug }) => {
  const { data } = useSWR(`/api/views/${slug}`, fetcher);

  return <>{data?.total ? `${data.total} views` : `–––`}</>;
};

export default PageViews;
```

Finally, you can add the logic to your pages to increment pages views and display it. Example: `/components/BlogLayout.tsx`

```tsx
import { FC, useEffect } from 'react';
import PageViews from '@components/PageViews';

interface BlogLayoutProps {
  slug: string;
}

const BlogLayout: FC<BlogLayoutProps> = ({ slug }) => {
  useEffect(() => {
    fetch(`/api/views/${slug}`, {
      method: 'POST'
    });
  }, [slug]);

  return (
    <div>
      <h1>{slug}</h1>
      <div>
        <PageViews slug={slug} />
      </div>
    </div>
  );
};

export default BlogLayout;
```

Whenever you load your page, it should make a `POST` request to increment your page views. If you wanted, you could check out the Supabase Table Editor view and see your table update as you increment page views on your website.

![Verify your table is populated on Supabase screenshot](https://dev-to-uploads.s3.amazonaws.com/i/8i9okv8bupqirjecuucy.png)

Bam! You now have a real-time page view tracker for your blog or whatever page you want.

---

- Supabase [Docs](https://supabase.io/docs)
- Next.js [Docs](https://nextjs.org/docs/getting-started)
- SWR [Docs](https://swr.vercel.app/)
- Follow me on [Twitter](https://twitter.com/CodeByCorey) for random posts about tech and programming.