Entity Relationships with case-insensitive PrimaryKeys don't work

[montycb]

I have two entities in a Many-to-One/One-to-Many relationship, with one entity having a case-insensitive primary key. When I attempt to load both entities using 'find' and 'populate', the 'find' call will fail if there is a difference in capitalization between the two entities. However, the database handles this correctly, allowing for a manual join between the two tables.

import 'reflect-metadata';
import { Collection, Entity, ManyToMany, ManyToOne, MikroORM, PrimaryKey, Property, wrap, Embeddable, Embedded, EntityCaseNamingStrategy, OneToMany, OptionalProps } from '@mikro-orm/core';
import { PostgreSqlDriver } from '@mikro-orm/postgresql';

@Entity()
class A {
  @PrimaryKey({ columnType: 'citext' })
    id!: string

  @OneToMany(() => B, b => b.a)
    b = new Collection<B>(this)
}

@Entity()
class B {
  @PrimaryKey()
    id!: number

  @ManyToOne(() => A)
    a!: A  
}


describe('test', () => {
  let orm: MikroORM<PostgreSqlDriver>;

  beforeAll(async () => {
      orm = await MikroORM.init({
          entities: [A, B],
          dbName: 'test',
          type: 'postgresql'
      })
  await orm.em.execute('create extension if not exists citext;')
  await orm.schema.refreshDatabase()
})

test('populate with citext', async () => {
  const knex = orm.em.getKnex()

  const aInsert = knex.queryBuilder()
    .insert([{id: 'test'}, {id: 'asdf'}])
    .into('a')
  await orm.em.execute(aInsert)


  const bInsert = knex.queryBuilder()
    .insert([{a_id: 'test'}, {a_id: 'ASDF'}])
    .into('b')
  await orm.em.execute(bInsert)

  const a_test = await orm.em.find(A, 'test', {populate: ['b']}) // ok, test===test
  const a_asdf = await orm.em.find(A, 'asdf', {populate: ['b']}) // fails, asdf===ASDF, this should work
  })
})

[B4nan]

Not sure what you expect here, the ORM sees two values, they are not the same, there is no concept of "case insensitive" things in the ORM, that is a database engine feature. Especially here, when you use some driver-specific opt-in extension, how do you expect the ORM to respect that?

I can surely fix the type error because of the mismatch, but I don't think there is a way to make this actually work (map the different case values). I also consider this as a very weird thing to do, a case insensitive PK sounds out of space to me :]

[B4nan]

If you want to access the raw value, you can define a formula property next to the PK (and same for the FK if you want to access that value too)

import { Collection, Entity, ManyToOne, PrimaryKey, OneToMany, TextType, Property } from '@mikro-orm/core';
import { MikroORM } from '@mikro-orm/postgresql';

class CitextType extends TextType {

  getColumnType() {
    return 'citext';
  }

  convertToJSValueSQL(key: string): string {
    return `lower(${key})`;
  }

}

@Entity()
class A {

  @PrimaryKey({ type: CitextType })
  id!: string;

  @Property({ formula: a => `${a}.id` })
  idRaw!: string;

  @OneToMany(() => B, b => b.a)
  b = new Collection<B>(this);

}

@Entity()
class B {

  @PrimaryKey()
  id!: number;

  @ManyToOne(() => A)
  a!: A;

  @Property({ formula: a => `${a}.a_id` })
  aRaw!: string;

}

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    entities: [A, B],
    dbName: 'test-4843',
    debug: true,
  });
  await orm.schema.ensureDatabase();
  await orm.schema.execute('create extension if not exists citext;');
  await orm.schema.refreshDatabase();
});

test('populate with citext', async () => {
  const knex = orm.em.getKnex();

  const aInsert = knex.queryBuilder()
    .insert([{ id: 'test' }, { id: 'asDf' }])
    .into('a');
  await orm.em.execute(aInsert);

  const bInsert = knex.queryBuilder()
    .insert([{ a_id: 'test' }, { a_id: 'ASdF' }])
    .into('b');
  await orm.em.execute(bInsert);

  const a1 = await orm.em.find(A, 'test', { populate: ['b'] });
  expect(a1[0].idRaw).toBe('test');
  expect(a1[0].b.$.toArray()).toEqual([{ id: 1, a: 'test', aRaw: 'test' }]);
  await orm.em.flush();
  orm.em.clear();

  const a2 = await orm.em.find(A, 'asdf', { populate: ['b'] });
  expect(a2[0].idRaw).toBe('asDf');
  expect(a2[0].b.$.toArray()).toEqual([{ id: 2, a: 'asdf', aRaw: 'ASdF' }]);
  await orm.em.flush(); // to test there wont be any update query overriding the raw value to lower case
});

[montycb]

I have successfully tested your proposed solutions. However, I encountered another scenario in which they do not function as expected. This scenario involves entities with two case-insensitive PrimaryKeys. While the formula solution does work in this context, it seems that applying a formula to a ManyToOne relationship with multiple foreign keys is not the intended usage, as illustrated in the following TypeScript code snippet:

formula: a => `lower(${a}.id1) as id1, lower(${a}.id1)`

It's worth noting that I included 'as id1' but not 'as id2'. MikroORM appears to consistently append 'as $columnName' at the end, even when multiple columns are referenced.

Additionally, your second solution does not handle cases involving multiple PrimaryKeys. Here is the updated test case for your review:

import { Collection, Entity, ManyToOne, PrimaryKey, OneToMany, TextType } from '@mikro-orm/core';
import { MikroORM } from '@mikro-orm/postgresql';

