How to run custom SQL queries using functions in Supabase

Razvan Statescu
2 min readOct 28, 2021

--

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.

--

--

Razvan Statescu

Web3 builder 👨‍💻 Find me working on Giants Village, CoinDrip Finance, xDevHub, web3print, xDevGuild