Node Api rest - change database dynamically|

Question

Is it possible to change the pool config database?

I have a rest API with node/express, and I have multiple databases. So I need that when a user.company login in my frontend, the API rest, choose the database that user should use.

My configuration file for the bank is this .env

JWT_KEY=XXXXXXX 

POOL1_USER=root
POOL1_PASSWORD=xxxxxx
POOL1_DATABASE=data1  
POOL1_HOST=host.domain.com  
POOL1_PORT=3306

Meu arquivo db.js é este:


const mysql = require("mysql");

const pool1 = mysql.createPool({
  connectionLimit: 10,
  user: process.env.POOL1_USER,
  password: process.env.POOL1_PASSWORD,
  database: process.env.POOL1_DATABASE,
  host: process.env.POOL1_HOST,
  port: process.env.POOL1_PORT,
});


module.exports = { pool1 };

Is this my controllers.js file?


const mysql = require("../db").pool1;

exports.adminGroup = (req, res, next) => {
  mysql.getConnection((error, conn) => {
    if (error) {
      return res.status(500).send({ error: error });
    }
    conn.query(
      "INSERT INTO adminGroup SET ?",
      [req.body],
      (error, results) => {
        conn.release();
        if (error) {
          return res.status(500).send({ error: error });
        }
        response = {
          mensagem: "Group add",
          grupoCriado: {
            id: results.insertId,
            grupo: req.body.group,
          },
        };
        return res.status(201).send(response);
      }
    );
  });
};

I need to dynamically change the database, as I have the same frontend for the same rest API, but I have multiple databases that can even be on different hosts.

It may be that what I'm trying to implement is not possible, so does anyone have any different suggestions?

DBs hosted on different hosts (well, instances) require a separate connection pool. If the databases are hosted on the same mysql instance, then you can simply use dbname.tablename syntax to access tables in different databases. — Shadow
– Shadow, Commented Jan 11, 2022 at 13:35
I understood. there are two situations that I have in my structure, and you guys showed me solutions for them, thank you very much. — Nebenzahl
– Nebenzahl, Commented Jan 11, 2022 at 13:49

Abdelraouf Ferah · Accepted Answer · 2022-01-11 13:25:36Z

1

Before you use the query to select a table from a database, you need to switch the database, use this query to achieve that.

con.query("USE your_db_name", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });

then after it use the query that you want like this

const mysql = require("../db").pool1;

exports.adminGroup = (req, res, next) => {
  mysql.getConnection((error, conn) => {
    if (error) {
      return res.status(500).send({ error: error });
    }
con.query("USE your_db_name", function (err, result, fields) {
        if (err) throw err;
        console.log(result);
      });
    conn.query(
      "INSERT INTO adminGroup SET ?",
      [req.body],
      (error, results) => {
        conn.release();
        if (error) {
          return res.status(500).send({ error: error });
        }
        response = {
          mensagem: "Group add",
          grupoCriado: {
            id: results.insertId,
            grupo: req.body.group,
          },
        };
        return res.status(201).send(response);
      }
    );
  });
};

answered Jan 11, 2022 at 13:25

Abdelraouf Ferah

709 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shadow Over a year ago

If the db is on a different host, then "use dbname" is not enough to change the database. The question hints at different dbs being on different hosts.

Collectives™ on Stack Overflow

Node Api rest - change database dynamically|

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related