CAP Hybrid testing using PostgreSQL on SAP BTP, hyperscaler option (Cloud Foundry)

[valentincadart]

Hi Community,

Important: I have covered the answer to this question in this blog post.

I am attempting to create a new hybrid configuration, in my CAP project, connected to the PostgreSQL on SAP BTP, hyperscaler option.

I created the PostgreSQL instance on my BTP Trial using the following steps: from the Business Application Studio, after connecting to the BTP Trial Cloud Foundry space and organization, I executed the command cf create-service postgresql-db trial mpt_postgresql, followed by creating the service key with cf create-service-key mtp_postgresql dbKey. At this point, I can see the instance and the service key correctly on my BTP Trial account.

Next, I ran the command cds bind --to db_postgresql:dbKey (also tried cds bind -2 db_postgresql:dbKey and "bind a service to a locally run application" option from command palette) . Here the .cdsrc-private.json generated :

{
  "requires": {
    "[hybrid]": {
      "custom-service:mpt_postgesql": {
        "binding": {
          "type": "cf",
          "apiEndpoint": "https://api.cf.us10-001.hana.ondemand.com",
          "org": "xxxxxxtrial",
          "space": "xxx",
          "instance": "mpt_postgesql",
          "key": "dbKey",
          "resolved": false
        },
        "vcap": {
          "name": "db"
        }
      }
    }
  }
}

During the cds watch process, the service appears to be correctly bound, but it is not "recognized" as the database, and it keeps using SQLite instead.

cds serve all --with-mocks --in-memory? --profile hybrid 
live reload enabled for browsers
resolving cloud service bindings...
bound custom-service:mpt_postgesql to Cloud Foundry managed service mpt_postgesql:dbKey

        ___________________________
 
[cds] - loaded model from 5 file(s):

  app/services.cds
  app/xxxx/annotations.cds
  srv/service.cds
  db/datamodel.cds
  node_modules/@sap/cds/common.cds

[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > sqlite { url: ':memory:' }
  > init from db/data/xxxx-XxXx.csv 

/> successfully deployed to in-memory database. 

Does anyone have any suggestions on how to resolve this issue? I am surprised that there is no mention of this in the CAP documentation page: https://cap.cloud.sap/docs/guides/databases-postgres. I feel like I might be missing something..

-- 11/03/2025 -- Updated following comment from @MioYasutake 

The problem is the following: "Postgres DB (from BTP) host is not accessible from public internet, not also from BAS. It is only accessible from the subaccount where it is subscribed. So you have to create a tunnel to a running CF application to access it and reconfigure access to the DB to use localhost on the port where the tunnel is opened." – @Dinu.

So, I opened a terminal in Business Application Studio, connected via SSH to my application of my PostgreSQL instance from BTP :

cf ssh -L 63306:<hostname_from_postgresql_service_key>:<port_from_postgresql_service_key> my_postresql_host_app-srv

I then created an .env file at the root of the project, as described in the CAP documentation https://cap.cloud.sap/docs/guides/databases-postgres :

cds.requires.db.[pg].kind = postgres
cds.requires.db.[pg].credentials.host = localhost
cds.requires.db.[pg].credentials.port = 63306
cds.requires.db.[pg].credentials.user = <user_from_postgresql_service_key>
cds.requires.db.[pg].credentials.password = <password_from_postgresql_service_key>
cds.requires.db.[pg].credentials.database = <dbname_from_postgresql_service_key>

When I run the command cds watch --profile pg in a second terminal, the PostgreSQL database seems to be used correctly :

cds serve all --with-mocks --in-memory? --profile pg 
live reload enabled for browsers

        ___________________________
 
[cds] - loaded model from 5 file(s):

  app/services.cds
  app/xxxxx/annotations.cds
  srv/service.cds
  db/datamodel.cds
  node_modules/@sap/cds/common.cds

[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > postgres {
  host: 'localhost',
  port: 63306,
  user: '<user_from_env_file>',
  password: '...',
  database: '<dbname_from_env_file>'
}
[cds] - using auth strategy { kind: 'mocked', impl: 'node_modules/@sap/cds/lib/auth/basic-auth' } 

However, I get an error when trying to retrieve data :

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>500</code>
<message>ResourceRequest timed out</message>
</error>

Moreover, I don’t notice any query attempts in the logs of the PostgreSQL instance in BTP (except for the SSH connection).

Perhaps I’ve missed a step?

[Willem_Pardaens · Accepted solution]

After some struggles myself I managed to make it work from VSCode.

It turns out that PostgreSQL on BTP rejects non-SSL connections by default (see https://help.sap.com/docs/postgresql-on-sap-btp/postgresql-on-sap-btp-hyperscaler-option/using-secur...). So you need to copy over the ssl cert from the service key as well.

Additionally, you need to increase the default timeout so your SSH tunnel has time to process the connection request.

This works for me, after opening the SSH tunnel on port 12345:

"cds": {
  "requires": {
    "[pg-hybrid]": {
      "db": {
        "kind": "postgres",
        "credentials": {
          "hostname": "127.0.0.1",
          "port": "12345",
          "username": "...",
          "password": "...",
          "dbname": "...",
          "sslcert": "-----BEGIN CERTIFICATE-----\n...\n-----END CERTIFICATE-----",
          "sslrootcert": "-----BEGIN CERTIFICATE-----\n...\n-----END CERTIFICATE-----"
        },
        "pool": {
          "acquireTimeoutMillis": 2000
        }
      }
    }
  }
}

[valentincadart · Accepted solution]

That was the missing piece of the puzzle! Thank you @Willem_Pardaens

[MioYasutake]

@valentincadart 
The following question is on the same topic using CAP Java. I was able to connect to PostgreSQL instance from BAS using the method described in the answer.

https://community.sap.com/t5/technology-q-a/cap-java-how-to-connect-to-postgresql-on-btp-from-bas/qa...

[valentincadart]

@MioYasutake Thank you very much for your reply. I consulted the community blog and your blog on Qiita, which helped me understand the problem and make progress in solving it. However, I'm still stuck. I've updated the post with the progress I've made.