DEV Community

Cover image for How to run custom SQL queries using functions in Supabase
Răzvan Stătescu
Răzvan Stătescu

Posted on • Originally published at Medium

How to run custom SQL queries using functions in Supabase

Recently I needed to run custom SQL functions in a Supabase project. Their Javascript SDK doesn't support this so the only way is via database functions. You can then call those functions using the Javascript SDK.

Supabase

Let's look at a very simple example using a table called users

CREATE OR REPLACE FUNCTION all_users()
  RETURNS TABLE (f_id   uuid   
               , f_email   text
               , f_full_name text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT id, email, full_name FROM users
END
$func$;
Enter fullscreen mode Exit fullscreen mode

The above function will return the id, email, full_name for all users in the table. To quickly test the function you can run SELECT * FROM all_users();.

Now, moving to your Javascript code, you can run the function using the following syntax:

let { data, error } = await supabase.rpc('all_users')
Enter fullscreen mode Exit fullscreen mode

It's that simple 😅.

Now, let's look at a function that receives two parameters:

CREATE OR REPLACE FUNCTION all_users(created_from timestamp, created_to timestamp)
  RETURNS TABLE (f_id   uuid   
               , f_email   text
               , f_full_name text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT id, email, full_name FROM users BETWEEN created_from AND created_to
END
$func$;
Enter fullscreen mode Exit fullscreen mode

And, we can call this function from the Javascript SDK like this:

const { data, error } = await supabase
  .rpc('all_users', { created_from: ..., created_to: ... })
Enter fullscreen mode Exit fullscreen mode

If you want to reach me, check out my Twitter.

Article posted using bloggu.io. Try it for free.

Top comments (2)

Collapse
 
pradeepb28_ profile image
deepu

If we ever want to go back and see the source code of the function, do you know how to do it?

Collapse
 
canrau profile image
Can Rau

I'd always use migrations, this way you keep track of all the details and can even version control them. I use github.com/urbica/pg-migrate

Alternatively you can try the supabase cli & local development supabase.com/docs/guides/cli/local...