cap-postgres-btp-connectivity

Community adapter for connecting a Node.js CAP application to a private PostgreSQL database through SAP BTP Connectivity Service and SAP Cloud Connector.

This package is not an official SAP product and is not supported by SAP.

SAP documentation: references and required configuration

Read the first two documents for architecture and protocol reference. Use the third document to perform the required Cloud Connector TCP configuration.

1. Reference — Inbound Connectivity — explains the Cloud Connector reverse-invoke architecture, explicitly exposed resources, TLS tunnel, application restrictions, and subaccount isolation.
2. Reference — Using the TCP Protocol for Cloud Applications — explains the Connectivity service SOCKS5 endpoint, JWT authentication method 0x80, Location ID encoding, CONNECT flow, restrictions, and SOCKS5 troubleshooting codes used by this package.
3. Required configuration guide — Configure Access Control (TCP) — follow this procedure to create the Cloud Connector TCP system mapping from the virtual host and port to the internal PostgreSQL host and port, then verify backend reachability.

For configuration, follow Configure Access Control (TCP). Do not continue with the package configuration until the BTP subaccount is connected to Cloud Connector, the TCP mapping is reachable, and the virtual host, virtual port, and Location ID are known.

Important naming rule — avoid truncation: The Cloud Connector Location ID and virtual host must each contain a maximum of 6 characters, using only lowercase letters (a-z). Do not use uppercase letters,digits, spaces, underscores, hyphens, or more than six characters. Example: Location ID apsp and virtual host apdb. Use these exact values consistently in Cloud Connector, the SAP BTP Destination, and CAP configuration.

Install

npm install cap-postgres-btp-connectivity

The package declares compatible versions of @cap-js/postgres, @sap-cloud-sdk/connectivity, and pg as peer dependencies. Current npm versions install those peers automatically. Applications that use an older package manager must install the peer dependencies explicitly.

CAP configuration

{
  "cds": {
    "requires": {
      "db": {
        "kind": "postgres",
        "impl": "cap-postgres-btp-connectivity",
        "destination": "POSTGRES_DB_CONFIG",
        "cloudConnectorLocationId": "MY_LOCATION",
        "localPort": 4010,
        "logLevel": "info",
        "logRepeatIntervalMillis": 10000,
        "pool": {
          "min": 1,
          "max": 10,
          "testOnBorrow": true,
          "acquireTimeoutMillis": 60000,
          "idleTimeoutMillis": 30000
        }
      }
    }
  }
}

Supported environment overrides are POSTGRES_DESTINATION, PG_DESTINATION, SCC_LOCATION_ID, CONNECTIVITY_PROXY_HOST, CONNECTIVITY_PROXY_PORT, CONNECTIVITY_LOCAL_PORT, SOCKS5_HANDSHAKE_TIMEOUT, PG_QUERY_TIMEOUT, and PG_CONNECTION_TIMEOUT.

Required SAP BTP Destination

Create the Destination with every field below. None of the listed fields should be omitted, even when a value could otherwise be inferred or defaulted by PostgreSQL, SAP Cloud SDK, or the package.

| Section | Field | Required value | |---|---|---| | Main Properties | Name | POSTGRES_DB_CONFIG (or the exact name configured in cds.requires.db.destination) | | Main Properties | Type | HTTP | | Main Properties | Proxy Type | Internet | | Main Properties | URL | https://www.postgresql.org/ | | Authentication | Authentication | BasicAuthentication | | Authentication | User | PostgreSQL database user | | Authentication | Password | PostgreSQL database password | | Additional Properties | host | Cloud Connector virtual PostgreSQL host: maximum 6 lowercase alphanumeric characters | | Additional Properties | dbname | PostgreSQL database name | | Additional Properties | port | Cloud Connector virtual PostgreSQL port, normally 5432 | | Additional Properties | cloudConnectorLocationId | Exact Cloud Connector Location ID: maximum 6 lowercase alphanumeric characters |

The package uses the additional host, port, dbname, and cloudConnectorLocationId properties for PostgreSQL routing. The host and port must be the Cloud Connector virtual host and port, not the physical Azure/private database address. Property names are case-sensitive for this standard configuration.

Common PostgreSQL aliases such as PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE remain recognized for backward compatibility, but new Destinations should use the exact fields shown above.

