NodeJS - Sequelize how to declare association for eager loading only once to be used for queries lat-CodePudding

The problem:

Whenever I fetch a user, I always have to declare/include the association on the query to get its role:

const user = await DB.PORTALDB.models.AccessUser.findOne({
   where: { email },
   include: [ // EVERY QUERY, I HAVE TO INCLUDE THIS
      {
       model: DB.PORTALDB.models.AccessUserRoleLup,
       as: 'role'
      }
   ]
});

Now there are instance where I forget to include this association so I get a undefined role.

My question is, is there a way where I only set this association once so that I don't have to include this later on my queries?

This the model for my AccessUser table

const AccessUser = <AccessUserStatic>sequelize.define<AccessUserInstance>(
    'AccessUser',
    {
      user_id: {
        autoIncrement: true,
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        primaryKey: true
      },
      email: {
        type: DataTypes.STRING(255),
        allowNull: false
      },
      firstname: {
        type: DataTypes.STRING(255),
        allowNull: false
      },
      lastname: {
        type: DataTypes.STRING(255),
        allowNull: false
      },
      password: {
        type: DataTypes.STRING(255),
        allowNull: false
      },
      disable: {
        type: DataTypes.TINYINT,
        allowNull: false,
        defaultValue: 0
      },
      role_id: {
        type: DataTypes.SMALLINT,
        allowNull: false,
        defaultValue: 0
      },
      created_modified: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    }, {
    tableName: 'access_user',
    timestamps: false,
    indexes: [
      {
        name: "PRIMARY",
        unique: true,
        using: "BTREE",
        fields: [
          { name: "user_id" },
        ]
      },
    ]
  });

AccessUserRoleLup table

const AccessUserRoleLup = <AccessUserRoleLupStatic>sequelize.define<AccessUserRoleLupInstance>(
    'AccessUserRoleLup',
    {
      role_id: {
        autoIncrement: true,
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        primaryKey: true
      },
      role_name: {
        type: DataTypes.STRING(50),
        allowNull: false
      },
      role_code: {
        type: DataTypes.CHAR(50),
        allowNull: false,
        defaultValue: ""
      }
    }, {
    tableName: 'access_user_role_lup',
    timestamps: false,
    indexes: [
      {
        name: "PRIMARY",
        unique: true,
        using: "BTREE",
        fields: [
          { name: "role_id" },
        ]
      },
    ]
  });

Association:

db.models.AccessUser.hasOne(db.models.AccessUserRoleLup, {
  foreignKey: 'role_id',
  as: 'role'
});

CodePudding user response：

Use defaultScope for AccessUser. defaultScope is defined in a model definition and it is always applied (unless you removed inline).

const AccessUser = <AccessUserStatic>sequelize.define<AccessUserInstance>(
'AccessUser', 
{
  user_id: {
    autoIncrement: true,
    type: DataTypes.INTEGER.UNSIGNED,
    allowNull: false,
    primaryKey: true
  },
  ...
}, {
  tableName: 'access_user',
  timestamps: false,
  defaultScope: {     // Add this 
    include: [{
      model: AccessUserRoleLup,
      as: 'role'
    }]
  },
  ...
});

With this model definition, all queries will include AccessUserRoleLup.

If you would like to remove for a certain query, use .unscoped().

// These will automatically add eager loading for role
DB.PORTALDB.models.AccessUser.findAll()
DB.PORTALDB.models.AccessUser.findOne() 

// These won't fetch role
DB.PORTALDB.models.AccessUser.unscoped().findAll()
DB.PORTALDB.models.AccessUser.unscoped().findOne()

More detail about scope: https://sequelize.org/master/manual/scopes.html

CodePudding user response：

My initial workaround was to create a utility function for querying the user like so:

export const getAccessUser = (where: WhereOptions, include?: IncludeOptions) => {
    return new Promise(async (resolve, reject) => {
        try {
            const user = await DB.PORTALDB.models.AccessUser.findOne({
                where: where,
                include: [
                    {
                        model: DB.PORTALDB.models.AccessUserRoleLup,
                        as: 'role'
                    },
                    ...[include]
                ]
            });

            resolve(user);
        } catch (err) {
            reject(err);
        }
    });
}

I wonder if my question above can be done in much simpler way.