How do you get the table names of all of the tables within the project?

[justinfarrelldev]

Hello,

I am curious how you would go about getting all the table names programmatically from the project?

I want to use this info to create queries which automatically remove entries for specific users across all of the tables (IE, run "DELETE * from (table names here) WHERE user_id = 123").

The reason I don't just want to hardcode the table names is because I want this to scale with the app when I inevitably add new tables (and that the new rows will always be related to specific user desires, at least for the foreseeable future).

So far, I have tried using this but it didn't seem to do anything (forgive me, I am relatively new to Supabase in this context as well as this library):

    supabaseClient
        .schema('information_schema')
        .from('tables')
        .delete()
        .eq('user_id', user?.id);

I assumed the logic was wrong with the above, so I tried doing this but Typescript yelled at me because .eq does not exist in this context:

    supabaseClient
        .schema('information_schema')
        .from('tables').delete().select('*').eq('user_id', user.id)

Lastly, I tried using ChatGPT and it gave me this:

    const { data, error } = await supabaseClient
        .from('information_schema.tables')
        .select('table_name')
        .eq('table_schema', 'public') // Adjust schema name if needed
        .eq('table_type', 'BASE TABLE');

This looks close and doesn't have any visual errors, but when ran it gives this: 'relation "public.information_schema.tables" does not exist'

I would really appreciate an explanation on how to grab the table names from the project programmatically like this (or what the best practice is, if this is a bad idea). Thank you!

[GaryAustin1]

You are not going to be able to do this from a REST client directly. You will need an RPC call to a security definer Postgres function to get access to these schemas that should not or cannot be exposed to the API. There also will be privilege issues on them.

There is an open API call than can get you tables. https://postgrest.org/en/v12/references/api/openapi.html

You really should use though cascade deletes on your foreign keys columns to delete all information from tables when the parent table (maybe all the way up to auth.users) row is deleted. Then you don't need to worry about it and it is enforced by your table relationships.

[orliesaurus]

I would love the product team to explore the ability to add a way to find tables and other information about the database, this would be very good for anyone who is trying to create AI agents with Supabase :) Cheerios m8s

[CodeFromAnywhere]

I second this. If we could get the openapi as described by PostgREST, that would be great.