Drizzle Team•11mo ago

Common ways to reference and work with the `user_id` when managed by Supabase

As far as I understood, it's a valid approach to keep user management and its related auth schemas the way Supabase provides it out of the box. All my custom tables, and their schema, have been created/managed by drizzle. I created a local users table so I can reference it in my relations for joint queries, e.g. in toolsToQualitiesToUsersRelations. But when I migrate, it doesn't work; either I include the local users table in my migration - which I don't want -, or it throws an error if I do not include it. How can I make it work AND/OR what's the adviced approach to work in harmony with Supabase and its user management?

12 Replies

lexixonOP•11mo ago

I guess we're meant to use a users_view here that we can make part of the public schema and then only work with users_view in drizzle-orm. Unfortunately we can't work with foreign keys then, like referencing a user_id in each entry in tools_to_qualities_to_users (or tools_to_qualities_to_users_view). So an alternative is to just use a proxy table users with one column uuid and keep that table in sync with auth.users via a trigger. Does that make sense or am I overcomplicating?

Angelelz•11mo ago

I would just keep my schema in sync. Can you introspect you supabase schema with drizzle kit and adjust it accordingly?

francis•11mo ago

So an alternative is to just use a proxy table users with one column uuid and keep that table in sync with auth.users via a trigger.

sure, but you don't have to be that restrictive. Surely there must be other user-specific data you want to save? I save things like name, "has the user accepted TOS", etc on my profiles table, and keep that in sync with auth.users via triggers.

lexixonOP•11mo ago

Thanks, that makes a lot of sense. I thought I may still just get the user information like name from auth.users directly but there's probably good reason to just duplicate/sync this information into a profiles table. If, let's say, you changed the user's email address, would you do this in the profiles table and build a mechanic to sync/write into auth.users automatically or is it easier if we stay one directional with such auth-related info and write to auth.users directly? I don't fully understand how I could keep it completely in sync given the auth.users table on Supabase side is not exposed and unless I want to build my own auth, I need another method to keep "main user data" in sync (as proposed by @francis ). Or do I miss something?

Angelelz•11mo ago

I don't have much experience in supabase, I was assuming you had access to auth.users schema

francis•11mo ago

@lexixon I extracted my implementation into a gist https://gist.github.com/fnimick/37269e61f4e2374290b76554d8f994ab

Gist

Create a public.profile table and keep it in sync with supabase `...

Create a public.profile table and keep it in sync with supabase auth.users for selected fields in both directions. - supabase_profile_sync.sql

francis•11mo ago

you don't want to update the user email through this, you want to do it via email change in supabase (ideally), so I sync back only for metadata fields.

lexixonOP•11mo ago

Much appreciated, thanks! I got it to work.

Shubham-Sinha•10mo ago

@francis How does the drizzle schema look like when generating this migration ?

create table public.profile (
    -- UUID from auth.users
    id uuid primary key references auth.users on delete cascade,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    terms_accepted_at timestamp,
    email varchar(255),
    full_name text,
    avatar_url text
);

create table public.profile (
    -- UUID from auth.users
    id uuid primary key references auth.users on delete cascade,
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    terms_accepted_at timestamp,
    email varchar(255),
    full_name text,
    avatar_url text
);

francis•10mo ago

? use a drizzle pull afterward to pull the changes in you'll end up with them perfectly in sync

ar7casper•9mo ago

@francis Is there a way to do it via the orm? Problem is that there's no access to auth.... Feels meh to have part of the setup in sql and part in an orm, any hints on that?

francis•9mo ago

you can add auth to the drizzle schemas but that doesn't work well with introspection, so I don't.

Gaming

Programming

Common ways to reference and work with the `user_id` when managed by Supabase