i have auth user relation in my public table in supabase

how can i join it with useTable hook?

30 Replies

Hi @danyalutsevich. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- To join the auth user relation in your public table in Supabase using the useTable hook, you can utilize the meta property to specify the relational data you want to fetch. Here's how you can do it: 1. In your useTable hook, add a meta object with a select property that includes the relational data you want to fetch:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, auth.users(*)",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, auth.users(*)",
  },
});

This tells the Supabase data provider to fetch all columns from your public table and join it with the auth.users table Using meta to pass values to data provider (https://refine.dev/docs/data/packages/supabase/#using-meta-to-pass-values-to-data-provider). 2. If you need to filter or sort based on the joined table, you can use dot notation in your filters or sorters:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, auth.users(*)",
  },
  filters: {
    initial: [
      {
        field: "auth.users.email",
        operator: "contains",
        value: "@example.com",
      },
    ],
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, auth.users(*)",
  },
  filters: {
    initial: [
      {
        field: "auth.users.email",
        operator: "contains",
        value: "@example.com",
      },
    ],
  },
});

3. Remember that for this to work, you need to have the proper relationships set up in your Supabase database between your public table and the auth.users table Relationship Between Tables (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database). 4. If you encounter any issues with permissions, make sure you have the correct Row Level Security (RLS) policies in place for accessing the auth.users table. By following these steps, you should be able to join your public table with the auth user relation in Supabase using the useTable hook. The joined data will be available in the tableQueryResult.data object. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai it does not work thats the error message i get ""failed to parse select parameter (,auth.users())" (line 1, column 8)"

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- I apologize for the confusion in my previous answer. It seems that the syntax I provided doesn't work directly with Supabase's auth schema. Let's try a different approach based on the error message you're receiving. The issue is likely due to Supabase's restrictions on accessing the auth schema directly. Instead, we need to use the public schema and join with the users table there. Here's a corrected approach: 1. First, make sure you have a users table in your public schema that mirrors the necessary information from auth.users. This is a common pattern in Supabase applications. 2. Then, modify your useTable hook like this:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users(*)",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users(*)",
  },
});

This assumes that your public table has a foreign key relationship with the users table in the public schema Using meta to pass values to data provider (https://refine.dev/docs/data/packages/supabase/#select---handling-one-to-many-relationship). If you're still encountering issues, it might be helpful to double-check your table structure and relationships. Make sure that: 1. You have a users table in your public schema. 2. Your your_public_table has a foreign key relationship with the users table. 3. You have the necessary permissions set up to access both tables. If the problem persists, you might need to specify the exact columns you want to select instead of using the wildcard *. For example:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "id, name, users(id, email)",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "id, name, users(id, email)",
  },
});

Replace id, name with the actual column names from your public table, and id, email with the columns you need from the users table. If you're still facing issues after trying these solutions, it would be helpful to know more about your table structure and the specific relationship between your public table and the users table. This information would allow for a more tailored solution to your specific setup. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai now this is the error im getting Could not find a relationship between 'user' and 'users' in the schema cache"

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- I apologize for the confusion. It seems that the relationship between your tables is not properly set up or recognized by Supabase. Let's try to troubleshoot this issue step by step. 1. First, make sure that you have a proper relationship defined between your tables. In Supabase, you typically need to have a foreign key constraint set up for the relationship to be recognized. 2. Check your table structure. It appears you have a user table (singular) and a users table (plural). This mismatch might be causing the issue. Supabase usually expects the table names to be plural. 3. If you're trying to join the auth.users table with your public table, you need to create a shadow table in the public schema. Here's an example of how to set this up, as mentioned in the Refine documentation Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database):

-- Create a table for public users  
create table users (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This trigger automatically creates a public.users entry when a new user signs up via Supabase Auth.  
create or replace function public.handle_new_public_user()  
returns trigger as $$  
begin  
  insert into public.users (id, full_name, avatar_url)  
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');  
  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_public_user();

-- Create a table for public users  
create table users (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This trigger automatically creates a public.users entry when a new user signs up via Supabase Auth.  
create or replace function public.handle_new_public_user()  
returns trigger as $$  
begin  
  insert into public.users (id, full_name, avatar_url)  
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');  
  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_public_user();

4. After setting up the proper relationship, try using the useTable hook like this:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users(*)",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users(*)",
  },
});

