CODEWITHSUNDEEP
mysqlnode.jssequelize.js
JackRabbit
JackRabbit

Reputation: 135

Sequelize: how to eager load, with associations, raw: true?

Eager loading in Node.js is the only way I know how to use Sequelize. I am trying to export all User survey data from a MySQL DB with nested includes. There are over 500k rows of survey answers, and my script crashes from running out of memory due to creating an instance for every row returned.

I want to make the query "raw" and just get the simple object data, but then it only returns the first associated record of each include, instead of an array containing them all. Is there any way to get all associated records and also make it raw? Or is Sequelize not supposed to be used this way for large queries? Here's my query:

db.User.findAll({
  include: [
    { model: db.Referrer }, // has one Referrer
    { model: db.Individual }, // has many Individuals (children)
    {
      model: db.UserSurvey, // has many UserSurveys
      include: {
        model: db.Answer, // UserSurveys have many Answers
        include: {
          model: db.Question // survey question definition
        }
      }
    }
  ],
  raw: true // only returns first Individual, UserSurvey, Answer, etc.
  nest: true // unflattens but does not fix problem
})

This query works fine if I limit the returned rows. It only crashes from the size of the data set if I don't limit it. I have tried adding raw to the top level and everywhere inside the various includes, and nothing seems to work. Should I just try to base the query on Answer, so all relationships only require a single record? Or is there a way to make these complex queries raw and also include all related records? Thanks for reading, this has had me stumped for a few days.

Upvotes: 3

Views: 3139

Answers (1)

Anatoly
Anatoly

Reputation: 22758

As Sequelize documentation says about raw option:

sequelize will not try to format the results of the query, or build an instance of a model from the result

That means that if you have 1 main record and 2 associated records you'll get 2 records because it's what Sequelize gets from SQL-query.

I suppose that in your case you should use limit and offset options in a cycle to get records by chunks. That way you won't cause out of memory result.

Also to get plain objects instead of models use get({ plain: true }) for each model like this:

const users = db.User.findAll({
  include: [
    { model: db.Referrer }, // has one Referrer
    { model: db.Individual }, // has many Individuals (children)
    {
      model: db.UserSurvey, // has many UserSurveys
      include: {
        model: db.Answer, // UserSurveys have many Answers
        include: {
          model: db.Question // survey question definition
        }
      }
    }
  ]
})
const plainUsers = users.map(x => x.get({ plain: true }))

Upvotes: 6

Related Questions