how to get all the rows in recursive function calling in mysql2 and Nodejs?

Question

i am new in Javascript. I am trying to get all row and their parent rows with promise and async await

here is my code

const e = require('express')
const mysql2 = require('mysql2')


const pool = mysql2.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: '',
  database: 'dreamselling',
  waitForConnections: false,
  connectionLimit: 10,
  queueLimit: 0
})

let levels=[];
const getLevel = (id,levels) => {
  return new Promise((resolve, reject) => {
    let sql = `select id,memberName,parent_id from member_table where id=${id}`;
    pool.getConnection((error, connection) => {
      connection.query(sql, (err, rows) => {
        if (rows && rows[0].parent_id != null) {
          // console.log(rows[0]);
          levels.push((rows[0]))
          getLevel(rows[0].parent_id,levels)

        } else {
          pool.end()
        }
      })
    })
  })

}

async function myfunc() {
  let a = await getLevel(7)
  console.log(levels);
  // console.log('level',a);
}
myfunc()

i can see the rows with console.log(rows[0]) but cannot return from the function i need help about this

Answer 1

Try to use async await as suggested by @robertklep.
Also, make sure that you use a prepared statement:

const e = require('express');
const mysql2 = require('mysql2');

const pool = mysql2.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: '',
  database: 'dreamselling',
  waitForConnections: false,
  connectionLimit: 10,
  queueLimit: 0,
});

let levels = [];

const getLevel = async (id, levels) => {
  const promisePool = pool.promise();
  const [rows, fields] = await promisePool.query(
    'select id,memberName,parent_id from member_table where id = ?',
    [id]
  );
  if (rows && rows[0].parent_id !== null) {
    levels.push(rows[0]);
    getLevel(rows[0].parent_id, levels);
  } else {
    pool.end();
  }
};

function myfunc() {
  getLevel(7);
  console.log(levels);
}

myfunc();