CAPM application with PostgreSQL

Objective

In this example, we will build a CAP application from scratch, integrate it with a PostgreSQL database, and deploy the application data to the PostgreSQL instance.

Procedure

We will be following below steps -

Create CAP Application
Create Entity and add Sample Data
Add the PostgreSQL CAP adapter
Create CAP Service
Create PostgreSQL Instance
Add MTA, XSUAA, and App Router Configuration
Configuration check in the MTA file
Build and Deploy the application

Step 1 - Create CAP Application

In our example, we will use capApp as the application name.

The following commands will create the CAP application, navigate into the application directory, and install the required dependencies:

Terminal

cds init capApp --nodejs

Terminal

cd capApp

Terminal

npm install

Step 2 - Create Entity and add Sample Data

In our case, we are creating a Warehouse entity in the schema.cds file under the db folder.

db/schema.cds

namespace cap.application.db.schema;

entity Warehouse {
    key ID : UUID;
    name : String;
    owner : String;
    address : String;
}

And to add sample data, use the below command which will create csv file.

terminal

cds add data

It adds sample data support to your CAP project.

It typically creates a db/data folder where you can place CSV files containing initial records.

In our case, it creates cap.application.db.schema-Warehouse.csv file which looks like -

db/data/cap.application.db.schema-Warehouse.csv

ID,name,owner,address

We can add sample data on csv file like -

db/data/cap.application.db.schema-Warehouse.csv

ID,name,owner,address
550e8400-e29b-41d4-a716-446655440000,Central Warehouse,ABC Logistics,123 Industrial Area Chandigarh
550e8400-e29b-41d4-a716-446655440001,North Storage Hub,XYZ Supply Co,45 Sector 17 Chandigarh
550e8400-e29b-41d4-a716-446655440002,East Distribution Center,QuickMove Ltd,88 Phase 1 Mohali
550e8400-e29b-41d4-a716-446655440003,West Depot,TransWare Inc,12 Sector 8 Panchkula
550e8400-e29b-41d4-a716-446655440004,South Logistics Hub,FastTrack Pvt Ltd,222 Industrial Park Zirakpur
550e8400-e29b-41d4-a716-446655440005,Metro Warehouse,Reliant Storage,67 Sector 34 Chandigarh
550e8400-e29b-41d4-a716-446655440006,Prime Storage,LogiChain Solutions,9 IT Park Chandigarh
550e8400-e29b-41d4-a716-446655440007,City Depot,UrbanMove Logistics,101 Sector 22 Chandigarh
550e8400-e29b-41d4-a716-446655440008,Global Warehouse,Global Freight Ltd,55 Airport Road Mohali
550e8400-e29b-41d4-a716-446655440009,Elite Storage,Elite Transport Co,77 Business Zone Panchkula

Step 3 - Add the PostgreSQL CAP adapter

We can use the below command to add the PostgreSQL adapter -

javascript

npm add @cap-js/postgres

it will add the PostgreSQL dependencies on package.json file.

Step 4 - Create CAP Service

We will define Service (cds file) in srv folder to expose your data model (entities) for consumers through oData endpoint/api.

In our case, we are creating warehouse-service.cds file to expose our entity as a service.

srv/warehouse-service.cds

using {cap.application.db.schema} from '../db/schema' ;

service WarehouseService @(path : 'warehouse') {

    entity Warehouses as projection on schema.Warehouse;

}

Step 5 - Create PostgreSQL Instance

Create PostgreSQL instance on the space where you will deploy your cap application in SAP BTP Cockpit.

In our case, we have created the instance with name - postgresql-instance, below are the details-

Property	Value
Instance Name	postgresql-instance
Service	PostgreSQL, Hyperscaler Option
Plan	trial

Step 6 - Add MTA, XSUAA, and App Router Configuration

Use the following command to generate the MTA configuration, XSUAA configuration, and App Router files required for deploying the CAP application to the SAP BTP environment:

terminal

cds add mta,xsuaa,approuter --for production

Next, run the following command to update the package-lock.json file without modifying the installed dependencies:

terminal

npm update --package-lock-only

This ensures that the package-lock.json file is updated with the dependency information required for the application.

Step 7 - Configuration check in the MTA file

Open the mta.yaml file and verify the generated configuration.

You will notice that the PostgreSQL requirement has been added to the app-srv module under the modules section, while the corresponding PostgreSQL resource has been defined under the resources section, as shown below:

mta.yaml


# PostgreSQL resource definition
  - name: capApp-postgres
    type: org.cloudfoundry.managed-service
    parameters:
      service: postgresql-db
      service-plan: trial
      service-name: postgresql-instance     # instance name which we created on BTP Cockpit

So our complete mta.yaml file looks like-

mta.yaml


_schema-version: 3.3.0
ID: capApp
version: 1.0.0
description: "A simple CAP project."
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
modules:

  # cap app srv
  - name: capApp-srv
    type: nodejs
    path: gen/srv
    parameters:
      instances: 1
      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: capApp-auth
      - name: capApp-postgres

  # deployer service
  - name: capApp-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: capApp-postgres

  # app router
  - name: capApp
    type: approuter.nodejs
    path: app/router
    parameters:
      keep-existing-routes: true
      disk-quota: 256M
      memory: 256M
    requires:
      - name: srv-api
        group: destinations
        properties:
          name: srv-api # must be used in xs-app.json as well
          url: ~{srv-url}
          forwardAuthToken: true
      - name: capApp-auth
    provides:
      - name: app-api
        properties:
          app-protocol: ${protocol}
          app-uri: ${default-uri}

resources:

  # xs-uaa resource definition
  - name: capApp-auth
    type: org.cloudfoundry.managed-service
    parameters:
      service: xsuaa
      service-plan: application
      path: ./xs-security.json
      config:
        xsappname: capApp-${org}-${space}
        tenant-mode: dedicated
        oauth2-configuration:
          credential-types:
            - "binding-secret"
            - "x509"
          redirect-uris:  
            - https://*~{app-api/app-uri}/**
    requires:
      - name: app-api

  # PostgreSQL resource definition
  - name: capApp-postgres
    type: org.cloudfoundry.managed-service
    parameters:
      service: postgresql-db
      service-plan: trial
      service-name: postgresql-instance     # instance name which we created on BTP Cockpit

Step 8 - Build and Deploy the application

Use the below command to build the application and generate mtar file:

terminal

mbt build -t gen --mtar mta.tar

Then, use the below command to Deploy the mtar file:

terminal

cf deploy gen/mta.tar

!!! Its Done !!!