Replace your_public_table with the actual name of your table. 5. If you're still encountering issues, you might need to specify the exact relationship in your select statement. For example:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users!inner(*)",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_public_table",
  meta: {
    select: "*, users!inner(*)",
  },
});

This syntax is used for inner joins in Supabase, as mentioned in the Refine documentation FAQ - How can I use Supabase Realtime with relational queries? (https://refine.dev/docs/data/packages/supabase/#faq). If you're still facing issues after trying these solutions, it would be helpful to know more about your exact table structure, the relationships between your tables, and the specific query you're trying to execute. This information would allow for a more precise solution tailored to your specific setup. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai how can solve this problem without creating new table?

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- Based on the information provided in the knowledge sources and our previous conversation, it appears that creating a new table in the public schema to mirror the auth.users table is the recommended approach for accessing user data in Supabase while using refine. However, if you want to solve this problem without creating a new table, there are a couple of alternative approaches you can consider: 1. Use the Supabase client directly: Instead of using the useTable hook, you can use the Supabase client directly to fetch the data you need. This approach allows you to write custom SQL queries or use the Supabase JavaScript client to join the tables manually. Here's an example of how you might do this:

   import { useEffect, useState } from 'react';
   import { supabaseClient } from './your-supabase-client-file';

   const YourComponent = () => {
     const [data, setData] = useState([]);

     useEffect(() => {
       const fetchData = async () => {
         const { data, error } = await supabaseClient
           .from('your_public_table')
           .select(`
             *,
             auth.users!inner(id, email)
           `);

         if (error) console.error('Error fetching data:', error);
         else setData(data);
       };

       fetchData();
     }, []);

     // Render your component using the fetched data
   };

   import { useEffect, useState } from 'react';
   import { supabaseClient } from './your-supabase-client-file';

   const YourComponent = () => {
     const [data, setData] = useState([]);

     useEffect(() => {
       const fetchData = async () => {
         const { data, error } = await supabaseClient
           .from('your_public_table')
           .select(`
             *,
             auth.users!inner(id, email)
           `);

         if (error) console.error('Error fetching data:', error);
         else setData(data);
       };

       fetchData();
     }, []);

     // Render your component using the fetched data
   };

This approach bypasses the refine data provider and allows you to directly query the auth.users table. 2. Custom data provider method: You can create a custom method in your data provider to handle this specific case. This would involve extending the Supabase data provider provided by refine. Here's a conceptual example:

   import { dataProvider } from "@refinedev/supabase";
const customDataProvider = {
     ...dataProvider(supabaseClient),
     getList: async ({ resource, pagination, filters, sorters, meta }) => {
       if (resource === 'your_public_table' && meta?.includeAuthUsers) {
         // Custom logic to join with auth.users
         const { data, error } = await supabaseClient
           .from(resource)
           .select(`*, auth.users!inner(*)`)
           // Add pagination, filtering, and sorting as needed
         
         if (error) throw error;
         
         return {
           data,
           total: data.length // You might need a separate count query for accurate pagination
         };
       }
       
       // For other cases, use the default getList method
       return dataProvider(supabaseClient).getList({ resource, pagination, filters, sorters, meta });
     },
   };

   import { dataProvider } from "@refinedev/supabase";
const customDataProvider = {
     ...dataProvider(supabaseClient),
     getList: async ({ resource, pagination, filters, sorters, meta }) => {
       if (resource === 'your_public_table' && meta?.includeAuthUsers) {
         // Custom logic to join with auth.users
         const { data, error } = await supabaseClient
           .from(resource)
           .select(`*, auth.users!inner(*)`)
           // Add pagination, filtering, and sorting as needed
         
         if (error) throw error;
         
         return {
           data,
           total: data.length // You might need a separate count query for accurate pagination
         };
       }
       
       // For other cases, use the default getList method
       return dataProvider(supabaseClient).getList({ resource, pagination, filters, sorters, meta });
     },
   };

Then you can use this custom data provider in your refine setup and in your useTable hook:

   const { tableProps, tableQueryResult } = useTable({
     resource: "your_public_table",
     meta: {
       includeAuthUsers: true
     },
   });

   const { tableProps, tableQueryResult } = useTable({
     resource: "your_public_table",
     meta: {
       includeAuthUsers: true
     },
   });

However, it's important to note that these approaches may have limitations and potential security implications. The recommended way to handle this in Supabase is still to create a public users table that mirrors the necessary information from auth.users, as it provides better control over data access and adheres to Supabase's security model Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database). If you're encountering persistent issues or have specific requirements that prevent you from creating a new table, it might be beneficial to consult the Supabase documentation or reach out to their support for guidance on best practices for your particular use case. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai how can i use multiple db schemas in supabase client?

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- To use multiple database schemas with the Supabase client in refine, you can utilize the meta property in your data hooks. This allows you to specify different schemas for different resources or queries. Here's how you can do it: 1. First, make sure your Supabase client is set up to use the public schema by default. This is typically done in your supabaseClient.ts file:

