db-postgres: public schema is hard coded in migration steps

[ainteger]

Link to reproduction

No response

Describe the Bug

I try to use db-postgres with another schema then public by using a connectionstring with schema provided but I only get tables created in public schema.

I am lacking the possibility to set specific schema for migrations and I also see some hard-coded references to public schema

• packages/db-postgres/src/connect.ts row 25-26
• packages/db-postgres/src/migrateFresh.ts row 39
• packages/db-postgres/src/utilities/migrationTableExists.ts row 6

1. Is it possible to set those to public only if schema are not set in connectionstring?
2. Can we change so that tables are created in the correct schema?

To Reproduce

1. Run npx create-payload-app@latest
2. Select blank project template
3. Create an .env with the value DATABASE_URI=postgres://myuser:[email protected]:5432/mypayloaddb?schema=notpublicschema

Excepting payload tables to be created in database mypayloaddb and schema notpublicschema

Actual payload tables to be created in database mypayloaddb and schema public

Payload Version

^2.0.0

Adapters and Plugins

db-postgres: 0.2.1

[primalivet]

+1 Also need to use another schema than public.

[dsod]

Searched for a possibility to do this as well. What I found and tried:

• Passing ?schema={shemaName} to the connection string does not have any effect.
• Setting the search_path postgres option does have an effect, but migrations will still point to the public schema
• Drizzle has an API for this: https://orm.drizzle.team/docs/schemas. I suppose a schema name option would have to be exposed by payload and the schema definitions needs to be attached to the schema instead of the regular db object.

[denolfe]

The current postgres adapter only has support for the public. However, it looks like this should be possible.

We'd need to add an additional configuration option for the schema name to the adapter, then make sure all of our touchpoints with the DB are properly scoped to the specified schema.

Moving this to a discussion as a feature request.