[SOLVED] Supabase: how to query users by eMail?

Supabase: how to query users by eMail?

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', 'doe@example.com')
  .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', 'doe@example.com')
  .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

Solution

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?