Specifying specific fields with Sequelize (NodeJS) instead of *

Question

Alright so I have a project in NodeJS where I'm utilizing Sequelize for a MySQL ORM. The thing works fantastically however I'm trying to figure out if there is a way to specify what fields are being returned on a query basis or if there's even a way just to do a .query() somewhere.

For example in our user database there can be ridiculous amounts of records and columns. In this case I need to return three columns only so it would be faster to get just those columns. However, Sequelize just queries the table for everything "*" to fulfill the full object model as much as possible. This is the functionality I'd like to bypass in this particular area of the application.

Answer 1

You have to specify the attributes as a property in the object that you pass to findAll():

Project.findAll({attributes: ['name', 'age']}).on('success', function (projects) {
  console.log(projects);
});

How I found this:

The query is first called here: https://github.com/sdepold/sequelize/blob/master/lib/model-definition.js#L131
Then gets constructed here: https://github.com/sdepold/sequelize/blob/master/lib/connectors/mysql/query-generator.js#L56-59

Answer 2

Try this in new version

template.findAll({
    where: {
        user_id: req.params.user_id 
    },
    attributes: ['id', 'template_name'], 
}).then(function (list) {
    res.status(200).json(list);
})

Answer 3

Use the arrays in the attribute key. You can do nested arrays for aliases.

Project.findAll({
  attributes: ['id', ['name', 'project_name']],
  where: {id: req.params.id}
})
.then(function(projects) {
  res.json(projects);
})

Will yield:

SELECT id, name AS project_name FROM projects WHERE id = ...;

Answer 4

All Answers are correct but we can also use include and exclude as well

Model.findAll({
  attributes: { include: ['id'] }
});

Model.findAll({
  attributes: { exclude: ['createdAt'] }
});

Source