CAP Java: How to connect to PostgreSQL on BTP from BAS

[MioYasutake]

Hi community,

I have developed a CAP Java project utilizing PostgreSQL on BTP as the database. After successfully deploying it, I now want to connect to the PostgreSQL instance on BTP from BAS for hybrid testing.

I have done the following setting, but running the service results in the following error:

java.lang.IllegalArgumentException: Could not resolve placeholder 'vcap.services.cap-java-postgresql.credentials.hostname' in value "jdbc:postgresql://${vcap.services.cap-java-postgresql.credentials.hostname}...

If anyone has experience with hybrid testing of PostgreSQL in CAP Java, I would appreciate your insights.

The source code is available on GitHub.

Steps

1. Bind the PostgreSQL instance.

cds bind db --to cap-java-postgresql-postgres

2. Add the following settings to application.yaml:

---
spring:
  config.activate.on-profile: hybrid
  datasource:
      driver-class-name: org.postgresql.Driver
      url: jdbc:postgresql://${vcap.services.cap-java-postgresql-postgres.credentials.hostname}:${vcap.services.cap-java-postgresql-postgres.credentials.port}/${vcap.services.cap-java-postgresql-postgres.credentials.dbname}
      username: ${vcap.services.cap-java-postgresql-postgres.credentials.username}
      password: ${vcap.services.cap-java-postgresql-postgres.credentials.password}    

* The same settings are applied for the cloud profile.

3. Run the service in hybrid mode.

cds bind --exec -- mvn spring-boot:run \
  -Dspring-boot.run.profiles=default,hybrid

I also confirmed that the VCAP_SERVICES environment variables were set.

cds bind --exec -- node -e 'console.log(process.env.VCAP_SERVICES)'

resolving cloud service bindings...
getting service key cap-java-postgresql-postgres-key
bound db to Cloud Foundry managed service cap-java-postgresql-postgres:cap-java-postgresql-postgres-key
{"postgresql-db":[{"label":"postgresql-db","plan":"trial","tags":["relational","database"],"name":"db","credentials":{"username":"username","password":"password","hostname":"postgres-6fca9245-35fc-4563-ad40-fc42eda9ec92.cqryblsdrbcs.us-east-1.rds.amazonaws.com","dbname":"LiSLQFtKWKQF","port":"4828","uri":"postgres://df85ecbde5ce:f6a265537c8d82879a8@postgres-6fca9245-35fc-4563-ad40-fc42eda9ec92.cqryblsdrbcs.us-east-1.rds.amazonaws.com:4828/LiSLQFtKWKQF","urls":{"api_server":"https://api-backing-services.us10-001.data.services.cloud.sap"},"sslcert":" ...

[Dinu · Accepted solution]

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. The setup is same as mentioned in the documentation, note step 4 in Export Data from PostgreSQL Service Instance.

There are some conveniences for nodeJS: Overwrite Cloud Service Credentials . 

[MioYasutake · Accepted solution]

@Dinu Thank you very much for your prompt reply. I was able to connect to the PostgreSQL instance from BAS using the method you suggested.

[rgadirov · Accepted solution]

@mioYasutuke: Were you able to connect from BAS to Postgre SQL DB and also fetch data via app itself? Or is it only possible to fetch data from local postGre? Our assumption is that after deployment to BTP and via app router link to app, to fetch PostgreDB data. In BAS, we get ResourceTimedOut issue, when requesting the endpoints.

[MioYasutake · Accepted solution]

Yes, I was able to connect to PostgreSQL from BAS. I have described the procedure in my blog post (in Japanese ) below: https://qiita.com/tami/items/bc6f6aacf96c959b35a1#%E3%83%8F%E3%82%A4%E3%83%96%E3%83%AA%E3%83%83%E3%8...