Wrong sql request generated using include, with a basic association example (only with Jest !)

[clemzangrilli]

I have a problem with sequelize V6 and a very basic example of one-to-many association.

My associations :

    this.coupleTable.hasMany(this.userTable);
    this.userTable.belongsTo(this.coupleTable);

What is working :

1. The foreign key "actionId" is successfully created in the userTable, and it references the id of coupleTable.
   

2. I can retrieve one user and his couple with two requests, using getCouple().

    const user = await db.userTable.findByPk(userId);
    const couple = await user?.getCouple();

What is not working

--> I cannot retrieve one user and his couple with an eager fetch, i.e one request.

const userWithCouple = await db.userTable.findByPk(userId, { include: db.coupleTable });

The sql request is generated with the wrong JOIN clause, strictly the opposite of what I expect.

Instead of having :

LEFT OUTER JOIN user.coupleId = couple.id

We have :

LEFT OUTER JOIN user.id = couple.coupleId

However, my example seems to fit the doc perfectly : doc-eagerLoading-V6

What am I doing wrong here ?

Thank you for your help.

[clemzangrilli]

A long time to find out that the problem was only occurring in the jest tests.

And the responsible is here : jest.resetModules();

-> If you encounter a similar issue with a weird behaviour of sequelize with jest, just do not reset the modules, or find an alternative method to reset only what you want.

Why ?

I do not know. It could be a bug, or perhaps not. Maybe someone reading this will have the answer.

In my opinion, it is a bug. Because everything works fine (eager fetch one -> many with hasMany, associations, BelongsToGetAssociation, etc.), but not the eager fetch many -> one (belongsTo).

[WikiRik]

Could you share some code on how you run the test? When are you executing resetModules() for example? Is it at the start of a test or before you start a sequelize instance/connection? That could help us figuring out the issue further

[clemzangrilli]

Yes of course, I was executing resetModules before starting a sequelize connection, and all of this in the beforeAll.

describe('Problematic test, () => {
  const env = process.env;
  let db: Db;

  beforeAll(async () => {
    jest.resetModules();
    process.env = { ...env };
    process.env.NODE_ENV = 'test';
    db = await getDbInstance(true); // Constructs a new Db instance with sequelize, inits the models and the associations, then sync(force: true)
  });

  beforeEach(async () => {
    process.env = env;
    await resetDb();
  });

  afterAll(async () => {
    await db.sequelize.connectionManager.close();
  });

  it('should get user with its associated couple, async () => {
    // Given
    const userId = 'userId'
    const coupleId = 'coupleId'

    // When
    // HasMany association works perfectly fine
    const coupleWithUsers = await db.coupleTable.findByPk(coupleId, {
      include: { model: db.userTable },
    });

    // BelongsTo does not work, builds a wrong request
    const userWithItsCouple = await db.userTable.findByPk(userId, {
      include: { model: db.coupleTable },
    });

    // Then
    expect ...
  });

  async function resetDb(): Promise<void> {
    await db.sequelize.truncate({ cascade: true });
    await db.seed();
  }