The deployed CAP application must be bound to SAP BTP Destination and Connectivity service instances.

MTA PostgreSQL deployer

CAP generates the PostgreSQL deployer in gen/pg. It is a separate Node.js module from the CAP server in gen/srv, so the adapter must also be present in the deployer's generated package.json and node_modules. Add one command immediately after cds build in mta.yaml:

build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
        - npm install cap-postgres-btp-connectivity --prefix gen/pg

The deployer module itself must be bound to both services. A complete deployer module looks like this:

- name: <APP-NAME>-postgres-deployer
  type: nodejs
  path: gen/pg
  requires:
    - name: <APP-NAME>-connectivity-service
    - name: <APP-NAME>-destination-service
  parameters:
    build-parameters: null
    buildpack: nodejs_buildpack
    memory: 1024M
    no-route: true
    no-start: true
    tasks:
      - command: npm start
        memory: 1024M
        name: deploy-to-postgresql

Replace the module and service names with the names used by the consuming application. path: gen/pg points to the deployer generated by cds build. The module is not started as a long-running application; no-start: true keeps it stopped, while the deploy-to-postgresql task runs npm start to deploy the database artifacts. no-route: true is appropriate because the deployer does not expose an HTTP endpoint.

No application-owned PostgreSQL adapter or SOCKS5 proxy files need to be copied into gen/pg or gen/srv. Do not separately install axios, pg, @cap-js/postgres, @sap-cloud-sdk/connectivity, or winston in the MTA commands; the library and its dependency declarations provide the required runtime modules. The normal CAP server build carries the root application dependency into gen/srv; the single extra install is needed only for the isolated gen/pg deployer.

For local development, the SOCKS5 proxy defaults to 127.0.0.1:3004. Change only the local port with localPort:

{
  "localPort": 4010
}

or with an environment variable:

CONNECTIVITY_LOCAL_PORT=4010 npm start

Production ignores localPort and uses onpremise_socks5_proxy_port from the Connectivity binding. proxyPort or CONNECTIVITY_PROXY_PORT is an explicit override for both local and production environments.

Local development with Cloud Foundry SSH

The local CAP process needs two things at the same time:

1. Cloud credentials for the Connectivity and Destination service instances.
2. An SSH port-forward from local port 3004 to the Connectivity SOCKS5 proxy endpoint.

Cloud Foundry cannot SSH directly into a managed service instance. The tunnel must pass through a running application container that can reach the Connectivity proxy. Use the deployed CAP server application, or another small host application, bound to the Connectivity service.

No application deployed yet: cf ssh <CONNECTIVITY-SERVICE-INSTANCE> is not supported. A Connectivity service instance is a managed service, not an SSH-accessible application container. Binding it locally with cds bind provides credentials but does not create a TCP route from the developer machine into Cloud Foundry. You must use a running host application in the same Cloud Foundry space.

If the CAP application has not been deployed, choose one of these options:

1. Use any existing running application in the same space as the SSH tunnel host. It does not need to be the CAP application, but it must support SSH and be able to reach the Connectivity proxy.
2. Deploy a small temporary SSH host application, bind the Connectivity service instance to it, and use that application name in the cf ssh command.
3. Deploy the CAP server application normally and use its running instance as the tunnel host.

For an existing or temporary host application, prepare it as follows:

cf bind-service <SSH-HOST-APP> <CONNECTIVITY-SERVICE-INSTANCE>
cf restage <SSH-HOST-APP>
cf ssh-enabled <SSH-HOST-APP>

If SSH is disabled and your account has permission to enable it:

cf enable-ssh <SSH-HOST-APP>
cf restage <SSH-HOST-APP>

Then open the tunnel through host application instance 0:

cf ssh <SSH-HOST-APP> -i 0 -L 3004:<ONPREMISE_PROXY_HOST>:<ONPREMISE_SOCKS5_PROXY_PORT> -N

Only the Connectivity service must be bound to the SSH host because the host is used solely as a network hop. The local CAP process still needs local bindings for both Connectivity and Destination, configured with cds bind as described below. The SSH host must remain running while the tunnel is in use; a stopped or crashed application instance cannot forward traffic.

1. Log in and select the correct Cloud Foundry space

