Live updating page views with Supabase and Next.js

This post starts with a brief introduction. If you want to jump down to the tutorial, click here. You can also see the live updating views in action in the top right of this page! The analytics events themselves require no JavaScript and occur completely on the server thanks to the new (in beta) Next.js middleware. If you have JavaScript enabled, the view count should live update thanks to Supabase Realtime. You can try it by opening the page in a new tab and eyeing the view count.

Contents

• Motivations
• How to do it
  • Supabase: Setting up the Database and Realtime
  • Next.js: Adding server-side analytics
  • Next.js: Adding a live updating view counter
• Next steps

Motivations

When possible, I avoid requiring JavaScript and exposing my users to unnecessary tracking. The problem is that I decided I wanted to track views on my blog, and Next.js analytics don't provide the vanity metrics I'm looking for. I previously used Matomo analyzing my nginx log, and while matomo is a fantastic piece of open-source software, I don't want to mess with the PHP API (or use PHP for the first time in ~7 years) in order to get post views on the front-end.

Coincidentally, Supabase announced their Series B a few days ago, and that pushed me to finally give it a try and see how their product works. If you're unfamiliar with Supabase, they're an open-source Firebase alternative with a hosted option: you get a postgres database and fancy dashboard and CLI for creating tables, edge functions, authentication and more. They also make it incredibly easy to subscribe to database updates, making syncing and connectivity a breeze.

So far, I love everything about it except the dashboard: everything is so slow. Creating a new, unnamed SQL query can take 5 seconds:

Dashboard slowness aside, this tutorial will walk you through using Supabase and Next.js middleware to have live-updating view counts and server-side analytics.

How

Note that this tutorial expects you to already have a Next.js project. If you don't, I recommend following the official Next.js tutorial.

Supabase: Setting up the Database and Realtime

1. Register an account with Supabase here: https://app.supabase.io/

2. Create a Supabase project and fill out the new project form.

   

   If the above step confuses you, you need to click your organization in the dropdown of the 'new project' button. That took me a little too long to figure out...

   

3. Navigate to the Table Editor in the left nav bar and click new table. Fill it out to match below, and be sure to click the gear icon to deselect allowing null: Alternatively, if you're comfortable with SQL, you can write and save a SQL query that you can use again in the future. It may look something like this:

   CREATE TABLE analytics (
       id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       slug text UNIQUE NOT NULL,
       views bigint DEFAULT 1 NOT NULL,
       updated_at timestamp DEFAULT NOW() NOT NULL
   );
   

   CREATE TABLE analytics (
       id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       slug text UNIQUE NOT NULL,
       views bigint DEFAULT 1 NOT NULL,
       updated_at timestamp DEFAULT NOW() NOT NULL
   );
   

   You can input the query in the SQL Editor tab in the left nav bar a few options below the Table Editor.

4. Now, we need to add a stored procedure (AKA a stored SQL function we can call from the Supabase API). Navigate to the Database tab in the left nav bar and select the Functions menu item inside:

   

   Note that Supabase offers Database Functions and Edge Functions — we're using Database Functions!

5. Click Create a new Function in the top right and and fill the settings out to match below. We're establishing a name we can use to reference the function, the schema (AKA what tables it has access to), the return value (void in this case), and a single argument that is the page path.

   The query is as follows:

   BEGIN
       IF EXISTS (SELECT FROM analytics WHERE slug=page_slug) THEN
           UPDATE analytics
           SET views = views + 1,
               updated_at = now()
           WHERE slug = page_slug;
       ELSE
           INSERT into analytics(slug) VALUES (page_slug);
       END IF;
   END;
   

   BEGIN
       IF EXISTS (SELECT FROM analytics WHERE slug=page_slug) THEN
           UPDATE analytics
           SET views = views + 1,
               updated_at = now()
           WHERE slug = page_slug;
       ELSE
           INSERT into analytics(slug) VALUES (page_slug);
       END IF;
   END;
   

   The function updates the row if it exists (if the page_slug argument is in the slug column), otherwise it creates a new row.

   P.S. I believe I found this query somewhere online but now I can't find the original source — if you recognize it please let me know so I can provide credit!

