Define integer array type field in loopback 4 model with Postgresql

[DilupaSampath]

I want to define integer array filed. When i'm define integer array type like this in my loopback4 model file

  @property({
    type: 'array',
    itemType: 'number',
  })
  option?: number[];

when i'm run "npm run migrate" it creates this "option" field data type as TEXT in Postgresql data table.
I set my model field like this it works fine,

  @property({
    type: 'array',
    itemType: 'number',
    postgresql: {
      dataType: 'integer ARRAY',
    },
  })

but when i'm inserting values postgresql give this error.

 error: malformed array literal: "[4,5]"

Could you please tell me how do i resolve this.

[bajtos]

Thank you @DilupaSampath for reporting the problem. Please create a small application we can use to reproduce the problem locally and perform further investigation. See https://loopback.io/doc/en/contrib/Reporting-issues.html#loopback-4x-bugs

[dhmlau]

Did some digging. I think there are 2 issues listed here but @DilupaSampath is more interested in finding out a resolution for the first one.

Issue 1 - Cannot specify the integer array in the request body

It seems like the PostgreSQL connector does not support array types out of the box. The workaround is to use the JSON type discussed in loopbackio/loopback-connector-postgresql#31 (comment).

@DilupaSampath, is it something you're looking for? Could you please give it a try?

Issue 2 - migration script does not create the array types

After running npm run migrate, the column has type TEXT instead of number array.

testdb=# \d test
              Table "public.test"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 id     | text |           | not null | 
 option | text |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

If specify the following in the model, we'll get integer[] as the type

postgresql: {
      dataType: 'integer ARRAY',
    },

Table definition:

testdb=# \d test
                 Table "public.test"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 id     | text      |           | not null | 
 option | integer[] |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

@b-admike pointed out the code snippet that is responsible for the type mapping: https://github.com/strongloop/loopback-connector-postgresql/blob/master/lib/migration.js#L478-L499

---

@raymondfeng @bajtos, would it be a feature in the postgresql connector?

[b-admike]

I think for the error on create it's probably a bug/feature that touches on https://github.com/strongloop/loopback-connector-postgresql/blob/master/lib/postgresql.js#L632 when inserting records.

[dhmlau]

Thanks @b-admike. I'll mark it as a feature for now. Thanks.