cf login -a <CF-API-ENDPOINT>
cf target -o <ORG> -s <SPACE>

For a deployed CAP server, verify that it has both required service bindings. A dedicated SSH-only host needs only the Connectivity binding:

cf services
cf env <CAP-SRV-APP>

If the deployed CAP server bindings are missing, bind them and restage the application:

cf bind-service <CAP-SRV-APP> <CONNECTIVITY-SERVICE-INSTANCE>
cf bind-service <CAP-SRV-APP> <DESTINATION-SERVICE-INSTANCE>
cf restage <CAP-SRV-APP>

Do not copy the credentials printed by cf env into source control.

2. Bind the cloud services to the local CAP project

The easiest method is to inherit all supported service bindings from the deployed CAP server application:

npx cds bind --to-app-services <CAP-SRV-APP>

The short form is equivalent:

npx cds bind -a <CAP-SRV-APP>

To bind the service instances directly instead, run these commands from the CAP application root:

npx cds bind connectivity --to <CONNECTIVITY-SERVICE-INSTANCE>
npx cds bind destination --to <DESTINATION-SERVICE-INSTANCE>

cds bind creates service keys when needed and stores only binding metadata in .cdsrc-private.json under the hybrid profile. It does not store the resolved secrets in that file. Keep .cdsrc-private.json out of shared source control because it contains user- and landscape-specific configuration.

Verify that both bindings resolve:

npx cds env get requires.connectivity.credentials.onpremise_proxy_host --profile hybrid --resolve-bindings
npx cds env get requires.connectivity.credentials.onpremise_socks5_proxy_port --profile hybrid --resolve-bindings
npx cds env get requires.destination.credentials.uri --profile hybrid --resolve-bindings

If the Destination binding uses a different credential field instead of uri, resolving the complete requires.destination.credentials object is possible, but its output contains secrets and must not be saved or shared.

3. Open the local SOCKS5 port-forward

Use the onpremise_proxy_host and onpremise_socks5_proxy_port values resolved from the Connectivity binding in the previous step:

cf ssh <CAP-SRV-APP> -i 0 -L 3004:<ONPREMISE_PROXY_HOST>:<ONPREMISE_SOCKS5_PROXY_PORT> -N

• -i 0 selects application instance 0; use another running instance index if required.
• -L maps local 127.0.0.1:3004 to the Connectivity SOCKS5 proxy as seen from the application container.
• -N opens only the port-forward and does not start a remote shell.
• Keep this terminal running for the entire local CAP session.

Check SSH availability before opening the tunnel:

cf ssh-enabled <CAP-SRV-APP>
cf space-ssh-allowed

If application SSH is disabled and your account is authorized to change it:

cf enable-ssh <CAP-SRV-APP>
cf restage <CAP-SRV-APP>

If local port 3004 is already occupied, select another local port—for example 4010—on both sides:

cf ssh <CAP-SRV-APP> -i 0 -L 4010:<ONPREMISE_PROXY_HOST>:<ONPREMISE_SOCKS5_PROXY_PORT> -N

{
  "localPort": 4010
}

4. Start CAP with the resolved local bindings

In a second terminal, from the CAP application root:

npx cds watch --profile hybrid

The local process receives the resolved Connectivity and Destination credentials through VCAP_SERVICES. This package obtains the Connectivity OAuth token and Destination configuration from those bindings, then connects to 127.0.0.1:3004 (or the configured localPort). The SSH tunnel carries that SOCKS5 traffic through the selected Cloud Foundry application instance to SAP BTP Connectivity and Cloud Connector.

References: CAP Hybrid Testing and cds bind, Cloud Foundry SSH service access, and SAP Connectivity TCP protocol.

Logging

The package emits structured JSON logs for configuration, destination lookup, OAuth token lifecycle, SOCKS5 negotiation, PostgreSQL connection lifecycle, and pool validation. Passwords, tokens, client credentials, user names, database names, hosts, URLs, and Location IDs are always redacted.

Set logLevel in the CAP database configuration or use CAP_PG_LOG_LEVEL. Supported values are debug, info (default), warn, error, and silent.

CAP_PG_LOG_LEVEL=debug npm start

Use debug while diagnosing connectivity and info in production. The logger never prints protocol payloads or secret values.

