Asynchronous SqLite module on Express.js (Node.js)

Question

I'm trying make synchronous request to db and throw data to express. Here's code

app.get('/', (req, res) => {
  let db = new sqlite3.Database('./Problems.db');
  let sql = 'SELECT * FROM Problems ORDER BY problem_id';
  let data;
  db.all(sql, [], (err, rows, res) => {

    data = DBFetchingAllData(rows, db);
  });
  res.render('pages/index', { data });
});

Since db.all() is asynchronous function, res.renders catches undefined, it's my problem one. Second problem is in function DBFetchingAllData, I supposed it returns rows, but instead returns nothing. If somebody helps me make DBFetchingAllData returns rows properly and make db.all() synchronous, I'll be really appreciate.

function DBFetchingAllData(rows, db) {

    rows.forEach((row, index) => {
    // loop over the problem_questions_id
    // Array with answers
    row.answer = [];
    let row_with_id = _.split(row.problem_questions_id, ',');

    row_with_id.forEach((id) => {
      let sql = `SELECT * FROM QuestionsAndAnswers WHERE id = ?`;
      // use db.all not db.get to fetch an array of answers
      // this call is asynchronous so we need to check if we are done inside the callback
      db.get(sql, [id], (err, answer) => {
        // "answers" is an array here
        row.answer.push(answer);
        // if the index is one less than the length it's the last
        if (index === rows.length-1) {
          // we're done!
          return rows;
        }
      });
    });
  });
}

El houcine bougarfaoui · Accepted Answer · 2017-11-11 22:06:52Z

1

first problem's solution :

simply call res.render in all callback function

app.get('/', (req, res) => {
  let db = new sqlite3.Database('./Problems.db');
  let sql = 'SELECT * FROM Problems ORDER BY problem_id';
  let data;
  db.all(sql, [], (err, rows, res) => {

    data = DBFetchingAllData(rows, db);
    res.render('pages/index', { data });
  });

});

second solution : you forgot to return rows at the end of the function :

      function DBFetchingAllData(rows, db) {
         return Promise((resolve)=>{

            rows.forEach((row, index) => {
            // loop over the problem_questions_id
            // Array with answers
            row.answer = [];
            let row_with_id = _.split(row.problem_questions_id, ',');

            row_with_id.forEach((id) => {
              let sql = `SELECT * FROM QuestionsAndAnswers WHERE id = ?`;
              // use db.all not db.get to fetch an array of answers
              // this call is asynchronous so we need to check if we are done inside the callback
              (function(row,index){
                 db.get(sql, [id], (err, answer) => {
                   // "answers" is an array here
                   row.answer.push(answer);
                   // if the index is one less than the length it's the last
                   if (index === rows.length-1) {
                  // we're done!
                      resolve(rows)
                   }
                 });
              })(row,index)
            });

          });
        });
    }

edited Nov 11, 2017 at 22:06

answered Nov 11, 2017 at 21:35

El houcine bougarfaoui

37.6k10 gold badges40 silver badges38 bronze badges

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

1 Comment

Rami Chasygov Over a year ago

rows returns before rows.forEach(...) or rows.forEach() doesn't change array and returns original array

Collectives™ on Stack Overflow

Asynchronous SqLite module on Express.js (Node.js)

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