Enumerable Arrays

[duckies]

Hello, I am coming from TypeORM and quite enjoying this library so far, however I seem to have a bit of a snag around arrays of enumerables. This using PostgreSQL by the way.

Is your feature request related to a problem? Please describe.
A typical case with this for me is storing the static roles of users as string enumerables so it doesn't have to be converted in frontend code.

Describe the solution you'd like
I have tried a few variations of the following, however the schema generator does not seem to understand it. The Role.Unverified, as the desired default value, is seemingly referenced as a column in this instance.

@Enum({ items: () => Role })
roles: Role[] = [Role.Unverified];

or

 @Enum({ items: () => Role, default: [Role.Unverified] })
  roles: Role[] = [Role.Unverified];

error: alter table "user" add column "roles" text check ("roles" in ('Admin', 'Educator', 'Volunteer', 'Verified', 'Unverified')) not null default Unverified; - column "unverified" does not exist

Describe alternatives you've considered
It also did not work with an enum of numbers, so I am assuming they are not supported currently.

[B4nan]

Not natively supported, but should be possible via custom type: https://mikro-orm.io/docs/custom-types

[jackiboy]

Here is an example of creating and retriving an array in postgres.

note toJSON is used instead of convertToJSValue. This ensures a JSON array is returned in your response.

export class RoleType extends Type {
  convertToDatabaseValue(value: any): string {
    if (value.length) {
      return `{${value.join(',')}}`;
    }
    throw ValidationError.invalidType(RoleType, value, 'JS');
  }

  toJSON(value: any) {
    return value;
  }

  getColumnType() {
    return 'text[]';
  }
}

[jshearer]

@B4nan I see you mentioned this issue in that commit ☝️ , is it possible to define a column as ArrayType of an Enum, and have the postgres driver create the appropriate constraints?

[B4nan]

Not really tbh, I am not sure how it could work. Enums are implemented via text fields in postgres (that's how knex is working), same as arrays, but I don't see a way to have "enum like check constraints" for arrays. Using native postgres enums might help, but that would mean a lot of additional work, and still the pg array type is basically just a concatenated string (so we would still have no type safety on the array value afaict).

That is the reason why I did not close this just yet, but thinking about it now, I really do not see a clear way to support it, so I might just close it now... :]

You could implement your own custom type that would validate the items for you. We could have such type in the repository, prepared for anyone, that would work based on the enum metadata.

[duckies]

I have had no complaints using the ArrayType, but before then it was fairly simple to make a custom type for native postgres enums.

I prefer to keep validation on a layer above the ORM, so strict enum checking has not been necessary. I haven't checked, but is it possible to (or add) to define custom metadata in the property decorator that could be checked in a custom type, or maybe a validation callback?

[B4nan]

You could pass anything to the custom type via constructor and provide a configured instance of the type:

@Property({ type: new MyEnumType(['a', 'b', 'c']) })
enumArray: ('a' | 'b' | 'c')[]

[B4nan]

v4.2 will allow this:

enum Role {
  User = 'user',
  Admin = 'admin',
}

@Enum({ items: () => Role, array: true, default: [Role.User] })
roles: Role[] = [Role.User];

And the items will be checked on flush automatically to mimic the db constraints.

[mehulchauhan]

First of all many thanks for this library. I am new to Typescript, Node.JS eco-system. Any help will be really appreciated.

@B4nan : I just tried v4.2 allowed pattern to define role. and still facing error:

DriverException: create table `user` (`id` int unsigned not null auto_increment primary key, `created_at` datetime not null, `updated_at` datetime not null, `roles` text not null default 'PUBLIC') default character set utf8mb4 engine = InnoDB; - BLOB, TEXT, GEOMETRY or JSON column 'roles' can't have a default value

I am having MySQL DB and plain User.ts for defining entity. I am new to Typescript, can you please help me to resolve this error also I wanted to make roles column as varchar NOT text with defined length?

command I used for DB are:

npx mikro-orm migration:create
npx mikro-orm migration:up

[B4nan]

You might want to try defaultRaw and wrap the value in parens, as apparently mysql 8 requires you to provide the default value for TEXT columns as expression (god knows why, it feels like very artificial limitation, and I'd expect this to be handled on knex's side).

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html#data-type-defaults-explicit

