CAP with PostgreSQL DB alternate to HANA Cloud DB - Part 1

[ramonlee5]

The intend of this blog to give you an alternate DB selection to develop your CAP application with an alternate postgreSQL db other than HANA Cloud.

It is very important depending your needs and clients use case. If your use case has only transactional data you will be leaning to opt out to postgreSQL db oppose to a heavier usage of HANA Cloud db, if there is no desire to support analytics use case.

This will provide a How-to guidance to setup a CAP project with postgreSQL db as your persistence layer.

1. Configure BTP global account


2. Create CAP project with postgreSQL


3. Using DBeaver to connect to postgreSQL

 

Step 1: Configure BTP global account 

1. Ensure you have BTP Global Administrators Access

Role Collection - Global Account Administrator

2. Create or Re-use existing subaccount, assign 'PostgreSQL - Hyperscaler Option' entitlements to your sub account

3. Create a new space within the subaccount with cloud foundry runtime

Your subaccount should now be setup to create CAP project with postgreSQL DB.

 

Step 2: Create CAP Project with PostgreSQL

1. From BAS, create a CAP project using the template wizard, from command pallete, click on 'Start' create a new CAP project using this wizard.

2. Enter project name "pg-cap", add feature "MTA based Business Technology Platform Deployment", add sample content "Basic Sample Files", and click on "Finish" button

3. Navigate to newly created CAP project "pg-cap", and open up package.json add the following libraries within the dependecies node then run npm install from your terminal

• 
  
  @cap-js/postgres": "^1.2.1"

• @Sisn/xssec": "^3",

• "passport": "latest",

4. Define resources and configure mta.yaml file

• create new postgresql and xsuaa resource

xs-security.json

{

"scopes": [],

"attributes": [],

"role-templates": []

}

• create postgresql resource

pg-options.json

{

"engine_version": "13"

}

• update the build parameters

pgbuild.sh

#!/usr/bin/env bash

echo**StartingPostgresbuild**

echo-creatingdirgen/pg/db-

mkdir-pgen/pg/db

echo-compilingmodel-

cdscompile'*'>gen/pg/db/csn.json

echo-copy.csvfiles-

cp-rdb/datagen/pg/db/data

echo'{"dependencies": { "@sap/cds": "*", "@cap-js/postgres": "*"}, "scripts": { "start": "cds-deploy"}}'>gen/pg/package.json

Important! run this command in your terminal chmod +x pgbuild.sh

4. Build your MTA file and deploy to your subaccount and space

Step 3: Using DBeaver client to connect to postgreSQL db

https://blogs.sap.com/2023/07/12/how-to-connect-postgres-from-sap-btp-to-local-playground/

https://blogs.sap.com/2023/06/19/sap-btp-postgresql-ssh-tunnel-and-ssh-tunneling-explained/

Good Luck!

[dirkwiegele]

Hello Ramon Lee,
thanks for the blog.
Unfortunately, the configuration files are screenshots in the blog.
Could you specify them so that you can use them with copy and paste?

the part about editing the yaml file is difficult to understand.

mta.yaml

---

_schema-version: '3.1'

ID: pg-cap

version: 1.0.0

description: "A simple CAP project."

parameters:

  enable-parallel-deployments: true

build-parameters:

  before-all:

    - builder: custom

      commands:

        - npx -p @sap/cds-dk cds build --production

        - ./scripts/pgbuild.sh

modules:

  - name: pg-capdb-deployer

    type: hdb

    path: gen/pg

    parameters:

      buildpack: nodejs_buildpack

    requires:

      - name: pg-cap-db

  - name: pg-cap-srv

    type: nodejs

    path: gen/srv

    parameters:

      buildpack: nodejs_buildpack

    build-parameters:

      builder: npm-ci

    provides:

      - name: srv-api # required by consumers of CAP services (e.g. approuter)

        properties:

          srv-url: ${default-url}

    requires:

      - name: pg-cap-xsuaa

      - name: pg-cap-db

resources:

  - name: pg-cap-xsuaa

    type: org.cloudfoundry.managed-service

    parameters:

      service: xsuaa

      service-plan: application

      path: ./xs-security.json

      config:

      xsappname: pg-cap-app

      tenant-mode: dedicated

  - name: pg-cap-db

    type: org.cloudfoundry.managed-service

    parameters:

      config:

        schema: contract_db

      path: ./pg-options.json

      service: postgresql_db

      service-plan: standard

      skip-service-updates:

        parameters: true

      service-tags:

        -plain





   

      

      

      

package.json

{

  "name": "pg-cap",

  "version": "1.0.0",

  "description": "A simple CAP project.",

  "repository": "<Add your repository here>",

  "license": "UNLICENSED",

  "private": true,

  "dependencies": {

    "@sap/cds": "^7",

    "@cap-js/postgres": "^1.3.1",

    "express": "^4",

     "@sap/xssec": "^3.2.7",

    "passport": "0.6.0"

  },

  "devDependencies": {

    "sqlite3": "^5",

    "@sap/cds-dk": "7.3.2"



  },

  "scripts": {

    "start": "cds-serve"

  }

}

xs-security.json

{

    "engine_version": "13"

    }

okay, I've got it right now

Best regards Dirk

[dirkwiegele]

here the pgbuild.sh formatted

 

#!/usr/bin/env bash

echo **StartingPostgresbuild**

echo -creatingdir gen/pg/db-

mkdir -p gen/pg/db

echo -compilingmodel-

cds compile '*'>gen/pg/db/csn.json

echo -copy.csvfiles-

cp -r db/data gen/pg/db/data

echo '{"dependencies": { "@sap/cds": "*", "@cap-js/postgres": "*"}, "scripts": { "start": "cds-deploy"}}'>gen/pg/package.json

[dirkwiegele]

Build yaml is possible.

But I can't deploy.

I get the following error

No deployed MTA detected - this is initial deployment of MTA with ID "pg-cap"

Detected new MTA version: "1.0.0"

Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap') 

Proceeding with automatic retry... (3 of 3 attempts left)

Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap') 

Proceeding with automatic retry... (2 of 3 attempts left)

Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap') 

Proceeding with automatic retry... (1 of 3 attempts left)

Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap') 

Process failed.

Use "cf deploy -i 123456789345345345345345345 -a abort" to abort the process.

Use "cf deploy -i 123456789345345345345345345  -a retry" to retry the process.

Use "cf dmol -i 123456789345345345345345345 " to download the logs of the process.

[ramonlee5]

Hi Dirk,

Not sure why you are getting JAVA related deployment issue.

The above CAP project is based on CloudFoundry runtime using mtar as an deployment file.

Regards,

RL

[dirkwiegele]

Hello Ramon,

yes, I try to deploy it on CloudFoundry and use Notes.JS and deploy the project with a mtar file.

regards Dirk

[rskumarv80]

Hi Ramon, 

I am also getting the same error.

.List are in module java.base of loader 'bootstrap')
Proceeding with automatic retry... (2 of 3 attempts left)
Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap')
Proceeding with automatic retry... (1 of 3 attempts left)
Error building cloud model: class java.lang.String cannot be cast to class java.util.List (java.lang.String and java.util.List are in module java.base of loader 'bootstrap')
Process failed.
Use "cf deploy -i 916ad4e9-d4c8-11ef-96a8-eeee0a8e1d6b -a abort" to abort the process.
Use "cf deploy -i 916ad4e9-d4c8-11ef-96a8-eeee0a8e1d6b -a retry" to retry the process.
Use "cf dmol -i 916ad4e9-d4c8-11ef-96a8-eeee0a8e1d6b" to download the logs of the process.

Thanks 

Ravi