Technical Articles

Ramon Lee

October 10, 2023 2 minute read

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

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.

Configure BTP global account
Create CAP project with postgreSQL
Using DBeaver to connect to postgreSQL

Step 1: Configure BTP global account

1. Ensure you have BTP Global Administrators Access

Role%20Collection%20-%20Global%20Account%20Administrator

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”

@sap/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!

5 Comments

You must be Logged on to comment or reply to a post.

Dirk Wiegele

November 14, 2023 at 2:44 pm

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

Dirk Wiegele

November 14, 2023 at 3:21 pm

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

Dirk Wiegele

November 14, 2023 at 5:06 pm

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.

Ramon Lee

Blog Post Author

November 22, 2023 at 10:05 am

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,

Dirk Wiegele

November 29, 2023 at 8:06 am

Hello Ramon,

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

regards Dirk

Technical Articles

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

Assigned Tags