0

I am relatively new to the Node JS and I would like to query different databases. I wish I could group the query result into one array. I don't know the best way. i have a json example where i get database data from. Databases can be 2 or > 2. I thought if it made sense to use a loop.

The query is the same for each database, the databases have the same tables

SELECT * FROM my_db.USER  (this is an example)

const express = require('express')
const mysql = require('mysql2')
const app = express()

const database = [
{
    "host": "127.0.0.1",
    "user": "root",
    "password": "administrator",
    "database": "demo_db"
},
{
    "host": "127.0.0.1",
    "user": "root",
    "password": "administrator",
    "database": "demo_db"
},
{
    "host": "127.0.0.1",
    "user": "root",
    "password": "administrator",
    "database": "demo_db"
}
];

const connections = database.map(c => mysql.createConnection(c))

app.get('/', function (req, res) {

const sql = 'SELECT * FROM users'

Promise.all(connections.map(conn => conn.execute(sql, (err, ris, fileds) => {
    console.log(ris); //this send the result of query
}))).then(results => {
    res.send(results);
})
     app.listen(5000, () => {
     console.log('Server started')
   })

How can I adapt this code to multiple database connection? Thanks in advance

Max
  • 3
  • 4

2 Answers2

1

I would create an Array of connections looping through the configuration file. Then you can use the connections list to execute the queries. Something like:

const connections = connectionConfig.map(c => mysql.createConnection(c))
// ...

app.get('/', (req, res) => {
  const sql = 'SELECT * FROM my_db.USER'

  Promise.all(connections.map(db => db.query(sql))).then(results => {'
    // results is a list of the resultset from the N databases, you can
    // do whatever you want with that.
  })
})

Here you can get a look at Array.map function

ema
  • 5,668
  • 1
  • 25
  • 31
  • I understand that the way of the loop was very wrong :( ,I try to use your example and understand it. connectionConfig.map remains difficult for me to understand - Thanks – Max Jan 27 '22 at 12:32
  • I did some tests and db.query - db is not defined – Max Jan 27 '22 at 13:41
  • I fixed the code, there was a typo – ema Jan 27 '22 at 13:49
  • I have update the code in the answer, I would the result in json but I have the problem – Max Jan 27 '22 at 14:11
  • What do you mean? `res.json(results)` would return json to the caller – ema Jan 27 '22 at 14:43
  • I get this error : TypeError: Converting circular structure to JSON – Max Jan 27 '22 at 14:57
  • That's a problem of your json structure. More infos here: https://stackoverflow.com/questions/11616630/how-can-i-print-a-circular-structure-in-a-json-like-format?noredirect=1&lq=1 – ema Jan 27 '22 at 15:21
  • If you used **require(mysql2/promise)** it would be better – Max Jan 27 '22 at 15:49
  • With mysql2 i get this result [{"_events":{},"_eventsCount":0,"next":null,"sql":"SELECT * FROM users","insertId":0,"queryTimeout":null,"_rows":[],"_fields":[],"_result":[],"_fieldCount":0,"_rowParser":null,"_executeOptions":{"sql":"SELECT * FROM users"},"_resultIndex":0,"_localStream":null,"_connection":null},{"_events":{},"_eventsCount":0,"next":null,"sql":"SELECT * FROM users","insertId":0,"queryTimeout":null,"_rows":[],"_fields":[],"_result":[],"_fieldCount":0,"_rowParser":null,"_executeOptions":{"sql":"SELECT * FROM users"},"_resultIndex":0,"_localStream":null,"_connection":null}] – Max Jan 27 '22 at 17:23
  • I update the code the question – Max Jan 27 '22 at 17:28
0

I publish the answer if anyone needs it. I don't know if it's the best way. Finally I used mysql2 for node. Thanks anyway to ema

const express = require('express')
const mysql = require('mysql2')
const app = express()
const database = [

{
    "host": "localhost",
    "user": "root",
    "password": "administrator",
    "database": "demo_db2"
},
{
    "host": "localhost",
    "user": "root",
    "password": "administrator",
    "database": "demo_db2"
}

];

const connections = database.map(c => mysql.createConnection(c))

app.get('/', function (req, res) {
const result = [];
let i = 0;
const sql = 'SELECT nome FROM users';
connections.forEach(value => {
    value.execute(sql, (err, row) => {
        result.push(row);
        i++;
        if (i == connections.length) {
            res.send(result);
        };
    });
});
})
const PORT = '5000';
app.listen(PORT, (err) => {
if (err)
    console.log(err);
    console.log('Server up and running on port ' + PORT);
})
Max
  • 3
  • 4