[linkthai]

v4.2 will allow this:

enum Role {
  User = 'user',
  Admin = 'admin',
}

@Enum({ items: () => Role, array: true, default: [Role.User] })
roles: Role[] = [Role.User];

And the items will be checked on flush automatically to mimic the db constraints.

@B4nan For some reason, when I run migration with this, it always create a new migration file like

export class Migration20231012023939 extends Migration {

  async up(): Promise<void> {
    this.addSql('alter table "admin" alter column "permissions" type text[] using ("permissions"::text[]);');
  }

  async down(): Promise<void> {
    this.addSql('alter table "admin" alter column "permissions" type text[] using ("permissions"::text[]);');
  }

}

I add a bracket outside the enum and the migration command doesn't create a new file anymore

  @Enum({
    items: () => [AdminPermission],
    array: true,
    default: [],
  })
  permissions: AdminPermission[];

But it prevents me from making changes to permissions as it is incorrect type.

[B4nan]

items: () => [AdminPermission],

This is indeed wrong, the callback is supposed to be returning the enum, not an array with the enum being its element. When it returns an array, it needs to return the items, e.g. items: () => [AdminPermission.Foo, AdminPermission.Bar].

I can't help you without seeing a complete repro, hard to guess what you are doing and what your setup is.

Note that you can enable verbose logging to see why the schema comparator issues new migration - just add MIKRO_ORM_VERBOSE=1 before the CLI call.

[linkthai]

@B4nan thank for the answer, this is what I did

export enum AdminPermission {
  ROOT = 'ROOT', // the root admin, required to manage other admins
  ACCESS = 'ACCESS', // give access to the dashboard for other admin
}

@Entity()
export class Admin extends Base {
  @Enum({
    type: 'enumArray',
    items: () => AdminPermission,
    columnType: 'text',
    array: true,
    default: [],
  })
  permissions: AdminPermission[];
}

After I removed the items: () => AdminPermission, it actually stops creating new file in migration like expected. Is this so it could validate at the database level? I run a GraphQL server so I already have validation for enum so maybe it's not really required for me.

These are the verbose logging if I add the items: () => AdminPermission in the @enum decorator:

[schema] 'enumItems' changed for column public.admin.permissions { column1: { name: 'permissions', type: 'text', mappedType: TextType {}, length: null, precision: null, scale: null, nullable: false, default: "'{}'", unsigned: false, autoincrement: false, comment: null, primary: false, unique: false, enumItems: [] }, column2: { name: 'permissions', type: 'text', mappedType: TextType {}, unsigned: false, autoincrement: false, primary: false, nullable: false, default: "'{}'", enumItems: [ 'ROOT', 'ACCESS' ] }}
[schema] column public.admin.permissions changed { changedProperties: Set(1) { 'enumItems' } }
[schema] 'enumItems' changed for column public.admin.permissions { column1: { name: 'permissions', type: 'text', mappedType: TextType {}, unsigned: false, autoincrement: false, primary: false, nullable: false, default: "'{}'", enumItems: [ 'ROOT', 'ACCESS' ] }, column2: { name: 'permissions', type: 'text', mappedType: TextType {}, length: null, precision: null, scale: null, nullable: false, default: "'{}'", unsigned: false, autoincrement: false, comment: null, primary: false, unique: false, enumItems: [] }}
[schema] column public.admin.permissions changed { changedProperties: Set(1) { 'enumItems' } }

[B4nan]

Works fine for me, I need to see a complete reproduction.

edit: actually, drop the columnType: 'text', it might be about that - as the actual column type will be text[], not text.

[linkthai]

@B4nan oh yeah, I should probably drop it, the original problem exist before I added columnType

Sorry, that's all there is to it, so I don't even know what else to reproduce. I'll give some more information that I think could help. I'm using Postgres and Fastify.

This is the migration command, I'm using this because the mikro-orm .bin bash file doesn't work well with my src/... path.
"migration": "node --no-warnings -r tsconfig-paths/register --loader ./loader.js ./node_modules/@mikro-orm/cli/cli.js migration:create",

