Custom Column/Property Names

[letynsoft]

Description/Steps to reproduce

Hi,
Is there any way how to specify a custom column name using the @Property decorator with loopback4?

I have following property

  @property({
    type: 'number',
    name: 'shift_id',
  })
  shiftId?: number;

But the mssql connector (migrate script) doesn't seem to support this (is attemping to create duplicate columns, ...).

I have wrote a little quick-fix (rewriting modelDefinition when define() is called), but that's ugly

Expected result

To migrate the schema

Actual Result

Cannot migrate database schema { RequestError: Column names in each table must be unique. Column name 'shift_id' in table 'dbo.Ntls' is specified more than once

Additional information

$ node -e 'console.log(process.platform, process.arch, process.versions.node)'
linux x64 10.16.0

$ npm ls --prod --depth 0 | grep loopback
├── @loopback/boot@1.4.2
├── @loopback/context@1.20.0
├── @loopback/core@1.8.3
├── @loopback/openapi-v3@1.6.3
├── @loopback/repository@1.8.0
├── @loopback/rest@1.16.1
├── @loopback/rest-explorer@1.2.3
├── @loopback/service-proxy@1.2.3
├── loopback-connector-mssql@3.3.0

[dhmlau]

Transferred to loopback-next repo since this is related to LB4.

@letynsoft , it would be something like:

  @property({
    type: 'string',
    required: true,
    postgresql: {
      columnName: 'TODO_TITLE',
    },
  })
  title: string;

Please see my example in here: https://github.com/dhmlau/loopback4-example-todo-customized#if-i-have-a-table-name-or-column-name-that-is-different-from-the-model-name-and-property-what-should-i-change.

[letynsoft]

Thanks for the reply, tried that, but it doesn't seem to work with mssql connector:

  @property({
    type: 'number',
    required: true,
    mssql: {
      columnName: 'shift_id',
    },
  })
  shiftId: number;

The strange thing is that the mssql connector get's confused and thinks there is the 'shift_id' column but only 'shiftId' exists at that moment...
If i remove the mssql object completely, it creates the camel case columns just fine (but i would much prefer to have snake_case)...

[dougal83]

The strange thing is that the mssql connector get's confused and thinks there is the 'shift_id' column but only 'shiftId' exists at that moment...

Have you migrated the database after asking the column to be named as below?:

mssql: {
      columnName: 'shift_id',
 },

Sounds to me that it is the most likely source of confusion.

[letynsoft]

Yes i have tried migrating, that's the issue. The migration process doesn't acknowledge this correctly and fails.
When this is used during runtime (and the table is migrated), it works correctly (for some strange reason)

[Hideart]

I have wrote a little quick-fix (rewriting modelDefinition when define() is called), but that's ugly

Hello. Can you tell me about your quick-fix, please?
P.S. The issue for MS SQL Server is actual now.

[sestienne]

Transferred to loopback-next repo since this is related to LB4.

@letynsoft , it would be something like:

  @property({
    type: 'string',
    required: true,
    postgresql: {
      columnName: 'TODO_TITLE',
    },
  })
  title: string;

Please see my example in here: https://github.com/dhmlau/loopback4-example-todo-customized#if-i-have-a-table-name-or-column-name-that-is-different-from-the-model-name-and-property-what-should-i-change.

Hi,
1/ I tried to use this syntax for sqlite3 but it seems not working ? I try it with "sqlite" & "sqlite3". What's wrong ? Is it supported ? What are supported connectors ? Mysql ? MongoDB ?

@model({
  settings: {
    sqlite3: {
      table: 'categories',
    }
  }
})

2/ I also make a dummy test in a property and it doesn't work.

@property({
    type: 'number',
    id: true,
    generated: true,
    sqlite3: {
      columnName: 'CAT_ID',
    },
    sqlite: {
      columnName: 'CAT_ID',
    }
  })
  id?: number;

3/ ow to define the database column name for @belongsTo annotation ?

@belongsTo(() => Category, { keyTo: 'id', name: 'category' })
categoryId?: number;


Thanks in advance for your response.

[agnes512]

@letynsoft @Hideart hi, we have documented it on our site, the Relation metadata section shows how to customize your foreign key name and db column names. Please try it out and let us know if it works.

@sestienne I don't think we support sqlite3, you might need to use MySQL connector.

[dhmlau]

Closing due to inactivity.