Supabase: How to query users table?

Ever wondered how to query your users table in Supabase? This was my questioned of the day: I developed a Node.js middleware for my Vue Time Tracking App that I hopefully can release soon. The exact question was: "how to query users by email?".

But from the beginning: I have Projects in my time tracking app. Users can add other users to a project in order to collaborate with them. When adding a user to a project - which by the way is done with the users email address - the app has to determine wether the user already exists or not. If it exists, thats great: then just add the user to the project and send a little notification via mail. If the user does not yet exist, create an entry on the invitation table and also notify the user by email (but with a different transaction mail template).

As I don't want to have this bunch of code and logic in the frontend, I created a little Node.js app in combination with express.js to provide an REST endpoint. My idea was to just listen to this endpoint for incoming invitation requests and query the users table for the given email address using the Supabase JavaScript library.

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)
  })

But I ended up getting this error:

relation "public.users" does not exist

It's not possible to query the auth users table directly

As I realized, it's not possible to query the internal auth users table directly. Instead you have to create a copy of it with all relevant data you want to user later on. You can also add other fields to store more user profile data like "first name", "last name", "birth date", "address", etc.

As the only thing I want to do is querying the existing users I just created a small user table. You can do so my using the Supabase SQL Editor. Just select "SQL" in the main menu and click "New query". This opens up a tiny editor window where you can type the following query:

-- USERS
create table public.users (
  id uuid not null primary key, -- UUID from auth.users
  email text
  first_name text,
  last_name text
);
comment on table public.users is 'Profile data for each user.';
comment on column public.users.id is 'References the internal Supabase Auth user.';

This creates a new public table "users" with an id field referencing the auth.users UUID. I also added a field for the email address that I want to query and fields for the first and the last name what I'm going to use for the user profiles.

Triggers: Automatically create an entry on the users table when a  new user is created

Now comes the trick: you (or your app) does not have to fill in the entries in the new public.users table. Instead you can use a trigger to automatically do the job for you whenever a new user is created. Or exactly: whenever a new user entry on the auth.users table is created.

Keep in mind: we created a public.users table. This has nothing to do with the auth.users table which is an internal database that handles the authentication process for your app users to access the Supabase API.

To enable the trigger just visit the SQL editor again and type:

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Just klick the "RUN" button on the bottom right corner of the editor. Thats it!

Now every time you create a new user or a user signs up to your app the trigger runs and creates a new entry with the users id (UUID) and it's email address in the public.users table.

And now you can query the public.users table for the users email address.