Relation tables ignore schemas after updating MikroORM

[Verkleckert]

Describe the bug

After updating the MikroORM package from version 6.4.5 to 6.4.7 it doesn't respect the schema in the relation tables.

When using this code:

    async getOccupantByUserUuid(uuid: string): Promise<Occupant> {
        const user = await this.em.findOne(
            User,
            { uuid: uuid },
            { populate: ['occupant_uuid', 'institution', 'institution.uuid'], schema: 'schoolid' },
        );
        if (!user) throw new _NotFoundException('No user was found');

        const occupant = await this.em.findOne(
            Occupant,
            { uuid: user.occupant_uuid },
            { populate: ['roles.permissions'], schema: user.institution.uuid },
        );

        if (!occupant) throw new _NotFoundException('No occupant was found');
        return occupant;
    }

Specifically this part:

const occupant = await this.em.findOne(
            Occupant,
            { uuid: user.occupant_uuid },
            { populate: ['roles.permissions'], schema: user.institution.uuid },
        );

it ignores the schema for the relations as seen in this error message:

[Nest] 22152  - 02.03.2025, 23:19:54   ERROR [ExceptionsHandler] TableNotFoundException: select "o0"."uuid", "r1"."uuid" as "r1__uuid", "r1"."name" as "r1__name", "r1"."description" as "r1__description", "p3"."uuid" as "p3__uuid", "p3"."name" as "p3__name" from "7f3af19a-3028-426c-a267-cc9c1d77b766"."occupa
nt" as "o0" left join "7f3af19a-3028-426c-a267-cc9c1d77b766"."role_occupants" as "r2" on "o0"."uuid" = "r2"."occupant_uuid" left join "7f3af19a-3028-426c-a267-cc9c1d77b766"."role" as "r1" on "r2"."role_uuid" = "r1"."uuid" left join "role_permissions" as "r4" on "r1"."uuid" = "r4"."role_uuid" left join "schoolid"."permission" as "p3" on "r4"."permission_uuid" = "p3"."uuid" where "o0"."uuid" = '610c254b-c86a-4f61-8a66-b28edbafb3fb' - relation "role_permissions" does not exist

As seen for the role table it uses the specified schema:
"7f3af19a-3028-426c-a267-cc9c1d77b766"."role"

but not for the relation table:
"role_permissions"

As additional info here is the used Entity:

import { Collection, Entity, ManyToMany, Property } from '@mikro-orm/core';
import { _BaseEntityWithDates } from '../base';
import { Role } from './role.entity';

@Entity({ schema: '*' })
export class Occupant extends _BaseEntityWithDates {
    @Property({ nullable: true, default: null, lazy: true })
    studentId: string;

    @ManyToMany(() => Role, role => role.occupants, { nullable: true })
    roles: Collection<Role> = new Collection<Role>(this);
}

Reproduction

Reproduction seen as above. If more concrete Reproduction is needed feel free to ask!

What driver are you using?

@mikro-orm/postgresql

MikroORM version

6.4.7

Node.js version

v23.6.0

Operating system

Windows

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Discord.
• The provided reproduction is a minimal reproducible example of the bug.

[Verkleckert]

As an addition here is the log of the request with the previous version of MikroORM:

select "o0"."uuid", "r1"."uuid" as "r1__uuid", "r1"."name" as "r1__name", "r1"."description" as "r1__description", "p3"."uuid" as "p3__uuid", "p3"."name" as "p3__name" from "7f3af19a-3028-426c-a267-cc9c1d77b766"."occupant" as "o0" left join "7f
3af19a-3028-426c-a267-cc9c1d77b766"."role_occupants" as "r2" on "o0"."uuid" = "r2"."occupant_uuid" left join "7f3af19a-3028-426c-a267-cc9c1d77b766"."role" as "r1" on "r2"."role_uuid" = "r1"."uuid" left join "7f3af19a-3028-426c-a267-cc9c1d77b766"."role_permissions" as "r4" on "r1"."uuid" = "r4"."role_uuid" left join "schoolid"."permission" as "p3" on "r4"."permission_uuid" = "p3"."uuid" where "o0"."uuid" = '610c254b-c86a-4f61-8a66-b28edbafb3fb'

