Allow Postgres adapter's beforeSchemaInit to handle sql views

[JesperWe]

Describe the Bug

The docs state about beforeSchemaInit:

Runs before the schema is built. You can use this hook to extend your database structure with tables that won't be managed by Payload. ... One use case is preserving your existing database structure when migrating to Payload.

The ability to perserve existing database structure is however limited to databases that have tables that are not to be managed by Payload.
If the DB has SQL views defiend, Drizzle will attempt to delete those views and there is no way in the PostgresSchema type to add views, it only supports Enums, Relations and Tables.

Drizzle itself supports adding pgView, but that feature does not seem to be propagated.

Link to the code that reproduces this issue

https://github.com/payloadcms/payload/blob/beta/packages/drizzle/src/postgres/types.ts

Reproduction Steps

One easy way to reproduce is to enable the very popular PostGIS extension in a Postgres database

CREATE EXTENSION postgis;

and then try to enable Payload for that database. The extra spatial_ref_sys table required by PostGIS can be handled in beforeSchemaInit but the required views can not. Drizzle will try to delete them, which will fail.

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

Binaries:
Node: 20.11.0
npm: 10.8.2
Yarn: 1.22.19
pnpm: 9.12.2
Relevant Packages:
payload: 3.0.0-beta.123
next: 15.0.1
@payloadcms/db-mongodb: 3.0.0-beta.123
@payloadcms/db-postgres: 3.0.0-beta.123
@payloadcms/graphql: 3.0.0-beta.123
@payloadcms/next/utilities: 3.0.0-beta.123
@payloadcms/richtext-lexical: 3.0.0-beta.123
@payloadcms/richtext-slate: 3.0.0-beta.123
@payloadcms/translations: 3.0.0-beta.123
@payloadcms/ui/shared: 3.0.0-beta.123
react: 19.0.0-rc-65a56d0e-20241020
react-dom: 19.0.0-rc-65a56d0e-20241020

[jmikrut]

Hey @JesperWe — I think this is something that we can likely add to Payload. Good call. I will add this to the roadmap and we should be able to get to it shortly!

[max-degterev]

Yep and integrated support for GIS would be awesome

[JesperWe]

Yep and integrated support for GIS would be awesome

Yes it would, but it is something that would not be too hard to implement yourself with all the flexibility of Custom Components that Payload has.

This issue does make it way harder, since adding the PostGIS extension to a DB breaks Payload in a deep Drizzle way that is not that easy to work around.

For now I work around it by storing GeoJSON in a Payload JSON field. But without PostGIS you loose all ability to do stuff like sorting based on distance etc.

So thanks for making it a prio roadmap thing!

[max-degterev]

With first party GIS we get polygon/polyline which is essential for routing

[stefgootzen]

Also big +1 for this. We solve it by storing our geometries in a completely different database. That obviously doesn't allow you to use joins/relationships/fk's to payload's managed database.

[r1tsuu]

Update here. PostGIS / Point field support will be very soon, check the comment from @ainsleyclark #8644 (comment) in the related discussion for this!

As soon as Drizzle's PR will be released I think we can also merge this.

[JesperWe]

@denolfe

Was this closed because you can now add arbitrary views to the schema? I couldn't find where that was implemented if so.

Or just because PostGIS now works? (This was just an example here, not the core of the issue)

[denolfe]

This was a mass closure of all roadmap-complete discussions. Looks like there is more here to look into. Reopening.

[cameronrdecker]

Hello, just wondered if there was any movement on this feature, or if I should try to find a workaround in the meantime? Thank you!

[JesperWe]

Well @jmikrut originally assigned this a Roadmap prio 1 label and said "we should be able to get to it shortly", but at the v3 release it got swept up in some cleanup and marked complete (which it is not) so maybe we could get it back on the radar?

It is a small task (adding Drizzle pgView in parallel with the others) that would unblock people with custom views in their existing databases trying to add Payload.

[JesperWe]

@jmikrut will #10984 help here?

I am facing this issue again now where I would like to use two tools (payload and a custom backend) operating on the same postgres database, where the custom backend needs an SQL view in the database.

[JesperWe]

Looped back to this today and found out that Drizzle tableFilter is now exposed in Payload config.
Problem solved, since it allows to filter both tables and views.

[engin-can]

Any idea how I can use pgMaterializedView instead of pgTable in beforeSchemaInit?

import { postgresAdapter } from '@payloadcms/db-postgres'
import {
  integer,
  pgTable,
  serial,
} from '@payloadcms/db-postgres/drizzle/pg-core'

postgresAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          addedTable: pgTable('added_table', { <---------- HOW TO USE pgMaterializedView HERE?
            id: serial('id').notNull(),
          }),
        },
      }
    },
  ],
})