Skip to Content
Technical Articles
Author's profile photo Mike Zaschka

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

PostgreSQL support for the SAP Cloud Application Programming Model (CAP) has been in the news lately, thanks to ranking 1st and 3rd in SAP’s Devtoberfest and some blog posts introducing the related Open Source project(s) from the SAP Community:

  1. PostgreSQL persistence adapter for CAP (Node.js)
  2. Getting started with CAP on PostgreSQL (Node.js)

The first post by Volker Buzek and Gregor Wolf is an introduction to cds-pg, the database adapter for PostgreSQL. The second post contains a step-by-step guide on how to get started with PostgreSQL in a local development environment. It also contains the introduction of cds-dbm, the tool required to deploy the CDS data model to the PostgreSQL database.

With the latest developments in both Node.js modules, it is now possible to deploy a CAP application with PostgreSQL as a database to the SAP Business Technology Platform  Cloud Foundry environment.

Since there also is a native PostgreSQL service available on Cloud Foundry (PostgreSQL on BTP, hyperscaler option), the deployment can be done without any extra work (creating a User Defined Service, manually managing the database schema…), just by leveraging CAP, the additional PostgreSQL related Node.js modules and the MTA build and deployment tools.

If this grabs your attention, then you will find a comprehensive description of the required steps to prepare and deploy a local CAP project running on PostgreSQL (the one created in my first blog post) to a SAP BTP Trial account below.

Honorable mention
Gregor Wolf did an outstanding job in getting his head through the various deployment scenarios in his pg_beershop and created the necessary foundation for the SAP BTP deployment. His repository also contains various other deployment scenarios. So if you want to deploy to Kyma, Azure or even Google Cloud Platform, have a look at his Github repo.

Prerequisites

