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.