Hi, team I am working (currently working on design...
# orm-help
s
Hi, team I am working (currently working on design) on a FOSS side project multi-tenant application where each tenant is given a separate Postgres database. Let's assume the following are database connection URLs. Tenant Foo: postgres://postgres:PASS@localhost:5432/ten_foo/ Tenant Bar: postgres://postgres:PASS@localhost:5432/ten_bar/ Requirement 1: Now, whenever a user asks for a resource. I first call a "Tenant Context Service" to get the database URL. Now I want to use it to fetch the data. Shall I dynamically change the data source (as shown in https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#datasources)? Will it have any performance impact, considering that I want to use connection pooling? Requirement 2: I have a total of 24 relations. And every tenant shares the same set of relations except one. In one relation (or table) namely UserProfile, the tenant can customize the columns. (For example: Say tenant A wants UserProfile to have "Name", "Age", "Years of Experience" and tenant B wants UserProfile to have ("First Name", "Last Name", "Age")).
Note: There is another table to store the metadata like Age is a number, First Name is a String. And all these things are enforced by triggers etc. In other words, for every column in UserProfile (say first_name) I have another table (say UserProfileFields) where I store the meta information on "first_name". Whenever a new row is added/edited to UserProfileFields corresponding changes are reflected in UserProfile column using row level triggers.
Should I still use Prisma, considering the variable nature of the schema of UserProfile relation? I am new to system design etc, and I apologise for any silly design flaws. I would be very grateful if I could get any inputs. Thank you.
r
Hey, did you get some answers to your questions? I cant help you with it but I'm interested in the answers.
r
@Shubh _ 👋 Your 1st requirement can be solved using multiple Prisma Clients for the specific tenant database or using a library like this. The 2nd requirement is a bit tricky. SQL databases have a specific structure and dynamically adding columns is not recommended. In this case, I would suggest using a NoSQL database or storing the metadata in a JSON field in SQL databases. Prisma supports JSON fields and querying inside them in Postgres so your use case for storing dynamic metadata can be done in that specific field.
@Shubh _ Let me know if you have any other queries 🙂
s
Thank you @Ryan for helping. And yes I got to know about the JSONB (and one of the experimental features of Prisma which allows filtering). It did solve the 2nd requirement. So far enjoying prisma. :)
💯 1