Sequelize.js: join tables without associations

To anyone who end up googling here: there is a workaround, not sure it was available back then, but as of 5.21.7 it works. you can pass association object to include parameter:

const res = await bank.findAll({
        include: [
            {
                model: account,
                association: new HasMany(bank, account, {/*options*/}),
            },
        ],
    })

HasMany is an association constructor, which can be taken from Sequelize.Associations, or sequelize instance. there are also other association constructors there. For TS you will need to cast manually since that property not in types.

UPD: for BelongsToMany you need to call _injectAttributes() on association prior usage, and I didnt check others apart from HasMany.

It seem that while it is possible to define a custom on condition, it is not possible to include relations without defining associations first. This is implied by the documentation wording for findAll method (search for options.include on page):

A list of associations to eagerly load using a left join. Supported is either { include: [ Model1, Model2, ...]} or { include: [{ model: Model1, as: 'Alias' }]} or { include: ['Alias']}. If your association are set up with an as (eg. X.hasMany(Y, { as: 'Z }, you need to specify Z in the as attribute when eager loading Y).

The docs on options.include[].on are even more terse:

Supply your own ON condition for the join.

I ended up solving my problem using Postgres views as a workaround. It is also possible to inject a raw query and bypass sequelize limitations but I would use that only as a development / prototyping hack and come up with something more robust / secure in production; something like views or stored procedures.