Column is always integer when running npm run migrate

[dhmlau]

Description / Steps to reproduce / Feature proposal

I have a model property of type string. After running npm run migrate, the table gets created, and the type shows integer.

• I'm using postgresql (not sure if it makes a difference)
• my model ts looks like:

import {Entity, model, property} from '@loopback/repository';

@model()
export class Customer extends Entity {
  @property({
    type: 'string',
    id: true,
    generated: true,
  })
  custid: string;

  @property({
    type: 'string',
    required: true,
  })
  custname: string;

  constructor(data?: Partial<Customer>) {
    super(data);
  }
}

Running \d customer on postgresql, it shows:

testdb=# \d customer
                               Table "public.customer"
  Column  |  Type   | Collation | Nullable |                 Default                  
----------+---------+-----------+----------+------------------------------------------
 custid   | integer |           | not null | nextval('customer_custid_seq'::regclass)
 custname | text    |           | not null | 
Indexes:
    "customer_pkey" PRIMARY KEY, btree (custid)

Current Behavior

custid is of type integer on the database

Expected Behavior

custid should be of type text instead.

See Reporting Issues for more tips on writing good issues

[dhmlau]

FYI - my sample repo is here: https://github.com/dhmlau/loopback4-example-todo-customized

The workaround for me is to alter the table after being created using npm run migrate.

testdb=# ALTER TABLE customer ALTER COLUMN custid TYPE text;
ALTER TABLE
testdb=# \d customer
                              Table "public.customer"
  Column  | Type | Collation | Nullable |                 Default                  
----------+------+-----------+----------+------------------------------------------
 custid   | text |           | not null | nextval('customer_custid_seq'::regclass)
 custname | text |           | not null | 
Indexes:
    "customer_pkey" PRIMARY KEY, btree (custid)

[bajtos]

I think the PostgreSQL connector is enforcing the type of the primary key to be a number. You can see that the custid column is configured to use a default value computed by nextval('customer_custid_seq'::regclass).

I think the same problem may apply to other SQL connectors too.

While this behavior is intended, I can see how it can be confusing for people building LB4 applications.

[bajtos]

Need to check how to migrate uuid as property type. And do we want the migrated to have uuid as type + auto generate

FYI, I think it should be possible to replace generated: true with defaultFn option set to uuidv4, see https://loopback.io/doc/en/lb3/Model-definition-JSON-file.html#general-property-properties.

As I understand the differences, generated: true means that the database is expected to generate the values (i.e. LoopBack sends undefined primary key, database returns the generated value), whereas defaultFn: 'uuidv4 means the primary key is generated by LoopBack at creation time.

[bajtos]

In the pull request #4270, we have run into this issue too. When running acceptance tests for repository-mysql, automigration fails because

Type TEXT ad AUTO_INCREMENT are not a valid combination.

[agnes512]

Notice:
the solution in loopbackio/loopback-connector-postgresql#404 allows you to generate uuid inside of PostgreSQL and use your own extension/function.

BUT this can be done easily with the existing functionality defaultFn:

export class Customer extends Entity {
  @property({
    id: true,
    type: 'string'
    defaultFn: 'uuidv4',
    // generated: true,  -> not needed
    // useDefaultIdType: false,  -> not needed
  })
  uuid_id: string;

  @property({
    generated: true,
    type: 'number',
  })
  int_id: number;
}

With definition, LB3/4 generates uuid for you.

Ref: General property properties

In PostgreSQL, integer can be auto-generated even the property is not the id property (e.g int_id)