sequelize

Sequelize Database Entity Relation for Postgres SQL- One To One Relation

For one-to-one relation in database schema, it is easy to define the relation in Model Class using Sequelize BelongsTo method. Let us consider a schema given below which has Users and UserProfile models. One user can have one UserProfile, Which is 1-to-1 relation.

Sequelize one-to-one relation
Sequelize one-to-one relation

One-to-One Relation using belongsTo in Sequelize

Users Model

<code>
const UserModel = db.define(
  'users',
  {
    userId: {
      field: 'userId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: 'firstName',
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: 'lastName',
      type: sqlOperator.STRING(100),
    },
    email: {
      field: 'email',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true,
      validate: {
        isEmail: true
      }
    },
    userName: {
      field: 'userName',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true
    },
    phone: {
      field: 'phone',
      type: sqlOperator.STRING(13),
      allowNull: true,
      // unique: true
    },
    photo: {
      field: 'photo',
      type: sqlOperator.STRING(1024),
      defaultValue: null,
      validate: {
        isUrl: true
      }
    },
  },
  {
    tableName: 'users',
    timestamps: true,
    freezeTableName: true,
  }
);

UserModel.associate = async () => {
  console.log('--- Establishing relations between user and user profile ---');

  const UserProfileModel = require('./UserProfileModel');

  UserModel.belongsTo(UserProfileModel, {
    foreignKey: 'profile',
    targetKey: 'userProfileId'
  }); /** foriengKey and targetKey are both from table */
}

module.exports = UserModel;
</code>

UserProfile Model

<code>
const UserProfileModel = db.define(
  'user_profiles',
  {
    userProfileId: {
      field: 'userProfileId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    address: {
      field: 'address',
      type: sqlOperator.STRING(100),
      require: true
    }
  },
  {
    tableName: 'user_profiles',
    timestamps: true,
  }
);

UserProfileModel.associate = async () => {
  console.log('--- Establishing relations between user profile ---');
}

module.exports = UserProfileModel;
</code>

While defining one to one relation, relation can be defined in any of the model, either User Model or UserProfile model. It will work fine with any way.

Let us understand foreign key and target key in belongsTo as given above.

foreignKey: ‘profile’, since we are defining relation in Users Model, the user profile id will be added to users table and that is why it become one to one relation. Hence foreignKey here means the column name to be added in Source Table that is Users Model.


targetKey: ‘userProfileId’, here the Source is Users model and since we are defining relation in Users Model, then the target model is UserProfiles model, hence the target key here is the primary key of UserProfile table.

As per sequelize documentation, it says “BelongsTo inserts the association key in the source model”, source model here is Users Model.

Postgres Sequelize Invalid value Error: while executing Find Query

I have a Model Defined as

<code>const sequelizeHelper = require("../helpers/sequelizeHelper");
const db = sequelizeHelper.db;
const sqlOperator = sequelizeHelper.sqlOperator;

const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    },
    isBroadcast : {
      field : "isBroadcast",
      type: sqlOperator.BOOLEAN,
      defaultValue: false
    },
    isDeleted : {
      field : "isDeleted",
      type: sqlOperator.BOOLEAN,
      defaultValue: false
    },
    isForAllStaff : {
      field : "isForAllStaff",
      type: sqlOperator.BOOLEAN,
      defaultValue: false
    } 
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.associate=()=>{
  const UserModel = require("./UserModel");
  
  NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    // as : "noticeBy", //This is needed as there are multiple user mapping so sequelize will not get to know which one to use
    targetKey: "userId"
  });

  NoticesModel.belongsTo(UserModel, {
    foreignKey: "noticeTo",
    // as: "noticeTo", //This is needed as there are multiple user mapping so sequelize will not get to know which one to use
    targetKey: "userId"
  });

  const MasterNoticesStateModel = require("./MasterNoticesStateModel");

  NoticesModel.belongsTo(MasterNoticesStateModel,{
    foreignKey : {
      name : "noticeState",
      allowNull: false
    },
    targetKey : "stateId"
  });

  const MasterClassModel = require("./MasterClassModel");

  NoticesModel.belongsTo(MasterClassModel,{
    foreignKey: "classs",
    targetKey: "classId"
  });

  //Associating Notice to school
  const SchoolModel = require("./SchoolModel");
  NoticesModel.belongsTo(SchoolModel,{
    foreignKey: "schoolId",
    targetKey: "schoolId"
  });

  const MasterUserRolesModel = require("./MasterUserRolesModel");
  NoticesModel.belongsTo(MasterUserRolesModel,{
    foreignKey: "roleId",
    targetKey: "roleId"
  });
  NoticesModel.sync({ alter: true });
};

module.exports = NoticesModel;</code>

And a Sequelize $or conditioned Query I have is as follows

