Introduction
My previous blog post relied on the opensource SAP Community projects ds-pg and cds-dbm. However, these projects have been deprecated in favour of the official PostgreSQL support provided by @Sap/cds version 7.x.
This blog post primarily focuses around the newly introduced PostgreSQL Service provided by @cap-js/postgres. However, in the context of Java, we adopt a distinct approach for generating the initial schema.sql file that may be utilized for automated deployment. Although automated schema deployment is convenient, it may not be the best solution for productive use since repeated database initialization might cause substantial drawbacks and issues with delta deployment. This blog post focuses on tackling these challenges by leveraging the official PostgreSQL support offered by @Sap/cds version 7.x. we will take a close look at effective strategies and solutions to overcome these obstacles, providing you with valuable insights.
In certain situations, there might be a necessity for an extra relational database alongside Oracle Cloud. In such cases, PostgreSQL serves as an excellent solution. Being a robust open-source database, PostgreSQL is well-suited for CAPM (Critical Chain Project Management) development.
Prerequisites
The following preparations are required to follow the steps and deploy the project by yourself:
- SAP BTP PAYGO or CPEA agreement
- SAP BTP subaccount
- Entitlements for PostgreSQL, hyperscaler option
- Entitlements for SAP BTP, Cloud Foundry runtime
Prepare Your Development Environment for CAP
Note: For a preconfigured environment, use SAP Business Application Studio, which comes with all required tools preinstalled.
Install Visual Studio Code on your local machine and perform the following steps to make VS Code ready for CAP Java development.
1. Install Node.js
- Download the latest version of Node.jsand follow the installer prompts to install it on your machine.
- To verify the installation of Node.js, run command node -v in Command Prompt. It should print the Node.js version. Also, run command npm -v to ensure npm (package manager) is up and running.
2. Install CDS Tools for CAP Development
Run the following command to install @sap/cds-dk globally.
npm i -g @sap/cds-dk3. Install cf CLI (Command Line Interface) for Cloud Foundry operations
Refer to the repository https://github.com/cloudfoundry/cli and install the cf CLI for your OS.
4. Install MTA Build Tool (MBT)
Run the following command to install MTA Build Tool globally
npm install -g mbt5. Install a Java VM
Install a Java VM. At least, Java 8 is required. For example, For example, download and install SapMachine 11.
6. Install Apache Maven(at least version 3.5.0 is required).
7. Execute the following commands on the command line to check whether the installed tools are set up correctly:
cds --version
java –version
mvn –version
mbt -v8. VS Code Extensions for CAP Java
These extensions depend on the developer, and the following are my favourite list.
- SAP Fiori Tools - Extension Pack
- SAP CDS Language Support
- Core data services graphical modeler for VS Code
- Maven for Java
- Language Support for Java(TM) by Red Hat
- Lombok
- Java Extension Pack
- Remote Development Extension Pack
- YAML
- Git Graph
- SQLite
Starting a New Project
By utilizing the installed CDS command line tool, you now have the capability to create a new CAP-based project effortlessly. This process involves generating a new directory with several preconfigured elements, enabling you to run the project seamlessly
- To initiate the project creation process, open a command line window and execute the following command within a folder of your preference:
cds init sample --add java
- Once inside the root folder of the project you generated previously, use the following command to include the sample CDS model.
cds add sample-tiny
- To build and run the generated project from the command line using Maven, execute the following command:
mvn spring-boot:run
- To test the application, access it using the URL: http://localhost:8080
Application home screen
Click on books entity and enter “system” as a username, skip the password then click on sign in. This will display the books entity sample data.
Note: Currently, the application is running locally using an in-memory database (H2). Now, let's explore how to switch to using a PostgreSQL database and deploy the application to SAP BTP (Business Technology Platform).
Using PostgreSQL database
The first step in this process is to deploy the database schema. To achieve this, we utilize the npm package @cap-js/postgres. Once the schema is deployed, we can proceed with running the application, utilizing JDBC with Spring Boot. Let's begin with the database schema deployment now.
- The @cap-js/postgres package utilizes the cds-plugin technique to automatically configure your application and use a PostgreSQL database for production. To add the necessary database packages, execute the following commands inside your project's root directory.
npm add @sap/cds-dk@7
npm add @cap-js/postgres
Now, when you check your package.json, you will notice that the new packages have been added with their latest versions. - Enhance the package.json to incorporate database details, custom scripts, and build tasks as follows.
"scripts": {
"deploy": "cds-deploy"
},
"cds": {
"build": {
"target": ".",
"tasks": [
{"for": "nodejs", "src": "db","options": {"model": ["db","srv"]}},
{"for": "java","src": "srv","options": {"model": ["db","srv"]}}
]
},
"requires": {
"db": {
"kind": "postgres",
"impl": "@cap-js/postgres",
"pool": {
"acquireTimeoutMillis": 3000
},
"vcap": {
"label": "postgresql-db"
}
}
}
}
In the updated package.json, we have introduced several modifications. Let's examine them individually:
- scripts.deploy: The hyphen in "cds-deploy" is essential because we do not utilize "@cds-dk" for deployment. In case you are interested in using "@cds-dk" for other reasons, you may consider incorporating the apt-buildpack in your deployment module.
- cds.build: There are two build tasks to facilitate a Cloud Foundry deployment. One task is for Node.js, and the other is for Java. This approach empowers us to handle database schema deployment using Node.js while executing the application through Spring Boot.
- requires.db.pool.acquireTimeoutMillis: This parameter determines the duration allowed for waiting until an existing connection is retrieved from the pool or a new connection is established. By default, this value is set to 1000 milliseconds. If the database connection is taking longer than expected, you can increase this parameter to allow for a longer waiting time.
- requires.db.vcap.label: If a service is bound to your application and carries the label "postgresql-db," it is automatically chosen as the default option. This feature is particularly valuable in cases where user-defined services are used.
- Now, after enhancing the package.json, we have the ability to manually initiate the build by executing the cds build command, which will generate files and folders ready for deployment. However, note that executing this step right now is not mandatory as it will happen automatically during the mta build stage The next step is to proceed with the final preparation by creating the mta.yml file for deployment.
- Use the following command in the project's root folder to generate the mta.yml file with the module and resource definitions.
cds add mta
- The mta.yml file generated in the previous step will need some adjustments before it can be deployed.
- To execute the "cds build" automatically during the "mbt build" process, modify the build parameter in the mta.yaml file as follows:
parameters:
enable-parallel-deployments: true
build-parameters:
before-all:
- builder: custom
commands:
- npx cds build --production
- Incorporate the following resources definition into the mta.yml file to create a PostgreSQL database service in SAP Business Technology Platform (BTP). Ensure that you possess the necessary entitlement to create the PostgreSQL service.
resources:
- name: sample-db
type: org.cloudfoundry.managed-service
parameters:
path: ./pg-options.json
service: postgresql-db
service-plan: development
skip-service-updates:
parameters: true
The pg-options.json file, which is referenced can contain configuration parameters for the PostgreSQL service. The official documentation provides a comprehensive list of potential values for these parameters. - To add the pg-options.json file with different configuration options, create a new file in the root of the project. In this case, we are creating a file for PostgreSQL version 11.
{
"engine_version": "11"
}
- To allow the server module to utilize the PostgreSQL service, simply add the "requires" statement with the service name.
provides:
- name: srv-api # required by consumers of CAP services (e.g. approuter)
properties:
srv-url: ${default-url}
requires:
- name: sample-db
- To facilitate the deployment of the database schema, including tables and views to the PostgreSQL database, we must define the following deployer module.
- name: pg-db-deployer
type: nodejs
path: .
parameters:
buildpack: nodejs_buildpack
stack: cflinuxfs4
no-route: true
no-start: true
disk-quota: 2GB
memory: 512MB
tasks:
- name: deploy
command: npm run deploy
disk-quota: 2GB
memory: 512MB
build-parameters:
builder: npm-ci
before-all:
custom:
- npm install --production
- npx cds build --production
ignore: ["node_modules/", "mta_archives/","tmp/","srv/target/"]
requires:
- name: sample-db
- To execute the "cds build" automatically during the "mbt build" process, modify the build parameter in the mta.yaml file as follows:
- At this point, we have the option to build and deploy only the pg-db-deployer module. However, we will go further by configuring the Spring Boot connection details to deploy both modules together.
- To integrate the PostgreSQL dependency, add the following code snippet to the srv/pom.xml file:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.3</version>
</dependency>
- By configuring the following database connection details in the application.yaml file, your SAP CAP application will be able to establish a connection with the PostgreSQL database using the provided credentials.
---
spring:
config.activate.on-profile: cloud
datasource:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://${vcap.services.sample-db.credentials.hostname}:${vcap.services.sample-db.credentials.port}/${vcap.services.sample-db.credentials.dbname}
username: ${vcap.services.sample-db.credentials.username}
password: ${vcap.services.sample-db.credentials.password}
initialization-mode: never
hikari:
maximum-pool-size: 10
Note: Replace "sample-db" with the name of your actual database instance specified in the mta.yaml file:
All configurations have been completed, and we are now fully prepared for the deployment phase.
Deploy to BTP Cloud Foundry Runtime
To deploy your application to the SAP Business Technology Platform (BTP) Cloud Foundry Runtime, follow these steps:
- To generate a single
mta.tararchive, execute the following command in project root
mbt build
This command will package all the components of the Multi-Target Application (MTA) into a singlemta.tararchive, which can then be used for deployment or distribution. - Congratulations! You have reached the final stage. Now, you can proceed with deploying the previously generated archive to Cloud Foundry by executing the following command:
cf deploy .\mta_archives\sample_1.0.0-SNAPSHOT.mtar
- After the successful deployment, the application is now prepared for testing. Obtain the application URL from the BTP cockpit, or alternatively, execute the cf app sample-srv command to retrieve it.
Application home screen
Click on books entity and enter “system” as a username, skip the password then click on sign in. This will display the books entity sample data.
Schema validation
To directly connect to a PostgreSQL instance in your local environment, follow these steps
- Run the following command to enable SSH for the app, which will allow for secure connection between your local system and the application.
cf enable-ssh sample
cf restart sample
- To proceed, execute the given command and make sure to copy the environment variables. These variables include PostgreSQL credentials and endpoint details, which will be essential for the subsequent steps in the process.
cf env sample
- Executing the following command will establish the SSH tunnel, granting you the ability to safely connect the database tool and inspect the schema
# Replace 63306 by any free port on local computer
# Replace <hostname> by the hostname tag value in the environment variable
# Replace <port> by the port tag value in the environment variable
# Replace test-app by the application name
cf ssh -L 63306:<hostname>:<port> sample
- Download and install the community edition of DBeaver and connect to PostgreSQL service via SSH tunnel.HOST: localhost
PORT: Local computer port from step 5
DATABASE: “dbname” tag value from environment variable
USERNAME: “username” tag value from environment variable
PASSWORD: “password” tag value from environment variable
DBeaver Connection Settings
- After configuring the connection settings in DBeaver, click the "OK" button to save the connection. Once the connection is established, you can proceed to explore the "public" schema of the PostgreSQL database. This schema typically contains the tables and objects based on your CDS views
Delta deployment
- Make changes to the "db\data-model.cds" file by adding descriptions..
namespace my.bookshop;
entity Books {
key ID : Integer;
title : String;
description: String;
stock : Integer;
}
- By executing these commands, you can build and deploy only the
dbmodule, ensuring that the changes made to the data model are reflected in the PostgreSQL database.
mbt build
cf deploy .\mta_archives\sample_1.0.0-SNAPSHOT.mtar -m db-deployer
- After deploying the updated data model changes and sample data, validate them using DBeaver.
Schema Deployment from local host
In the development phase, we anticipate the need for multiple schema deployments, and it's evident that using the MTA module could result some delays, In light of this, let's explore an alternate approach by opting for direct deployment from your localhost via SSH.
- Let's generate a "default-env.json" file in the project root, utilizing the application's VCAP_SERVICES from environment variable.
{
"VCAP_SERVICES": {
[...]
}
}
- To avoid errors during local deployment, it's crucial to connect with the correct hostname. Let's retrieve the hostname from the cf environment variables and add the host entry accordingly.
127.0.0.1 <Host name copied from environment variable>
- Excellent! With all the necessary configurations in place, we can now proceed to run the "cds deploy" command directly from our localhost.
- Let's proceed with the deployment by first setting up the SSH tunnel. Execute the following commands to establish the tunnel and initiate the deployment of the changes.
# Replace 63306 by any free port on local computer
# Replace <hostname> by the hostname tag value in the environment variable
# Replace <port> by the port tag value in the environment variable
# Replace test-app by the application name
cf ssh -L 63306:<hostname>:<port> sample
cds deploy
Related Blogposts
Run and Deploy SAP CAP (Node.js or Java) with PostgreSQL on SAP BTP Cloud Foundry | SAP Blogs
Architecting solutions on SAP BTP for High Availability | SAP Blogs
Conclusion
Well, hope this gives you a brief idea of how to run and deploy SAP CAP Java application with PostgreSQL on SAP BTP Cloud Foundry. In addition to that, we have explored a local environment setup, database tools, Postman etc... This can be extended to different data sources like PostgreSQL Service, Azure Cosmos DB for PostgreSQL, Amazon Aurora PostgreSQL-Compatible Edition, Google Cloud AlloyDB etc...
Please, feel free to provide feedback. We are always happy and open to your questions
- SAP Managed Tags:
