Sequelize: mapping between raw data and model

Question

I have some trouble to retrieve data from MySQL database using a raw query. The problem is the mapping between raw data and the instances of model defined in sequelize. In particular those fields that have underscored names in the database and camelcased in the model.

I defined the Store model in this way:

sequelize.define('stores', {
    id: {
        type: DataTypes.INTEGER(11),
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    ...
    postalCode: {
        type: DataTypes.STRING,
        field: 'postal_code',
    },
    ...
}

and I get data from a routine in the database using this code:

sequelize.query('CALL get_stores()', {model: Stores, type: sequelize.QueryTypes.RAW}).then(function (stores) {
    console.log(stores);
}

according to the documentation (http://docs.sequelizejs.com/en/latest/docs/raw-queries/) if I define the option "model" the function returns an instance of Store instead of raw data. But this is not true and, for all fields whose name is different in the model and in the database (see postalCode -> postal_code), the response I get returns an object with the database field names (postal_code) instead of those defined in the model (postalCode).

I already tried using QueryTypes.SELECT instead of QueryTypes.RAW but without success.

Please note that there is no problem when using the findAll or findOne method.

Answer 1

You can set the option mapToModel: true in your query to instruct Sequelize to translate the returned field names to the equivalent in the model you provide.

const User = sequelize.define('user', {
  userId: {
  field: 'user_id',
  type: DataTypes.BIGINT,
  primaryKey: true
})

sequelize.query(
  'select user_id from user where user_id > 1000',
  {
    mapToModel: true,
    model: User
  }
)

This feature is tucked away in the reference documentation: http://docs.sequelizejs.com/class/lib/sequelize.js~Sequelize.html#instance-method-query