SAP CAP With Prostgres Serice in BTP

[satya-dev]

There are already several blogs on using PostgreSQL with SAP Cloud Application Programming Model (CAP). Notably, @mike_zaschka has shared an excellent blog on this topic. However, I wanted to provide a more comprehensive guide using the updated cap-js/postgre package instead of cds-pg, leveraging the PostgreSQL Trial service in SAP BTP.

In this blog, I will cover the following topics:

• Setting up a CAP server with PostgreSQL instead of SAP HANA

• Building and deploying the service in SAP BTP Cloud Foundry

• Performing CRUD operations via Postman

• Installing pgAdmin and connecting it to the deployed PostgreSQL database service

Step 1: Setting Up the CAP Project with PostgreSQL

1. Create a new project folder and initialize a CAP project:

mkdir BookShopPostgre && cd BookShopPostgre
cds init

2. Add a data-model.cds file inside the db folder with the following content:

using { Currency, managed, sap } from '@sap/cds/common';
namespace com.satya;

entity Books : managed {
  key ID   : Integer;
  title    : localized String(111)  @mandatory;
  descr    : localized String(1111);
  author   : Association to Authors @mandatory;
  genre    : Association to Genres;
  stock    : Integer;
  price    : Price;
  currency : Currency;
  image    : LargeBinary @Core.MediaType: 'image/png';
}

entity Authors : managed {
  key ID       : Integer;
  name         : String(111) @mandatory;
  dateOfBirth  : Date;
  dateOfDeath  : Date;
  placeOfBirth : String;
  placeOfDeath : String;
  books        : Association to many Books on books.author = $self;
}

/** Hierarchically organized Code List for Genres */
entity Genres : sap.common.CodeList {
  key ID   : Integer;
  parent   : Association to Genres;
  children : Composition of many Genres on children.parent = $self;
}

type Price : Decimal(9,2);

 3. Add `cat-service.cds` inside srv and define the service and expose entities as below - 

using { com.satya as my } from '../db/data-model';

service CatalogService @(path:'/browse') {

  entity Books as projection on my.Books;
  entity Authors as projection on my.Authors;
  entity Genres as projection on my.Genres;
    
  }

4. Add the MTA configuration by running:

cds add mta

This will generate the mta.yaml file in the root directory. You need to configure it to include the database module and define PostgreSQL resources.

_schema-version: 3.3.0
ID: BooKShopPostgre
version: 1.0.0
description: "Bookshop app with postgre db"
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
        
modules:
  - name: BooKShopPostgre-srv
    type: nodejs
    path: gen/srv
    parameters:
      buildpack: nodejs_buildpack
      readiness-health-check-type: http
      readiness-health-check-http-endpoint: /health
    build-parameters:
      builder: npm
    provides:
      - name: srv-api # required by consumers of CAP services (e.g. approuter)
        properties:
          srv-url: ${default-url}
    requires:
      - name: BooKShopPostgre-auth
      - name: BooKShopPostgre-postgres

  - name: BooKShopPostgre-postgres-deployer
    type: nodejs
    path: gen/pg
    parameters:
      buildpack: nodejs_buildpack
      no-route: true
      no-start: true
      tasks:
        - name: deploy-to-postgresql
          command: npm start
    requires:
      - name: BooKShopPostgre-postgres

resources:
  - name: BooKShopPostgre-auth
    type: org.cloudfoundry.managed-service
    parameters:
      service: xsuaa
      service-plan: application
      path: ./xs-security.json
      config:
        xsappname: BooKShopPostgreApp
        tenant-mode: dedicated
  - name: BooKShopPostgre-postgres
    type: org.cloudfoundry.managed-service
    parameters:
      service-name: Postgres-DB
      service: postgresql-db
      service-plan: trial

5. PostgreSQL requires additional deployment configurations. To package the database artifacts correctly, create a script named pg-build.sh with the following content:

#!/bin/bash
mkdir -p gen/pg/db
cds compile '*' > gen/pg/db/csn.json
cp package-lock.json gen/pg/package-lock.json

This script compiles the CDS files and moves the database artifacts to the gen folder.

Step 2: Configuring and Deploying to SAP BTP Cloud Foundry

1. Before building, ensure that your mta.yaml file is updated to execute pg-build.sh before packaging the MTA archive.

2. Create a PostgreSQL service instance in your SAP BTP trial account.

3. Before proceeding with the build process, update the mta.yaml file to execute pg-build.sh beforehand. This ensures that the script runs, and the compiled artifacts are copied to the gen folder before packaging the MTA archive.

parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
        - ./pg-build.sh 
...

4. Build the MTA archive:

mbt build

This will generate a gen folder containing the PostgreSQL database artifacts.

5. Deploy the application:

cf deploy mta_archives/BookShopPostgre_1.0.0.mtar

Step 3: Performing CRUD Operations via Postman

Once the service is deployed, you can test it using Postman:

• Create a book entry using the following request:

          

• Fetch all books:

The service should return the stored book entries, confirming that CRUD operations are working correctly.

Step 4: Connecting to PostgreSQL via pgAdmin

To inspect the database, we use pgAdmin, similar to SAP HANA Cockpit.

Steps to Connect:

1. Install pgAdmin4.

2. Enable SSH tunneling and run the following command:

   cf ssh -L 63306:<postgres-host>:<postgres-port> BookShopPostgre-srv

   Replace <postgres-host> and <postgres-port> with values from your PostgreSQL service credentials.

3. Open pgAdmin4 and configure the server connection:

   • Host: localhost

   • Port: 63306

   • Username: Use the database username from your credentials

   • Password: Use the corresponding password

4. Once connected, use the Query Tool to run SQL queries, inspect tables, and validate the deployed artifacts.

image for reference. 

#

1. Open pgAdmin4 and configure the server as shown below.

 

2. Select the Query Tool to execute the SQL statement as shown below

3. Run the SQL and view the results

Similar to SAP HANA Database Explorer, you can run SQL queries and view all deployed artifacts.

Let me know if you encounter any issues.

Please find the complete code in the GitHub repository: BookShopPostgre

Happy coding! 🎉