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) { 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.

<!--
HELP US HELP YOU, PLEASE
- Do a quick search to avoid duplicate issues
- Provide as much information as possible (reproduction sandbox, use case for features, etc.)
- Consider using a more suitable venue for questions such as Stack Overflow, Gitter, etc.
-->

_See [Reporting Issues](http://loopback.io/doc/en/contrib/Reporting-issues.html) 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 https://github.com/strongloop/loopback-next/pull/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 strongloop/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)