Supabase: how to query users by eMail?

Question

I'm using Supabase in a Node JS middleware. I am developing an invite function thats receives an eMail address of an existing supabase user via a REST Endpoint. Now it should query the users table in order to get the users ID. But this does not seem to work:

(im using the Supabase JavaScript library an the admin key that bypasses the row level security):

const { createClient,   } = require('@supabase/supabase-js')
const supabase = createClient(process.env.SUPABASE_URL, process.env.SUPABASE_KEY)
supabase
  .from('users')
  .select('id')
  .eq('email', '[email protected]')
  .then(response => {
    console.log(response)
  })

I'm getting this error:

'relation "public.users" does not exist'

I also tried a query with

supabase
  .from('users')
  .select('id')
  .eq('email', '[email protected]')
  .then(response => {
    console.log(response)
  })

But this also failed. It seems that it is not possible to query the users table.

What am I doing wrong? How can I query for a User ID by a given eMail?

Can anonybody push me in the right direction?

Thanks,

Niko

Answer 1

Just answering for future users:

The best way to accomplish that is through rpc functions.

On the supabase dashboard, create a new query with the following code:

CREATE OR REPLACE FUNCTION get_user_id_by_email(email TEXT)
RETURNS TABLE (id uuid)
SECURITY definer
AS $$
BEGIN
  RETURN QUERY SELECT au.id FROM auth.users au WHERE au.email = $1;
END;
$$ LANGUAGE plpgsql;

Now you can call this rpc function on your code using the following functions. (I am using supabase-js v2)

  const { data, error } = await supabaseAdmin.rpc(
   "get_user_id_by_email",
    {
      email: "[email protected]",
    }
 );

Pay attention to the fact i am using supabaseAdmin for this call becuase I on the server side.

Answer 2

Found the solution by myself:

You cannot directly query the auth.users table. Instead you have to create a copy (e.g. public.users or public.profile, etc.) with the data you want to use later on.

You can user triggers to automatically create an entry to the public.users table as soon as a user is created.

I wrote down some code examples and details in my blog post: Supabase: How to query users table?