export const supabaseClient = createClient(SUPABASE_URL, SUPABASE_KEY, {
  db: {
    schema: "public",
  },
  auth: {
    persistSession: true,
  },
});

export const supabaseClient = createClient(SUPABASE_URL, SUPABASE_KEY, {
  db: {
    schema: "public",
  },
  auth: {
    persistSession: true,
  },
});

Establishing Supabase connection (https://refine.dev/docs/data/packages/supabase/#establishing-supabase-connection) 2. Then, in your data hooks (like useTable, useList, etc.), you can specify a different schema using the meta property:

const { tableProps, tableQueryResult } = useTable({
  resource: "your_table",
  meta: {
    schema: "your_custom_schema",
  },
});

const { tableProps, tableQueryResult } = useTable({
  resource: "your_table",
  meta: {
    schema: "your_custom_schema",
  },
});

Using meta to pass values to data provider (https://refine.dev/docs/data/packages/supabase/#schema---using-custom-schema) 3. If you want to use a specific schema for all operations on a particular resource, you can set it in your resource configuration:

<Refine
  dataProvider={dataProvider(supabaseClient)}
  resources={[
    {
      name: "posts",
      // This resource will use the "public" schema by default
    },
    {
      name: "products",
      // This resource will use the "custom_schema" for all operations
      meta: { schema: "custom_schema" },
    },
  ]}
/>

<Refine
  dataProvider={dataProvider(supabaseClient)}
  resources={[
    {
      name: "posts",
      // This resource will use the "public" schema by default
    },
    {
      name: "products",
      // This resource will use the "custom_schema" for all operations
      meta: { schema: "custom_schema" },
    },
  ]}
/>

Multiple Data Providers - Usage (https://refine.dev/docs/data/data-provider/#usage-1) 4. You can also override the schema for individual queries even if you've set a default schema for a resource:

const { data, isLoading } = useOne({
  resource: "products",
  id: 1,
  meta: {
    schema: "another_schema", // This will override the schema set in the resource config
  },
});

const { data, isLoading } = useOne({
  resource: "products",
  id: 1,
  meta: {
    schema: "another_schema", // This will override the schema set in the resource config
  },
});

By using these methods, you can work with multiple schemas in your Supabase database within your refine application. Remember to ensure that your Supabase permissions are set up correctly to allow access to the different schemas you're using. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai i need to use 2 schemas in a single request

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- To use two schemas in a single request with Supabase in refine, you'll need to take a slightly different approach. The standard meta.schema option doesn't support multiple schemas in one query. However, you can achieve this by using a custom SQL query or by leveraging Supabase's ability to reference tables from different schemas directly in your select statement. Here's how you can do it: 1. Using a custom SQL query:

import { useCustom } from "@refinedev/core";
import { supabaseClient } from "utility";

const { data, isLoading, error } = useCustom({
  url: "",
  method: "get",
  meta: {
    rawQuery: `
      SELECT 
        schema1.table1.*,
        schema2.table2.*
      FROM 
        schema1.table1
      JOIN 
        schema2.table2 ON schema1.table1.id = schema2.table2.table1_id
    `
  }
});

import { useCustom } from "@refinedev/core";
import { supabaseClient } from "utility";

const { data, isLoading, error } = useCustom({
  url: "",
  method: "get",
  meta: {
    rawQuery: `
      SELECT 
        schema1.table1.*,
        schema2.table2.*
      FROM 
        schema1.table1
      JOIN 
        schema2.table2 ON schema1.table1.id = schema2.table2.table1_id
    `
  }
});

In this approach, we're using the useCustom hook from refine to execute a raw SQL query that joins tables from two different schemas Multiple Data Providers (https://refine.dev/docs/guides-concepts/data-fetching/#multiple-data-providers). 2. Using Supabase's cross-schema references:

import { useList } from "@refinedev/core";

const { data, isLoading, error } = useList({
  resource: "schema1.table1",
  meta: {
    select: "*, schema2.table2(*)"
  }
});

import { useList } from "@refinedev/core";

const { data, isLoading, error } = useList({
  resource: "schema1.table1",
  meta: {
    select: "*, schema2.table2(*)"
  }
});

This method uses Supabase's ability to reference tables from different schemas directly in the select statement Using meta to pass values to data provider (https://refine.dev/docs/data/packages/supabase/#schema---using-custom-schema). Both of these methods allow you to fetch data from two different schemas in a single request. The choice between them depends on your specific use case: - Use the custom SQL query if you need more complex joins or operations. - Use the cross-schema reference if your needs are simpler and you want to leverage refine's built-in data hooks. Remember to ensure that your Supabase permissions are set up correctly to allow access to both schemas you're trying to query. Also, note that while these methods allow you to query across schemas, they may have performance implications for large datasets. Always consider the efficiency of your queries, especially when working with multiple schemas. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai update this query so it will update public managers when auth.users was updated -- Create a table for public managers.
create table managers (
id uuid references auth.users not null primary key,
updated_at timestamp with time zone,
username text unique,
full_name text,
avatar_url text
);
-- This trigger automatically creates a public.managers entry when a new user signs up via Supabase Auth.
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
insert into public.managers (id, full_name, avatar_url)
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
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_public_user();

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- To update the public.managers table when auth.users is updated, we need to modify the existing trigger and create a new one for handling updates. Here's the updated query that will create a new manager entry when a user is created and update the existing entry when a user is updated:

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, updated_at)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', now());
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, updated_at)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', now());
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

This updated query does the following: 1. It keeps the original table creation and the insert trigger for new users. 2. It adds a new function handle_public_user_updated() that updates the public.managers table when a user is updated in auth.users. 3. It creates a new trigger on_auth_user_updated that fires after an update on auth.users and executes the handle_public_user_updated() function. Now, whenever a user is created or updated in auth.users, the corresponding entry in public.managers will be created or updated accordingly. This approach follows the pattern described in the Refine documentation for setting up Supabase for user management Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database), but extends it to handle both inserts and updates. Remember to ensure that your Supabase permissions are set up correctly to allow these operations on both the auth.users and public.managers tables. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai add delete also so when a user from auth.users is deleted it will be deleted from public.managers

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- Certainly! I'll modify the SQL script to include a delete trigger that will remove the corresponding entry from public.managers when a user is deleted from auth.users. Here's the updated SQL script:

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, updated_at)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', now());
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

