Track of Page Views Using Supabase on a Sveltekit project

Track of Page Views Using Supabase on a Sveltekit project

Written by jeblister on Mar 29th, 2023 ・ Views ・ Report Post

jeblister Visit User Profile

To keep track of page views on a SvelteKit project using Supabase, the following steps can be taken based on the information provided in the sources:

1. Set up a database hosted by Supabase

  • Register an account with Supabase
  • Create a new Supabase project
  • Navigate to the Table Editor in the left nav bar and click new table
  • Fill out the new table form to match the following:
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
);
  • Be sure to click the gear icon to deselect allowing null

2. Create a function to increment page views

  • On SQL Editor menu, click Create a new Query that will create a function
  • Create a SQL function called update_views that increments the view count in the views table. This function should accept a text argument that checks whether the blog post already exists in the table and if it does, it increments its view count by 1. If it doesn’t, it creates a new entry for the post whose view count defaults to 1.
 CREATE OR REPLACE FUNCTION update_views(page_slug TEXT)
 RETURNS void
 LANGUAGE plpgsql
 AS $$
 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;
 $$;

3. Find these environment variables in your Supabase project settings

PUBLIC_SUPABASE_URL=https://your-project.supabase.co
PUBLIC_SUPABASE_ANON_KEY=your-anon-key

add those values to the .env file

3. Setting up SevelteKit to Add a live-updating view counter

  • Import your public Supabase client
// src/lib/database/index.ts
import { createClient } from '@supabase/supabase-js'
import {
PUBLIC_SUPABASE_URL,
PUBLIC_SUPABASE_ANON_KEY,
} from '$env/static/public'

export const supabase = createClient(
PUBLIC_SUPABASE_URL,
PUBLIC_SUPABASE_ANON_KEY
)

- Create a function to update the views count

// src/lib/database/index.ts
export async function update_iews(slug: string): Promise<void> {
try {
await fetch(`/api/views/${slug}`, {
method: 'post',
body: JSON.stringify(slug),
headers: { 'Content-Type': 'application/json' },
})
} catch (error) {
console.error(`Update views: ${error.message} for /api/views/${slug}`)
}
}

- Create a function to get total views of a all page

// src/lib/database/index.ts
export async function getViews(): Promise<View[]> {
	try {
		const response = await fetch(`/api/views.json`)
		return await response.json()
	} catch (error) {
		console.error(`getViews: ${error.message}`)
	}
}

- Make views a member of your store


#/src/lib/stores/views.ts
import { writable } from 'svelte/store'
import { browser } from '$app/environment'

import { getViews } from '$lib/database'

if (browser) {
	getViews().then((result) => {
		views.set(result)
	})
}

export const views = writable([])

- Create the Sveltekit routes to update views on the database and get total views


# src/routes/api/views/[slug]/+server.ts

import { json } from '@sveltejs/kit'

import type { RequestHandler } from '@sveltejs/kit'

import { supabase } from '$lib/database'

export const POST: RequestHandler = async ({ request }) => {
const slug = await request.json()

    try {
    	// Call our stored procedure with the page_slug set by the request params slug
    	await supabase.rpc('update_views', { page_slug: slug })
    	return json({
    		success: true,
    		message: `Successfully incremented page: ${slug}`,
    	})
    } catch (err) {
    	return json({ error: err.message })
    }

    return new Response(undefined, { status: 303, headers: { location: '/' } })

}

export const GET: RequestHandler = async ({ params }) => {
	const slug = await params.slug

	try {
		// Query the analytics table in the database where slug equals the request params slug.
		const { data } = await supabase
			.from('analytics')
			.select('slug, views')
			.filter('slug', 'eq', slug)
		console.log('Return total view 🖱', data)
		return json({
			total: data[0]?.views || null,
		})
	} catch (err) {
		return json({ error: err.message })
	}
	return new Response(undefined, { status: 303, headers: { location: '/' } })
}

- Call the update on the client side after every page load

To track page views, the API route created in step before should be hit every time a user navigates to a page.

#/**src**/routes/[slug]/+page.svelte

<script lang="ts">
	import { browser, dev } from '$app/environment'

	import { updateViews } from '$lib/database'

	export let data

	if (!dev && browser) {
		updateViews(data.frontmatter.slug)
	}
</script>
  • the slug is something that can be stored manually on page creation so that each page can be associated with itself from the DB, here we pass the frontmatter data from our CMS on the +page.server.ts file.
  • Whenever you load your page, it should make a POST request to increment your page views.

###- Return the view count in your SvelteKit component We can now create a component to display the page view count.

#/src/ui/viewCount.svelte

<script lang="ts">
	import { views } from '$lib/stores/views'
	export let slug: string
</script>
<span>{$views.find((data) => data.slug === slug)?.views.toLocaleString() ?? 0} {views.length === 1 ? 'view' : 'views'}</span>

and use it on any page <viewCount slug="slug".

By following these steps, a real-time page view tracker for a SvelteKit project can be created using Supabase.

Resources and Inspiration :

Comments (0)