6. Now we need to enable Realtime so Supabase broadcasts our changes when we subscribe. Navigate to the Database item in the navigation bar and select Replication in the side menu. You should see the following page:

7. Click the 0 tables button the Source column and toggle the new table you created (I called mine analytics):

8. Finally, navigate to the Settings at the bottom of the navigation and select the API submenu. Copy down the anon public API key, the service_role API key, and the project URL in the Configuration box: we'll need them in the next section.

Do not ever expose or share your service_role key — it bypasses row-level security and should only ever be used and seen by your server or you.

Next.js: Adding server-side analytics

1. First, we need to add the API keys and project URL to our environment, which will let us access them in our code. If you're hosting with Vercel, I recommend using their CLI or dashboard to add the keys, which you can read about here. Otherwise, create or modify your .env file to contain the following:

   NEXT_PUBLIC_SUPABASE_URL=<your URL>
   NEXT_PUBLIC_SUPABASE_ANON_KEY=<anon_public_key>
   SUPABASE_SERVICE_KEY=<service_role_key>
   

   NEXT_PUBLIC_SUPABASE_URL=<your URL>
   NEXT_PUBLIC_SUPABASE_ANON_KEY=<anon_public_key>
   SUPABASE_SERVICE_KEY=<service_role_key>
   

   If you're just creating the .env, be sure to add it to your .gitignore so it won't be pushed online.

   For security, Next.js doesn't automatically expose your environment variables to the client. If you want to do that, you need to prefix the key with NEXT_PUBLIC_. This is a great feature, but don't let it stop you from manually verifying you aren't exposing your service key elsewhere.

2. Add the @supabase/supabase-js package from npm to your project:

   yarn add @supabase/supabase-js
   

   yarn add @supabase/supabase-js
   

   or

   npm install @supabase/supabase-js
   

   npm install @supabase/supabase-js
   

3. Create two files, supabase/public.js and supabase/private.js, where you want to; I put them in lib. Private will contain a connection with the service_role key while public will use the anon one. Two files aren't necessary, but I like distinguishing them so I can be sure which I'm using.

   They should each look something like this:

   import { createClient } from '@supabase/supabase-js'
   
   if (
     !process.env.NEXT_PUBLIC_SUPABASE_URL ||
     !process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
   ) {
     throw new Error('Missing env vars SUPABASE_URL or SUPABASE_ANON_KEY')
   }
   
   const publicClient = createClient(
     process.env.NEXT_PUBLIC_SUPABASE_URL,
     process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
   )
   
   export default publicClient
   

   import { createClient } from '@supabase/supabase-js'
   
   if (
     !process.env.NEXT_PUBLIC_SUPABASE_URL ||
     !process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
   ) {
     throw new Error('Missing env vars SUPABASE_URL or SUPABASE_ANON_KEY')
   }
   
   const publicClient = createClient(
     process.env.NEXT_PUBLIC_SUPABASE_URL,
     process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
   )
   
   export default publicClient
   

   import { createClient } from '@supabase/supabase-js'
   
   if (!process.env.SUPABASE_URL || !process.env.SUPABASE_SERVICE_ROLE_KEY) {
     throw new Error(
       'Missing env vars SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY'
     )
   }
   
   const privateClient = createClient(
     process.env.SUPABASE_URL,
     process.env.SUPABASE_SERVICE_ROLE_KEY
   )
   
   export default privateClient
   

   import { createClient } from '@supabase/supabase-js'
   
   if (!process.env.SUPABASE_URL || !process.env.SUPABASE_SERVICE_ROLE_KEY) {
     throw new Error(
       'Missing env vars SUPABASE_URL or SUPABASE_SERVICE_ROLE_KEY'
     )
   }
   
   const privateClient = createClient(
     process.env.SUPABASE_URL,
     process.env.SUPABASE_SERVICE_ROLE_KEY
   )
   
   export default privateClient
   

   The only difference between private and public is the second argument passed to createClient: change that depending on which you want to use.