class CitextType extends TextType {

  getColumnType() {
    return 'citext';
  }

  convertToJSValueSQL(key: string): string {
    return `lower(${key})`;
  }

}

@Entity()
class A {

  @PrimaryKey({ type: CitextType })
  id1!: string;

  @PrimaryKey({ type: CitextType })
  id2!: string;

  @OneToMany(() => B, b => b.a)
  b = new Collection<B>(this);

}

@Entity()
class B {

  @PrimaryKey()
  id!: number;

  @ManyToOne(() => A)
  a!: A;

}

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    entities: [A, B],
    dbName: 'test-4843',
  });
  await orm.schema.ensureDatabase();
  await orm.schema.execute('create extension if not exists citext;');
  await orm.schema.refreshDatabase();
});

test('populate with citext', async () => {
  const knex = orm.em.getKnex();

  const aInsert = knex.queryBuilder()
    .insert([{ id1: 'test1', id2: 'test2' }, { id1: 'asDf', id2: 'teST' }])
    .into('a');
  await orm.em.execute(aInsert);


  const bInsert = knex.queryBuilder()
    .insert([{ a_id1: 'test1', a_id2: 'test2' }, { a_id1: 'ASdF', a_id2: 'TEst' }])
    .into('b');
  await orm.em.execute(bInsert);

  const a1 = await orm.em.find(A, ['test1', 'test2'], { populate: ['b'] }); // ok, test===test
  expect(a1[0].b.$.toArray()).toEqual([{ id: 1, a: { id1: 'test1', id2: 'test2'} }]);
  orm.em.clear();

  const a2 = await orm.em.find(A, ['asdf', 'test'], { populate: ['b'] }); // fails, asdf===ASDF, this should work
  expect(a2[0].b.$.toArray()).toEqual([{ id: 2, a: {id1: 'asdf', id2: 'test'} }]);
});

[B4nan]

I see, this can be worked around if you also normalize the value via convertToJSValue on runtime, this will make the FK also lower-cased.

import {
  Collection,
  Entity,
  ManyToOne,
  PrimaryKey,
  OneToMany,
  TextType,
  PrimaryKeyType,
} from '@mikro-orm/core';
import { MikroORM } from '@mikro-orm/postgresql';

class CitextType extends TextType {

  getColumnType() {
    return 'citext';
  }

  convertToJSValueSQL(key: string): string {
    return `lower(${key})`;
  }

  convertToJSValue(value: string): string {
    return value.toLowerCase();
  }

}

@Entity()
class A {

  [PrimaryKeyType]?: [string, string];

  @PrimaryKey({ type: CitextType })
  id1!: string;

  @PrimaryKey({ type: CitextType })
  id2!: string;

  @OneToMany(() => C, c => c.a)
  c = new Collection<C>(this);

}

@Entity()
class B {

  @PrimaryKey({ type: CitextType })
  id!: string;

  @OneToMany(() => C, c => c.b)
  c = new Collection<C>(this);

}

@Entity()
class C {

  @PrimaryKey()
  id!: number;

  @ManyToOne(() => A)
  a!: A;

  @ManyToOne(() => B)
  b!: B;

}

let orm: MikroORM;

describe('populate with citext', () => {

  beforeAll(async () => {
    orm = await MikroORM.init({
      entities: [A, B, C],
      dbName: 'test-4843',
    });
    await orm.schema.ensureDatabase();
    await orm.schema.execute('create extension if not exists citext;');
    await orm.schema.refreshDatabase();

    const knex = orm.em.getKnex();

    await knex.queryBuilder()
      .insert([{ id1: 'test1', id2: 'test2' }, { id1: 'asDf', id2: 'teST' }])
      .into('a');

    await knex.queryBuilder()
      .insert([{ id: 'test3' }, { id: 'TEst' }])
      .into('b');

    await knex.queryBuilder()
      .insert([{ a_id1: 'test1', a_id2: 'test2', b_id: 'test3' }, { a_id1: 'ASdF', a_id2: 'TEst', b_id: 'TEST' }])
      .into('c');
  });

  afterAll(() => orm.close());

  test('composite FK', async () => {
    const a1 = await orm.em.fork().find(A, ['test1', 'test2'], { populate: ['c'] });
    expect(a1[0].c.$.toArray()).toEqual([{ id: 1, a: { id1: 'test1', id2: 'test2' }, b: 'test3' }]);

    const a2 = await orm.em.fork().find(A, ['asdf', 'test'], { populate: ['c'] });
    expect(a2[0].c.$.toArray()).toEqual([{ id: 2, a: { id1: 'asdf', id2: 'test' }, b: 'test' }]);
  });

  test('simple FK', async () => {
    const b1 = await orm.em.fork().find(B, 'test3', { populate: ['c'] });
    expect(b1[0].c.$.toArray()).toEqual([{ id: 1, a: { id1: 'test1', id2: 'test2' }, b: 'test3' }]);

    const b2 = await orm.em.fork().find(B, 'test', { populate: ['c'] });
    expect(b2[0].c.$.toArray()).toEqual([{ id: 2, a: { id1: 'asdf', id2: 'test' }, b: 'test' }]);
  });

});

[B4nan]

Got it fixed, it will work correctly even without the convertToJSValue in next version.

41425cb?w=1

[montycb]

I've tested your solution, and it appears to be functioning perfectly. Thank you for your assistance with my unusual use case.