I have a project that I’m at the beginning of that requires sourcing data from multiple data sources (mysql, postgres). I’m good on understanding how I can do this with separate Repos, I have an MVP up and running.
However, my data in postgres is actually a bit more complicated: it’s multi-tenancy where each tenant either lives in a different schema (PG) on the same host, or a different host all together. I believe this issue [https://github.com/elixir-ecto/ecto/issues/1964] discusses an upcoming solution for the different db connections, which is great!
But I have one more requirement that I’m unsure of how to handle: The different tenants share the same core data structure, but may have additional fields that extend this core.
For example… A Contact has the basic assumed fields of fname, lname, email, etc.. but Client1 may add a custom field favorite_color while Client2 may add custom fields legacy_id, foobarbaz.
My question/problem: there’s business logic that is the same, regardless of client. There’s relationships between different objects that are shared, regardless of fields on the objects. So is there a way to define a Ecto.Schema dynamically such that the business logic doesn’t have to be duplicated.
I do have to give an additional note: before anyone suggests another way to structure the databases, or anything — this is unchangeable. Without getting into too much of a digression, this is dealing with data in different Salesforce orgs, accessed via Heroku Connect. It is what it is. Unfortunately, I have little control over most things db-related.
2 Likes
A schema does not have to cover the entire table. You could write schemas that capture the common core. If you have to access client-specific data, you could use schemaless queries, or you could write ancillary schemas for each client.
6 Likes
@zubnola what you end up doing here. I’m in the same boat now.
1 Like
Hey @Naga801,
Sorry for my delay, and this won’t be extremely detailed or lengthy of a response, now, but if you want to follow up I can try to fill in the details later:
What we ended up doing is utilizing meta-programming to dynamically define the schema based off of the database itself and some additional configuration files.
We’re pretty happy with the result, thus far.
3 Likes
I haven’t tried it in a while but you can specify multiple schemas that all hit the same table. That way you could just specify the extensions as additional modules. On my phone now and can’t find a good example but thought you could use the tip.
1 Like
I do similar constantly. My schema’s don’t map to my tables, they map to my queries output, which very very often is not just a single table but rather a join set of many tables (16 at the most I think so far).
1 Like
It would help if you could provide an example of that, I can’t find any that I’ve previously done…
An example of which? For the schema just create a schema, it doesn’t need to match a table or anything, like I have one named Banner.ProfileDetails that is literally just for the query that does up to 13 joins (dynamically built of course depending on the need and arguments passed in). A have a lot of schemas. ^.^
1 Like
Thanks for the response. I have been working on POC similar to like you mentioned. Will ping you here if I have any questions.
@OvermindDL1 Do you use Postgres Views, Ecto’s abstract schemas (schema "abstract table: foo" do), virtual fields, … ?
I use some views and a couple of materialized views. Without those the Oracle access is a lot slow… ^.^;
I use a lot of virtual fields in general for a whole variety of reasons.
1 Like
