InvalidFieldNameException on multiple foreign key columns to same table

[njeirath]

Describe the bug
I'm trying to implement a pivot table manually (so I can add additional properties to the relationship) but where the the two entities being related are in the same table. For example, say I had a table of users and wanted to be able to store a friendship relationship along with when they became friends.

In code it's something like this:

export class FriendLink {
    requestor: User
    requestee: User
    created: Date

    [PrimaryKeyType]: [string, string]   //User is using a string as it's ID

    constructor(requestor: User, requestee: User) {
        this.requestor = requestor
        this.requestee = requestee
        this.created = new Date()
    }
}

export const schema = new EntitySchema<FriendLink>({
    path: __filename,
    name: 'FriendLink',
    class: FriendLink,
    tableName: 'friend_link',
    properties: {
        requestor: {
            reference: 'm:1',
            entity: 'User',
            nullable: false,
            primary: true,
            joinColumn: 'requestor_user_id',
        },
        requestee: {
            reference: 'm:1',
            entity: 'User',
            nullable: false,
            primary: true,
            joinColumn: 'requestee_user_id',
        },
        created: {type: 'datetime', nullable: false},
    },
})

export const entity = FriendLink

However I'm getting the following with this setup:

Stack trace

InvalidFieldNameException: create table `friend_link` (`created` varchar not null, primary key (`requestor_user_id`, `requestee_user_id`)); - SQLITE_ERROR: no such column: requestor_user_id

To Reproduce
Steps to reproduce the behavior:

1. Using the above code as an example
2. Initialize the ORM
3. call await orm.getSchemaGenerator().createSchema()

Expected behavior
It appears createSchema is adding the created column but is skipping both the requestor_user_id and requestee_user_id for some reason which I would expect it to include in the table definition.

Additional context
It seems to me the issue is likely related to have two columns referencing the same table because if I remove one of the two the createSchema call works. Not sure if I'm just doing something wrong or if this is an issue but would appreciate whatever help you can provide.

Versions

Dependency	Version
node	v14.5.0
typescript	3.8.3
mikro-orm	4.0.7 (also tried with 3.6)
your-driver	sqlite

[B4nan]

Right, this is sqlite only problem, the reason why the FKs are not defined in the table right ahead is the limitation of sqlite, that does not allow creating FKs on existing columns (on the other hand, FKs are basically disabled in sqlite due to a limitation of knex, that does not allow it even when its totally valid approach). Will try to do something about it...

For now you could use migrations and fix the query manually.