Postgres (out of beta)

[zubricks]

Close out all open issues relating to Postgres support.

[fpolli]

Really happy to see this. Ghost not supporting Postgres was a big reason I started looking for other CMSes to consider, and seeing Postgres support on Payload helped me stop here.

[Tj3rk]

@zubricks Will the postgres package go out of beta for payload v2? And if so, could you give an estimate when this will be done?

[thanaParis]

Now that Postgres support is out of beta, the Point field is still not supported. I am considering Payload CMS and currently use the postgis extension with Postgres. Is there any explanation of what the challenges were for adding Point? Any feature branch open or roadmap for when it would be added?

[kapilbarad]

@thanaParis Is db-postgres really out of Beta? The docs say it otherwise - https://payloadcms.com/docs/database/postgres

The Postgres database adapter is currently in beta. If you would like to help us test this package, we'd love to hear if you find any bugs or issues!

CC: @zubricks

[r1tsuu]

@thanaParis Is db-postgres really out of Beta? The docs say it otherwise - https://payloadcms.com/docs/database/postgres

The Postgres database adapter is currently in beta. If you would like to help us test this package, we'd love to hear if you find any bugs or issues!

CC: @zubricks

I don't think it's out, since v3 Payload is beta itself? We still have some issues, and it's also a good point about Point field, i think the reason behind this could be that Drizzle doesn't support it, but there are community solutions https://gist.github.com/ItsWendell/38ebe96b34d00d9138ce23cc363d7009

[DanRibbens]

I was not planning on backporting the changes we have been making to the database adapters to 2.0 though it wouldn't be a large effort if we do decide to. The database adapters are currently undergoing on an overhall to abstract out the drizzle implementation from the database specifics that need to be handled to maintain postgres support and add sqlite as a new db adapter.

Once that is done we could assess bringing the db changes back to v2 and remove the beta. I would prefer to move everything forward to v3, but if there is enough demand we would consider a v2 update that includes all these changes.

We have looked into supporting point fields for postgres but have shelved that initiative until we get through this refactor.

[kapilbarad]

@DanRibbens, I would love to see db-postgres out of beta for V2 as We can't shift to V3 for just db-postgres as V3 is still in beta. Do we've any rough timeline on when the V3 will be out of beta along with Postgres?

Also, As you mentioned that Backporting the changes of Postgres from V3 is not too much effort, Let us know if we can help there to speed things up.

[valerioleo]

I was super happy to see the progress in https://github.com/payloadcms/payload/releases/tag/v3.0.0-beta.39, where the relationships between tables have been made more in line to what is expected of a relational DB.

The next great part missing is composite keys, but I haven't found any mention of it being discussed.
Can you share if this is under your radar and considered necessary before marking the adapter out of beta? @DanRibbens

[DanRibbens]

Thanks @valerioleo, great feedback!

We could probably make a pattern for composite keys. We had a configurable option for custom indexes in mongodb but it was removed in v2 when we moved to the db adapter pattern. The general advice is to add indexes inside of migration, though I can conceptually see how that probably isn't going to work well using dev push for schema changes in development and then any migrations generated after might not include those indexes.

I'm interested in this discussion if you want to propose how you would want to configure composite keys. I can think of doing this one of two ways:

1. Reintroduce the indexes property to collections, as we had in v1 (MongoDB only). The typing for this would have to be generic and then be implemented in any db adapters. If the configuration is the same across DBs then collection settings would be ideal, but I suspect not all features will be possible across DBs long term and we have to closely consider what indexing options are that Payload supports.
2. Add collection indexes to the db adapter under a new property. Adding to the collections db specific properties is something we have mostly avoided so far so it might be useful to configure that on the db adapter perhaps.
3. A third option might be to add a db extension function that is called after DB init and before connect so that a project could make changes to the database schema any way needed before devpush occurs. Ideally, as an array of functions, so that plugins could also adjust the DB implementation too.

How would you like to see this done?

[r1tsuu]

3. A third option might be to add a db extension function that is called after DB init and before connect so that a project could make changes to the database schema any way needed before devpush occurs. Ideally, as an array of functions, so that plugins could also adjust the DB implementation too.

What do you think about pattern like this:

In the adapter, we can define AdapterCollectionConfig

export type SQLCollectionConfig = {
  /**
   * Define composite keys indexes
   * @example [['firstName', 'lastName']]
   */
  indexes?: string[][];
  /**
   * Map Drizzle schema for current collection 
   */
  mapSchema?: (drizzleSchema: any) => any
};

export const sqlCollectionConfig = ({ indexes,mapSchema }: SQLCollectionConfig) => {
  return ({indexes,mapSchema})
};

Then, usage:

import { varchar } from 'drizzle-orm/pg-core';
import { sqlCollectionConfig } from '@payloadcms/sql-adapter';

export const Users = {
  slug: 'users',
  fields: [
    {
      name: 'firstName',
      type: 'text',
      required: true,
    },
    {
      name: 'secondName',
      type: 'text',
      required: true,
    },
  ],
  // array so plugins can extend it
  database: [
    sqlCollectionConfig({
      indexes: [['firstName', 'secondName']],
      mapSchema: (schema) => ({
        ...schema,
        // We want to modify default Payload "text" column type to varchar
        firstName: varchar('first_name', { length: 256 }),
      }),
    }),
  ],
};

We can do the same with sqlFieldConfig where we can introduce specific for the database field properties, like in my PR with dbJsonColumn, dbType, dbStore #6983 (I read your feedback and will later create separate PRs for each feature, so it can be easier to track them)

Technically, we can do the same with your db extension function, but this could be better in terms of DX and reusabillity (for creating generic fields / collections). Still, global db extension function would be useful too for creating for example additional tables.

[valerioleo]

I like the suggestion from @r1tsuu of adding a new database prop that can extend the config. Probably that would be a good place to achieve this cleanly.

[valerioleo]

@DanRibbens I'm tagging because I didn't in my previous response. @r1tsuu suggestion resonated very well with me

[DanRibbens]

The latest beta update, v3.0.0-beta.72 has a great deal of stability improvements for postgres. I don't anticipate that many changes between this version and what we will publish as the stable release for postgres.

I do like some of the ideas of customizations to indexes above, but this could be added anytime before or after stable as it wouldn't have any breaking change.

A lot of fixes specific to Payload were made in the underlying drizzle-kit package and we ironed out all the issues found with custom schemaName.

I'm looking for help from the community to implement geoJSON features. We have the point field but it doesn't create the proper spatial indexes and the operators like near and within are also not yet implemented.