<code>const query&#91;"where"] = {};
const orConditions = &#91;];
if (noticeParams.noticeTo) {
      orConditions.push({
        noticeTo: {
          $eq: noticeParams.noticeTo
        }
      });
    }
    if (noticeParams.classId) {
      orConditions.push({
        classs: {
          $eq: noticeParams.classId
        }
      });
    }
    if (noticeParams.roleId) {
      orConditions.push({
        roleId: {
          $eq: noticeParams.roleId
        }
      });
      orConditions.push({
        isForAllStaff: {
          $eq: true
        }
      });
    } else if (noticeParams.isForAllStaff) {
      orConditions.push({
        isForAllStaff: {
          $eq: noticeParams.isForAllStaff
        }
      });
    }
    query&#91;"where"] = {
      $or: orConditions
    }</code>

Postgres Sequelize Exception while executing Find Query

Error: Invalid value { noticeTo: { ‘$eq’: ’02faac43-a777-4bb8-902b-da0eebbcd70e’ } }
at Object.escape (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/sql-string.js:65:11)
at PostgresQueryGenerator.escape (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:986:22)
at _joinKeyValue.value.map.item (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2486:69)
at Array.map ()
at PostgresQueryGenerator._whereParseSingleValueObject (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2486:52)
at PostgresQueryGenerator.whereItemQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2268:19)
at Utils.getComplexKeys.forEach.prop (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2173:25)
at Array.forEach ()
at PostgresQueryGenerator.whereItemsQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2171:35)
at PostgresQueryGenerator.getWhereConditions (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2583:19)
at PostgresQueryGenerator.selectQuery (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1315:28)
at QueryInterface.select (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/query-interface.js:1122:27)
at Promise.try.then.then.then (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/sequelize/lib/model.js:1759:34)
at tryCatcher (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:547:31)
at Promise._settlePromise (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:604:18)
at Promise._settlePromise0 (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:649:10)
at Promise._settlePromises (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/promise.js:729:18)
at _drainQueueStep (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:93:12)
at _drainQueue (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:86:9)
at Async._drainQueues (/Users/rama/Data/Development/Social/vk_proj/source/server/node_modules/bluebird/js/release/async.js:102:5)
at Immediate.Async.drainQueues as _onImmediate
at runCallback (timers.js:705:18)
at tryOnImmediate (timers.js:676:5)
at processImmediate (timers.js:658:5)
at process.topLevelDomainCallback (domain.js:126:23)

Solution for Postgres Sequelize Invalid value Error:

Very strange is the query written above is as per the standards of sequelize library, however its throwing a strange exception. I fixed by changing the query as follows

NOTE: instead of using $or and $eq, I am using sequelize op ( operator )

Update: As per latest sequelize library v4.x, For better security Sequelize recommends dropping alias operators $ (e.g $and, $or …) – Reference

<code>const Op = require('sequelize').Op

const query&#91;"where"] = {};
const orConditions = &#91;];
if (noticeParams.noticeTo) {
      orConditions.push({
        noticeTo: {
          &#91;Op.eq]: noticeParams.noticeTo
        }
      });
    }
    if (noticeParams.classId) {
      orConditions.push({
        classs: {
          &#91;Op.eq]: noticeParams.classId
        }
      });
    }
    if (noticeParams.roleId) {
      orConditions.push({
        roleId: {
          &#91;Op.eq]: noticeParams.roleId
        }
      });
      orConditions.push({
        isForAllStaff: {
          &#91;Op.eq]: true
        }
      });
    } else if (noticeParams.isForAllStaff) {
      orConditions.push({
        isForAllStaff: {
          &#91;Op.eq]: noticeParams.isForAllStaff
        }
      });
    }
    query&#91;'where'] = {
      &#91;Op.or]: orConditions
    }</code>

PostgreSQL: ERROR: SequelizeDatabaseError operator does not exist character varying = bigint1

Getting this Error for find query using sequelize library in nodejs:

Error: SequelizeDatabaseError: operator does not exist: character varying = bigint1

My Sequelize Database Model looks as follows

<code>const sequelizeHelper = require("../helpers/sequelizeHelper");
const db = sequelizeHelper.db;
const sqlOperator = sequelizeHelper.sqlOperator;
const UserRolesModel = require("./UserRolesModel");

const UserModel = db.define(
  "users",
  {
    userId: {
      field: "userId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: "firstName",
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: "lastName",
      type: sqlOperator.STRING(100),
    },
    email: {
      field: "email",
      type: sqlOperator.STRING(1025),
      validate: {
        isEmail: true
      }
    },
    userName: {
      field: "userName",
      type: sqlOperator.STRING(1025),
    },
    phone: {
      field: "phone",
      type: sqlOperator.STRING(13),
    },
    photo: {
      field: "photo",
      type: sqlOperator.STRING(1024),
      defaultValue: null,
      validate: {
        isUrl: true
      }
    },
    studentDetails: {
      field: "studentDetails",
      type: sqlOperator.JSON,
      defaultValue: null,
    },
    profession: {
      field: "profession",
      type: sqlOperator.STRING(13),
      defaultValue: ""
    },
  },
  {
    tableName: "users",
    timestamps: true,
    indexes: &#91;
      {
        unique: true,
        fields: &#91;"email"],
        name: "user_email_unique_index"
      },
      {
        unique: true,
        fields: &#91;"userName"],
        name: "user_user_name_unique_index"
      },
    ]
  }
);

