How to implement a counter in Supabase

You have built a web app and one of the features it has is link sharing. You want to track how many times a link has been opened.

It is simple right? You have to fetch the data from the database, increment it and then update it in DB?

SELECT view_count FROM user_link WHERE user_id='23d70c9e-b86f-4c4a-8c92-79615b6e487c';

view_count = view_count + 1

UPDATE user_link SET view_count=view_count WHERE user_id='23d70c9e-b86f-4c4a-8c92-79615b6e487c';

The above doesn't work. It looks like it would work, but it does not work accurately.

The code has a race condition because reading and updating the view count are done as separate operations. If another update occurs between the read and the write, it can be overwritten, causing the stored view_count to be inaccurate.

How to solve this?

There are 2 ways to solve it.

Lock the row when you read it and release it after you update it SELECT FOR UPDATE
Increment view_count as an atomic operation.

2nd solution is the best as it is fast and doesn't need a database lock.

UPDATE user_link
SET view_count = view_count + 1
WHERE user_id='23d70c9e-b86f-4c4a-8c92-79615b6e487c';

This update query, does not specify the new view_count. Instead it asks the database to do the increment for you. It is an atomic operation and is safe from race conditions.

Supabase API's don't support raw SQL execution at the time of writing this post. You will have to create a database function and call it with rpc.

Create a function with the following sql

UPDATE user_link
SET view_count = view_count + 1
WHERE user_id=user_id;

Specify user_id as the argument for the function.

Give the function a name. Something like increment_user_link_view_count.

We will use sql language in this post. But you can also use plpgsql.

You can now call the function from your code like this. The below example uses Supabase JS client.

await supabase.rpc('increment_user_link_view_count', {
  user_id: '23d70c9e-b86f-4c4a-8c92-79615b6e487c'
});

This ensures that the view count is updated accurately in the database. Since database engine is tasked with the increment, it ensures that the increments happen automically.