Home > other >  Dynamically choosing database/tables in pg-promise API
Dynamically choosing database/tables in pg-promise API

Time:10-03

pg-promise API recommends an initial connection object like:

var pgp = require('pg-promise')();
const mysqlcon = `postgres://${process.env.DB_USER}:${process.env.DB_PASSWORD}@${process.env.DB_HOST}:5432/my_database}` //note that 'my_database' is hardcoded here
var db = pgp(mysqlcon);
module.exports = db;

then I use this object to query table_customers in my_database database:

var db = require('./models/postgres')
db.many('SELECT * from table_customers')
  .then(function (data) {
    console.log('DATA:', data)
  })
  .catch(function (error) {
    console.log('ERROR:', error)
})

This works perfectly fine, but if I were to select another table from another database, how could I dynamically modify the connection object to change my_database to another_database?

CodePudding user response:

create variable for dynamic change TABLE or DATABASE

var db = require('./models/postgres')
db.many('SELECT * FROM ${YOUR_VAR}')
.then(function (data) {
console.log('DATA:', data)
  })
.catch(function (error) {
 console.log('ERROR:', error)
})

CodePudding user response:

Connections in PostgreSql are database-bound, so you just use two database objects:

const mySqlCon1 = `postgres://.../database1`;
const mySqlCon2 = `postgres://.../database2`;

export const db1 = pgp(mySqlCon1);
export const db2 = pgp(mySqlCon2);
  • Related