NodeJS

Bulk queries with promises

Implementing a Promise function we can recurvise execute a list of queries to a data base. This function wait for the response of each query for execute the next of the list.

Array

This is the array of queries to do

le queries = [
  `INSERT IGNORE INTO keywords ( name ) VALUES ( 'keyword')`,
  `INSERT IGNORE INTO tip_keywords ( tip_id, keyword_id ) VALUES ( '1', 'keyword' )`
];

Function

This function implements a Promise pattern

const queriesExecute = (q, index = 0) => {
  let queries = q;
  new Promise(function(resolve, reject) {
    console.log(queries[index]);
    connection.query(queries[index], (err, row) => {
      if (err) {
        reject(err);
      } else {
        console.log(`Keyword added!`.green);
        resolve(row);
      }
    });
  })
  .then(data => {
    if (index < queries.length - 1) {
      index++;
      queriesExecute(queries, index);
    } else {
      connection.end();
    }
  })
  .catch(err => {
    queryError(err, query);
  });
};

Full example

This example get the key from param and edit the secon query with an Helper function.

'use strict';
const db = require('./DataBase')();
const connection = db.connect();
const tip = process.argv[3];
const keyword = process.argv[4];
const queryError = (err, query) => {
  console.log(`Error while performing Query:`.red);
  console.log(`${query}`.yellow);
  console.log(`${err}`.red);
  connection.end();
};
const queriesExecute = (q, index = 0) => {
  let queries = q;
  new Promise(function(resolve, reject) {
    console.log(queries[index]);
    connection.query(queries[index], (err, row) => {
      if (err) {
        reject(err);
      } else {
        console.log(`Keyword "${keyword}" added!`.green);
        resolve(row);
      }
    });
  })
  .then(data => {
    if (index < queries.length - 1) {
      index++;
      queryHelper(queries, index, {id: data.insertId});
      queriesExecute(queries, index);
    } else {
      connection.end();
    }
  })
  .catch(err => {
    queryError(err, query);
  });
};
const queryHelper = (query, index, data) => {
  query[index] = query[index].replace('%KEYWORD_ID%', data.id);
  return query;
};
queriesExecute([
  `INSERT IGNORE INTO keywords ( name ) VALUES ( '${keyword}')`,
  `INSERT IGNORE INTO tip_keywords ( tip_id, keyword_id ) VALUES ( '${tip}', '%KEYWORD_ID%' )`
]);