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.
Top comments (2)
If we ever want to go back and see the source code of the function, do you know how to do it?
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...