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"

• @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!