Specify Postgres schema used for Directus tables

[torpid birch]

I have set up Directus with a Postgres connection to Supabase.

I was wondering if there's a way to specify the schema used for the tables used for the Directus App, so everything with directus_

There's no need for it to be in the public schema as it really should be hidden from the Supabase API.

I saw you can make Directus aware of different schemas, which is nice, but I could't find anything in the official documentation, nor would it be possible to specify in Directus admin that which DB schema should be used when adding a new collection.

From a different message this would be the config:
DB_SEARCH_PATH="public,other,schema"

[grave leaf]

Thanks for posting! This is a community powered server, so you may or may not get an answer based on available help and expertise. To increase your chances of somebody being able to help you, please help us help you making sure you:

• Adding an explanation of exactly what you're trying to achieve.
• Adding any and all related code or previous attempts.
• Describing the exact issue or error you are facing.
• Posting any screenshots if applicable.
• Reading through https://stackoverflow.com/help/how-to-ask.

When you're done with this thread, please close it. Thanks! ✨

(If you have a support agreement and need help, please contact the core team via email.)

[nocturne flint]

afaik Directus can be made aware of other schemas for reading but the first in the list will be used for core tables and creating new tables

[torpid birch]

Interesting. So if set it up with it being:
DB_SEARCH_PATH="directus,public''
it could technically work, but I when I use Directus admin to add new collections it wouldn't be added to public, but could possibly have it moved with Postgres function

[nocturne flint]

Can't say i've tried doing that 😬

[fierce cairn]

I have a database with 8 schemas in addition to public. Your question is the same as mine. I would like to create tables in other schemas and not just in the first one.

[rough needle]

Does the syntax work like this? Because some time ago, people talked about a syntax like "array:schem1,schema2". Does it work by just passing schemas as a list?

[nocturne flint]

both notations should work, array: is just explicitly casting it to an array which we also default to underwater (for this specific var)

https://docs.directus.io/self-hosted/config-options.html#environment-syntax-prefix

[rough needle]

Ah ok, thanks. If I make a schema "core" for system tables and my custom ones (that I create in data studio) and want directus to use postgis that is installed in public schema, do I have to define it like "core,public"? Or do I need to include more schemas like the tiger, tiger_data or topology schemas?

[nocturne flint]

IIRC you'll need to define any schema you want to directly read from

[rough needle]

Ah ok, so I suppose that without setting that env var, directus only uses public schema. As geo data stuff worked fine that way, I suppose that those other schemas are used by postgis itself but are irrelevant to directus, so I will just use "core,public". I have not yet understood in detail how postgis and related extensions work and if directus needs actually fuzzystrmatch also added to "core" in my case.