Blog

The following tutorial explains how to connect to Postgresql database which requires SSL certificate using Knex

The SSL certificate is not required for all Postgresql databases.

This tutorial is applicable only for Postgresql databases which requires SSL certificate For eg: RDS for Postgresql requires all clients to connect using SSL/TLS.

In app.js we call the db connection from index.js

app.js
"use strict";
const express = require("express");
var bodyParser = require("body-parser");
const app = express();
const fs = require("fs");
const db = require("./index");
const cors = require("cors");
const { xss } = require("express-xss-sanitizer");

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cors());
app.use(xss());
app.options("*", cors());
//boilerplate code for express handling of request and response
fs.readdirSync(__dirname + "/controller")
  .filter(function (file) {
    return file.indexOf(".") !== 0 && file !== "endpoints.js";
  })
  .forEach(function (file) {
    var route = require("./controller/" + file);
    route.Controller(app, db);
  });

// catch 404 and forward to error handler


// error handler

In index.js we have the db connection code. We can either use the remote certificate provided by AWS RDS or download the certificate for local server usage.

index.js
const fs = require("fs");
const db = require('knex')({
    client: 'pg',
    connection: {
      host: dbHost,
      port: dbPort,
      user: dbUserName,
      database: dbName,
      password: dbPassword,
      ssl: {
        //ca: fs.readFileSync("/etc/ssl/certs/rds-ca-*****.pem").toString() // remote certificate
        //ca: fs.readFileSync(path.resolve(__dirname, "../../certs/global-bundle.p7b")).toString()//local downloaded certificate
      }
    }
  });
module.exports=db

In the controller file we retrieve the records from db and return the response

retrieve_records_endpoint.js
module.exports.Controller = (app, db) => {
    app.get(
        "/get/records",
        (req, res) => {
            try {
                let queryResult = db.select("column_name")
                .from("table_name")
                .where({condition}) 
                res.status(200).json({ success: true, data: queryResult}) 
                   
            } catch (e) {
                res.status(500).json({ success: false });
            }
        }
    );
}