How to insert value ‘null’ into MariaDB with Sequelize?

I’m building an App in Node.js to manage a database for a coding school. So far I have 2 models: Courses and Teachers. In the form to add a course I inserted an option to not select a teacher for a course.

I can insert the value of ‘null’ without a problem when using the DB GUI:

INSERT INTO courses (name, start_date, end_date, hours, teacher_id) VALUES ("Databases", '2020-04-18', '2020-04-19', 1, null);

But it doesn’t work in the form. This is my Courses module:

const { Sequelize, DataTypes } = require('sequelize');
const db = require('../config/database');
const Teachers = require('./Teachers');

const Courses = db.define('Courses', {
  course_id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(40)
  },
  hours: {
    type: DataTypes.INTEGER
  },
  start_date: {
    type: DataTypes.DATEONLY
  },
  end_date: {
    type: DataTypes.DATEONLY
  },
  teacher_id: {
    type: DataTypes.INTEGER,
    allowNull: true,
    references: {
      model: Teachers,
      key: 'teacher_id'
    }
  }
}, {
  freezeTableName: true
});

Courses.belongsTo(Teachers, {foreignKey: 'teacher_id'});

module.exports = Courses;

This is the select-option in my form to add a course:
(I have also tried with value="NULL" or an empty string.)

<select 
  type="number"
  name="teacher_id" 
  id="teacher_id" 
  class="input-box">
    <option value="" selected disabled hidden>Please select</option>
    <option value="null">not determined yet</option>
    <option value="2">Jane Doe</option>
    <option value="5">Steven Smith</option>
    <option value="11">Arthur Miller</option>
</select>

This is my long error message:

Executing (default): INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?);
DatabaseError [SequelizeDatabaseError]: (conn=2067, no: 1366, SQLState: 22007) Incorrect integer value: 'null' for column `codingschool`.`courses`.`teacher_id` at row 1
sql: INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?); - parameters:['HTML & CSS','1','2021-03-16','2021-03-19','null','2021-03-16 20:13:35.832','2021-03-16 20:13...]
    at Query.formatError (/Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/dialects/mariadb/query.js:280:16)
    at Query.run (/Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/dialects/mariadb/query.js:66:18)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async /Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/sequelize.js:619:16
    at async MySQLQueryInterface.insert (/Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/dialects/abstract/query-interface.js:748:21)
    at async model.save (/Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/model.js:3954:35)
    at async Function.create (/Users/cb/Clients/Hamburg Coding School/node_modules/sequelize/lib/model.js:2207:12) {
  parent: SqlError: (conn=2067, no: 1366, SQLState: 22007) Incorrect integer value: 'null' for column `codingschool`.`courses`.`teacher_id` at row 1
  sql: INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?); - parameters:['HTML & CSS','1','2021-03-16','2021-03-19','null','2021-03-16 20:13:35.832','2021-03-16 20:13...]
      at Object.module.exports.createError (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/misc/errors.js:55:10)
      at PacketNodeEncoded.readError (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet.js:505:19)
      at Query.readResponsePacket (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/cmd/resultset.js:46:28)
      at PacketInputStream.receivePacketBasic (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet-input-stream.js:104:9)
      at PacketInputStream.onData (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet-input-stream.js:169:20)
      at Socket.emit (events.js:315:20)
      at addChunk (_stream_readable.js:309:12)
      at readableAddChunk (_stream_readable.js:284:9)
      at Socket.Readable.push (_stream_readable.js:223:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
    fatal: false,
    errno: 1366,
    sqlState: '22007',
    code: 'ER_TRUNCATED_WRONG_VALUE_FOR_FIELD',
    sql: 'INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?);',
    parameters: [
      'HTML & CSS',
      '1',
      '2021-03-16',
      '2021-03-19',
      'null',
      '2021-03-16 20:13:35.832',
      '2021-03-16 20:13:35.832'
    ]
  },
  original: SqlError: (conn=2067, no: 1366, SQLState: 22007) Incorrect integer value: 'null' for column `codingschool`.`courses`.`teacher_id` at row 1
  sql: INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?); - parameters:['HTML & CSS','1','2021-03-16','2021-03-19','null','2021-03-16 20:13:35.832','2021-03-16 20:13...]
      at Object.module.exports.createError (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/misc/errors.js:55:10)
      at PacketNodeEncoded.readError (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet.js:505:19)
      at Query.readResponsePacket (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/cmd/resultset.js:46:28)
      at PacketInputStream.receivePacketBasic (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet-input-stream.js:104:9)
      at PacketInputStream.onData (/Users/cb/Clients/Hamburg Coding School/node_modules/mariadb/lib/io/packet-input-stream.js:169:20)
      at Socket.emit (events.js:315:20)
      at addChunk (_stream_readable.js:309:12)
      at readableAddChunk (_stream_readable.js:284:9)
      at Socket.Readable.push (_stream_readable.js:223:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
    fatal: false,
    errno: 1366,
    sqlState: '22007',
    code: 'ER_TRUNCATED_WRONG_VALUE_FOR_FIELD',
    sql: 'INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?);',
    parameters: [
      'HTML & CSS',
      '1',
      '2021-03-16',
      '2021-03-19',
      'null',
      '2021-03-16 20:13:35.832',
      '2021-03-16 20:13:35.832'
    ]
  },
  sql: 'INSERT INTO `Courses` (`course_id`,`name`,`hours`,`start_date`,`end_date`,`teacher_id`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?);',
  parameters: [
    'HTML & CSS',
    '1',
    '2021-03-16',
    '2021-03-19',
    'null',
    '2021-03-16 20:13:35.832',
    '2021-03-16 20:13:35.832'
  ]
}

Thanks a lot for taking a look!

Answer

It looks like a “null” != null problem. Probably you are trying to insert the string “null”. Hence the error message. tearcher_id must be an integer or null, but not “null”.

Check the place in your code where the course object is created. If the option “null” was selected in the form, you must set course.teacher = null and not just take the value from the form.

Leave a Reply

Your email address will not be published. Required fields are marked *