Sequelize automatically sets a default value for NOT NULL columns

I am currently running Sequelize.js code on my MySQL database, that is created using migrations. I have a table with persons that is defined like this:

return queryInterface.createTable('Persons', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        unique: true,
        type: Sequelize.INTEGER
      },
      email: {
        allowNull: false,
        unique: true,
        type: Sequelize.STRING
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });

and the resulting table looks like this:

`Persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`)
)

When I add an entry to the database using Model.create({}) (with nothing between the brackets), the following object is added to the database:

id  email   firstName   lastName    createdAt   updatedAt
1   ''      NULL        NULL        2019-05-21 15:33:13 2019-05-21 15:33:13

Every NOT NULL column I have in my database gets a default value (empty string for varchar, false for boolean, NOW() for datetime).

The Sequelize.js docs state the following:

setting allowNull to false will add NOT NULL to the column, which means an error will be thrown from the DB when the query is executed if the column is null. If you want to check that a value is not null before querying the DB, look at the validations section below.

title: { type: Sequelize.STRING, allowNull: false },

I want to receive the error as stated in the official docs but have no clue what I’m doing wrong.

Answer

I fixed the issue.

I had to add allowNull to the model definition. This gave some declaration issues when trying it first, however now after revisiting the issue it worked and I get the correct error.

module.exports = (sequelize, DataTypes) => {
    const Persons = sequelize.define('Persons', {
        email: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        firstName: DataTypes.STRING(40),
        lastName: DataTypes.STRING(40)
    }, {});
    return Persons;
};

Leave a Reply

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