connecting to dynamic postgresql database hosts using sails app version 0.12 based on the ui input to back end api's

Question

My requirement is to connect to dynamic databases of postgresql after the sails is lifted in sails api v 0.12. From angular ui, i have a drop down of different DB values i am sending one param (dbhostname)to dummycontroller.js and want to connect to that particular db whenever the db string name matches in connections.js

1) how to pass the api dummycontroller.js param ((dbhostname)) to connections.js 2) how to dynamically access the db hosts in connections.js or models.js. where to put them with if conditions ?

dummycontroller.js

from ui select drop down, the db value is passed to dummycontroller.js with api trigger

http://localhost:1337/api/dummy/dynamicDb?db=db

module.exports = {
dynamicDb: function (req, res, next) {

    const db= req.query.db;
console.log(db);

const qry = "select * from person where city= "newyork";
console.log(qry);
    dummy.query(qry,function (err, resp) {
            //user = JSON.parse(user);
        if (err) {
          return res.json({
            error: err
          });
        }
        if (resp === undefined) {
          return res.notFound();
        } else
                console.log(resp);
          return res.json({
            userData: resp.rows
          });

      }

    );
        }}

I want to capture db value and send to connections or models.js and select different db host based on db value and then trigger the db query in dummycontroller.js.

Answer 1

I found a work around for above scenario using pg node module in my sails app. Install pg plugin in your app i.e. npm install pg.

I have created dbconnections.js in the sails app controllers

const a = {
  host: 'abc.com',
  port: 'xxxx',
  database: 'xxxx',
  user: 'xxxx',
  password: 'xxxx'
}

const b = {
  host: 'ecd.com',
 port: 'xxxx',
  database: 'xxxx',
  user: 'xxxx',
  password: 'xxxx'
}


module.exports = { a: a, b: b};

Then in my other controllers I have used pg plugin to match with the user passed db env through api, i.e in my other controller

var sourceFile = require('./dbconnections.js');

const {
      Client
    } = require('pg');

    var match_env;

    for (x in sourceFile) {

      if (x === env) {
        match_env = sourceFile[x];

      }
    }

    const client = new Client(match_env)
 now client object will have all postgresql transactions which one could execute
    client.connect()
    const qry = "select * from person where city= "newyork";
    client.query(qry, function (err, resp) {

    console.log("before", client)

    client.end();
    console.log("after", client)
    //user = JSON.parse(user);
    if (err) {
      return res.json({
        error: err
      });
    }
    if (resp === undefined) {
      return res.notFound();
    } else
      console.log(resp);
    return res.json({
      userData: resp.rows
    });

  }

);


Based on the use case we could move the above code to other js file and call in as 
many controller as we can and this would work with multiple for multiple db 
connections of postgres sql in sails app.