Why does Sequelize think my models are not associated?

Question

I am trying to make a Sequelize query but it keeps telling me that my models are not associated. Yet I do declare an association... I guess I simply don't understand associations as well as I thought...

I made a repo which you can clone to see the problem for yourself: https://github.com/WaltzApp/sequelize-troubleshooting

I think the reason I am having this problem is that Sequelize doesn't support composite foreign keys, so I have to use a custom on clause in my join, but it's not working as expected since I can't specify the same join method in the associations themselves. That would explain why I sometimes get perfectly functional SQL printed out by Sequelize, but the query still fails (presumably because although the database query succeeds, converting the results to Sequelize entiteis fails).

A previous version of this reproduction effort is copied below as well:

Relevant Models

usersRoles:

module.exports = (sequelize, DataTypes) => {
  const usersRoles = sequelize.define('usersRoles',
    {
      key: {
        type: DataTypes.UUID,
        primary: true,
      },
      userUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      roleUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      oemUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      propertyManagerUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      tenantUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      [MORE FIELDS IRRELEVANT TO MY PROBLEM]
    },
    {
      tableName: 'usersRoles',
      indexes: [
        {
          name: 'userRoleOem',
          fields: ['userUid', 'roleUid', 'oemUid'],
          unique: true,
          where: {
            oemUid: { $ne: null },
            propertyManagerUid: null,
            tenantUid: null,
          },
        },
        {
          name: 'userRolePropertyManager',
          fields: ['userUid', 'roleUid', 'propertyManagerUid'],
          unique: true,
          where: {
            oemUid: null,
            propertyManagerUid: { $ne: null },
            tenantUid: null,
          },
        },
        {
          name: 'userRoleTenant',
          fields: ['userUid', 'roleUid', 'tenantUid'],
          unique: true,
          where: {
            oemUid: null,
            propertyManagerUid: null,
            tenantUid: { $ne: null },
          },
        },
        {
          name: 'compositePK',
          fields: ['userUid', 'roleUid', 'oemUid', 'propertyManagerUid', 'tenantUid'],
          unique: true,
        },
      ],
      freezeTableName: true,
      timestamps: false,
    });

  usersRoles.removeAttribute('id');

  usersRoles.associate = (models) => {
    models.usersRoles.belongsTo(models.oems, { foreignKey: 'oemUid' });
    models.usersRoles.belongsTo(models.propertyManagers, { foreignKey: 'propertyManagerUid' });
    models.usersRoles.belongsTo(models.tenants, { foreignKey: 'tenantUid' });
    models.usersRoles.belongsTo(models.users, { foreignKey: 'userUid' });
    models.usersRoles.belongsTo(models.roles, { foreignKey: 'roleUid' });
    models.usersRoles.belongsTo(models.invitations, { as: 'invitation', foreignKey: 'compositePK' });
  };

  return usersRoles;
};

invitations:

module.exports = (sequelize, DataTypes) => {
  const invitations = sequelize.define('invitations',
    {
      uid: {
        type: DataTypes.UUID,
        primaryKey: true,
      },
      guestUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      mappingGroupUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      invitedByUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      adminUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      propertyManagerUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      tenantUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      [MORE FIELDS IRRELEVANT TO MY PROBLEM]
    }, {
      tableName: 'invitations',
      freezeTableName: true,
      timestamps: false,
    });

  invitations.associate = (models) => {
    models.invitations.belongsTo(models.propertyManagers, { foreignKey: 'propertyManagerUid' });
    models.invitations.belongsTo(models.tenants, { foreignKey: 'tenantUid' });
    models.invitations.belongsTo(models.mappingGroups, { foreignKey: 'mappingGroupUid' });
    models.invitations.belongsTo(models.users, { as: 'guest', foreignKey: 'guestUid' });
    models.invitations.belongsTo(models.users, { as: 'invitedBy', foreignKey: 'invitedByUid' });
    models.invitations.belongsTo(models.users, { as: 'admin', foreignKey: 'adminUid' });
    models.invitations.hasMany(models.usersRoles, { as: 'invitation', foreignKey: 'compositePK' });
  };

  return invitations;
};

Notice above that I associate the invitations and usersRoles models with the line models.invitations.hasMany(models.usersRoles, { foreignKey: 'compositePK' }).

The Query

const path = require('path');
const glob = require('glob');
const Promise = require('bluebird');

const Sequelize = require('sequelize');
const configs = require('./test/_helpers/getConfigs');

const models = {};

const performQuery = (db) => {
  const { usersRoles, invitations } = db; // eslint-disable-line max-len

  const sql = {
    include: {
      model: invitations,
      as: 'invitation',
      on: {
        user: Sequelize.where(
          Sequelize.col('usersRoles.userUid'),
          '=',
          Sequelize.col('invitation.guestUid')),
        propertyManager: Sequelize.where(
          Sequelize.col('usersRoles.propertyManagerUid'),
          '=',
          Sequelize.col('invitation.propertyManagerUid')),
        tenant: Sequelize.where(
          Sequelize.col('usersRoles.tenantUid'),
          '=',
          Sequelize.col('invitation.tenantUid')),
      },
    },
  };
  return usersRoles.findAll(sql);
};

