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.

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$;

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')

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$;

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

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

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

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

Comments

[deepu]

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

[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...