sequelize many-to-many select returns join table attributes

Question

I have an existing db schema created by a Rails application that describes a many-to-many relationship between organizations and rate plans. I'm trying to redefine the relationship in node and sequelize and it appears that findOne executes a SELECT statement that contains the join table attributes. I did find another post about this, but the solution didn't seem to work (Sequelize Join Models Include many to many).

module.exports = (sequelize, DataTypes) => {
    const Organization = sequelize.define('organizations', {
        id: {
            allowNull: false,
            primaryKey: true,
            type: DataTypes.UUID
        },
        accountType: {
            field: 'account_type',
            type: DataTypes.UUID,
        },
        createdAt: {
            field: 'created_at',
            type: DataTypes.DATE,
        },
        updatedAt: {
            field: 'updated_at',
            type: DataTypes.DATE,
        }
    }, {});
    Organization.associate = function (models) {
        Organization.belongsToMany(models.rate_plans, {
            through: models.organizations_rate_plans,
            as: 'ratePlans',
            foreignKey: 'organization_id',
            otherKey: 'rate_plan_id'
        });
    };
    return Organization;
};

module.exports = (sequelize, DataTypes) => {
    const RatePlan = sequelize.define('rate_plans', {
        id: {
            allowNull: false,
            primaryKey: true,
            type: DataTypes.UUID
        },
        tag: {
            field: 'tag',
            type: DataTypes.STRING,
        },
        createdAt: {
            field: 'created_at',
            type: DataTypes.DATE,
        },
        updatedAt: {
            field: 'updated_at',
            type: DataTypes.DATE,
        },
    }, {});
    RatePlan.associate = function (models) {
        RatePlan.belongsToMany(models.organizations, {
            through: models.organizations_rate_plans,
            as: 'organizations',
            foreignKey: 'rate_plan_id',
            otherKey: 'organization_id'
        });
    };
    return RatePlan;
};

Since my join table doesn't follow the sequelize naming convention, I guess I'm forced to define it, right? Also, please note, there is no id PK on this table, just the 2 FKs.

module.exports = (sequelize, DataTypes) => {
    const OrganizationRatePlan = sequelize.define('organizations_rate_plans', {
        id: {
            primaryKey: true,
            field: 'id',
            type: DataTypes.INTEGER,
        },
        organizationId: {
            field: 'organization_id',
            type: DataTypes.UUID,
        },
        ratePlanId: {
            field: 'rate_plan_id',
            type: DataTypes.UUID,
        },
    }, {});
    return OrganizationRatePlan;
};

Here's my query:

    const organization = await db.organizations.findOne({
        where: { id: organizationId },
        include: [{
            model: db.rate_plans,
            as: 'ratePlans',
            through: { model: db.organizations_rate_plans, attributes: [] }
        }]
    });

and here's the SQL statement that gets execute:

SELECT "organizations"."id", "organizations"."account_type" AS "accountType", "organizations"."created_at" AS "createdAt", "organizations"."updated_at" AS "updatedAt", "ratePlans"."id" AS "ratePlans.id", "ratePlans"."tag" AS "ratePlans.tag", "ratePlans->organizations_rate_plans"."organization_id" AS "ratePlans.organizations_rate_plans.organizationId", "ratePlans->organizations_rate_plans"."rate_plan_id" AS "ratePlans.organizations_rate_plans.ratePlanId", "ratePlans->organizations_rate_plans"."createdAt" AS "ratePlans.organizations_rate_plans.createdAt", "ratePlans->organizations_rate_plans"."updatedAt" AS "ratePlans.organizations_rate_plans.updatedAt", "ratePlans->organizations_rate_plans"."organization_id" AS "ratePlans.organizations_rate_plans.organization_id", "ratePlans->organizations_rate_plans"."rate_plan_id" AS "ratePlans.organizations_rate_plans.rate_plan_id" FROM "organizations" AS "organizations" LEFT OUTER JOIN ( "organizations_rate_plans" AS "ratePlans->organizations_rate_plans" INNER JOIN "rate_plans" AS "ratePlans" ON "ratePlans"."id" = "ratePlans->organizations_rate_plans"."rate_plan_id") ON "organizations"."id" = "ratePlans->organizations_rate_plans"."organization_id" WHERE "organizations"."id" = 'd7f8b0c7-f530-4f6b-9862-76ac440af868'

and the error that I'm getting:

{ "stack": "Error: Failed to load the organization d7f8b0c7-f530-4f6b-9862-76ac440af868. SequelizeDatabaseError: column ratePlans->organizations_rate_plans.createdAt does not exist\n at OrganizationRepository.loadById (/usr/src/app/src/persistence/db/organization-repository.js:13:19)", "message": "Failed to load the organization d7f8b0c7-f530-4f6b-9862-76ac440af868. SequelizeDatabaseError: column ratePlans->organizations_rate_plans.createdAt does not exist", "name": "Error" }

The join model column names shouldn't be selected (especially since I added the attributes=[] to findOne). Moreover, they are kinda weird. It seems they are either the rate plan's columns or the organization's columns. I'm still not sure if I defined to many-to-many relationship correctly.

Answer 1

It seems this is a known issue in Sequelize (https://github.com/sequelize/sequelize/issues/5481 and https://github.com/sequelize/sequelize/issues/5590.). It's not what I expected to see and have to do coming from Rails, but instead of accessing organization.rate_plans, I went the other route: rate_plans.where(organization_id=...). The through: { attributes: [] } is superfluous with includeIgnoreAttributes: false.

async function findOrganizationById(organizationId) {
    return await db.organizations.findOne({
        where: { id: organizationId },
        includeIgnoreAttributes: false,
        include: [{
            model: db.rate_plans,
            as: 'ratePlans',
            through: { model: db.organizations_rate_plans, attributes: [] }
        }]
    });
}