Run Custom SQL Queries with Supabase Functions

Supabase is an open-source alternative to Firebase that offers a PostgreSQL database, authentication, and API tools for developers. One of the most powerful features of Supabase is the ability to run custom SQL queries using functions. In this article, we'll show you how to use this powerful feature to retrieve data from your Supabase database using JavaScript.

Step 1: Create a function

Open the SQL editor in the Supabase dashboard and create a new function. Here is an example function that retrieves a user's name and email from the "users" table using the user_id:

CREATE OR REPLACE FUNCTION public.get_user_data(user_id uuid)
RETURNS TABLE (name text, email text)
LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY SELECT name, email FROM users WHERE id = user_id;
END;
$function$

Step 2: Call the function

Once the function is created, you can call it from your client-side code. In this example, we'll use JavaScript and the Supabase JS library to call the function:

const { data, error } = await supabase
  .from('get_user_data(user_id)')
  .select('*')
  .params({ user_id: '12345678-1234-1234-1234-123456789abc' });

In this example, we're passing the user_id parameter to the function and retrieving the data using Supabase's from() method. Note that we're using single quotes to wrap the function name and parameter, and double quotes for the select statement.

That's it! You can create complex functions using SQL and call them directly from your client-side code. This feature can greatly simplify your app's logic and save you a lot of time.

How to run custom SQL queries using functions in Supabase

Comments

More from this blog

Improve Readability with Numeric Separators in JavaScript

Free Google reCAPTCHA Alternative: Cloudflare Turnstile

How to detect dark mode using JavaScript

Create a Username from a String in JavaScript

JavaScript regex: Allow letters and numbers, but not just numbers

Step 1: Create a function

Step 2: Call the function

Command Palette

Comments

More from this blog

Step 1: Create a function

Step 2: Call the function