-- This function handles user deletions
create or replace function public.handle_public_user_deleted()
returns trigger as $$
begin
  delete from public.managers
  where id = old.id;
  return old;
end;
$$ language plpgsql security definer;
-- This trigger deletes the public.managers entry when a user is deleted from auth.users
create trigger on_auth_user_deleted
  after delete on auth.users
  for each row execute procedure public.handle_public_user_deleted();

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, updated_at)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', now());
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

-- This function handles user deletions
create or replace function public.handle_public_user_deleted()
returns trigger as $$
begin
  delete from public.managers
  where id = old.id;
  return old;
end;
$$ language plpgsql security definer;
-- This trigger deletes the public.managers entry when a user is deleted from auth.users
create trigger on_auth_user_deleted
  after delete on auth.users
  for each row execute procedure public.handle_public_user_deleted();

This updated script adds the following: 1. A new function handle_public_user_deleted() that deletes the corresponding entry from public.managers when a user is deleted from auth.users. 2. A new trigger on_auth_user_deleted that fires after a delete operation on auth.users and executes the handle_public_user_deleted() function. Now, the public.managers table will stay in sync with auth.users for all operations: - When a new user is created in auth.users, a corresponding entry is created in public.managers. - When a user is updated in auth.users, the corresponding entry in public.managers is updated. - When a user is deleted from auth.users, the corresponding entry in public.managers is deleted. This approach ensures that your public.managers table remains consistent with the auth.users table, following the pattern described in the Refine documentation for setting up Supabase for user management Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database). Remember to test these triggers thoroughly in a safe environment before applying them to your production database, and ensure that your Supabase permissions are set up correctly to allow these operations on both the auth.users and public.managers tables. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai i cant delete these functions because other objects depends on it who depends on these functions

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- To find out which objects depend on these functions in Supabase, you can use the PostgreSQL system catalogs. Specifically, you can query the pg_depend and related tables to identify the dependencies. Here's a query that can help you find the objects depending on your functions:

