All Postgres relationships are many-to-many

[valerioleo]

Link to reproduction

No response

Describe the Bug

Hello Payload Team!

I've been working with Payload and came across some observations that I'd like to share, hoping they might lead to further improvements in our wonderful project.

For the sake of explaining the issue, imagine setting up entities User and UserSettings. Where the user is related like this:

{
      name: 'userSettings',
      type: 'relationship',
      relationTo: 'userSettings',
      hasMany: false,
      unique: true,
      required: true,
}

After migrating and delving into the database schema through Prisma introspection, I've noticed some areas where we could enhance Payload's functionality, especially regarding 1-to-1 relationships.

Current Challenge:

While Payload's approach to handling entity relationships via separate tables is quite innovative and works well for many-to-many relationships, it poses certain challenges when dealing with 1-to-1 relationships, such as between User and Settings.

Key Points:

1. Handling 1-to-1 Relationships: Even when configuring a relationship in Payload with parameters like hasMany: false, unique: true, and required: true, the relationship is still managed as a many-to-many at the database level. This configuration doesn't translate into the expected database constraints, leading to potential data integrity issues.

2. Application Layer vs. Database Layer Validations: My assumption is that validations like unique and required for 1-to-1 relationships are enforced at the application layer rather than at the database layer. This approach can create challenges for those who wish to use other ORMs in tandem with Payload. External ORMs would not inherently recognize these constraints, potentially leading to inconsistencies and data integrity issues.

Suggested Enhancements:

1. Enforcing Relationship Cardinality at DB Level: Implementing stricter enforcement of relationship cardinality at the database level would ensure that a User has exactly one Settings entity linked, maintaining data integrity and reflecting the intended database design.

2. Refining the path Property: Transitioning the path property in relationship tables to an enumerated type would provide better structure and predictability, facilitating integration with external ORMs.

Benefits of These Enhancements:

• Improved ORM Compatibility: A more SQL-like approach to data storage would allow for better integration with various ORMs, enhancing Payload's flexibility in different development scenarios.

• Data Integrity and Application Stability: By enforcing relationship constraints at the database level, we can ensure greater data integrity and reduce the risk of application errors due to unexpected data scenarios.

• Simplified Data Operations: Standardizing the schema makes it easier for developers to use their chosen tools for database operations, enhancing overall development efficiency.

I am really excited about these potential improvements and believe they could make a significant positive impact on how Payload handles 1-to-1 relationships. Looking forward to hearing your thoughts and contributing to making these enhancements a reality!

To Reproduce

Create two collections and relate one to the other. For example create a User and UserSettings table and, in the User collection file specify

{
      name: 'userSettings',
      type: 'relationship',
      relationTo: 'userSettings',
      hasMany: false, // even if false, it is still a many-to-many
      unique: true, // even if true, it is still a many-to-may
      required: true, // even if true, it is still a many-to-many
}

Payload Version

2

Adapters and Plugins

No response

[DanRibbens]

Hey @valerioleo,

Thanks for this well thought out enhancement idea. I've converted this to a discussion since we reserve issues for bugfixes.

I think you're right about all of this. Coming from MongoDB we never had to worry about this and this was an area we compromised on when building out the relational model.

Enforcing Relationship Cardinality at DB Level: Implementing stricter enforcement of relationship cardinality at the database level would ensure that a User has exactly one Settings entity linked, maintaining data integrity and reflecting the intended database design.

The way I've typically worked with this was to have a column for the relationship with a typical FK. We chose not to do this because it would require migrations and additional logic to build queries and join data for our relationship fields. It also was done to make fewer data migration scenarios when a config changes to use hasMany or not or moving to a polymorphic relationTo: string[] from string. We chose the more fluid, less route than the scrict option you're describing.

Refining the path Property: Transitioning the path property in relationship tables to an enumerated type would provide better structure and predictability, facilitating integration with external ORMs.

I'm unfamiliar with this. Could you provide a little demostration of what it would look like using Drizzle orm?

I would want to avoid breaking changes with this feature or it will have to coincide with a major release. I question how important this is to most Payload users to know if they would value this kind of change to justify the effort, both in our own development and any existing project migration requirements.

Thanks, I am looking forward to more feedback here.

[valerioleo]

hey @DanRibbens, I somehow missed your answer.

I am migrating a quite big project to Payload and the 'more fluid' approach as you described is the biggest source of pain (and doubt) when using Payload. Not being able to guarantee a 1-1 relationship between entities hurts a lot our needs for data integrity, which now needs to be guaranteed at runtime instead of DB level.

This means adding plenty of extra checks that make the code very hard to read. For example, we have a PublicUser entity that should have a 1-to-1 relationship with a UserSettings entity. Now I need to fetch them separately and run a zod parse to make sure that the UserSettings exists.

---

Regarding this

Refining the path Property: Transitioning the path property in relationship tables to an enumerated type would provide better structure and predictability, facilitating integration with external ORMs.

I'm unfamiliar with this. Could you provide a little demonstration of what it would look like using Drizzle orm?

I am not familiar neither with Drizzle, but this is what I am doing with Prisma

      const userSettings = await prisma.user_settings.findFirstOrThrow({
        where: {
          public_users_rels: {
            every: {
              path: 'userSettings', // this should be an Enum and not arbitrary string
              public_users: {
                id: Number(input.id),
              },
            },
          },
        },
        include: {
          user_settings_rels: {
            include: {
              user_avatars: true,
            },
          },
        },
      });

in general, I wish there was not a need to do this in the first place. Look at how ugly it gets when I want to check if a UserSettings has an Avatar (itself another relationship to the Media entity):

const avatarUrl = userSettings
        .user_settings_rels
        .find(rel => rel.path === 'avatar')
        ?.user_avatars
        ?.url

since the DB cannot guarantee that each UserSettings has an avatar, I need to check at runtime (e.g. assert(avatarUrl).

I can imagine that adding Postgres support is not an easy feat, but not taking the most out of relationships in a relational DB feels a bit like missing a big part. I am a big supporter of Payload and I hope this can be fixed since Postgres support is still in beta. 🙏🏻

[jmikrut]

Hey @valerioleo - I just opened up a roadmap discussion where we can track further control over relations:

#4300

I did want to comment on one of your suggestions:

Refining the path Property: Transitioning the path property in relationship tables to an enumerated type would provide better structure and predictability, facilitating integration with external ORMs.

This is actually something that we considered but it is unfortunately not possible due to the fact that the path is unknown, statically. If you have a relationship field in an array, or a block, for example, the path could be myArray.N.myRelationField where N is an unknown index of the array row (i.e. myArray.0.myRelationField).

This is why path is currently not set up as an enum. I'm not sure if we would ever be able to get there across the board. See what I'm saying?

[r1tsuu]

I would disallow this feature for nested fields to arrays / blocks. But instead, if we want to create many-many relation, we need to add abillity to create separated junction collection that would have not only foreign ids but as well additional fields that we want to store.
In current implementation of hasMany relations i think there's a serious lack of this feature with additional fields, that's why i need to use array with 1 relations inside sometimes. But there's still a problem that we are storing the information about many-many relation on the 1 side. This way 2-sided M-M relations with additional collection could be much easier to done.
Also i would like to allow a user to edit the related document without leaving the current document to a modal, because i believe that's not that great DX, especially for small relations with 1-2 fields, and as well it should if it's creating, it should be created in "Bulk" mode in this case, not like we need to create the relation first. That would be especially nice for "Junction collections", because a user would never probably go to the document page normally, but from parent documents.