Integrating PL/pgSQL and Supabase in Bokkah.com: A Real-World React Use Case

In my previous post, Getting Started with PL/pgSQL and Supabase, I introduced PL/pgSQL as a powerful tool for enhancing PostgreSQL databases. In this post, I’ll show how I used it in my app, "Bokkah – Recipes for all", to build a dynamic follower count feature.

Originally known as “React Recipes” during its early development, Bokkah.com is a modern recipe-sharing platform built with React. The integration of Supabase, PL/pgSQL, and React ensures a seamless and efficient user experience.

Let’s explore the follower count feature in detail.

---

The Follower Count Feature: A Full-Stack Perspective

In "React Recipes," users can follow collections of recipes. To ensure a responsive and scalable user experience, I’ve implemented the following architecture:

1. PL/pgSQL Function: Calculates follower counts dynamically in Supabase.
2. Node.js Method: Fetches collection details, including the follower count via a Supabase RPC (Remote Procedure Call).
3. React Component: Provides users with an interactive way to follow or unfollow collections.

---

Step 1: PL/pgSQL Function in Supabase

The cornerstone of this feature is the get_followers_count function, which calculates the number of followers for a collection. I shared this function in detail in the previous article, but here’s a quick recap:

CREATE OR REPLACE FUNCTION get_followers_count(collection_id UUID)
RETURNS INT AS $$
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM follows
        WHERE entity_id = collection_id AND entity_type = 'collections'
    );
END;
$$ LANGUAGE plpgsql;

This function lets me fetch follower counts dynamically and ensures accuracy without redundant computations.

---

Step 2: Node.js Backend Method with Supabase RPC

To integrate the get_followers_count function, I updated the backend method to make an RPC call to Supabase. Here’s the method:

export const getCollectionById = async (collectionId) => {
    const { data, error } = await supabase
        .from('collections')
        .select(
            `
              id,
              name,
              description,
              private,
              created_at,
              created_by:users!collections_created_by_fkey (
                uid,
                name,
                username,
                image
              ),
              collection_invited_users (
                users:users!collection_invited_users_user_uid_fkey (
                  uid,
                  name,
                  username,
                  image
                )
              ),
              collection_recipes (
                recipe_id,
                recipes (
                  id,
                  title,
                  description,
                  image,
                  created_at,
                  category (*),
                  author (
                    uid,
                    name,
                    username,
                    image
                  ),
                  draft
                )
              )
            `
        )
        .eq('id', collectionId);

    if (error) throw new Error(error.message);

    const collection = data[0];

    if (!collection) {
        throw new Error('Collection not found');
    }

    const invitedUserIds = collection.collection_invited_users.map((user) => user.users);
    const users = [collection.created_by, ...invitedUserIds];
    const recipes = collection.collection_recipes.map((item) => item.recipes);

    // Fetch followers_count using the RPC function
    const followersCountResponse = await supabase.rpc('get_followers_count', { collection_id: collectionId });
    let followersCount = 0;

    if (followersCountResponse.error) {
        console.error('Error fetching followers count:', followersCountResponse.error.message);
    } else {
        followersCount = followersCountResponse.data + users.length;
    }

    return {
        ...collection,
        invitedUserIds,
        users,
        recipes,
        followers_count: followersCount,
    };
};

Why Use RPC for Follower Counts?

• Efficiency: The follower count is fetched only when needed, reducing overhead.
• Flexibility: The RPC call can be reused across different parts of the app.
• Error Handling: Ensures graceful degradation if the count cannot be retrieved.

---

Step 3: Interactive Frontend with <FollowButton />

To complete the feature, I added a React component, <FollowButton />, to handle user interactions.

import React, { useState } from 'react';
import { followEntity, unfollowEntity } from '../../util/http';
import Button from "./Button";

const FollowButton = ({ uid, entityId, entityType, initialIsFollowing }) => {
  const [isFollowing, setIsFollowing] = useState(initialIsFollowing);

  const handleFollow = () => {
    followEntity({ uid, entityId, entityType }).then(() => setIsFollowing(true));
  };

  const handleUnfollow = () => {
    unfollowEntity({ uid, entityId, entityType }).then(() => setIsFollowing(false));
  };

  return (
    <Button onClick={isFollowing ? handleUnfollow : handleFollow} isPrimary={!isFollowing} isDestructive={isFollowing}>
      {isFollowing ? 'Unfollow' : 'Follow'} {entityType.charAt(0).toUpperCase() + entityType.slice(1)}
    </Button>
  );
};

export default FollowButton;

---

How This Approach Enhances React Recipes

1. Seamless Data Flow

The database, backend, and frontend communicate efficiently, ensuring accurate follower counts.

2. Scalability

As user interactions grow, the database-driven architecture can handle increased load.

3. User Experience

The interactive <FollowButton /> enhances engagement by providing real-time feedback.

---

Linking Back to PL/pgSQL Basics

If this post piqued your interest in PL/pgSQL, I encourage you to read my previous article, Getting Started with PL/pgSQL and Supabase. It provides the foundational knowledge needed to implement features like the one discussed here.

For more details on "Bokkah, a React powered App" visit the app at bokkah.com and explore the seamless integration of Supabase, PL/pgSQL, and React.