How to return a data from Express request by doing asynchronous sqlite3 queries within a loop?

0
votes

I am very new to NodeJS and Express and struggling to use sqlite3 module.

Let's assume I have a database with 2 tables: cars, car_details.
This is a simple example of what I am trying to do:

router.get('/get-car-info', (req, res) => {
    let car_details = [];
    let sql = '';
    let cars = ['audi', 'bmw', 'benz', 'honda', 'toyota];

    cars.forEach(car => {
        sql = `SELECT * FROM car_details WHERE name=${car}`;

        db.get(sql, function(err, data) {
            if (!err) {       
                car_details.push(data);
            }
        });
    });

    res.json({ 'success': true, 'cars': car_details });
});

This is a basic example but its enough to describe the problem.
Since db.get is async request, by the time the loop is done, the response is empty since it gets called before queries are finished.

If there was just one query then fine, I would send the response within callback but not sure how to achieve that when having a loop/recursion situation.

Thanks in advance...

2
node.jsexpressnode-sqlite3

2 Answers

0
votes

You can use where in clause to get data for more details visit here 

router.get('/get-car-info', (req, res) => {
      let car_details = [];
      let cars = ['audi', 'bmw', 'benz', 'honda', 'toyota];

      function inParam(sql, arr) {
        return sql.replace('?#', arr.map(() => '?').join(','))
      }

      db.get(inParam('SELECT * FROM car_details WHERE name in (?#)', car), car, function(err, data) {
        if (!err) {
          console.log(data)
          car_details.push(data);
        }
      });
 });
0
votes

You can do something like this.. but please verify the car_details result , i am hoping each db.get request return data directly..

   router.get('/get-car-info', async (req, res) => {
        let sql = '';
        let cars = ['audi', 'bmw', 'benz', 'honda', 'toyota];    
        let car_details = await Promise.all(cars.map(car => {
            sql = `SELECT * FROM car_details WHERE name=${car}`;
            return db.get(sql);
        }));

        res.json({ 'success': true, 'cars': car_details });
    });