const sequelize = new Sequelize(
  configs.get('DB_NAME'),
  configs.get('DB_USER'),
  configs.get('DB_PSSWD'),
  {
    dialect: configs.get('DB_TYPE'),
    host: configs.get('DB_HOST'),
    port: configs.get('DB_PORT'),
  });

const modelsPath = path.join(__dirname, 'sequelize/models');
const globOpts = {
  cwd: modelsPath,
  ignore: '**/index.js',
  realPath: false,
  nosort: true,
  nodir: true,
};

glob('**/*.js', globOpts, (err, files) => {
  Promise.map(files, (file) => {
    const model = sequelize.import(path.join(modelsPath, file));
    models[model.name] = model;
  })
    .then(() => {
      Object.keys(models).forEach((modelName) => {
        if (models[modelName].associate) {
          models[modelName].associate(models);
        }
      });
      return performQuery(models);
    })
    .then((res) => {
      console.log('SUCCESS', res);
    }, (reason) => {
      console.error('FAILED', reason);
    });
});

The Output

FAILED { SequelizeEagerLoadingError: invitations is not associated to usersRoles!
    at Function._getIncludedAssociation (.../node_modules/sequelize/lib/model.js:573:13)
    at Function._validateIncludedElement (.../node_modules/sequelize/lib/model.js:489:53)
    at options.include.options.include.map.include (.../node_modules/sequelize/lib/model.js:385:37)
    at Array.map (native)
    at Function._validateIncludedElements (.../node_modules/sequelize/lib/model.js:381:39)
    at Promise.try.then.then (.../node_modules/sequelize/lib/model.js:1525:14)
    at tryCatcher (.../node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (.../node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (.../node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (.../node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (.../node_modules/bluebird/js/release/promise.js:693:18)
    at Async._drainQueue (.../node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (.../node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (.../node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:637:20)
    at tryOnImmediate (timers.js:610:5)
    at processImmediate [as _immediateCallback] (timers.js:582:5) name: 'SequelizeEagerLoadingError' }

Further attempt I tried adding the following line to the invitations model:

models.usersRoles.belongsTo(models.invitations);

In this case, Sequelize prints some SQL which, when executed, works exactly as intended, but apparently it doesn't execute it properly because the query still doesn't work, instead saying SequelizeDatabaseError: relation "usersRoles" does not exist.

Executing (default): SELECT "usersRoles"."key", "usersRoles"."userUid", "usersRoles"."roleUid", "usersRoles"."oemUid", "usersRoles"."propertyManagerUid", "usersRoles"."doorSchedule", "usersRoles"."cardId", "usersRoles"."notifyBy", "usersRoles"."contactEmail", "usersRoles"."tenantUid", "usersRoles"."createdAt", "usersRoles"."compositePK", "usersRoles"."invitationUid", "invitation"."uid" AS "invitation.uid", "invitation"."status" AS "invitation.status", "invitation"."guestUid" AS "invitation.guestUid", "invitation"."firstName" AS "invitation.firstName", "invitation"."lastName" AS "invitation.lastName", "invitation"."email" AS "invitation.email", "invitation"."mobile" AS "invitation.mobile", "invitation"."mappingGroupUid" AS "invitation.mappingGroupUid", "invitation"."doorSchedule" AS "invitation.doorSchedule", "invitation"."invitedByUid" AS "invitation.invitedByUid", "invitation"."adminUid" AS "invitation.adminUid", "invitation"."acceptedAt" AS "invitation.acceptedAt", "invitation"."rejectedAt" AS "invitation.rejectedAt", "invitation"."propertyManagerUid" AS "invitation.propertyManagerUid", "invitation"."tenantUid" AS "invitation.tenantUid", "invitation"."contactEmail" AS "invitation.contactEmail", "invitation"."notifyBy" AS "invitation.notifyBy", "invitation"."createdAt" AS "invitation.createdAt", "invitation"."updatedAt" AS "invitation.updatedAt" FROM "usersRoles" AS "usersRoles" LEFT OUTER JOIN "invitations" AS "invitation" ON "usersRoles"."userUid"="invitation"."guestUid" AND "usersRoles"."propertyManagerUid"="invitation"."propertyManagerUid" AND "usersRoles"."tenantUid"="invitation"."tenantUid"; Query failure { SequelizeDatabaseError: relation "usersRoles" does not exist

Answer 1

You are never calling the Model.associate() method so the models aren't being associated with each other. The trick is that the associations may require models that have not yet been defined, so the associates have to come last. After you models are loaded loop through them and call model.associate(models) to create the associations.

The userRole definition also does not include an association to invitations. In Sequelize a relationship must be defined from the side you want to query from, or both if you plan to query the relationship from both objects. Since the invitations has many userRoles, I assume that userRoles belongs to an invitations. You reference the table as invitation in your query, so you probably want to alias the association with the as attribute.

userRoles

models.usersRoles.belongsTo(models.invitations, {
  as: 'invitation', 
  foreignKey: 'compositePK',
});