Node.js database insertion from a file with Sequelize

Question

Maybe I don't understand fully promises or Sequalize, but after some time working with it, get a feeling that async DB operations works well only for simpler cases. And by number of questions asking for synchronous DB access it looks I'm not the only.

Here is my case. Suppose we have CSV file with compounds, where each line holds information about compound, and semicolon separated list of its ingredients. And we want to populate Ingredient table from it, but without duplications.

For example, if file contains rows

C1 IngA;IngB

C2 IngA;IngC

We want Ingredient table with three records, IngA, IngB, and IngC. So when line is read, it should save compound, for each ingredient check if already exists, and if not add it. Here is the code:

var lineReader=require('readline').createInterface({
  input: require('fs').createReadStream(filename)
});

lineReader.on('line', function(line) {

let parts=line.split('\t');
compoundModel.create({
  name: parts[0],
}).then(entity => {
  let ingredients=parts[1].split(';');

  ingredients.forEach((ing, index) => {
    ingModel.findOne({
      where: {name: ing}
    }).then(ingEntity => {
      if (ingEntity) {
        return ingEntity;
      }
      return ingModel.create({
        name: ing
      })
    });
  }).then(ingEntity => {
    //something else
  });
});
});    
 

The problem is that IngA gets inserted in a table twice. My guess is that find or create Sequelize methods returns promises, and line reading from a file is faster that DB insertion. So IngA from first row is not yet inserted when new line is read and IngA tried to be found.

I've tried several approaches, but everything seems too complicated for this kind of task. And more importantly, doesn't work.

Answer 1

You require to do transaction with locking.

Do a table level locking to prevent phantom reads occurring in your case

http://docs.sequelizejs.com/en/v3/api/transaction/

Answer 2

Please fine below solution, it should work.

var await = require('asyncawait/await');

var lineReader=require('readline').createInterface({
  input: require('fs').createReadStream(filename)
});

lineReader.on('line', function(line) {

let parts=line.split('\t');
compoundModel.create({
  name: parts[0],
}).then(entity => {
  let ingredients=parts[1].split(';');

  ingredients.forEach((ing, index) => {
        await(ingModel.findOrCreate({
          where: {name: ing}, defaults: {name: ing},
        }).spread(function(_record, _created){
        //Do something if required. _create will return true in case of entry already exists
        }))
  }).then(ingEntity => {
    //something else
  });
});
});    

Before executing this do npm install asyncawait. With the help of await it will wait till promise completes it's execution before executing next promise.