Sequelize automatically sets a default value for NOT NULL columns

Question

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 1

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;
};

Answer 2

This can happen in two cases.

First, if you have entered the allowNull later after defining the model then you need to resync the table with sync({force: true}).

Second, allowNull will work if attributes like email and firstName are not provided to create(). for example create({email:'',firstName:''}) will fill empty values.But if you do create({email:''}),then skipping firstName attribute will cause validation error.

In case if you are doing create(req.body) as from an HTML form tag or something and body has fields email and firstName set to empty strings by default if no values were passed. then empty values will be entered. As sequelize don't consider empty string values in req.body object a null value.

You can adjust your validations to avoid such behavior by adding notEmpty: true to your validations as below.

email: {
        allowNull: false,
        unique: true,
        type: Sequelize.STRING
        validate : {
           notEmpty: true
        }

or you can do the below little hack by putting the allowNull in a validate property like below and it will work ;)

 email: {
        unique: true,
        type: Sequelize.STRING
        validate : {
           allowNull: false
        }

Hope I made myself clear :)