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.