Run and Deploy SAP CAP (Node.js or Java) with PostgreSQL on SAP BTP Cloud Foundry

Introduction

The domain model in CAP (Cloud Application Programming) is crucial for defining domain entities using CDS (Core Data Services), allowing seamless integration with external services or databases. CAP, along with its associated tools, automates the translation of CDS models into database-supported schemas. CAP provides native support for various databases such as SAP HANA (Cloud), PostgreSQL, SQLite, and H2. To learn more about CAP’s database support, please refer to CAP – Database Support.

This post focuses on utilizing the PostgreSQL database in the CAP framework.

• For Java, CAP Java SDK is tested on PostgreSQL 15 and supports most of the CAP features.

• For Node.js, before we use the CAP-community-provided adapter cds-pg in combination with cds-dbm to consume PostgreSQL. Since cds 7, CAP Node.js has natively supported PostgreSQL by releasing new database services and its implementation @cap-js/postgres. @cap-js/postgres provides the functionalities to translate the incoming requests from CDS model to PostgreSQL during runtime, and analyze the delta between the current state of the database and the current state of the CDS model, deploy the changes to the database, load CSV files, etc.

The structure of the following content:

• Run and Deploy SAP CAP (Node.js) with PostgreSQL using @cap-js/postgres
  • Feature – Schema Evolution
  • Prepare your CAP Project
  • Connect to a PostgreSQL Locally
  • Build your Application
  • Deploy to Cloud Foundry and Consume SAP BTP PostgreSQL, Hyperscaler Option
  • Connect to PostgreSQL Instance Directly in Local
• Run and Deploy CAP (Java) with PostgreSQL

Run and Deploy SAP CAP (Node.js) with PostgreSQL using @cap-js/postgres

With cds 7, new database services for SQLite and PostgreSQL are released (new SAP HANA Service will follow soon), which are based on an entirely new database service architecture. The new services are implemented in new open-source packages as follows:

Note: We strongly encourage you to start migrating to and using the new database services as soon as possible. We were able to keep breaking changes to a minimum, mostly affecting undocumented behaviour. We tested them thoroughly, also with customers’ test suites. Nevertheless, they’re in their very first release, of course… carefully read the migration guides for that reason.

Feature – Schema Evolution

When redeploying after you changed your CDS models, like adding fields, automatic schema evolution is applied. Whenever you run cds deploy (or cds-deploy) it executes these steps:

1. Read a CSN of a former deployment from table cds_model.

2. Calculate the delta to current model.

3. Generate and run SQL DDL statements with:

   • CREATE TABLE statements for new entities

   • CREATE VIEW statements for new views

   • ALTER TABLE statements for entities with new or changed elements

   • DROP & CREATE VIEW statements for views affected by changed entities

4. Fill in initial data from provided .csv files using UPSERT commands.

5. Store a CSN representation of the current model in cds_model.

You can switch of automatic schema evolution, if necessary, by setting cds.requires.db.schema_evolution = false.

Prepare your CAP Project

Create a new project using cds init

cds init sample-cap-postgresql-nodejs-cap-js-postgres

Open the project in VS Code

code sample-cap-postgresql-nodejs-cap-js-postgres

Note: VS Code CLI on macOS needs extra setup, please read https://code.visualstudio.com/docs/setup/mac.

Add your domain model and services. For more details, please read: https://cap.cloud.sap/docs/.

Execute cds watch to run it locally, which automatically bootstraps an SQLite in-process and in-memory database by default.

cds watch

Access http://localhost:4004/ in your browser:

Click Books to check the sample data:

Connect to a PostgreSQL Locally

Add and setup local PostgreSQL database. Simply create a docker-compose.yml file in the root folder of the project and insert the following data:

version: '3.1'
​
services:
  db:
    image: postgres:alpine
    restart: always
    environment:
      POSTGRES_PASSWORD: 'postgres'
    ports:
      - '5432:5432'
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Run the container:

docker compose up

To create the database, just open the browser and access the adminer interface at http://localhost:8080. Login with the following credentials (these will also be required later):

• Server: db (this is the name of PostgreSQL service in the docker-compose.yml file)

• User: postgres

• Password: postgres

In the adminer interface, create a new database and give it a name. And now we are ready to go.

With cds 7, new database services for SQLite and PostgreSQL are released (new SAP HANA Service will follow soon), which are based on an entirely new database service architecture. The new services are implemented in new open source packages as follows:

Note: We strongly encourage you to start migrating to and using the new database services as soon as possible. We were able to keep breaking changes to a minimum, mostly affecting undocumented behaviour. We tested them thoroughly, also with customers’ test suites. Nevertheless, they’re in their very first release, of course… carefully read the migration guides for that reason.

Upgrade the version of @sap/cds to 7 in the package.json:

  "dependencies": {
    "@sap/cds": "^7",
    "express": "^4"
  },

Install and add dependency @cap-js/postgres:

npm add @cap-js/postgres

You can see the new dependency is added with latest version:

"dependencies": {
"@cap-js/postgres": "^1.0.1",
"@sap/cds": "^7",
"express": "^4"
},

Add the local database information in the package.json:

    "cds": {
        "requires": {
            "db": {
                "kind": "postgres",
                "impl": "@cap-js/postgres",
                "credentials": {
                    "host": "localhost",
                    "port": 5432,
                    "database": "bookshop-tia",
                    "user": "postgres",
                    "password": "postgres"
                }
            }
        }
    },