[B4nan]

Reproduction seen as above. If more concrete Reproduction is needed feel free to ask!

Please always provide a complete reproduction.

[Verkleckert]

I couldn't reproduce the error in a seperate project but i could debbug the code and find the code that fails.

The createJoinExpression function, in the QueryBuilderHelper file it sets the schema variable to public instead of the correct shema. Before executing the code (state of the piture) theschema variable hast the correct value (the uuid) but it replaces it with public. the relation table here only exists in the schema with the uuid.

Ass seen in this version with the old version of MikroORM the join.schema was undefied which resulted in the shemavariable to stay the value it has:

I coulnd't yet figure out why join.schema is set tu public but I am still searching.

I hope this helps

[B4nan]

Please try to find a repro, I know this line changed recently, so it's not helping much on its own, I need to see why it's not working.

[Verkleckert]

Reproduction

After a lot of testing i managed to reproduce the error.

So for this example i have a table "Role" with the wildcard shema and a table "permission" just in the default schema "test". My second schema for this test is "schoolid". Because of this it generates a "role" and a "role_permissions" table in both schemas and a "permission" table just in the "test" schema as specified.

Now if i want to get data from the "role" table and populate it with "permissions" it uses the default schema for the relation table no matter wich schema i specify.:

    async runEntityTest(uuid1: string) {
        return await this.em.findOne(
            Role,
            {uuid: uuid1},
            {populate: ['permissions'], schema: "schoolid"},
        )
    }

Log

Here is the logged select request from MikroORM:

[Nest] 29268  - 03.03.2025, 18:43:18     LOG [MikroORM] [query] select "r0"."uuid", "r0"."name", "r0"."description", "p1"."uuid" as "p1__uuid", "p1"."name" as "p1__name" from "schoolid"."role" as "r0" left join "role_permissions" as "r2" on "r0"."uuid" = "r2"."role_uuid" left join "test"."permission" as "p1" on "r2"."permission_uuid" = "p1"."uuid" where "r0"."uuid" = '97bb956a-ef65-40aa-9ab1-a1d4747e1df0' [took 3 ms, 1 result]

Used Entities

@Entity({ schema: 'test', tableName: 'permission' })
export class Permission extends _BaseEntity {
    @Property({ unique: true })
    name: string;

    @ManyToMany(() => Role, role => role.permissions, { nullable: true })
    roles: Collection<Role> = new Collection<Role>(this);

    constructor(name: string) {
        super();
        this.name = name;
    }
}

@Entity({ schema: '*' })
export class Role extends _BaseEntityWithDates {
    @Property({ unique: true })
    name: string;

    @Property({ nullable: true })
    description: string;

    @ManyToMany(() => Occupant, occupant => occupant.roles, { owner: true, nullable: true })
    occupants: Collection<Occupant> = new Collection<Occupant>(this);

    @ManyToMany(() => Permission, permission => permission.roles, { owner: true, nullable: false })
    permissions: Collection<Permission> = new Collection<Permission>(this);

    constructor(name: string) {
        super();
        this.name = name;
    }
}

[Verkleckert]

Hey, where you able to reproduce the bug or do you need any futher informations?

[B4nan]

Didn't have the time to look into this just yet. If you want to help, I'd appreciate a complete reproduction, so a GH project with everything set up, ideally based on https://github.com/mikro-orm/reproduction

[Verkleckert]

Hey, I tried it a long time to reproduce it with the Base you provided but coudn't get it to fully work. But i can provide my testrepo with a NestJS installation. It's not ideal but it works to see the error.
https://github.com/Verkleckert/nest-test

You can find full reproduction in the README file of the project. I hope this helps even if its not perfekt.

[B4nan]

If you can't reproduce this without nest involved, let's move it to the nest adapter repo. Note that the fix won't be prioritized, to do that I would really want to see a minimal reproduction.