Question Re: BTP NodeJS Express API with PostgreSQL Database Connection in Technology Q&A

BTP NodeJS Express API with PostgreSQL Database Connection

chad_fraser — Thu, 21 Jul 2022 18:14:15 GMT

I have exhausted my attempts to connect to my PostgreSQL Hyperscaler instance in BTP and thought I'd reach out to the community for help since I wasn't able to find an answer yet. I have a working node express api app that leverages a local postgreSQL database and returns data. But when I deploy to BTP I cannot connect to my PostgreSQL Hyperscaler instance - I get a 502 Bad Gateway response.

My question is what I do to connect to the database once I deploy the app to BTP? Can someone indicate what I'm doing wrong in my connection code?

I have a PostgreSQL Hyperscaler instance running.
I have the node app running in BTP.
I have enabled ssh for the application.
I have a Bind Service.

Here is the Bind Service (some information removed):

{
	"username": "9a5121579182",
	"password": "XXXXXXXXXX",
	"hostname": "postgres-fdb76d9f-e770-4d87-8cff-c105aa990222.cqryblsdrbcs.us-east-1.rds.amazonaws.com",
	"dbname": "XXXXXXXXXX",
	"port": "3959",
	"uri": "postgres://9a5121579182:XXXXXXXXXX@postgres-fdb76d9f-e770-4d87-8cff-c105aa990222.cqryblsdrbcs.us-east-1.rds.amazonaws.com:3959/XXXXXXXXXX",
	"sslcert": "-----BEGIN CERTIFICATE-----REMOVED FOR EXAMPLE-----END CERTIFICATE-----",
	"sslrootcert": "-----BEGIN CERTIFICATE-----REMOVED FOR EXAMPLE-----END CERTIFICATE-----"
}

Here is my connection in my queries.js file .env populated with the Bind Service information from above.

const Pool = require('pg').Pool
const pool = new Pool({ 
  
  user: `${process.env.DB_USER}`,
  host: `${process.env.DB_HOST}`,
  database: `${process.env.DB_DATABASE}`,
  password: `${process.env.DB_PASSWORD}`,
  port: `${process.env.DB_PORT}`

})

Here is my VCAP_SERVICES results:

VCAP_SERVICES: {
  "postgresql-db": [
    {
      "binding_guid": "4d953482-131d-4729-aa3a-812ef796da51",
      "binding_name": null,
      "credentials": {
        "dbname": "XXXXXXXXXX",
        "hostname": "postgres-fdb76d9f-e770-4d87-8cff-c105aa990222.cqryblsdrbcs.us-east-1.rds.amazonaws.com",
        "password": "XXXXXXXXXX",
        "port": "3959",
        "sslcert": "-----BEGIN CERTIFICATE-----REMOVED FOR EXAMPLE-----END CERTIFICATE-----",
	"sslrootcert": "-----BEGIN CERTIFICATE-----REMOVED FOR EXAMPLE-----END CERTIFICATE-----"
        "uri": "postgres://9a5121579182:XXXXXXXXXX@postgres-fdb76d9f-e770-4d87-8cff-c105aa990222.cqryblsdrbcs.us-east-1.rds.amazonaws.com:3959/XXXXXXXXXX",
        "username": "9a5121579182"
      },
      "instance_guid": "fdb76d9f-e770-4d87-8cff-c105aa990222",
      "instance_name": "XXXXXXXXXX",
      "label": "postgresql-db",
      "name": "XXXXXXXXXX",
      "plan": "trial",
      "provider": null,
      "syslog_drain_url": null,
      "tags": [
        "relational",
        "database"
      ],
      "volume_mounts": []
    }
  ]
}

Re: BTP NodeJS Express API with PostgreSQL Database Connection

chad_fraser — Thu, 28 Jul 2022 17:57:34 GMT

Well I solved the issue. What I was missing was the ssl key in the client config. The connection was failing because the connection requires ssl. Here is the query code.

const pg = require('pg');


pool = new pg.Client({
    host: db_hostname,
    port: db_port,
    database: db_database,
    user: db_username,
    password: db_password,
    ssl: {
        require: db_ssl,
        rejectUnauthorized : false
    }
  });

pool.connect();

const getData = (request, response) => {
  pool.query('SELECT * FROM some_table ORDER BY id ASC', (error, results) => {
    if (error) {
      throw error
    }
    response.status(200).json(results.rows)
  })
}

module.exports = {
    getData
  }

Re: BTP NodeJS Express API with PostgreSQL Database Connection

vbalko-claimate — Thu, 16 Feb 2023 14:04:14 GMT

Hello chad.fraser ,

thanks for sharing solution.

What is

ssl: {
require: db_ssl

statement? I couldnt find that in docu, what its used for? and What did you put into db_ssl - just true/false or cert from sslcert VCAP?

Re: BTP NodeJS Express API with PostgreSQL Database Connection

chad_fraser — Thu, 16 Feb 2023 16:46:51 GMT

The "required" property is a boolean and be set to "true".

ssl: {
        require: true,
        rejectUnauthorized : false
      }

Re: BTP NodeJS Express API with PostgreSQL Database Connection

vbalko-claimate — Fri, 17 Feb 2023 10:07:30 GMT

thank you...

Re: BTP NodeJS Express API with PostgreSQL Database Conne...

Kuldip_Botre81 — Sun, 31 Aug 2025 03:11:30 GMT

I got the solution of NestJS and TypeORM BTP postgres

import { DynamicModule, Module } from '@nestjs/common'; import { TypeOrmModule } from '@nestjs/typeorm'; @Module({}) export class DatabaseModuleBTP { static async forRoot(): Promise<DynamicModule> { const creds = JSON.parse(process.env.VCAP_SERVICES || '{}')["postgresql-db"][0].credentials; await new Promise(resolve => setTimeout(resolve, 1000)); return { module: DatabaseModuleBTP, imports: [ TypeOrmModule.forRoot({ type: 'postgres', host: creds.hostname, port: Number(creds.port), username: creds.username, password: creds.password, database: creds.dbname, ssl: { rejectUnauthorized: true, ca: creds.sslrootcert.replace(/\\n/g, '\n'), cert: creds.sslcert.replace(/\\n/g, '\n'), }, entities: [__dirname + '/../**/*.entity{.ts,.js}'], synchronize: true, logging: ['query', 'error', 'log', 'warn', 'info'], autoLoadEntities: true, }), ], }; } }