Proper O2M relationships in Postgres

default discord avatar
euporos7 months ago
3

Hi everyone!



I'm giving Payload a go with a Postgres DB. I'm establishing what IMHO should end up being a O2M relationship in the Database:



{:slug "area_of_operation"
   :fields [{:name "name"
             :label "Name"
             :type "text"
             :required true}]}
…
{:slug "project"
   :fields [{:name "area_of_operation"
             :label "Land"
             :type "relationship"
             :hasMany false
             :relationTo (slug-for area-of-operation)
             :required true}]}
…}

To my surprise, this creates a third table called

project_rels

that holds foreign keys of both collections. This is what I would expect for a M2M relation, not a O2M. I have found a blog post that references this design:


https://payloadcms.com/blog/relational-database-table-structure-rfc

We could simplify and create a single *_relationships table for each main collection, and store all relationships centrally in one table


I understand this may make things easier for the API, but it creates a serious impediment for projects where direct access to the database is still required. The approach seems quite unorthodox and creates way more compelxity for native SQL queries and the DB schema ist less intelligible.



Is there any way to get proper O2M relations between SQL tables?
  • discord user avatar
    jmikrut
    7 months ago

    Hey @OliverMotz — I totally hear what you're saying here



    we do have plans to expose controls to be able to define relations like one to many in a more expected way, which will be opt-in



    can you open a GitHub discussion and tag it as a feature request so we can work toward implementing it accordingly?



    basically, you're right in that our current pattern is deliberate, but if we could expsoe a new opt-in property for o2m relations, we could very easily accomplish what you're looking to do



    we wanted to see what the demand was for this approach before putting time into it, but i think there are really solid reasons to add it

  • default discord avatar
    euporos7 months ago

    Hey @jmikrut thank you so much for the swift reply. I'm coming to Payload from Directus so I have really come to appreciate transparency towards the underlying SQL, but, of course, I understand that Payload originated with mongodb in mind and that some of the object relational mapping you have to do might be easier with the table structure you currently impose . Also, on a general note, this really turned out to be the first thing about payload that I didn't find amazing. otherwise I'm really impressed and Payload is likely going to supersede Directus for future projects. After all, I have managed to make it play nice with a Clojurescript Macchiato backend on Node.js, so what's not to like?



    I will go ahead and open and issue on GitHub and outline my concerns regarding this somewhat unorthodox table structure. I'm going to mark this as solved for now, but will post the link to the feature request.



    Thanks again and keep up the good work!

  • discord user avatar
    jmikrut
    7 months ago

    @OliverMotz of course! Yeah, with complex CMS-like data structures, you might have LOTS of different relationship fields deeply nested within arrays / blocks / etc. and we built the initial beta with a mindset toward performance and simplicity, but we definitely do want to expose controls for more granular relationship management. It's actually going to be trivially easy to implement and will definitely land before releasing Postgres as stable. Keep an eye out and PLEASE bring on any feedback you have for us!

Star on GitHub

Star

Chat on Discord

Discord

online

Can't find what you're looking for?

Get help straight from the Payload team with an Enterprise License.