Import large data from JSON file to MYSQL database using Sequelize

Question

I am trying to import data from JSON file to mysql database using Sequelize. I have written the following javascript code to achieve the same. Though it is working for the small data set but when I ran it for large file (containing millions of records) it does not work and the errors I see like.

javascript heap out of memory then I ran with this node --max-old-space-size=4096 importRecords.js then I got 2nd error
Unhandled rejection SequelizeConnectionAcquireTimeoutError: Operation timeout at pool.acquire.catch.error (F:\demo-sequelize\node_modules\sequelize\lib\dialects\abstract\connection-manager.js:282:52)

var Sequelize = require('sequelize');
var JM = require('json-mapper');
const fs = require('fs');

var sequelize = new Sequelize('testdb', 'root', 'root', {
    dialect : 'mysql',
    pool: {
      max: 5,
      min: 0,
      idle: 10000
    },
});

var Profile = sequelize.define('profile', {
  firstName: Sequelize.STRING,
  lastName: Sequelize.STRING,
  email: Sequelize.STRING
});

let rawdata = fs.readFileSync('largeData.json');
let input = JSON.parse(rawdata);

for(let customer of input){
  //console.log(customer.email);

  Profile.sync({force: true}).then(function () {
    // Table created
    return Profile.create({
      firstName: customer.firstName,
      lastName: customer.lastName,
      email: customer.email
    });
  });

}

Can anyone suggest how can I achieve this with

1. minimum time may be using asynchronous execution.
2. In optimal way by minimizing sequelize logging while execution

Ashish Modi · Accepted Answer · 2020-02-02 15:06:38Z

2

I don't think reading this big file synchronously in memory is a good idea. Streaming is a better option in these kind of scenarios. There are many packages available which can do this kind of job. I will give an example for one of them.

stream-json (https://github.com/uhop/stream-json) - https://github.com/uhop/stream-json/wiki/StreamArray

const fs = require("fs");
const StreamArray = require('stream-json/streamers/StreamArray');

async function insertRec(row) {
  console.log(row);
  // code to insert the record
}

async function process() {
  return new Promise((resolve, reject) => {
    fs.createReadStream('path to json having array')
      .pipe(StreamArray.withParser())
      .on("data", async row => {
        await insertRec(row);
      })
      .on("error", err => {
        reject(err);
      })
      .on("end", () => {
        console.log("CSV file successfully processed");
        resolve();
      });
  });
}

process();

this assumes you have a json in this format:

[
  {
    "id": 1,
    "field": 2
  },
  {
    "id": 2,
    "field": 5
  }
]

THis will give you an idea on how to integrate with your existing solution.

answered Feb 2, 2020 at 15:06

Ashish Modi

7,7902 gold badges23 silver badges36 bronze badges

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

4 Comments

Buddy566 Over a year ago

Thank you Ashish for reply, I tried this and I was still getting the heap out of memory issue then I ran it using node --max-old-space-size=4096 importRecords.js . but this helped in reading file (took less time to read)

Ashish Modi Over a year ago

It shouldn’t happen. How big is your file?

Buddy566 Over a year ago

File size is about 290 MB

Ashish Modi Over a year ago

would it be possible for you to put the code on github so I can have a look as 290 MB is nothing. There must be something else which is causing this issue

Collectives™ on Stack Overflow

Import large data from JSON file to MYSQL database using Sequelize

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related