How to access custom schema from supabase-js client?

Question

I love Supabase but our team needs to use schema functionality that PostgreSQL offers - unfortunately we have been unsuccessfully to make schemas working so far.

Other info:

PostgreSQL 14
newest version of the Supabase JS SDK's
we have supabase hosted with Supabase's pro plan

What we have tried:

We created a new schema and added access to all types of authentication (why all? We first thought that maybe there is an error with authentication):
```
CREATE SCHEMA Brano;
GRANT USAGE ON SCHEMA Brano TO postgres, anon, authenticated, service_role, dashboard_user;
```
Exposed schema to the API via this setting:

Tried the following code:

var options = {
    schema: 'brano'
}

const supabaseUrl = 'supabaseUrl'
const supabaseKey = 'supabaseKey'
const supabaseClient = createClient(supabaseUrl, supabaseKey, options);

const { data, error } = await supabaseClient
    .from('test_brano')
    .insert([{
        data: 123
}]);

Got this error:

{
    "message":"permission denied for table test_brano",
    "code":"42501",
    "details":null,
    "hint":null
}

Links and documentation that we have tried reading (unfortunately we didn't make it work either way):

Did we missed something? Thanks in advance!

You also need grants, see my answer here: stackoverflow.com/a/74193175/1742820 — Pencilcheck
– Pencilcheck, Commented Oct 25, 2022 at 11:13

Mansueli · Accepted Answer · 2023-05-25 13:34:21Z

16

In addition to the first two steps you did:

Granting usage:

CREATE SCHEMA Brano;

GRANT USAGE 
ON SCHEMA Brano 
TO postgres, anon, authenticated, service_role, dashboard_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA brano
GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role, dashboard_user;

Exposing the schema in the Settings:

There's a third step that was missing:

Granting actions to be able to insert/select data:

GRANT SELECT, INSERT, UPDATE, DELETE 
ON ALL TABLES IN SCHEMA brano 
TO postgres, authenticated, service_role, dashboard_user, anon;

GRANT USAGE, SELECT 
ON ALL SEQUENCES IN SCHEMA brano 
TO postgres, authenticated, service_role, dashboard_user, anon;

⚠️ Warning ⚠️

You must set these grants again for every new table created in the custom schema.

Then you can call it as in your example:

Supabase JS v2:

    const options = {
      db: { schema: 'brano' }
    };
    const supabase = createClient(supabaseUrl, SUPABASE_KEY, options)
    const d = new Date(2018, 11, 24, 10, 33, 30, 0);
    const { data, error } = await supabase
      .from('test_brano')
      .insert([
        { data: 3, created_at: d }
      ])
    console.log(data)
    if (error) {
        console.log("error getting results");
        throw error;
    }

Supabase JS v1:

    const options = {
      schema: 'brano'
    }
    const supabase = createClient(supabaseUrl, SUPABASE_KEY, options)
    const d = new Date(2018, 11, 24, 10, 33, 30, 0);
    const { data, error } = await supabase
      .from('test_brano')
      .insert([
        { data: 3, created_at: d }
      ])
    console.log(data)
    if (error) {
        console.log("error getting results");
        throw error;
    }

edited May 25, 2023 at 13:34

answered Aug 8, 2022 at 18:29

Mansueli

7,1148 gold badges40 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mansueli Over a year ago

@NikitaChernenko the OP mentioned this in step 2.

Mykyta Chernenko Over a year ago

Missed it, I'll delete my comment

Jakub Szlaur · Accepted Answer · 2022-12-07 12:38:02Z

3

To access schema using the newest Supabase JS SDK'S you need to use different code according to the migration documentation:

New method:

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY, {
  db: {
    schema: 'custom',
  },
})

The main change is that: All client specific options within the constructor are keyed to the library: PR

And of course you need to run these queries:

CREATE SCHEMA IF NOT EXISTS  custom;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA custom TO ROLES_YOU_WANT_TO_GIVE_ACCESS;
GRANT USAGE ON SCHEMA custom TO ROLES_YOU_WANT_TO_GIVE_ACCESS;

answered Dec 7, 2022 at 12:38

Jakub Szlaur

2,1523 gold badges21 silver badges48 bronze badges

Comments

saike · Accepted Answer · 2023-01-11 17:10:55Z

2

Also, check out this very important github thread: https://github.com/supabase/supabase/discussions/10428

There is instructions to local development.

And don't forget to add -s schema_name when you diffing your local db changes

answered Jan 11, 2023 at 17:10

saike

1,06510 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to access custom schema from supabase-js client?

3 Answers 3

⚠️ Warning ⚠️

Supabase JS v2:

Supabase JS v1:

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

⚠️ Warning ⚠️

Supabase JS v2:

Supabase JS v1:

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related