SELECT DISTINCT
    dep.deptype,
    dep.refclassid::regclass AS ref_class,
    dep.refobjid::regclass AS ref_object,
    dep.refobjsubid,
    dep.classid::regclass AS dep_class,
    dep.objid::regclass AS dep_object,
    dep.objsubid
FROM pg_depend dep
JOIN pg_proc func ON func.oid = dep.refobjid
WHERE func.proname IN ('handle_new_public_user', 'handle_public_user_updated', 'handle_public_user_deleted')
  AND dep.deptype != 'i';

SELECT DISTINCT
    dep.deptype,
    dep.refclassid::regclass AS ref_class,
    dep.refobjid::regclass AS ref_object,
    dep.refobjsubid,
    dep.classid::regclass AS dep_class,
    dep.objid::regclass AS dep_object,
    dep.objsubid
FROM pg_depend dep
JOIN pg_proc func ON func.oid = dep.refobjid
WHERE func.proname IN ('handle_new_public_user', 'handle_public_user_updated', 'handle_public_user_deleted')
  AND dep.deptype != 'i';

This query will show you the objects that depend on the functions handle_new_public_user, handle_public_user_updated, and handle_public_user_deleted. The most likely dependencies are the triggers we created in the previous steps: 1. on_auth_user_created depends on handle_new_public_user 2. on_auth_user_updated depends on handle_public_user_updated 3. on_auth_user_deleted depends on handle_public_user_deleted To remove these functions, you'll need to drop the triggers first, then the functions. Here's how you can do it:

-- Drop triggers
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DROP TRIGGER IF EXISTS on_auth_user_updated ON auth.users;
DROP TRIGGER IF EXISTS on_auth_user_deleted ON auth.users;

-- Drop functions
DROP FUNCTION IF EXISTS public.handle_new_public_user();
DROP FUNCTION IF EXISTS public.handle_public_user_updated();
DROP FUNCTION IF EXISTS public.handle_public_user_deleted();

-- Drop triggers
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
DROP TRIGGER IF EXISTS on_auth_user_updated ON auth.users;
DROP TRIGGER IF EXISTS on_auth_user_deleted ON auth.users;

-- Drop functions
DROP FUNCTION IF EXISTS public.handle_new_public_user();
DROP FUNCTION IF EXISTS public.handle_public_user_updated();
DROP FUNCTION IF EXISTS public.handle_public_user_deleted();

Run these commands in order. First drop the triggers, then the functions. If you're still encountering issues after this, it's possible that there are other objects in your database that depend on these functions. In that case, you'll need to identify those dependencies using the query provided above and address them before you can drop the functions. Remember to be cautious when dropping database objects, especially in a production environment. Always backup your data before making significant changes to your database structure. Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database) ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

ratty-blushOP•4w ago

