Repository navigation
RFC: db-postgres opt-in tableSchema option to place tables and enums in more than one schema #19607
Bascht74
started this conversation in
Feature Requests & Ideas
Replies: 1 comment
|
Draft PR: #19608. Limits and open points are listed in the PR. The numbers in the post above are out of date:
|
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Uh oh!
There was an error while loading. Please reload this page.
Summary
An optional Postgres adapter option that picks the schema per table and enum:
Problem
We run a modular app on Payload 4 and want each module's tables in its own schema, so that roles for tools outside Payload (reporting, exports) can be granted per schema. Today every table and enum goes through the single
adapter.pgSchema. The only way around that is anafterSchemaInithook that writes Drizzle internals, and several places in@payloadcms/drizzleassume one schema (line numbers onmainat56033d9):postgres/schema/buildDrizzleTable.tsL63 (enum), L209 (table);postgres/init.tsL27 (_localesenum)utilities/pushDevSchema.tsL51postgres/dropDatabase.tsL6–7 (migrate:fresh)find/buildPolymorphicJoinWhere.tsL369–372utilities/createSchemaGenerator.tsL80–104 (generate:db-schema) and the predefined migrationslocalize-statusandsizes-to-variantsProposal
dropDatabaseSQL are byte-identical before and after the change (checked with thetest/databaseconfig in three variants: default,schemaName: 'custom',idType: 'uuid'; the snapshot differs only in its randomid, as two runs onmaindo).payload_migrationsstays in the default schema.@experimental, likeschemaName. The known limit from [3.0 Beta] Changes not applying to tables when using schemaName #5822 (a name may exist in only one schema) applies and is documented.Prototype and draft PR
We have a branch against
main(15 files, +811/−15): the option, the three build sites, push filter,dropDatabase, docs section, 2 unit specs and an int suite intest/database/table-schema/(runs in the existingpostgresvariant). The existingtest/databasesuite passes onpostgres,postgres-custom-schemaandsqlite. With a resolver that spreads every table and enum over three schemas, the same suite gives 218 passed / 2 failed; both failures come from a test helper intest/database/int.spec.tsthat looks up an index only inschemaName || 'public', not from the adapter.I will open a draft PR for the first part after posting this and link it below, so you can see the size of the change. It is a draft on purpose: happy to rework or close it depending on your answers.
Planned split
dropDatabase, docs, tests.generate:db-schema, predefined migrations, a CI variant running the full suite withtableSchema(the test helpertest/__helpers/shared/clearAndSeed/reset.tsL35–40 would need to qualify per table).Related: #8196 (schema hooks), #9885 (adapter-specific options belong on the adapter), #5822, discussion #5590, closed WIP #4015.
Questions
collectionSlug/globalSlugas well? Or only a small helper in the schema hooks, likeextendTable?pushDevSchema.ts. Should we wait for them and rebase?db-vercel-postgresand the polymorphic join fix, or keep PR 1 minimal and list them as not supported yet?Disclosure: some of this text is checked and modified with Claude Code using the repo's
AGENTS.md.I have read the changes, and the results above come from local runs. :-)
Btw: Thanks for Payload!!!!! Really great to use!!!
All reactions