If logLevel and CAP_PG_LOG_LEVEL are both omitted, the package uses info.

To avoid log flooding while the pool repeatedly attempts to restore an unavailable connection, identical log records are rate-limited. The first record is emitted immediately; identical records are suppressed for 10 seconds by default. When the interval passes, the next record contains details.suppressedRepeats with the number of omitted repetitions. A different event—especially connection.create.succeeded—is always emitted immediately.

Configure the interval in milliseconds:

{
  "logRepeatIntervalMillis": 30000
}

or:

CAP_PG_LOG_REPEAT_INTERVAL=30000 npm start

Set the interval to 0 to disable repeated-log suppression. This setting changes only logging; it does not disable or delay connection attempts.

Errors and diagnostics

Every package-generated failure is a BtpPostgresError. The thrown error and its structured connection.create.failed log contain:

• code: stable machine-readable failure identifier
• message: concise description; an underlying driver message is included when safe
• stage: the exact lifecycle stage that failed
• action: the corrective action for the user or operator
• retriable: whether a later pool acquisition may succeed after the underlying condition recovers
• cause: the original error on the thrown object (logs expose only safe cause metadata)

Common codes include:

| Stage | Error codes | |---|---| | Configuration and bindings | VCAP_SERVICES_INVALID, SERVICE_BINDING_MISSING, CONFIG_PORT_INVALID, CONFIG_TIMEOUT_INVALID | | Destination | DESTINATION_NOT_FOUND, DESTINATION_URL_INVALID, DESTINATION_HOST_MISSING, DESTINATION_PROPERTIES_MISSING, DESTINATION_FETCH_FAILED | | Connectivity token | CONNECTIVITY_CREDENTIALS_MISSING, CONNECTIVITY_TOKEN_HTTP_FAILED, CONNECTIVITY_TOKEN_INVALID, CONNECTIVITY_TOKEN_REQUEST_FAILED | | SOCKS5 | SOCKS5_PROXY_CONNECT_FAILED, SOCKS5_NEGOTIATION_FAILED, SOCKS5_AUTHENTICATION_FAILED, SOCKS5_TARGET_CONNECT_FAILED | | PostgreSQL | PG_DRIVER_LOAD_FAILED, PG_CLIENT_CREATE_FAILED, POSTGRES_CONNECT_FAILED, POSTGRES_RUNTIME_ERROR, POSTGRES_DISCONNECT_FAILED | | Pool and logger | POOL_CONFIG_INVALID, POOL_OPTION_INVALID, POOL_RANGE_INVALID, LOG_LEVEL_INVALID, LOG_REPEAT_INTERVAL_INVALID |

For example, a Cloud Connector mapping failure is reported as SOCKS5_TARGET_CONNECT_FAILED at stage socks5-target-connect, with an action directing the operator to verify the TCP mapping, Location ID, virtual endpoint, access policy, and database availability. Errors never include passwords, access tokens, client secrets, or protocol payloads in package logs.

Connection recovery and retries

testOnBorrow defaults to true, even when it is omitted from the pool configuration. Before CAP reuses an idle PostgreSQL client, the pool calls the package's connection validator. A client is invalid when it has closed, emitted an error, started ending, or terminated. The pool destroys an invalid client and creates a new SOCKS5 tunnel and PostgreSQL connection for a subsequent request.

Typical recovery logs are:

postgres.client.error
connection.validate.failed
connection.destroy.started
connection.create.started
socks5.connect.started
connection.create.succeeded

Connection replacement is not SQL-operation retry. A query that was running when the connection failed is returned to the caller as an error and is never automatically replayed by this package. Automatically replaying INSERT, UPDATE, or DELETE could execute a write twice when the server committed it before the connection was lost.

Applications may retry read-only operations with a limited application-level retry policy. Write retries should use transactions and an idempotency key or another operation identifier. If SAP Cloud Connector or PostgreSQL remains unavailable, connection creation fails and the pool request is bounded by acquireTimeoutMillis; the package does not run an infinite reconnection loop.

Security

• Never commit VCAP_SERVICES, service keys, destination exports, or database passwords.
• Rotate leaked credentials rather than only deleting their files.
• Configure PostgreSQL TLS with the ssl option when required by your network design.
• Use least-privilege database users and Cloud Connector mappings.