Multi Schema - Generate intermediate tables. (Postgres)

[Murzbul]

Describe the bug
First of all I want to thank and congratulate for this incredible ORM.

I am testing the option of using the schemas separately.

The problem I am having right now is that when creating a schema and generating the tables it does not generate the intermediate tables. Traditionally, of course, I haven't had a problem with that.

On the other hand, I don't know if it's a bug or if I'm actually missing something in the code.

To Reproduce
This is the code I have.

1. User Schema

I have the user schema with the wildcard so I can use it in any schema dynamically. I also have a relationship from N to M to roles. And for some reason it does not create the intermediate table.

import { EntitySchema } from '@mikro-orm/core';
import User from '../../Domain/Entities/User';

const UserSchema = new EntitySchema<User>({
    name: 'User',
    tableName: 'users',
    schema: '*',
    class: User,
    indexes: [{ name: 'id_user_1', properties: ['_id', 'email'] }],
    uniques: [{ name: 'unq_user_1', properties: ['_id', 'email'] }],
    properties: {
        _id: {
            type: 'uuid',
            defaultRaw: 'uuid_generate_v4()',
            primary: true,
            unique: true
        },
        firstName: {
            type: 'string'
        },
        lastName: {
            type: 'string'
        },
        email: {
            type: 'string',
            unique: true
        },
        password: {
            type: 'string'
        },
        permissions: {
            type: 'json',
            nullable: true
        },
        passwordRequestedAt: {
            type: 'Date',
            nullable: true
        },
        roles: {
            reference: 'm:n',
            entity: 'Role',
            fixedOrder: true,
            pivotTable: 'users_has_roles',
            lazy: false
        },
        createdAt: {
            type: 'Date',
            onCreate: () => new Date(), nullable: true
        },
        updatedAt: {
            type: 'Date',
            onCreate: () => new Date(),
            onUpdate: () => new Date(), nullable: true
        }
    }
});

export default UserSchema;

async save(entity: ITenantDomain): Promise<ITenantDomain>
{
    await this.repository.persistAndFlush(entity);
    const conn = this.em.getConnection();

    const generator = conn.getPlatform().getSchemaGenerator(this.em);
    await generator.createSchema({ schema: entity.slug });

    return entity;
}

UPDATED

Writing this I realized that by adding the wildcard in the role field of the user schema:

roles: {
    reference: 'm:n',
    entity: 'Role',
    fixedOrder: true,
    pivotTable: '*.users_has_roles',
    lazy: false
}

I have a partial solution since it creates the intermediate table but not the indexes.

Stacktrace

SyntaxErrorException: alter table "tenant5"."users_has_roles" add constraint "users_has_roles_user__id_foreign" foreign key ("user__id") references *."users" ("_id") on update cascade on delete cascade; - syntax error at or near "*"
    at PostgreSqlExceptionConverter.convertException (/usr/app/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:30:24)
    at PostgreSqlDriver.convertException (/usr/app/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:180:54)
    at /usr/app/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:184:24
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async SchemaGenerator.execute (/usr/app/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:317:13)
    at async SchemaGenerator.createSchema (/usr/app/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:31:9)
    at async TenantSqlRepository.save (/usr/app/dist/src/index.js:2218:9)
    at async TenantService.persist (/usr/app/dist/src/index.js:8363:16)
    at async TenantService.create (/usr/app/dist/src/index.js:8367:16)
    at async SaveTenantUseCase.handle (/usr/app/dist/src/index.js:8334:16)

Expected behavior
The behavior I expect is that it doesn't fail to create the indexes and that the wildcard correctly replaces each schema.

Additional context
It does not replace the schema name in the query when you use references at the end of the query.

alter table "tenant5"."users_has_roles" add constraint "users_has_roles_user__id_foreign" foreign key ("user__id")
references ."users" ("_id")
on update cascade on delete cascade; - syntax error at or near ""

Sorry if it's a little messy, it's the first time I'm reporting.

Versions

Dependency	Version
node	v16.3.0
typescript	v4.4.4
mikro-orm	v5.0.0-rc.0
mikro-orm/postgresql	v5.0.0-rc.0
pg	v8.7.1

[Murzbul]

Well I was messing around with the code.

And it seems I have a fairly simple solution.

I'll see if I can send the PR tomorrow. But I don't know if that code is in the right place though.

[B4nan]

We should probably respect the parent entity schema here (if different one is not specified explicitly in the pivotTable name):

https://github.com/mikro-orm/mikro-orm/blob/master/packages/core/src/metadata/MetadataDiscovery.ts#L446

This might need more changes elsewhere (probably in the SchemaGenerator), not sure.

[Murzbul]

Thank you very much for the quick answer.

I added those lines of code to make it work, most likely there shouldn't be that logic there.

If you think it's okay, I'll do the PR.

[B4nan]

There is a helper method called getReferencedTableName for this. You can see it is actually being used few lines after what you added. So instead of changing the value on foreignKey variable, we should use this getReferencedTableName method on other places too. But I don't see any other usage in this file. Maybe the problem is inside that method.

edit: the problem is indeed in the getReferencedTableName method that does not expect the star in table name

[Murzbul]

I understand what you mean.

I'll be checking it out, thanks.

[Murzbul]

Well after finishing some work things I was able to return to this topic. Sorry for the delay.

I enclose a screenshot, the only thing I did was invert that line and with that it works in both cases.

Cases I have tried.

1. Currently if we add the wildcard in the schema and also the wildcard in the relation. You are forced to add it in pivotTable attrribute, otherwise you cannot replace it.

2. In a simple situation with no wildcard with the same relationship.

[B4nan]

No worries, I am already dealing with this.

[Murzbul]

I did not want to stop mentioning that what I commented previously had solved both cases.

I reckon you found something else to look at in detail.