I tried to remove the permissions field

  async up(): Promise<void> {
    this.addSql('alter table "admin" drop column "permissions";');
  }

Then readd it and run migration

  async up(): Promise<void> {
    this.addSql('alter table "admin" add column "permissions" text[] not null default \'{}\';');
  }

then run migration again

async up(): Promise<void> {
    this.addSql('alter table "admin" alter column "permissions" type text[] using ("permissions"::text[]);');
  }

If this doesn't give you any hint, I'm probably gonna just remove the items field as I already did application level validation. Thanks for your time.

[B4nan]

Sorry, that's all there is to it, so I don't even know what else to reproduce.

You gave me one property definition, I want you to give me full entity definition (simplified, but complete), ORM config, tsconfig.json, package.json, that is a complete reproduction - something I can just pull and run myself. All those things matter, right now I don't even know what version are you using.

If this doesn't give you any hint

What could give me some hint is the debug logs, the ones you provided before were generated with the wrong columnType, since they mention the TextType, while this should be ArrayType (or EnumArrayType) instead.

One thing that might confuse you here is the snapshotting, maybe try disabling that when you debug things.

[linkthai]

@B4nan I made a Codesandbox with the files you need here: https://codesandbox.io/p/sandbox/hardcore-allen-qc869r

The loader.js I'm using to resolve the "src/modules/..." path since ts-node can't get it right. I'm running pnpm run migration to create new migration. I did turn off snapshot by default. I'm using tsx to transpile Typescript directly so no generated dist for Javascript.

This is the verbose logging for the migrations with the correct columnType

[schema] 'enumItems' changed for column public.admin.permissions { column1: { name: 'permissions', type: 'text[]', mappedType: ArrayType { hydrate: [Function: hydrate] }, length: null, precision: null, scale: null, nullable: false, default: "'{}'", unsigned: false, autoincrement: false, comment: null, primary: false, unique: false, enumItems: [] }, column2: { name: 'permissions', type: 'text[]', mappedType: ArrayType { hydrate: [Function: hydrate] }, unsigned: false, autoincrement: false, primary: false, nullable: false, default: "'{}'", enumItems: [ 'ROOT', 'ACCESS' ] }}
[schema] column public.admin.permissions changed { changedProperties: Set(1) { 'enumItems' } }
[schema] 'enumItems' changed for column public.admin.permissions { column1: { name: 'permissions', type: 'text[]', mappedType: ArrayType { hydrate: [Function: hydrate] }, unsigned: false, autoincrement: false, primary: false, nullable: false, default: "'{}'", enumItems: [ 'ROOT', 'ACCESS' ] }, column2: { name: 'permissions', type: 'text[]', mappedType: ArrayType { hydrate: [Function: hydrate] }, length: null, precision: null, scale: null, nullable: false, default: "'{}'", unsigned: false, autoincrement: false, comment: null, primary: false, unique: false, enumItems: [] }}
[schema] column public.admin.permissions changed { changedProperties: Set(1) { 'enumItems' } }

[B4nan]

Thanks, will try to look into that soon. It's weird I wasn't able to reproduce this in the tests, I can definitely see the problem in the code.

[linkthai]

@B4nan I upgraded to the latest version and everything works fine now. Thank you so much.

There is one more question regarding the type in @enum decoration, in your test and examples, you don't even have the property type, but if I omit it I get an error from ReflectMetadataProvider so I always have to included it. I have enabled the emitDecoratorMetadata in tsconfig.json.

Error: Please provide either 'type' or 'entity' attribute in Admin.permissions. If you are using decorators, ensure you have 'emitDecoratorMetadata' enabled in your tsconfig.json.
at ReflectMetadataProvider.initPropertyType (/.../node_modules/.pnpm/@mikro-orm+core@5.8.9_@mikro-orm+migrations@5.8.9_@mikro-orm+postgresql@5.8.9/node_modules/@mikro-orm/core/metadata/ReflectMetadataProvider.js:14:19)

[B4nan]

I believe that is about you working with TS in "non-standarad" ways, as tsc (and therefore ts-node) will emit the medatada here correctly. Or it could be about ESM and reflect metadata in general, not really sure.