How I tripped over a naked wire, using sequelize.js

Recently, I have been working on building a modestly robust back-end HR management service using the NodeJS/ExpressJS framework. After every necessary and preliminary set up that included choosing, installing, and setting up a postgresql database, I embarked on modelling this database.

As highly recommended, I chose to use the sequelize library. Everything was going fine as I was shuffling between absorbing the documentation and creating my models. When I was done with the first model (equivalent of a Table in the database), I decided to write a bunch of tests (using the jest framework). I generally grumble at writing tests, but I do so anyways.

It is important to show bits of my code at this point to help understand how I slippery ran into a pole standing right before my eyes and got stuck in it for 5 days.

In order to create a new model, using sequelize, you have the option of using sequelize.define or initialize a new Sequelize instance (using: new Sequelize). I opted for sequelize.define and below was the my simple Employee Table that I was modelling:

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

const Employee = sequelize.define('Employee', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    allowNull: {
      args: false,
      msg: 'id cannot be null'
      },
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: {
        args: false,
        msg: 'name cannot be null'
        },
    validate: {
      is: {
        args: /^[a-zA-Z'-]+\s?[a-zA-Z'-]*$/i,
        msg: 'Regex pattern does not match, expecting `firstName secondName`'
        // TODO: Regex pattern should have special chars name, like German, Yoruba special chars, etc.
      },
    }
  },
}

(async () => {
  await sequelize.sync();
})();

module.exports = { Employee, DataTypes };

The above code is not the full information, but it is enough for my demonstration. While testing, I tested for the behavior if I tried to create an entry into the table with a null name value. Using jest as my testing framework, I expected that this will throw an error with the message as indicated in the code above ('name cannot be null'), which it did. Particularly, a snippet from the code that tested this behavior is below.

test('test for null name', async () => {
    try {
      await Employee.create({
        name: null, // try to create employee with a null value.
        address: emp1.address,
        email: emp1.email,
        phone: emp1.phone,
        position: emp1.position,
        department: emp1.department,
      });
    } catch (error) {
      expect(error.message).toMatch("name cannot be null");
    }

Things seemed to be working pretty well, and my tests were all doing fine. After about 300 mini lines of test, I started to test the behavior of updating a given employee name to null and also expected the same error to be thrown as above. The code for the test was as below

test('test for name field when it is updated with a null value', async () => {
    try {
      await Employee.create({
        name: emp1.name,
        address: emp1.address,
        email: emp1.email,
        phone: emp1.phone,
        position: emp1.position,
        department: emp1.department,
      });
      let testEmp = await Employee.findOne( {where: { name: emp1.name } } ); // find the row with emp1.name.
      expect(testEmp.name).not.toBe(emp2.name);  //Test that emp1.name is not equal to emp2.name before update.

      // Update emp1.name to null.
      await Employee.update( {name: null},
        {
          where: { name: testEmp.name },
        }
      )
      // Use the id obtained from previous DB query to access the same row.
      testEmp = await Employee.findOne( {where: { id: testEmp.id } } );
      expect(testEmp).toBeNull();
    } catch(error) {
      // console.log(`Nasty Error ${error}`);
      expect(error.message).toBe("name cannot be null");
    }

As seen in the code above, I was expecting to get the error `name cannot be null`, just like I did when I created a new Employee. Instead this particular test was failing with the message below:
- name cannot be null
+ expect(testEmp).toBeNull();
+
+ Received: {"address": "762 Gibi Key", "createdAt": 2024-09-07T14:27:58.678Z, "department": "Sales & Logistics", "email": "Elijah31@yahoo.com", "id": "d6974fcd-d50d-4359-87c1-c09898259e7f", "name": null, "phone": "970.521.6889", "position": "novice", "updatedAt": 2024-09-07T14:27:58.683Z}

From this output, we see that my test was expecting the given error ('name cannot be null') to be thrown but instead, it was getting an actual value that showed that the name has been updated to null.

I tried to get into the documentation to see what was happening and tried to look up some other articles and information about it. I got dozens of articles but did not get an actual solution. I checked the database itself to check what was going on. When I checked the schema of the Employees table that I was modelling, I discovered below that the not null constraint had not been applied to all of the columns.

By the way, the createdAt and updatedAt column gets created automatically on every table by sequelize.

I knew from here that the problem was how I was implementing the allowNull in the model definition, but I was not sure I knew what to do especially given that it actually works when I am creating a new employee, but fails to check if the value is null when updating an employee. I tried a couple of stuff, including using migration scripts to update the not null columns, which worked.

When the migration script worked, I decide to remove the object I was passing to the allowNull key in my model definition. I wanted to pass only its respective boolean (false) without the msg, since the message that the lower level constraint applied by the migration script was different from the one that I specified.
So my model definition with this little tweak become the code below:

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

const Employee = sequelize.define('Employee', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    allowNull: false,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false, // CHANGE FROM OBJECT TO BOOLEAN.
    validate: {
      is: {
        args: /^[a-zA-Z'-]+\s?[a-zA-Z'-]*$/i,
        msg: 'Regex pattern does not match, expecting `firstName secondName`'
    }
  },
}

(async () => {
  await sequelize.sync();
})();

module.exports = { Employee, DataTypes };

After this change, I just randomly thought to drop the entire database and try this new script without using the migration script, and found out that the NOT NULL constraint was successfully applied to the database appropriately.

Consequently, the test that updated the value of the name of a given employee to null passed (i.e it threw an error as expected), but this time with an error message from sequelize and not customized by me.

test('test for name field when it is updated with a null value', async () => {
    try {
      await Employee.create({
        name: emp1.name,
        address: emp1.address,
        email: emp1.email,
        phone: emp1.phone,
        position: emp1.position,
        department: emp1.department,
      });
      let testEmp = await Employee.findOne( {where: { name: emp1.name } } ); // find the row with emp1.name.
      expect(testEmp.name).not.toBe(emp2.name);  //Test that emp1.name is not equal to emp2.name before update.

      // Update emp1.name to null.
      await Employee.update( {name: null},

        {
          where: { name: testEmp.name },
        }
      )
      // Use the id obtained from previous DB query to access the same row.
      testEmp = await Employee.findOne( {where: { id: testEmp.id } } );
      expect(testEmp).toBeNull();
    } catch(error) {
       // CHANGE THE CONTENT OF ERROR EXPECTED.
      expect(error.message).toBe("notNull Violation: Employee.name cannot be null");
    }

CONCLUSION: While, I think I might have glossed over some details in the use of the allowNull constraint, I still find this implementation a little odd.
From the documentation, it only used allowNull with false above as I eventually did and worked with. I blame myself for generalizing the idea of passing objects to the allowNull from what I read here validation notes in the documentation.
The documentation itself never specified allowNull as part of the built-in validation schema that accepts an object. However, I assumed it will.
It worked as a validation constraint, as we typically saw in the create method, but did not work when using the update method; I think this part is what got me stuck the most.

Either ways, I hope someone else does not get stuck for days like me.