Using Supabase to power a React-based browser game

Overview

Supabase is an open-source cloud based Postgres service. I have been wanting to have a hands-on experience with it for a while now. Hence, the idea of the Boring Browser Game came along. In this post, I show the usage of insert and select statements along with a Postgres trigger to execute a function to calculate clicks/sec before insertion.

Setup

I am using free version of the service (obviously), which is limiting but still gives plenty of features such as unlimited API requests, 500 MB database space and 1 GB file storage that are very useful for personal projects.

After creating a Project in Supabase following the steps in the documentation, I created a table named ‘click_local’ in the public schema with the following columns: id, no_of_clicks, user, duration.

Then, I created a .env.local file with the following credentials for supabase connection.

NEXT_PUBLIC_SUPABASE_URL=<SUBSTITUTE_SUPABASE_URL>
NEXT_PUBLIC_SUPABASE_ANON_KEY=<SUBSTITUTE_SUPABASE_ANON_KEY>

In the Next.js project, I installed supabase-js and created a file named server.ts where a connection with Supabase was initialized using the credentials from above.

import { createClient } from "@supabase/supabase-js";

// Initialize Supabase client
const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

export default supabase;

Insertion

In the root page.tsx of the app, I used the supabase client from above to insert row of data into the click_local table.

const sendGameResults = async (clickCount: number, duration: number) => {
  try {
    const { error } = await supabase
      .from("click_local")
      .insert({ no_of_clicks: clickCount, duration: duration });

    if (error) throw error;
      console.log("Game results saved successfully");
    } catch (error) {
      console.error("Error saving game results:", error);
    }
};

Insert statement is called via sendGameResults function in a useEffect hook after a game is over as shown below.

useEffect(() => {
    if (gameOver && startTime !== null) {
      const duration = Math.floor((Date.now() - startTime) / 1000);
      setGameDuration(duration);
      sendGameResults(clicks, duration);
    }
  }, [gameOver, clicks, startTime]);

When I played the game, it did not work as expected and a row of data was not inserted into the table. I got an error in the browser console with the following message.

message: 'new row violates row-level security policy for table "click_local"'

This was because I had enabled RLS (it’s enabled by default and I did not uncheck it) when creating table. Once RLS is enabled, you need to have policy defined to perform any mutation, hence the error. Because I don’t have any authentication system yet, I set my policy to also include anonymous users. It finally worked as expected and the score was saved.

alter policy "Allow inserts for all"
on "public"."click_local"
to authenticated, anon
with check (
  true
);

Triggers

Next, I wanted the table to include clicks per sec for the leaderboard later. To achieve that, I used a function that does the calculation as follows. It divides no_of_clicks by duration if duration is at least a second else returns 0.

-- Trigger function to calculate clicks per seconds
create or replace function calculate_clicks_per_second()
returns trigger
language plpgsql
as $$
begin
  new.clicks_per_sec := case
    when new.duration > 0 then new.no_of_clicks::NUMERIC / new.duration
    else 0
  end;
  
  RETURN new;
end;
$$;

The above function is used by a trigger before performing insert action in the table.

-- Actual trigger executing the function
create or replace trigger clicks_per_sec_trigger
before insert on click_local
for each row
execute function calculate_clicks_per_second();

Select statement

I used Supabase’s Select API to fetch data from the table and sorted it by clicks per second column before showing it on a shadcn/ui table in ‘/leaderboard’ url.

const { data, error } = await supabase
  .from("click_local")
  .select()
  .order("clicks_per_sec", { ascending: false });

if (error) {
  console.error("Error fetching leaderboard data:", error);
}

Deployment

This application is deployed on Vercel and is available at game.bimals.net.

Related Post/s

A Boring Browser Game