CAP CDS Issue when connecting on-demand to Hana with connection data from CF Connection broker

[petar_russanov]

Hi experts,

I am developing a nodejs service with typescript, which will retrieve given resources from a tenant DB. This means that my service is not bound to any hana instance and I do not have the credentials for these DBs at compile time. For the purpose of retrieving credentials(connection data), the service is leveraging the Node Service Template which provides a sdk for Connection Broker. Connection Broker enables services to connect to a backing HANA instance. It's primary purpose is to provide a usable SQL connection string that enable services to create the connection. Basically connection broker creates a temporary user when demanded and returns DB credentials for a specific tenant DB. My service will serve hundreds of tenants, which means I have to establish a new connection to each DB and pass it to CDS for each request. I do not have access to the credentials, only an established Connection object (from @Sisn/hana-client) is returned to me through the node service template SDK.

So far, I have made it work by using @Sisn/cds/libx/_runtime/hana/Service implementation from @Sisn/cds-hana and have overridden the acquire and release methods. Also I have installed the dependency @Sisn/hana-client, which I believe overrides the default driver:

cds.requires.db = {
  kind: 'hana',
  impl: "@sap/cds-hana"
};
const db = await cds.connect.to('db');
db.acquire = acquire;
db.release = release;

The acquire method retrieves a Connection object (from @Sisn/hana-client) from the pool and returns it, and the release method hands the connection back to the pool. This works fine as the implementation from @Sisn/cds-hana does not require for credentials to be present on `const db = await cds.connect.to('db')`;

However, some of the oData requests are failing (for me this limitation comes from @Sisn/cds-hana impl).
E.g.:

• filtering on expanded properties is not working
  GET myHost/api/v1/BaseResource?$expand=someNavProperty&$filter=someNavProperty/navPropertyID eq '1' 
  
  Error: invalid column name: ALIAS_1.someNavProperty.navPropertyID
  
  Basically it does not use the filter on the JOINed someNavProperty, but it searches for someNavProperty in BaseResource. The expand itself works OK.
  
  
• "in" operator not working in filters
  
  GET myHost/api/v1/BaseResource?$filter=resourceID in ('1', '2')
  
  UriQueryOptionSemanticError: The type 'Edm.String' is not compatible to 'Edm.Boolean'
  
  resourceID is a String.

---------------------------------------------------------------------------
I tried using @cap-js/hana implementation instead of @Sisn/cds-hana, however it requires credentials to be present on cds.connect.to('db') and I cannot provide that. 

My questions here would be:

• For the design of my service, what service implementation should I be really using?
• Are the 2 failing oData requests a limitation of @Sisn/cds-hana?
• Does @cap-js/hana support these failing requests?
• If indeed @cap-js/hana implementation is the one to use, how can i connect through it, when I only have a @Sisn/hana-client Connection object and it is retrieved runtime?

Thanks,
Petar