pass argument to mysql connection string

Question

Below is my code (under file db.js)

function connection() {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: "mydb.abcd1234.ap-southeast-1.rds.amazonaws.com",
      database: "mydatabase",
      user: "user",
      password: "password", 
      connectionLimit: 11,
      waitForConnections: true,
      queueLimit: 0,
      namedPlaceholders: true
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

const pool = connection();

module.exports = {
  connection: async () => pool.getConnection(),
  execute: (...params) => pool.execute(...params)
};

And to use it, I simply add var db = require('./../db.js'); at the top of the files that requires it. Additionally, below is how I'd execute any sql statements

const myResult = await db.execute("Select COUNT(*) AS dataCount from teachers WHERE username = ?", [username]);

But now I need to separate my database according to countries. So my database would be named with mydatabase_ + the country code, for example mydatabase_my , mydatabase_jp, mydatabase_uk etc etc. So how do I pass the country code to the function connection() ?

I tried something like

function connection(country) {
  try {
    const mysql = require('mysql2');

    const pool = mysql.createPool({
      host: "mydb.abcd1234.ap-southeast-1.rds.amazonaws.com",
      database: "mydatabase_" + country,
      user: "user",
      password: "password", 
      connectionLimit: 11,
      waitForConnections: true,
      queueLimit: 0,
      namedPlaceholders: true
    });

    const promisePool = pool.promise();

    return promisePool;
  } catch (error) {
    return console.log(`Could not connect - ${error}`);
  }
}

const pool = connection(country);

module.exports = {
  connection: async (country) => pool.getConnection(country),
  execute: (...params) => pool.execute(...params)
};

and at the calling page, I did

var db = require('./../db.js');
db.connection("my")

but for the above, I will get ReferenceError: country is not defined which refers to country at the line const pool = connection(country);

score 0 · Accepted Answer · answered Nov 01 '19 at 14:50

Here's how I did it:

const mysql = require('mysql2');

const pool = function(country){
  var mydb = mysql.createPool({
    host: "mydb.abcd1234.ap-southeast-1.rds.amazonaws.com",
      database: "mydatabase_" + country,
      user: "user",
      password: "password", 
      connectionLimit: 11,
      waitForConnections: true,
      queueLimit: 0,
      namedPlaceholders: true
  });
  return mydb;
}

const promisePool = function(country){
  return pool(country).promise();
}

module.exports = {
  connection: async (country) => promisePool(country).getConnection(),
  execute: (country,...params) => promisePool(country).execute(...params)
};

And in the page that uses mysql will have something like this:

db.connection(country)
const myResult = await db.execute(country, "Select * from schools");

pass argument to mysql connection string

1 Answers1