Calling functions via RPC in Supabase - Mini Project: Build & Apply

Calling functions via RPC in Supabase

📖 Scenario: You are working on a simple web app that needs to fetch user statistics from a database. Instead of writing complex queries in your app, you want to use a stored function in Supabase that returns the total number of active users.

🎯 Goal: Create and call a Supabase remote procedure call (RPC) function named get_active_user_count that returns the count of active users from the users table.

📋 What You'll Learn

Create a Supabase function named get_active_user_count that returns the count of users where is_active is true.

Use the Supabase client to call the get_active_user_count function via RPC.

Store the result in a variable named activeUserCount.

Ensure the code is clear and uses exact variable and function names as specified.

💡 Why This Matters

🌍 Real World

Using RPC functions in Supabase helps keep your app code clean and secure by moving complex queries to the database layer.

💼 Career

Many cloud and backend jobs require knowledge of calling database functions remotely and handling their results safely.

1

Create the Supabase function to count active users

Write the SQL code to create a function named get_active_user_count that returns an integer count of rows from the users table where is_active is true.

Supabase

-- Your SQL code here to create the function get_active_user_count

Hint

Use CREATE OR REPLACE FUNCTION with no input parameters and return type integer. The function body should count rows where is_active is true.

2

Initialize the Supabase client

Write JavaScript code to create a Supabase client instance named supabase using createClient with placeholders SUPABASE_URL and SUPABASE_ANON_KEY.

Supabase

CREATE OR REPLACE FUNCTION get_active_user_count()
RETURNS integer AS $$
  SELECT COUNT(*) FROM users WHERE is_active = true;
$$ LANGUAGE SQL STABLE;

// Initialize Supabase client
// Your code here

Hint

Use createClient from @supabase/supabase-js with the exact variable name supabase.

3

Call the RPC function using Supabase client

Write JavaScript code to call the RPC function get_active_user_count using supabase.rpc and store the returned count in a variable named activeUserCount.

Supabase

CREATE OR REPLACE FUNCTION get_active_user_count()
RETURNS integer AS $$
  SELECT COUNT(*) FROM users WHERE is_active = true;
$$ LANGUAGE SQL STABLE;

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

const supabase = createClient('SUPABASE_URL', 'SUPABASE_ANON_KEY');

// Call the RPC function and store the result
// Your code here

Hint

Use await supabase.rpc('get_active_user_count') and assign data to activeUserCount.

4

Handle errors and finalize the RPC call

Add code to check if error exists after the RPC call. If yes, throw the error. This completes the function call handling.

Supabase

CREATE OR REPLACE FUNCTION get_active_user_count()
RETURNS integer AS $$
  SELECT COUNT(*) FROM users WHERE is_active = true;
$$ LANGUAGE SQL STABLE;

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

const supabase = createClient('SUPABASE_URL', 'SUPABASE_ANON_KEY');

const { data, error } = await supabase.rpc('get_active_user_count');
const activeUserCount = data;

// Add error handling below
// Your code here

Hint

Check if error is truthy and throw it to handle failures.

Practice

1. What does supabase.rpc() do in a Supabase app?

easy

A. Creates a new table in the database

B. Uploads files to storage

C. Fetches user authentication details

D. Calls a custom database function remotely

Solution

1. Step 1: Understand the purpose of supabase.rpc()

   This method is designed to call remote procedure calls (RPC), which are custom functions defined in the database.

2. Step 2: Identify what supabase.rpc() does in the app context

   It runs these custom functions securely from the app, allowing logic to stay in the database.

3. Final Answer:

   Calls a custom database function remotely -> Option D

4. Quick Check:

   RPC calls = custom database functions [OK]

Hint: RPC means running database functions remotely [OK]

Common Mistakes:

• Confusing RPC with file uploads
• Thinking it creates tables
• Assuming it handles user login

2. Which is the correct way to call a function named get_user_data with parameter { user_id: 5 } using Supabase RPC?

easy