@kapa.ai add email and phone number to public.managers and also update functions -- Create a table for public managers.
create table managers (
id uuid references auth.users not null primary key,
updated_at timestamp with time zone,
username text unique,
email text unique, full_name text,
avatar_url text
);
-- This function handles new user creation create or replace function public.handle_new_public_user() returns trigger as $$ begin insert into public.managers (id, full_name, avatar_url, updated_at) values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url', now()); return new; end; $$ language plpgsql security definer; -- This trigger creates a public.managers entry when a new user signs up create trigger on_auth_user_created after insert on auth.users for each row execute procedure public.handle_new_public_user(); -- This function handles user updates create or replace function public.handle_public_user_updated() returns trigger as $$ begin update public.managers set full_name = new.raw_user_meta_data->>'full_name', avatar_url = new.raw_user_meta_data->>'avatar_url', updated_at = now() where id = new.id; return new; end; $$ language plpgsql security definer; -- This trigger updates the public.managers entry when a user is updated create trigger on_auth_user_updated after update on auth.users for each row execute procedure public.handle_public_user_updated();

kapa.ai•4w ago

Let me check the knowledge base for you again. One moment! :books: ---------------------- Certainly! I'll modify the SQL script to add email and phone number to the public.managers table and update the functions accordingly. Here's the updated SQL script:

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  email text unique,
  phone_number text,
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, email, phone_number, updated_at)
  values (
    new.id, 
    new.raw_user_meta_data->>'full_name', 
    new.raw_user_meta_data->>'avatar_url', 
    new.email,
    new.raw_user_meta_data->>'phone_number',
    now()
  );
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    email = new.email,
    phone_number = new.raw_user_meta_data->>'phone_number',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

-- Create a table for public managers.  
create table managers (  
  id uuid references auth.users not null primary key,  
  updated_at timestamp with time zone,  
  username text unique,  
  email text unique,
  phone_number text,
  full_name text,  
  avatar_url text  
);  

-- This function handles new user creation
create or replace function public.handle_new_public_user()
returns trigger as $$
begin
  insert into public.managers (id, full_name, avatar_url, email, phone_number, updated_at)
  values (
    new.id, 
    new.raw_user_meta_data->>'full_name', 
    new.raw_user_meta_data->>'avatar_url', 
    new.email,
    new.raw_user_meta_data->>'phone_number',
    now()
  );
  return new;
end;
$$ language plpgsql security definer;

-- This trigger creates a public.managers entry when a new user signs up
create trigger on_auth_user_created
  after insert on auth.users 
  for each row execute procedure public.handle_new_public_user();

-- This function handles user updates
create or replace function public.handle_public_user_updated()
returns trigger as $$
begin
  update public.managers
  set 
    full_name = new.raw_user_meta_data->>'full_name',
    avatar_url = new.raw_user_meta_data->>'avatar_url',
    email = new.email,
    phone_number = new.raw_user_meta_data->>'phone_number',
    updated_at = now()
  where id = new.id;
  return new;
end;
$$ language plpgsql security definer;

-- This trigger updates the public.managers entry when a user is updated
create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_public_user_updated();

Here are the changes made: 1. Added email and phone_number columns to the managers table. 2. Updated the handle_new_public_user() function to include email and phone_number when inserting a new manager. 3. Updated the handle_public_user_updated() function to include email and phone_number when updating a manager. Note that: - The email is directly accessed from new.email as it's a standard field in the auth.users table. - The phone_number is assumed to be stored in the raw_user_meta_data JSON field. If it's stored differently in your setup, you may need to adjust this. - The email field is set as unique in the managers table, matching the uniqueness constraint typically found in auth.users. This setup follows the pattern described in the Refine documentation for setting up Supabase for user management, with the addition of email and phone number fields Setting Up Supabase for Refine (https://refine.dev/blog/refine-pixels-3/#2-adding-tables-to-a-supabase-database). Remember to test these changes thoroughly in a safe environment before applying them to your production database. Also, ensure that your Supabase permissions are set up correctly to allow these operations on both the auth.users and public.managers tables. ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ----------------------

kapa.ai•4w ago

<@447478993101389824> Kapa.ai is still learning and improving, please let me know how I did by reacting below.

unwilling-turquoise•3w ago

Hello @danyalutsevich ,  kapa.ai is now available for free at https://refine.dev/docs! To improve our community interaction on the Refine Discord server and keep our focus on Q&A sharing, we invite you to our website to use kapa.ai. 🚀

i have auth user relation in my public table in supabase

Did you find this page helpful?