NodeJs Synchronous MYSQL Query and Foreach

Question

I have some mysql Query's in NodeJs that depends to a above result. Here is my code:

const sms = async function() {  
await connection.query('select * from sms where sms.status_id = 2',
    (err, rows) => {
      if (err) throw err
        rows.forEach(row => {
            startdate = moment(row.statdate).format('YYYY-MM-DD HH:mm:ss');
            enddate = moment(row.enddate).format('YYYY-MM-DD HH:mm:ss');                
            connection.query('select * from positions where sms_id = "'+row.id+'" and verified is null order by date asc',
                (err, rows2) => {
                   if (err) throw err
                   rows2.forEach(row2 => {
                        connection.query('HERE IS A QUERY TO INSERT A REGISTER IN ANOTHER TABLE', data, (err,res) => {
                            if (err) throw err
                            console.log(`Inserted ${res.insertId}`);
                        })
                   })
                }
            ); 
        })
    }
)

The problem is, that way, I don't have the expected result in the middle query.

How can i do this synchronous?

I try to use Promises, but i don't understand how to do.

An important missing piece of the puzzle is [how to promisify functions that accept a nodeback](https://stackoverflow.com/a/22519785/3478010). — Roamer-1888, Jul 02 '20 at 02:40

ΔO 'delta zero' · Accepted Answer · 2020-07-01T21:57:40.913

In most cases, you should avoid synchronous functions, especially if running server operations. Rather, I'd recommend using mysql2 and it's promise wrapper.

Then you can do:

const mysql = require('mysql2/promise');
...

// use promise, then, catch

const sms = async function() {  
  return connection.query('select * from sms where sms.status_id = 2')
    .then(([smss]) =>
      smss.map(row => {
        connection.query('select * from positions where sms_id = "'+row.id+'" and verified is null order by date asc')
          .then(([positions]) => positions)) // unwrap array
    )
    .then(positions => 
      positions.map(row => connection.query('...'))
    )
    .catch(e => {
      // process errors
    });
};

// or use awaits

const sms = async () => {
  let [smss] = await connection.query('select * from sms where sms.status_id = 2');
  let positions = await Promise.all(smss.map(row => {
    ... 
  });
};

Alternatively, you can use some better SQL queries to simplify the process. Like this:

select *
from sms
join positions on (positions.sms_id = sms.id)
where sms.status_id = 2
  and positions.verified is null
order by positions.date

It didn't work. Returns: (node:61661) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). — Bruno, Jul 01 '20 at 22:16
Then catch the error using the .catch() and past what is says — ΔO 'delta zero', Jul 01 '20 at 22:51

NodeJs Synchronous MYSQL Query and Foreach

1 Answers1