Better collation support

[stefansundin]

Is your feature request related to a problem? Please describe.

I am using mikro-orm with postgresql and I would love the ability to create a collation and then use it in @Property.

Describe the solution you'd like

Something like this to create the collation:

@Collation({
  name: 'case_insensitive',
  provider: 'icu',
  locale: 'und-u-ks-level2',
  deterministic: false,
})

Postgres supports the following CREATE COLLATION syntax:

CREATE COLLATION [ IF NOT EXISTS ] name (
    [ LOCALE = locale, ]
    [ LC_COLLATE = lc_collate, ]
    [ LC_CTYPE = lc_ctype, ]
    [ PROVIDER = provider, ]
    [ DETERMINISTIC = boolean, ]
    [ VERSION = version ]
)

And then to use the collation I want something like the following:

  @Property({ collation: 'case_insensitive' })
  email: string;

Describe alternatives you've considered

I am currently working around this limitation with a manual migration:

    this.addSql(
      `create collation if not exists "case_insensitive" (provider = icu, locale = 'und-u-ks-level2', deterministic = false);`,
    );
    this.addSql(
      'alter table "user" alter column "email" set data type text collate "case_insensitive";',
    );

It is ok since mikro-orm doesn't try to revert it.

Additional context

I think better collation support would be awesome. It is not a fringe feature.

[B4nan]

I would rather keep things simple, so the property option sounds good to me, but @Collation decorator does not (partially also because that would tie its definition to particular table, while its not table specific) - it would require quite a lot of stuff, you need to implement schema reflection and diffing for that. Even native postgres enums are not supported this way, this feels like much bigger niche than that.

I want to finish the native postgres enums refactor for v6, and this could end up as a very similar feature, so let's see how that goes.