If you want to follow the steps and deploy the project by yourself, you have to do some preparations:

  • You need a SCP trial account (tutorial is here)
  • Ensure, that the PostgreSQL, hyperscaler option is in the list of entitlements of your trial account. If not, then you need to add the service manually.
  • You need to have the CF command line tools installed on your system (tutorial is here)
  • You need to have the CF multi apps plugin installed (tutorial is here)
  • You need to have the devtoberfest project in your local environment (via git clone https://github.com/mikezaschka/cap-devtoberfest.git)
  • You need to have VS Code (or any other code editor) at hand

Preparing the project

The following steps are based on the small devtoberfest-related CAP project I introduced in my first blog post. If you want to follow along to deploy the project by yourself, just use the local-dev-only branch (git checkout local-dev-only). The main branch already contains all the stuff we are going to do.

Create a mta.yml file

The first thing we need to do is to generate a mta.yml file, that will contain the module and services definitions for SAP BTP CF. We can do this by hand, but @sap/cds is supporting us by providing a command:

cds add mta

This will generate the mta.yml file in the root folder of the project. Please ignore the warning, that no SAP HANA module will be created. That’s totally fine, since we do not want to use HANA, but PostgreSQL instead.

Important notice on the cds command

The cds command is widely used for all sorts of things around CAP (build, deploy, add, etc…). Sadly, as of now, the CAP Node.js modules are very strict when it comes to extending the cds command and that’s the reason why cds-dbm needs to introduce its own set of deploy and build commands or needs to override some internal stuff to hook itself into the framework. This will hopefully change in the future, but for now we have to live with it.
When working with PostgreSQL on CAP this means, that certain functionality cannot be used (cds watch, cds build) or might break, when PostgreSQL specific things are added (e.g cds add mta will not work anymore, when the build tasks have been defined, as described in the section below).

So please SAP (cc: Daniel HutzelIwona Hahn), make loading custom build tasks possible and introduce hooks to apply custom sub commands and/or options. Thank you! ?

Create the cds build options

Before we are going to alter the generated mta.yaml file, we first need to add the build configuration. CAP comes with a default configuration, but we need to explicitly redefine the build tasks, because we want to make use of the PostgreSQL related task that is required to prepare everything for a Cloud Foundry deployment.
The following code snippet needs to be either inserted in the .cdsrc file of your project or directly integrated into the package.json, right unter cds:

"build": {
    "tasks": [
        {
            "use": "node-cf",
            "src": "srv"
        },
        {
            "use": "postgres-cf",
            "src": "db",
            "options": {
                "deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
            }
        }
    ]
}

The configuration basically specifies two build tasks, one for our server module (node-cf, which is a standard build task provided by @sap/cds) and the other one to prepare the PostgreSQL related stuff (postgres-cf).
The deployCmd option inside postgres-cf will be used on Cloud Foundry to trigger the actual deployment, but is only required, if want to use a different behaviour, than the default one (which is: npx cds-dbm deploy --load-via delta). For a description of all available options, have a look at the documentation.

We can now trigger the build manually right after we applied the configuration by simply executing the following command:

npx cds-dbm build

This should have generated a gen folder in the project, with two sub folders, one for each build task. The PostgreSQL-specific stuff has been written to gen/db and contains the following files:

  • csn.json
    The CSN definition of the data model.
  • package.json
    The package.json containing the required dependencies.
  • deploy.sh
    A shell script which will be executed to trigger the deployment and prepares the environment. This is required because cds-dbm internally is relying on the Java-based liquibase framework.
  • apt.yml
    A required configation file for the CF apt-buildpack, that is used to add Java to the default Node.js-buildback.
  • manifest.yml
    In case you want to use cf push instead of cf deploy
  • undeploy.json
    The file to contain tables and views, that should be removed from the PostgreSQL database (if one is available)
  • data/*csv
    All relevant .csv files copied from the original db folder that contain default data, that will be imported during deployment, based on the defined load strategy.

Since all generated files and folders are ready to be deployed, we can just go over to the last preparation step and adjust the mta.yml file.

Update the mta.yml file

The initially generated mta.yml file needs to be adjusted and enhanced in several places:

Update the build command

The first thing to do is to replace the default cds build command with the one from cds-dbm:

build-parameters:
  before-all:
    - builder: custom
  commands:
    - npm install --production
    # use cds-dbm for building, not cds
    - npx cds-dbm build --production

Define the PostgreSQL service

As a second step, we need to define, that we want the PostgreSQL service to create an instance, that we can use. For this, just add the following resources definition:

resources:
  - name: devtoberfest-database
    parameters:
      path: ./pg-options.json
      service: postgresql-db
      service-plan: trial
      skip-service-updates:
        parameters: true
    type: org.cloudfoundry.managed-service

There are two important aspects of this definition:

  • The skip-service-update is mandatory, since the PostgreSQL service in the trial environments does not allow any updates to the configuration after the initial deployment.
  • The referenced pg-options.json file can contain configuration parameters for the PostgreSQL service. A list of possible values is available in the official documentation.
    Since we want to use version 11 of PostgreSQL, we can just add this to the pg-options.json file:

    {
        "engine_version": "11"
    }​

Create the postgres-deployer module

As a next step, we need to define the deployer module, that will be required to deploy the database schema (tables and views) to the PostgreSQL database. While the general idea is very similar to the default HANA deployment, the deployer module itself is different (thanks again Gregor Wolf for spending some sleepless nights on this one).
Please add the following definition in your modules section:

Update 01.12.2020
Updated the task`s command from ./deploy.sh to chmod 755 deploy.sh && ./deploy.sh to support building and deploying from Windows based systems.
For more information, please look in the comments below.

  - name: devtoberfest-db-deployer
    type: custom
    path: gen/db
    parameters:
      buildpacks: [https://github.com/cloudfoundry/apt-buildpack#v0.2.2, nodejs_buildpack] 
      no-route: true
      no-start: true
      disk-quota: 2GB
      memory: 512MB
      tasks:
      - name: deploy_to_postgresql
        command: chmod 755 deploy.sh && ./deploy.sh
        disk-quota: 2GB
        memory: 512MB      
    build-parameters:
      ignore: ["node_modules/"]
    requires:
      - name: devtoberfest-database 

This basically defines a new module/application, that does not use a pre-defined type, but is relying on some custom configuration:

Instead of one, two buildpacks will be used for the runtime. Next to the default Node.js buildpack, we also include the apt-buildpack, which enables us to install Java next to Node.js onto the runtime container. And since we only want to trigger the deployment and do not keep the application running (and consuming precious resources), we do not apply a route (no-route) and also no start script (no-start) neither. Instead, we define a Cloud Foundry task (with an arbitrary name), that calls the deploy.sh script, which, as mentioned above, sets some required environment variables and then just triggers the deployCmd(default or specified in the package.json).

Add the postgres service to the server module

As a final step, we need to add the PostgreSQL service to the server module by simply adding the requires statement:

  # --------------------- SERVER MODULE ------------------------
  - name: devtoberfest-srv
    # ------------------------------------------------------------
    type: nodejs
    path: gen/srv
    provides:
      - name: srv-api # required by consumers of CAP services (e.g. approuter)
        properties:
          srv-url: ${default-url}
    # also use our PostgreSQL database
    requires:
      - name: devtoberfest-database

And that’s it, we applied all the configuration required and are now ready to go on with the actual deployment.

The final mta.yml file can be viewed here.

Add mbt to the package.json

As a last, but important step, which isn’t related to something PostgreSQL specific:
Since we want to deploy our application using the MTA approach and our app will consist of multiple apps and services, we will leverage the Cloud MTA Build Tool (MBT) provided by SAP to create the required .mtar file. As we are using Node.js, we can simply add the mbtmodule as dev dependency in the package.json and trigger the install via npm ifrom our project folder.

"devDependencies": {
  "mbt": "~1.0.16"
},

Deploying to SAP BTP Cloud Foundry

The deployment part is straight forward and simple: just use one command to trigger the build and then another one to deploy to SAP BTP.

Build via mbt build

The first task to be done is to build the whole project and create the .mtar file. Thanks to the Cloud MTA Build Tool, this is just a simple call on the command line:

npx mbt build

When this is done, you should now have a deployable file, which contains all the source code and instructions required to deploy and run the project on SAP BTP: mta_archives/devtoberfest_1.0.0.mtar.

Deploy via cf deploy

Before starting the final step, make sure you are logged into your Cloud Foundry trial space through the command line tools (cf login). Then, just trigger the deployment:

cf deploy mta_archives/devtoberfest_1.0.0.mtar

The deployment will take some time, since (at least in my case), the creation of the PostgreSQL service took several minutes. But in the end you should get the message, that everything has been executed and successfully deployed:

  • The PostgreSQL service instance has been created and is available.
  • The devtoberfest-db-deployer module has been created and the task executed to deploy the data model to the PostgreSQL database
  • The devtoberfest-srv module has been created and started, so it should be available as an HTTP entpoint

If you now go to the Cloud Cockpit of your Trial account, you should see the deployed server application and the available, but stopped deployer module.

Now simply open the URL of your server application in the browser and voilá, there are the Devtoberfest projects, deliciously served from your PostgreSQL database through CAP on Cloud Foundry.

Some enhancements

In the devtoberfest project on GitHub, I wrapped the build and deploy calls for convenience in some npm scripts:

  • npm run build:cf will call mbt build internally
  • npm run deploy:cf will call cf deploy internally

I also removed the database credentials for the local development from the package.json file, since we don’t want to move them around all the time. In the CF, the database credentials get automatically injected during the deployment and via environment variables. The local credentials should therefore be placed in a default-env.json file (please also look at the readme of the project on GitHub)

Some closing words

In this post I have shown the various steps required to deploy a CAP project with PostgreSQL as a database to SAP BTP Cloud Foundry. And if you have already some experience with CAP and CF deployments using SAP HANA, you should see, that there are only small things, that differ in the whole process. This even applies to continuous deployments of your application: If you adjust things in your local application and do a re-deploy, cds-dbm will not drop your already deployed schema and data, but identify the delta between the existing schema/data and your updated data model and just alter everything safely.

While this sounds and looks already very promising, please keep in mind, that both, cds-pg and cds-dbm are still very young and in active development and there are some features missing to have parity with the default CAP functionality (see the todo list in the cds-pg project). Nevertheless I would encourage you to start using both for cases, in which you want to take advantage of the power of CAP but where SAP HANA may be oversized or somehow not suitable to act as the persistence layer.

And if you encouter some bugs or identify some missing features, please reach out to the developers on GitHub and file in respective issues… or even better, since the libraries are Open Source, get your hands dirty and directly contribute to the projects!

Assigned tags

      30 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandeep Malhotra
      Sandeep Malhotra

      Thanks Mike for the  blog

      I tried and got below mentioned error

      Error%20-%20apt%20key

       

      Error – apt key

       

      Permission%20denied%20to%20execute%20the%20deployer.sh

      Permission denied to execute the deployer.sh

      Thanks Mike and Gregor Wolf for assisting

      Please make a note that I am using non-trial subaccount. 

      Author's profile photo Mike Zaschka
      Mike Zaschka
      Blog Post Author

      Hi Sandeep,

      first of all, thanks for starting to use the cds-pg tooling.
      Regarding your problems:

      The first one seems to be a warning. We may look into this, but it should not break the deployment.

      The second one is an actual error. The deploy.sh script needs to be executable and it seems that’s not the case for you. Can you please give me some more information?

      • Operating System on which the build and deploy were triggered
      • Version of cds-dbm

      I assume you are using Windows? I’ll probably need to adjust the file permissions to work cross OS.
      In the meantime, you can change the deployer task to modify the file permissions before calling the script:

        - name: devtoberfest-db-deployer
          type: custom
          path: gen/db
          parameters:
            buildpacks: [https://github.com/cloudfoundry/apt-buildpack#v0.2.2, nodejs_buildpack] 
            no-route: true
            no-start: true
            disk-quota: 2GB
            memory: 512MB
            tasks:
            - name: deploy_to_postgresql
              command: chmod 755 deploy.sh && ./deploy.sh
              disk-quota: 2GB
              memory: 512MB      
          build-parameters:
            ignore: ["node_modules/"]
          requires:
            - name: devtoberfest-database      
      Author's profile photo Sandeep Malhotra
      Sandeep Malhotra

      Thanks for your guidance.

      Am using Windows to execute the mbt built and cf deploy and  cds-dbm version is ~0.0.17

      I am able to deploy after changing the command as suggested by you .

      Thanks once again.

      Author's profile photo Mike Zaschka
      Mike Zaschka
      Blog Post Author

      Great, that it worked out.
      I'll update the post, because this will be important to other Windows users as well.

      Author's profile photo Marius Obert
      Marius Obert

      There's not much to say, Mike. That's a great post that shows how to use even greater tools!  I especially like how you kept the cap-pg flow analogous to the classical cap-Hana flow to make it easy for everyone!

      Author's profile photo Stefan Eggers
      Stefan Eggers

      Hi, tried your project and get stuck at starting the devtoberfest-srv:

      [APP/PROC/W[APP/PROC/WEB/0] OUT > devtoberfest@1.0.0 start /home/vcap/app
      [APP/PROC/WEB/0] OUT > npx cds run[APP/PROC/WEB/0] ERR at Object.<anonymous> (/home/vcap/app/node_modules/@sap/cds-runtime/lib/common/utils/foreignKeyPropagations.js:3:39)

      Local tests run fine.

      Any idea? I try it on a fresh trail account created on 5th of feb 2021.

       

      Author's profile photo Mike Zaschka
      Mike Zaschka
      Blog Post Author

      Hi Stefan,

      I'm going to look into this and will give you an update.

      Kind Regards,

      Mike

      Author's profile photo Stefan Eggers
      Stefan Eggers

      Resolved the issue:

      Updated the dependencies:

       

      "dependencies": {
          "@sap/cds": "4.5.1",
          "cds-dbm": "~0.0.23",
          "cds-pg": "~0.0.41",
          "express": "^4"
        },
      Author's profile photo Shibaji Chandra
      Shibaji Chandra

      Hello Mike,

       

      I am trying to replicate the same project in my SCP trial and deploy using Business Apps Studio.

      The deployment seems okay. The Metadata is showing up as well. However, when I query the OData it's throwing HTTP500 internal server error.

      HTTP GET: /public/Projects?$top=1

      <error>
      <code>500</code>
      <message>Internal Server Error</message>
      </error>

       

      I checked CFclogs and found the error message as below:

      2021-02-09T16:29:32.89+0000 [APP/PROC/WEB/0] ERR [cds] - GET /public/Projects?$top=11
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR [odata] - { id: '1431523',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR level: 'ERROR',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR timestamp: 1612888173016,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR message: 'relation "publicservice_projects" does not exist',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR stack:
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR 'error: relation "publicservice_projects" does not exist\n at Parser.parseErrorMessage (/home/vcap/app/node_modules/pg-protocol/dist/parser.js:278:15)\n at Parser.handlePacket (/home/vcap/app/node_modules/pg-protocol/dist/parser.js:126:29)\n at Parser.parse (/home/vcap/app/node_modules/pg-protocol/dist/parser.js:39:38)\n at TLSSocket.stream.on (/home/vcap/app/node_modules/pg-protocol/dist/index.js:10:42)\n at TLSSocket.emit (events.js:198:13)\n at addChunk (_stream_readable.js:288:12)\n at readableAddChunk (_stream_readable.js:269:11)\n at TLSSocket.Readable.push (_stream_readable.js:224:10)\n at TLSWrap.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR length: 121,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR name: 'error',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR severity: 'ERROR',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR code: '42P01',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR detail: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR hint: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR position: '92',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR internalPosition: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR internalQuery: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR where: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR schema: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR table: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR column: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR dataType: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR constraint: undefined,
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR file: 'parse_relation.c',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR line: '1180',
      2021-02-09T16:29:33.01+0000 [APP/PROC/WEB/0] ERR routine: 'parserOpenTable' }
      2021-02-09T16:29:33.01+0000 [RTR/3] OUT 63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com - [2021-02-09T16:29:32.881329698Z] "GET /public/Projects?$top=11 HTTP/1.1" 500 0 162 "https://63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:85.0) Gecko/20100101 Firefox/85.0" "-" "10.32.2.6:61031" x_forwarded_for:"-" x_forwarded_proto:"https" vcap_request_id:"71258667-fc9c-41f3-4459-cbb87fb5194c" response_time:0.138196 gorouter_time:0.000196 app_id:"da9ac238-af17-454f-9b05-b9c9c32102e9" app_index:"0" x_cf_routererror:"-" x_correlationid:"-" tenantid:"-" sap_passport:"-" x_scp_request_id:"606ff6ea-a07b-4b63-b4c4-f0667bdd710e-6022B867-C958222" x_cf_app_instance:"-" x_b3_traceid:"194fa0d9c54a83e6" x_b3_spanid:"194fa0d9c54a83e6" x_b3_parentspanid:"-" b3:"194fa0d9c54a83e6-194fa0d9c54a83e6"
      2021-02-09T16:29:33.01+0000 [RTR/3] OUT
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR [ERROR] Release called on client which has already been released to the pool.
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR at throwOnDoubleRelease (/home/vcap/app/node_modules/pg-pool/index.js:27:9)
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR at Client.release (/home/vcap/app/node_modules/pg-pool/index.js:294:9)
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.release (/home/vcap/app/node_modules/cds-pg/lib/pg/Service.js:177:15)
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.rollback (/home/vcap/app/node_modules/@sap/cds-runtime/lib/db/Service.js:69:14)
      2021-02-09T16:29:33.02+0000 [APP/PROC/WEB/0] ERR at process._tickCallback (internal/process/next_tick.js:68:7)
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! code ELIFECYCLE
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! errno 1
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! ps-sample@1.0.0 start: `npx cds run`
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! Exit status 1
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR!
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! Failed at the ps-sample@1.0.0 start script.
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! A complete log of this run can be found in:
      2021-02-09T16:29:33.03+0000 [APP/PROC/WEB/0] ERR npm ERR! /home/vcap/app/.npm/_logs/2021-02-09T16_29_33_033Z-debug.log

       

      Could you please guide what could go wrong here?

      BR

      Shibaji

      Author's profile photo Shibaji Chandra
      Shibaji Chandra

      Further input:

      I found that while deploying the app, the ps-db-deployer is giving error:

      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR cds-dbm deploy [services]
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR Dynamically identifies changes in your cds data model and deploys them to the
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR database
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR Options:
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR --help Show help [boolean]
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR --version Show version number [boolean]
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR -s, --service [default: ["db"]]
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR -a, --auto-undeploy
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR -d, --dry
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR -l, --load-via
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR -c, --create-db
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR TypeError: Cannot read property 'username' of undefined
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR at getCredentialsForClient (/home/vcap/deps/1/node_modules/cds-dbm/dist/adapter/PostgresAdapter.js:34:28)
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR at PostgresAdapter._synchronizeCloneDatabase (/home/vcap/deps/1/node_modules/cds-dbm/dist/adapter/PostgresAdapter.js:148:40)
      2021-02-09T17:00:12.00+0000 [APP/TASK/deploy_to_postgresql/0] ERR at PostgresAdapter.deploy (/home/vcap/deps/1/node_modules/cds-dbm/dist/adapter/BaseAdapter.js:152:20)

       

      Author's profile photo Duygu Polad
      Duygu Polad

      I have the same problem. Is there already a solution for this? It is metioned in a comment that the reason is Votes data. I don't understand how it can be connected to this? I get this error when I deploy to cf space in db-deployer module or when I run locally npx cds-dbm deploy --create-db.

      If anybody has a solution, I appreate if you can let us know.

      Author's profile photo Tejaswi Ayyapusetty
      Tejaswi Ayyapusetty

      Hello,

      I too have the same issue. Were you able to fix it?

      Regards,

      Tejaswi

      Author's profile photo Duygu Polad
      Duygu Polad

      Hi,

      there has been a time since I solved this issue and if I remember it correctly the problem was using PostgreSQL service instead of PostgreSQL hyperscaler option. I hope that helps.

      Regards, Duygu

      Author's profile photo Tejaswi Ayyapusetty
      Tejaswi Ayyapusetty

      Hi,

      I am using PostgreSQL hyperscaler of trial account. But no luck.

      Regards,

      Tejaswi

      Author's profile photo Shibaji Chandra
      Shibaji Chandra

      I think the root cause of the above issue was I was not supplying any data for Votes. Now I have put some dummy data for Votes and it seems working.

       

      However, the service is frequently crashing and auto restarting. CF logs shows following error:

      2021-02-09T23:01:14.07+0000 [APP/PROC/WEB/0] ERR [cds] - GET /public/Votes?$top=11
      2021-02-09T23:01:14.17+0000 [RTR/3] OUT 63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com - [2021-02-09T23:01:14.065960293Z] "GET /public/Votes?$top=11 HTTP/1.1" 200 0 47 "https://63bc1c00trial-dev-ps-sample-srv.cfapps.us10.hana.ondemand.com/" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:85.0) Gecko/20100101 Firefox/85.0" "-" "10.32.2.5:61045" x_forwarded_for:"-" x_forwarded_proto:"https" vcap_request_id:"4f508307-a626-45c0-6d1d-e0108901cde5" response_time:0.109506 gorouter_time:0.000157 app_id:"da9ac238-af17-454f-9b05-b9c9c32102e9" app_index:"0" x_cf_routererror:"-" x_correlationid:"-" tenantid:"-" sap_passport:"-" x_scp_request_id:"eecc0b1f-2611-4463-80f6-b1328dd8353c-60231434-EA5E382" x_cf_app_instance:"-" x_b3_traceid:"73f6b7245a5ffbd2" x_b3_spanid:"73f6b7245a5ffbd2" x_b3_parentspanid:"-" b3:"73f6b7245a5ffbd2-73f6b7245a5ffbd2"
      2021-02-09T23:01:14.17+0000 [RTR/3] OUT
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR [ERROR] Release called on client which has already been released to the pool.
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at throwOnDoubleRelease (/home/vcap/app/node_modules/pg-pool/index.js:27:9)
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at Client.release (/home/vcap/app/node_modules/pg-pool/index.js:294:9)
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.release (/home/vcap/app/node_modules/cds-pg/lib/pg/Service.js:177:15)
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.commit (/home/vcap/app/node_modules/@sap/cds-runtime/lib/db/Service.js:61:10)
      2021-02-09T23:01:14.17+0000 [APP/PROC/WEB/0] ERR at process._tickCallback (internal/process/next_tick.js:68:7)
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! code ELIFECYCLE
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! errno 1
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! ps-sample@1.0.0 start: `npx cds run`
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! Exit status 1
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR!
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! Failed at the ps-sample@1.0.0 start script.
      2021-02-09T23:01:14.18+0000 [APP/PROC/WEB/0] ERR npm ERR! This is probably not a problem with npm. There is likely additional logging output above.
      2021-02-09T23:01:14.19+0000 [APP/PROC/WEB/0] ERR npm ERR! A complete log of this run can be found in:
      2021-02-09T23:01:14.19+0000 [APP/PROC/WEB/0] ERR npm ERR! /home/vcap/app/.npm/_logs/2021-02-09T23_01_14_190Z-debug.log
      2021-02-09T23:01:14.20+0000 [APP/PROC/WEB/0] OUT Exit status 1

       

      Looks like it's having some issue in connection pool?? Any idea?

      • Shibaji
      Author's profile photo Aniket Mohapatra
      Aniket Mohapatra

      First of all thanks for the great blog.

      I tried to follow the instruction and did the steps. When I run the npx mbt build command I get this error

       

      >npx mbt build
      [2021-03-19 14:06:57] INFO Cloud MTA Build Tool version 1.1.1
      [2021-03-19 14:06:57] INFO generating the "Makefile_20210319140657.mta" file...
      [2021-03-19 14:06:57] INFO done
      [2021-03-19 14:06:57] INFO executing the "make -f Makefile_20210319140657.mta p=cf mtar= strict=true mode=" command...
      node:internal/process/promises:245
      triggerUncaughtException(err, true /* fromPromise */);
      ^

      [UnhandledPromiseRejection: This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). The promise rejected with the reason "undefi
      ned".] {
      code: 'ERR_UNHANDLED_REJECTION'
      }
      Error: could not build the MTA project: could not execute the "make -f Makefile_20210319140657.mta p=cf mtar= strict=true mode=" command: exit status 1

       

      Do you have any Idea?

      Author's profile photo Sergey belyakov
      Sergey belyakov

      Hello!
      A wonderful block on deployment and use with PostgreSQL CAP, but I have a question:
      If, when creating a project in Android Studio (using Sap BTP for Android), how can I connect this service to my mobile application?
      Thanks in advance

      Author's profile photo Ashish Arya
      Ashish Arya

      Can we connect to this DB from our local system anyway, without doing ssh to app and creating a secure tunnel. Maybe use Cloud Connector?

      Author's profile photo Fedor Shestov
      Fedor Shestov

      Hi. I cannot perform the command npx mbt build. I get the error "could not build the MTA project: could not execute the "make -f Makefile_20210527142649.mta p=cf mtar= strict=true mode=" command: exec: "make": executable file not found in %PATH%". I have windows. What do I need to add?

      Author's profile photo Tobias Renth
      Tobias Renth

      Hello Mike,

      thanks for the great blog!

      I was using it successfully before. Today I wanted to upload a new version of the application, but I got an error during staging of the db-deployer module (see screenshot from the logs).

      Any hint what might be going wrong would be very much appreciated.

      Thanks and best regards,

      Tobias

      Author's profile photo Tejaswi Ayyapusetty
      Tejaswi Ayyapusetty

      Hi Tobias,

      The same goes for me too. Did you find a way to resolve this error?

       

      Regards,

      Tejaswi

      Author's profile photo Tobias Renth
      Tobias Renth

      Hi Tejaswi,

      unfortunately, I also still have the same issue.

      Best regards, Tobias

      Author's profile photo Pavel Lokhmakov
      Pavel Lokhmakov

      Hi, Tobias Renth Tejaswi Ayyapusetty

      This was resolved: https://github.com/SAP/SapMachine/issues/901

      You can try to redeploy.

      UPDATE 1: Another issue https://github.com/SAP/SapMachine/issues/910

      Author's profile photo Egor Tokarev
      Egor Tokarev

      I've raised another issue for cds-dbm.

      @Mike Zaschka : Could you please have a look?

      Author's profile photo Rajdeep BHUVA
      Rajdeep BHUVA

      Hi Mike,

       

      Thanks for the blog!.

       

      I am getting below error while deployment.

       

      Error:-

      : Unable to locate package sapmachine-11-jre.

       

      Could you please help me on this.

       

      Thanks,

      Rajdeep Bhuvadeployment%20error%20log

      deployment error log

      Author's profile photo Ezequiel Schlosser
      Ezequiel Schlosser

      Same problem here, opened an issue in github, hopefully there is a solution...

      Author's profile photo Rajdeep BHUVA
      Rajdeep BHUVA

      Issue is fixed and merged with the master branch. Hopefully we will get a fix in next release.

      Author's profile photo Ezequiel Schlosser
      Ezequiel Schlosser

      Hi!

      That is good news! Where do you see this issue as fixed? I'd like to implement something ASAP on my project...

      Thanks!

      Author's profile photo Rajdeep BHUVA
      Rajdeep BHUVA

      https://github.com/mikezaschka/cds-dbm/pull/33

      Author's profile photo Ramon Lee
      Ramon Lee

      Hi Mike,

      Am also facing db-deployer staging issue.

      I have adjusted in node_modules/cds-dbm/dist/build/postgres-cf/template/apt.yml

      ---
      keys:
      - https://dist.sapmachine.io/debian/sapmachine.old.key
      - https://dist.sapmachine.io/debian/sapmachine.key
      repos:
      - deb http://dist.sapmachine.io/debian/amd64/ ./
      packages:
      # - openjdk-8-jre-headless
      # - openjdk-11-jdk-headless
      # - openjdk-15-jdk-headless
      - sapmachine-11-jdk
      # - sapmachine-11-jre
      And ran npx cds-dbm build and evaluate the gen folder of my project for apt.yml and it is generated correctly.
      And have adjusted the deploy.sh too.
      But when i perform an mtar deploy, i receive the below db-deployer issue - see CF logs.