Method to set schema on a call by call basis.

[GaryAustin1]

Feature request

Right now you can only set a schema in initial supabase client setup. It would be very useful to be able to override the default ('public' normally) schema on individual calls.

Describe the solution you'd like

Add a postgrest-js operation .schema('myschema') that can be used with other operations on a single call basis.

Describe alternatives you've considered

Currently the below works in my environment. It does not impact storage and auth, but have not tested on realtime yet.

let schema = ''
var supabase = supabase.createClient(SUPABASE_URL, SUPABASE_KEY, {
    fetch: (requestInfo, requestInit) => {
        if (schema) requestInit.headers['Accept-Profile'] = schema
        return(fetch(requestInfo,requestInit))
    }

Then you do the following for a different schema

    schema = 'storage'
    const response = await supabase
        .from('objects')
        .select('*')
    schema = ''

For modular code one could add

export function setSchema(schemaName) {
 schema = schemaName
}

And call that before and after supabase database call to another schema.

Additional context

It appears the only thing required to make PostgREST work with an API available schema is setting the Accept-Profile' header. This should be easily done in postgrest-js with a .schema('myschema') function added to postgrest calls.

    const response = await supabase
        .from('objects')
        .schema('storage')
        .select('*')

[GaryAustin1]

I could swear I tested the code below before, and it did not pass down to postgrest-js after the initial setup, but seems to work (at least for javascript, not sure what typescript would complain about).

supabase.schema = 'storage'
await supabase.from('objects').select('*')
supabase.schema = 'public' // put it back

I don't find this documented anywhere, but it seems to work now... ONCE AGAIN not tested impact on realtime, if any...

Anyway to get the official way, if any to toggle schema for different requests?

[soedirgo]

+1 on using the builder pattern to switch schemas, though I'd put it before the .from(), e.g.

    const response = await supabase
        .schema('storage')
        .from('objects')
        .select()

[steve-chavez]

Might be worth thinking how the interface would look like when postgREST supports embedding on different schemas(just needs an additional header semantic). Maybe like:

const { data, error } = await supabase
  .schema({ countries: "core", cities: "protected" })
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)

I guess extending the .schema() method to take an object later on shouldn't be a problem.

[soedirgo]

That looks neat - do you have some links on PostgREST for more details on that?

[bdotsamir]

Plus one to this- it would be great if I didn't have to create a whole new client to access another public schema on my database.

[dshukertjr]

Hey @soedirgo @steve-chavez !

Are there any blockers to introduce an solution for this issue at the moment?

[steve-chavez]

@dshukertjr Just added some feedback on https://github.com/supabase/postgrest-js/pull/441#discussion_r1245390149

[bdotsamir]

What is the syntax at .schema({ countries: "core", cities: "protected" })? I don't understand what those values are except that countries and cities are tables

[Eldynn]

What is the syntax at .schema({ countries: "core", cities: "protected" })? I don't understand what those values are except that countries and cities are tables

I think it would be used to tell which tables is on which schema, I guess if in the query you have perhaps a join from a table in schema A on a table in schema B.

[steve-chavez]

const response = await supabase
  .schema('storage')
  .from('objects')
  .select()

https://github.com/supabase/postgrest-js/issues/280#issuecomment-1153479229

On second thought, since multiple schemas are not supported. I think we can just stick to having a schema() method.

Using a single schema for the API is also a best practice.

[ConProgramming]

const response = await supabase
  .schema('storage')
  .from('objects')
  .select()

#280 (comment)

On second thought, since multiple schemas are not supported. I think we can just stick to having a schema() method.

Using a single schema for the API is also a best practice.

@steve-chavez Why are multiple schemas not supported? I'm trying to do a foreign key join from a table in one schema to a table in another, and getting Searched for a foreign key relationship between 'table_a' and 'table_b' in the schema 'public', but no matches were found.