A. supabase.rpc('get_user_data', { user_id: 5 })

B. supabase.call('get_user_data', 5)

C. supabase.function('get_user_data', { id: 5 })

D. supabase.execute('get_user_data', user_id=5)

Solution

1. Step 1: Recall the syntax of supabase.rpc()

   The method takes the function name as a string and an object with parameters.

2. Step 2: Match the correct syntax with the options

   supabase.rpc('get_user_data', { user_id: 5 }) correctly uses supabase.rpc('function_name', { params }) format.

3. Final Answer:

   supabase.rpc('get_user_data', { user_id: 5 }) -> Option A

4. Quick Check:

   Correct RPC call syntax = supabase.rpc('get_user_data', { user_id: 5 }) [OK]

Hint: Use rpc('function', {params}) format [OK]

Common Mistakes:

• Using wrong method names like call or execute
• Passing parameters as separate arguments
• Using incorrect parameter keys

3. Given this code snippet:

const { data, error } = await supabase.rpc('multiply', { a: 3, b: 4 });
console.log(data);

What will be printed if the multiply function returns the product of a and b?

medium

A. 7

B. 12

C. undefined

D. Error

Solution

1. Step 1: Understand the function call and parameters

   The function multiply is called with a=3 and b=4.

2. Step 2: Calculate the expected return value

   Since the function returns the product, 3 * 4 = 12.

3. Final Answer:

   12 -> Option B

4. Quick Check:

   3 times 4 = 12 [OK]

Hint: Multiply parameters to find output [OK]

Common Mistakes:

• Adding instead of multiplying
• Assuming no return value
• Confusing data with error

4. You wrote this code:

const { data, error } = await supabase.rpc('get_user', { user_id: 10 });
console.log(data);

But you get an error saying the function does not exist. What is the likely cause?

medium

A. Parameters must be passed as an array, not an object

B. You forgot to import supabase client

C. The function name is misspelled or missing in the database

D. RPC calls do not support parameters

Solution

1. Step 1: Analyze the error message

   The error says the function does not exist, indicating a problem with the function name or its presence in the database.

2. Step 2: Check common causes for missing function errors

   Usually, the function is either misspelled or not created in the database schema.

3. Final Answer:

   The function name is misspelled or missing in the database -> Option C

4. Quick Check:

   Function missing error = wrong or absent function [OK]

Hint: Check function spelling and existence in DB [OK]

Common Mistakes:

• Passing parameters incorrectly
• Assuming import errors cause this
• Thinking RPC disallows parameters

5. You want to create a Supabase RPC call to get the total sales for a given store_id. The database function is named total_sales and takes store_id as input. Which code snippet correctly calls this function and handles errors?

hard

A. const { data, error } = await supabase.rpc('total_sales', { store_id }); if (error) console.error(error); else console.log(data);

B. const result = supabase.call('total_sales', store_id); console.log(result);

C. const { data } = await supabase.rpc('total_sales', store_id); console.log(data);

D. const { data, error } = supabase.rpc('total_sales', { store: store_id }); if (error) throw error; else console.log(data);

Solution

1. Step 1: Identify correct RPC call syntax with parameters

   The function name is 'total_sales' and parameters must be passed as an object with key matching the function input, here { store_id }.

2. Step 2: Check error handling and async usage

   const { data, error } = await supabase.rpc('total_sales', { store_id }); if (error) console.error(error); else console.log(data); uses await, destructures data and error, and handles error properly with console.error.

3. Final Answer:

   const { data, error } = await supabase.rpc('total_sales', { store_id }); if (error) console.error(error); else console.log(data); -> Option A

4. Quick Check:

   Correct RPC call with error check = const { data, error } = await supabase.rpc('total_sales', { store_id }); if (error) console.error(error); else console.log(data); [OK]

Hint: Use await, pass params as object, check error [OK]

Common Mistakes:

• Passing parameters incorrectly (not as object)
• Ignoring error handling
• Using wrong method names like call