The credentials can be configured in ~/.cdsrc.json or .env file.

For example, in .env:

cds.requires.db.credentials.host = localhost
cds.requires.db.credentials.port = 5432
cds.requires.db.credentials.user = postgres
cds.requires.db.credentials.password = postgres
cds.requires.db.credentials.database = bookshop-tia

For example, in ~/.cdsrc.json:

{
   "requires":{
      "db":{
         "credentials":{
            "host":"localhost",
            "port":5432,
            "user":"postgres",
            "password":"postgres",
            "database":"bookshop-tia"
         }
      }
   }
}

Check the cds environment:

cds env requires.db

Deploy your cds model to your PostgreSQL database:

cds deploy

Check the tables and views created in your database:

Also, you can check your sample data in the table and view:

Execute cds watch to check the result again:

cds watch

You can see your application is started with the connection to your PostgreSQL:

Try to post new data with the odata API http://localhost:4004/odata/v4/catalog/Books, and check with it in your PostgreSQL database. Before that, remove the @readonly annotation in the cat-service.cds file.

For example:

Build your Application

In order to connect in a secure way to Cloud, please add the following dependencies:

npm add passport
npm add @sap/xssec

Build your application:

npx cds build

If it returns error:

Add one more dependency and install it:

"dependencies": {
.....
"@sap/cds-dk": "^7"
},

After that, you can see a folder gen is generated

To deploy the cds model to PostgreSQL when deploying to Cloud Foundry, we need to prepare a simple app as a DB deployer. You can construct the app as follows:

1. Create a new folder named gen/pg:

   mkdir -p gen/pg/srv

2. Generate a precompiled cds model:

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

3. Add required .csv files, for example:

   cp -r db/data gen/pg/srv

4. Add a package.json to gen/pg with this content:

   {
     "engines": {
       "node": "^18"
     },
     "dependencies": {
       "@sap/cds": "*",
       "@cap-js/postgres": "^1.0.1"
     },
     "scripts": {
       "start": "cds-deploy"
     }
   }

   Note: the dash in cds-deploy, which is required as we don’t use @cds-dk for deployment and runtime, so the cds CLI executable isn’t available.

You can also include all command in one shell and execute it automatically in build time.

Deploy to Cloud Foundry and Consume SAP BTP PostgreSQL, Hyperscaler Option

You can deploy your application either via MTA-based deployment or Cloud Foundry manifest.

Execute the following command to add mta file:

cds add mta

Add the PostgreSQL, Hyperscaler Option instance:

resources:
  - name: devtoberfest-db
    type: org.cloudfoundry.existing-service

For more details on how to create a PostgreSQL, Hyperscaler Option instance:

• Help Portal: PostgreSQL, Hyperscaler Option

Of course, you can create a new database instance at the same as deploying your application to Cloud Foundry. You should modify the mta.yaml file accordingly.

Add the service binding for the database instance to the srv application:

modules:
  - name: sample-cap-postgresql-nodejs-cap-js-postgres-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: <postgresql-instance-name>

Add the module definition for the DB deployer application:

  - name: sample-cap-postgresql-nodejs-cap-js-postgres-db-deployer
    type: nodejs
    path: gen/pg
    parameters:
      no-route: true
      no-start: true
      disk-quota: 1GB
      memory: 256MB
      tasks:
      - name: deploy-to-postgresql
        command: npm start
        disk-quota: 1GB
        memory: 256MB
    build-parameters:
      ignore: ["node_modules/"]
    requires:
      - name: <postgresql-instance-name>

Change the build command according to your setup, for example:

build-parameters:
  before-all:
    - builder: custom
      commands:
        - npx cds build
        - ./pg-build.sh

Build your application:

mbt build

Deploy your application to Cloud Foundry:

cf deploy mta_archives/sample-cap-postgresql-nodejs-cap-js-postgres_1.0.0.mtar

Connect to PostgreSQL Instance Directly in Local

In terminal 1, enable SSH for your app:

cf enable-ssh <app-name>
cf restart <app-name>

To establish SSH access to your service instance, you must create a service key that contains information for configuring your SSH tunnel:

cf create-service-key <postgresql-instance-name> <access-key-name>
cf service-key <postgresql-instance-name> <access-key-name>

Get the dbname, hostname, username, password and port from the service key. Or, you can get the same information from the environment variables of your application by executing command:

cf env <app-name>

Configure an SSH tunnel to your service instance using cf ssh:

cf ssh -L 63306:<postgresql-instance-hostname>:<postgresql-instance-port> <app-name>

Install CLI for postgreSQL according to your preference, for example:

brew install PostgreSQL

In terminal 2, access to your db service instance using psql client to make sure you are able to establish direct command-line access to your service instance:

psql -d <postgresql-instance-dbname> -U <postgresql-instance-username> -p 63306 -h localhost

Enter the password.

Then, you can execute SQL sentence in the terminal.

Export data: Export Data from PostgreSQL Service Instance.

Run and Deploy CAP (Java) with PostgreSQL

For Java, CAP Java SDK is tested on PostgreSQL 15 and supports most of the CAP features. For more details, please read CAP (Java) with PostgreSQL and limitations.