How to work around Sequelize not supporting composite foreign keys

I am developing an advanced query with Sequelize (hitting a PostgreSQL database). It's not difficult in pure SQL, but I can't get it work in Sequelize, in part because they don't support composite foreign keys.

The best way to describe my problem is with code, so I made a repository with a minimal reproduction of my problem. You should be able to clone it and follow the instructions in the README to reproduce the problem locally. Basically, if you can get the query to run without errors and correctly return an empty array of rows, you've solved my problem :)

For convenience I will copy here the query and output themselves, but you can check the repo to look at the migrations, models and Sequelize configuration.

The Query

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

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

Output (truncated)

```
TypeError: Cannot read property 'toUpperCase' of undefined
at str.trim.replace (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:60)
at RegExp.[Symbol.replace] (native)
at RegExp.[Symbol.replace] (native)
at String.replace (native)
at camelize (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:108:21)
at Object.camelizeIf (/home/shawn/git/waltzapp/sequelize-troubleshooting/node_modules/sequelize/lib/utils.js:32:14)
at new H

I have never seen Sequelize, but your error reads:

TypeError: Cannot read property 'toUpperCase' of undefined

Where it says of undefined I would suggest your code is trying to convert a string to uppercase, but in fact, your string is null and the conversion (toUpperCase) fails on null.

That said, I don't see toUpperCase in the query you supplied, but you have the line model:invitations - does this mean that your query is acting on some data structure (table?) called 'invitations'?

If so, I would check there for the toUpperCase function. For each instance of the function, see whether it is being called on something that might have a null value. If so, then somehow ensure you don't get a null value - for example, add filter criteria to exclude null values, or use a casting function like isNull (if it exists for you) or coalesce to replace nulls with an empty string, like this:

coalesce(columnName, '')

Failing the above, post a little more info on what your data query really looks like, and why you think the composite foreign keys may relate to the error.

StackExchange Database Administrators Q#190062, answer score: 2