Can't create rows for a belongsTo model, truncated column error

[stemmlerjs]

Expected behavior

Able to create a row in my Question model via the Forest interface.

Actual behavior

Fails to create the row for my Question model; Insert statement uses an invalid string for the foreign key.

Failure Logs

When I create my Question row in the Admin interface for job id=1, the result I get in SQL is:

INSERT INTO `question` (`question_id`,`job_id`,`question_type_id`,`is_premium_question`,`text`,`created_at`,`updated_at`) VALUES (1,'1-1','1',true,'Whats your favourite food?','2018-02-19 06:28:49','2018-02-19 06:28:49');

It throws the following error because '1-1' doesn't exist as an integer for job_id on my Questions table.

[forest] 🌳🌳🌳  Unexpected error: WARN_DATA_TRUNCATED: Data truncated for column 'job_id' at row 1
SequelizeBaseError: WARN_DATA_TRUNCATED: Data truncated for column 'job_id' at row 1
    at Query.formatError (C:\node-univjobs-back\forest\node_modules\sequelize\lib\dialects\mysql\query.js:175:14)
    at Query._callback (C:\node-univjobs-back\forest\node_modules\sequelize\lib\dialects\mysql\query.js:49:21)
    at Query.Sequence.end (C:\node-univjobs-back\forest\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
    at Query.ErrorPacket (C:\node-univjobs-back\forest\node_modules\mysql\lib\protocol\sequences\Query.js:94:8)
    at Protocol._parsePacket (C:\node-univjobs-back\forest\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (C:\node-univjobs-back\forest\node_modules\mysql\lib\protocol\Parser.js:74:12)
    at Protocol.write (C:\node-univjobs-back\forest\node_modules\mysql\lib\protocol\Protocol.js:39:16)
    at Socket.<anonymous> (C:\node-univjobs-back\forest\node_modules\mysql\lib\Connection.js:109:28)    at emitOne (events.js:115:13)
    at Socket.emit (events.js:210:7)
    at addChunk (_stream_readable.js:266:12)
    at readableAddChunk (_stream_readable.js:253:11)
    at Socket.Readable.push (_stream_readable.js:211:10)
    at TCP.onread (net.js:587:20)

Context

I'm hoping that I've just configured something wrong with my sequelize relationships. Here's what the two relevant models look like.

My Question model:

module.exports = function(sequelize, DataTypes) {
  const Question =  sequelize.define('question', {
    question_id: {
      type: DataTypes.INTEGER(11),
      primaryKey: true,
      autoIncrement: true
    },
    job_id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: false,
      references: {
        model: 'job',
        key: 'job_id'
      },
      onDelete: 'cascade',
      onUpdate: 'cascade'
    },
    question_type_id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      references: {
        model: 'lists_question_types',
        key: 'question_type_id'
      }
    },
    is_premium_question: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      default: 0
    },
    text: {
      type: DataTypes.STRING(160),
      allowNull: false
    }
  },{
    timestamps: true,
    underscored: true, // force createdAt, updatedAt => created_at, updated_at
    tableName: 'question',
    instanceMethods: {

    }
  });

  Question.associate = (models) => {
    Question.belongsTo(models.Job, { foreignKey: 'job_id', targetKey: 'job_id', as: 'Job' })
    Question.belongsTo(models.ListsQuestionTypes, { foreignKey: 'question_type_id', targetKey: 'question_type_id', as: 'QuestionType' })
  }

  return Question;
};

My Job model

const Job =  sequelize.define('job', {
    job_id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      autoIncrement: true,
      primaryKey: true
    },
    employer_id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      references: {
        model: 'employer',
        key: 'employer_id'
      },
      onDelete: 'cascade',
      onUpdate: 'cascade'
    },
    title: {
      type: DataTypes.STRING(30),
      allowNull: false
    }
  },{
    timestamps: true,
    underscored: true,
    tableName: 'job'
  });

Job.associate = (models) => {
    Job.hasMany(models.Question, { as: 'Questions', foreignKey: 'job_id', sourceKey: 'job_id' })
}

• Package Version: 2.4.2
• Express Version: 4.13.4
• Sequelize Version: 3.24.8
• Database Dialect: MySQL
• Database Version:

[arnaudbesnier]

Hi @stemmlerjs, thank you for the feedback.

Can you try to comment out primaryKey: false, in the job_id definition and check if it fixes your issue?

[stemmlerjs]

@arnaudbesnier Thanks for your reply.

I gave that a shot and it didn't fix my problem. If I set primaryKey: true in the job_id definition, it introduces an additional number picker field which I can use, but I was hoping to make it work with the model relationships.

[arnaudbesnier]

Hey @stemmlerjs, one late question, is it normal that your Job model contains a composite primary key (job_id, employer_id)?

Looks like Forest cannot properly handle le references on models having composite primary keys. I think it should work if you comment out the primaryKey: true, of Job.employer_id but I don't know if it would make sense for your application.