4. Create an API route for submitting views. You could do this directly from the middleware but adding it as a serverless function gives you more freedom to extend it in the future. Also note that anyone can trigger this function, so you may want to protect it by adding a new environment variable and sending that with the request from the middleware. Then, only your server requests will ever have that value and can be verified as legitimate.

   import { NextApiRequest, NextApiResponse } from 'next'
   import supabase from '@lib/supabase/private'
   
   const handler = async (req: NextApiRequest, res: NextApiResponse) => {
     if (req.method === 'POST') {
       // `increment_views` is the name we assigned to the function in Supabase, and page_slug is the argument we defined.
       await supabase.rpc('increment_views', { page_slug: req.body.slug })
       return res.status(200).send('Success')
     } else {
       return res.status(400).send('Invalid request method')
     }
   }
   
   export default handler
   

   import { NextApiRequest, NextApiResponse } from 'next'
   import supabase from '@lib/supabase/private'
   
   const handler = async (req: NextApiRequest, res: NextApiResponse) => {
     if (req.method === 'POST') {
       // `increment_views` is the name we assigned to the function in Supabase, and page_slug is the argument we defined.
       await supabase.rpc('increment_views', { page_slug: req.body.slug })
       return res.status(200).send('Success')
     } else {
       return res.status(400).send('Invalid request method')
     }
   }
   
   export default handler
   

   The above code is in TypeScript; if you want JavaScript, you can remove the first import and change the function definition to const handler = async (req, res) =>. I included this to try and encourage you to try TypeScript if you aren't already; it's very helpful for exploring unfamiliar APIs, like the Next.js requests and responses.

5. Create a pages/_middleware.{jsx,tsx} file. The middleware will run on the server before every page request; nothing inside it is ever exposed to the client so we can safely use our private Supabase lib. However, we'll actually just send a POST request to our API handler instead.

   import { NextMiddleware, NextResponse } from 'next/server'
   const PUBLIC_FILE = /\.(.*)$/
   
   export const middleware: NextMiddleware = async (req, event) => {
     const pathname = req.nextUrl.pathname
     // we ignore running this middleware when the request is to a serverless function or a file in public/.
     // This is purely optional.
     const isPageRequest =
       !PUBLIC_FILE.test(pathname) && !pathname.startsWith('/api')
   
     const sendAnalytics = async () => {
       const slug = pathname.slice(pathname.indexOf('/')) || '/'
   
       // Change your production URL!
       const URL =
         process.env.NODE_ENV === 'production'
           ? 'https://maxleiter.com/api/view'
           : 'http://localhost:3000/api/view'
       const res = await fetch(`${URL}`, {
         method: 'POST',
         headers: {
           'Content-Type': 'application/json',
         },
         body: JSON.stringify({
           slug,
         }),
       })
   
       if (res.status !== 200) {
         console.error('Failed to send analytics', res)
       }
     }
   
     // event.waitUntil is the real magic here:
     // it won't wait for sendAnalytics() to finish before continuing the response,
     // so we avoid delaying the user.
     if (isPageRequest) event.waitUntil(sendAnalytics())
     return NextResponse.next()
   }
   

   import { NextMiddleware, NextResponse } from 'next/server'
   const PUBLIC_FILE = /\.(.*)$/
   
   export const middleware: NextMiddleware = async (req, event) => {
     const pathname = req.nextUrl.pathname
     // we ignore running this middleware when the request is to a serverless function or a file in public/.
     // This is purely optional.
     const isPageRequest =
       !PUBLIC_FILE.test(pathname) && !pathname.startsWith('/api')
   
     const sendAnalytics = async () => {
       const slug = pathname.slice(pathname.indexOf('/')) || '/'
   
       // Change your production URL!
       const URL =
         process.env.NODE_ENV === 'production'
           ? 'https://maxleiter.com/api/view'
           : 'http://localhost:3000/api/view'
       const res = await fetch(`${URL}`, {
         method: 'POST',
         headers: {
           'Content-Type': 'application/json',
         },
         body: JSON.stringify({
           slug,
         }),
       })
   
       if (res.status !== 200) {
         console.error('Failed to send analytics', res)
       }
     }
   
     // event.waitUntil is the real magic here:
     // it won't wait for sendAnalytics() to finish before continuing the response,
     // so we avoid delaying the user.
     if (isPageRequest) event.waitUntil(sendAnalytics())
     return NextResponse.next()
   }
   

