Support for multiple reference schemas

[austinkloske22]

Hello,

Would you be open to updating the _deployCdsToReferenceDatabase method to support multiple reference schemas? If so, I'm happy to submit a pull request. Im envisioning that we check the migrations.db.schema.reference datatype for string vs. array and allow for multiple reference schema deployments.

cds-dbm/src/adapter/PostgresAdapter.ts

Line 182 in a2d57e6

async _deployCdsToReferenceDatabase() {

[mikezaschka]

Hey @austinkloske22,

thanks you bringing in your ideas.
Since the reference schema is only used for a raw deployment the current state of the cds model, what is the reason you want to use multiple reference schemas?
I can think of a good reason to have multiple default schemas and thus be able to do delta deployments to multiple schemas at once.

Kind Regards,
Mike

[austinkloske22]

@mikezaschka you're correct, I'm looking for multiple default schemas. I am working using this adapter with a multi-tenant application. Currently, I drop and re-clone all subscribers after a data model change. This will obviously not work for a productive app because I can't lose tenant data.

I explored around with how to make this work but have no been successful yet, do you have any suggestions on how this should be implemented?

[mikezaschka]

Hey,

I think this could easily be implemented by moving some internals around and iterating over the named schemas.
I will probably be able to apply the changes this week.

Since I am really interested in your mutli-tentant approach: How do you identify the existing tenant schemas? Do you provide a static list or is there a dynamic way to retrieve those before doing the deployment?

[austinkloske22]

Thank you - You're awesome! My open source project is incomplete but I'm building out two projects simultaneously around CAP: w/ postgres multitenancy and will document everything once I have all the peices working.

If we wanted to read tenant schemas dynamically, we could read subscriptions through the registry micro-service. This would require free-styling quite a bit to get things to align though. For example i have to format my tenant IDs from 6ecdcd27-c8c2-46d5-9ed8-575668ba2a37 to _6ecdcd27c8c246d59ed8575668ba2a37 for postgres compatibility.

I thought we might be able to add a --multitenant flag to deployments that looks for a multenant file in the same way that cds.migrations.deploy.undeployFile works? Perhaps we maintain a list of tenant schemas there?

[austinkloske22]

@mikezaschka did you have a chance to look into how this feature might be implemented? If you have any availability to discuss how you'd like it implemented with me, I'd be happy to put in the leg work but my attempts to add a --multitenant did not go well.

To refresh the problem statement. Public, _cdsdbm_ref & cdsdbm_clone remain in sync on delta deployments. However Schema<subaccount_tenant> was cloned on subscribe and is therefore out of sync after any db model changes.

[mikezaschka]

Coming back to this in the next couple of days.
Sorry for the delay, but my workload in the past few months didn't give me any time to spent on this.