Custom schema filters missing causing conflicts on push

mcornielje090404 commented 7 months ago

Link to reproduction

No response

Describe the Bug

I am working on a project that requires the ability to use different schemas within the DB.

I have managed to setup targeting a custom schema successfully but have noticed when attempting to push to the schema via payload an error occurs if any of the tables in the new schema have the same name as a 'public' schema table.

For instance if I have a 'users_organisations' table on public and then point payload to a 'test' schema I cannot push my tables to the 'test' schema without receiving the following error.

error: relation "test.users_organisations" does not exist
    at /{{MY DIRECTORY}}/node_modules/.pnpm/pg-pool@3.6.1_pg@8.11.3/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DrizzleORMPgClient.query (/{{MY DIRECTORY}}/node_modules/.pnpm/drizzle-kit@0.20.14-1f2c838/node_modules/drizzle-kit/payload.js:34498:21)
    at async apply (/{{MY DIRECTORY}}/node_modules/.pnpm/drizzle-kit@0.20.14-1f2c838/node_modules/drizzle-kit/payload.js:36648:9)
    at pushDevSchema (/{{MY DIRECTORY}}/node_modules/.pnpm/@payloadcms+db-postgres@3.0.0-alpha.49_@types+react@18.2.64_payload@3.0.0-alpha.49_react@18.2.0/node_modules/@payloadcms/db-postgres/dist/utilities/pushDevSchema.js:35:5)
    at Object.connect (/{{MY DIRECTORY}}/node_modules/.pnpm/@payloadcms+db-postgres@3.0.0-alpha.49_@types+react@18.2.64_payload@3.0.0-alpha.49_react@18.2.0/node_modules/@payloadcms/db-postgres/dist/connect.js:77:5)
    at localOperations (/{{MY DIRECTORY}}/node_modules/.pnpm/payload@3.0.0-alpha.49_@swc+core@1.4.8_@swc+types@0.1.6/node_modules/payload/dist/index.js:239:27)
    at localOperations (/{{MY DIRECTORY}}/node_modules/.pnpm/payload@3.0.0-alpha.49_@swc+core@1.4.8_@swc+types@0.1.6/node_modules/payload/dist/index.js:239:27)
    at resetBaseDB (/{{MY DIRECTORY}}/src/database/reset/resetBaseDb/resetBaseDb.ts:5:18)
    at runAndKill (/{{MY DIRECTORY}}/src/database/runAndKill.ts:6:2) {
  length: 122,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'namespace.c',
  line: '428',
  routine: 'RangeVarGetRelidExtended'
}

After a little while of debugging and testing I realised that if I dropped my public schema so that it did not contain the 'users_organisations' table I could then push to the 'test' schema successfully.

In the drizzle documentation there is mention that you cannot have the same table across multiple schemas however, this can be bypassed using a schemaFilter.

I noticed in the codebase for payload/db-postgres there was no usage of the schemaFilter nor any way to add your own usage of the schemaFilter.

I believe implementing a way to add your own schemaFilter to the postgres db adapter would allow circumventing of this limitation and resolve it until drizzle develop a more permanent solution to the issue.

Note: This works fine the other way around from my testing. Having a 'test' schema table and then pushing a 'public' schema table with the same name goes through fine.

To Reproduce

Create a payload instance pointing at public and push the schema
Point the payload instance at a custom schema
Attempt to push the payload tables into the custom schema

Payload Version

3.0.0-alpha.49

Adapters and Plugins

@payloadcms/db-postgres

DanRibbens commented 7 months ago

I'm glad you pointed this out! I ran into some odd behavior in my testing and couldn't recreate it so I moved on. This explains what I was seeing now, thanks for opening this issue!

DanRibbens commented 7 months ago

@mcornielje090404 I can't decide if this needs to be an additional configurable property or if it is better to simply pass the db adapter schemaName through to drizzle in the schemaFilter property.

It seems reasonable to say that each Payload project can only use one schema and enforce that this way.

mcornielje090404 commented 7 months ago

@DanRibbens I reckon since we can only have 1 schema at any given point currently just automatically pass it through, would reduce possibility of devs accidentally missing it. Can revise later if changes are made to schema handling in Payload. Edit: and drizzle*

payloadcms / payload