6. You should now verify your views table is updating by loading pages and viewing the Supabase dashboard. If it's not updating, verify your connection details and try exploring the Supabase logs.

Next.js: Adding a live-updating view counter

1. Now we can finally add our updating view count. In your React component, import your public Supabase client.

   import supabase from '@lib/supabase/public'
   

   import supabase from '@lib/supabase/public'
   

2. Make views a member of your state:

   import supabase from '@lib/supabase/public'
   
   const Component = () => {
       // You may want to pass in an initial value for views if you're using getStaticPaths or similar.
       // That way, it won't start at 0 when the client loads.
       const [views, setViews] = useState(0)
       return (...)
   }
   

   import supabase from '@lib/supabase/public'
   
   const Component = () => {
       // You may want to pass in an initial value for views if you're using getStaticPaths or similar.
       // That way, it won't start at 0 when the client loads.
       const [views, setViews] = useState(0)
       return (...)
   }
   

3. Add a useEffect hook to subscribe and unsubscribe to changes on mount or unmount respectfully. This is all it takes to subscribe to recieve the changes made to the analytics table.

   import supabase from '@lib/supabase/public'
   
   const Component = () => {
     // You may want to pass in an initial value for views if you're using getStaticPaths or similar.
     // That way, it won't start at 0 when the client loads.
     const [views, setViews] = useState(0)
   
     // Subscribe to view updates.
     // Note that `id` is something I store manually on page creation so I can associate
     // each page with itself from the DB.
     // In practice, I recommend looking into subscribing to low level changes:
     // https://supabase.com/docs/reference/javascript/subscribe#listening-to-row-level-changes
     useEffect(() => {
       const sub = supabase
         .from('analytics')
         .on('UPDATE', (payload) => {
           if (payload.new.id === id) {
             setViews(payload.new.views)
           }
         })
         .subscribe()
   
       // The return function of a useEffect is fired on unmount
       return () => {
         sub.unsubscribe()
       }
     }, [id])
   
     return (
       <div>
         {views} {views.length === 1 ? 'view' : 'views'}
       </div>
     )
   }
   

   import supabase from '@lib/supabase/public'
   
   const Component = () => {
     // You may want to pass in an initial value for views if you're using getStaticPaths or similar.
     // That way, it won't start at 0 when the client loads.
     const [views, setViews] = useState(0)
   
     // Subscribe to view updates.
     // Note that `id` is something I store manually on page creation so I can associate
     // each page with itself from the DB.
     // In practice, I recommend looking into subscribing to low level changes:
     // https://supabase.com/docs/reference/javascript/subscribe#listening-to-row-level-changes
     useEffect(() => {
       const sub = supabase
         .from('analytics')
         .on('UPDATE', (payload) => {
           if (payload.new.id === id) {
             setViews(payload.new.views)
           }
         })
         .subscribe()
   
       // The return function of a useEffect is fired on unmount
       return () => {
         sub.unsubscribe()
       }
     }, [id])
   
     return (
       <div>
         {views} {views.length === 1 ? 'view' : 'views'}
       </div>
     )
   }
   

4. And there you have it! Automatically updating analytics that don't require JavaScript to be recorded! You can verify it works by opening the page in a new tab.

Next steps

There's quite a lot you can do from here, but here are some recommendations:

1. Only allow unique visitors, perhaps by storing IP hashes or using localStorage. This could probably be accomplished with Supabase Edge functions?
2. Build a visualization page for interacting and querying the results
3. Expand your analytics to include things like keeping track of the referer value.
4. Ignore certain user agents to reflect a more accurate view count