UserModel.associate = () => {
  UserModel.hasMany(UserRolesModel, {
    as: "roles"
  });
  UserModel.sync({ alter: true });  
}
module.exports = UserModel;
</code>

Solution:

This kind of issue usually occur when we try to compare mismatched data types. You cannot compare an integer with a varchar. PostgreSQL is strict and does not do any magic typecasting for you. I’m guessing SQLServer does typecasting automagically (which is a bad thing).

<code>const phone = 9998886660;
UserModel.count({
      where: {
        phone: phone
      }
    });</code>

The above code throws operator does not exist: integer = character varying exception, because we are trying to compare integer with string. To fix this issue change as follows

<code>const phone = "9998886660"; //this will fix the issue
UserModel.count({
      where: {
        phone: phone
      }
    });</code>

For other sequelize errors: Read More Here

Sequelize: Using “Include” when we are joining same table multiple times.

“Include” with single join

Let us first see how we can use include when we are joining 2 simple tables.

Note: If you are already aware of single join, then jump to section “Include” with multiple joins

Consider the following two tables,

User Table

<code>const UserModel = db.define(
  "users",
  {
    userId: {
      field: "userId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: "firstName",
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: "lastName",
      type: sqlOperator.STRING(100),
    },
    email: {
      field: "email",
      type: sqlOperator.STRING(255),
      validate: {
        isEmail: true
      }
    },
    {
    tableName: "users",
    timestamps: true
  }
);</code>

Notice Table

<code>const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId"
  });</code>

When we want to fetch the user who has posted the notice, we will use the following code,

<code>const query &#91;"include"] = &#91;{model: UserModel}]
// query can include all some conditions required, this is out of scope of this tutorial, for the same you can check out this 
const notices = await NoticeModel.findAll(query);</code>

Reference

After executing the above findAll, we will get all the notices and the respective user who has created that.

“Include” with multiple joins

In this section we will learn how to use when there are multiple join of the same table.

We will make some changes in the Notice table as follows,

<code>const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId"
  });

//Added one more new relation
NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeTo",
      allowNull: false
    },
    targetKey: "userId"
  });</code>

Now we will try to run the same above findAll, we will observe that neither noticeTo nor noticeBy will be filled.

What is happening here is Sequelize finds multiple userModels so it will not be sure which one to fetch.

Solution

To solve the above issue we will have to make the following changes

<code>const NoticesModel = db.define(
  "notices",
  {
    noticeId: {
      field: "noticeId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    title : {
      field: "title",
      type: sqlOperator.STRING(250),
    },
    notice : {
      field: "notice",
      type: sqlOperator.TEXT,
    }
  },
  {
    tableName: "notices",
    timestamps: true
  }
);

NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeBy",
      allowNull: false
    },
    targetKey: "userId",
    as : "noticedBy"
  });

//Added one more new relation
NoticesModel.belongsTo(UserModel, {
    foreignKey : {
      name : "noticeTo",
      allowNull: false
    },
    as: "noticedTo",
    targetKey: "userId"
  });</code>

And in the find we will do this,

const query[&quot;include&quot;] = [{model: UserModel, as: &quot;noticedBy&quot;}, {model: UserModel, as: &quot;noticedTo&quot;}]

const notices = await NoticeModel.findAll(query);

Once we add the above changes, both noticedBy and noticeTo will be filled. (Observe we have not kept the alias name same, we have changed them slightly).

Possible errors

uncaughtException: Naming collision between attribute ‘noticeTo’ and association ‘noticeTo’ on model notices. To remedy this, change either foreignKey or as in your association definition\nError: Naming collision between attribute ‘noticeTo’ and association ‘noticeTo’ on model notices

What does the above error mean?

It says that we have given same names to the attribute i.e foreign key and alias. We get this with the following code,

<code>  NoticesModel.belongsTo(UserModel, {
    foreignKey: "noticeTo",
    as: "noticeTo",
    targetKey: "userId"
  });</code>

As we can see the highlighted rows, both have the same names. Solution to this is very simple, just rename alias to something else.

<code>  NoticesModel.belongsTo(UserModel, {
    foreignKey: "noticeTo",
    as: "noticedTo",
    targetKey: "userId"
  });</code>

TADA!!! everything will work fine now.

For More Sequelize Errors: Read More Here