BTP NodeJS Express API with PostgreSQL Database Connection

[#1]

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?

1. I have a PostgreSQL Hyperscaler instance running.
2. I have the node app running in BTP.
3. I have enabled ssh for the application.
4. 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": []
    }
  ]
}

[Kuldip_Botre81]

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,
        }),
      ],
    };
  }
}​

[chad_fraser]

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

ssl: {
        require: true,
        rejectUnauthorized : false
      }

[vbalko-claimate]

thank you...

[chad_fraser]

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
  }

[vbalko-claimate]

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?