Loopback-next: Loopback Connector Disregards Property Name

[mikeTmw]

Description / Steps to reproduce / Feature proposal

The @ property decorator has a name property which should specify the column name in the database in case it does not match with the field name in your model. However, the code in loopback-connector/sql.js does not utilize this metada so you only get the field name as it's defined in your model. In my case, I am using DB2 and this causes issues because the generated SQL select statement uses double-quotes around each field signify to DB2 that the field name matches exactly with what is specified in the DB (i.e. case sensitive).

To Reproduce:
Create a DB2 database w

1. ith a table "DRIVER" that contains field "DRIVER_ID".
2. Setup a basic Loopback4 project like the Todo Tutorial (replacing instances of Todo with Driver) but use a DB2 based datasource.
3. Create the Model like the following:

@model({
  name: 'DRIVER',   // This corrects the case-sensitive issue at the Table level.
  }
})
export class Driver extends Entity {
  @property({
    name: 'DRIVER_ID', // This is ignored by field mapping functions.
    type: 'number',
    id: true,
  })
  fooId?: number;

1. Use VS Code to start the server in Debug and issue a GET against drivers
2. In the sql.js --> buildColumnNames note that the name is 'fooId' instead of 'DRIVER_ID'
3. Continue execution, you will receive an SQL0206N error from DB2: "fooId" is not valid in the context where it used.

Current Behavior

sql.js simply uses the field names as the DB column names.

Expected Behavior

sql.js should map to the @ property 'name' property to determine the correct column names.

Connectors to fix

• [x] SQL connectors - done by strongloop/loopback-connector#142
• [ ] MongoDB https://github.com/strongloop/loopback-connector-mongodb
• [ ] CouchDB2 https://github.com/strongloop/loopback-connector-couchdb2
• [ ] Cloudant https://github.com/strongloop/loopback-connector-cloudant (may depend on couchdb2)
• [ ] Cassandra https://github.com/strongloop/loopback-connector-cassandra

[bajtos]

Thank you @mikeTmw for opening this issue.

The @ property decorator has a name property which should specify the column name in the database in case it does not match with the field name in your model. However, the code in loopback-connector/sql.js does not utilize this metada so you only get the field name as it's defined in your model. In my case, I am using DB2 and this causes issues because the generated SQL select statement uses double-quotes around each field signify to DB2 that the field name matches exactly with what is specified in the DB (i.e. case sensitive).

I agree with you - ideally, our connectors should be honoring the name setting provided in property definition.

From what I can tell by reading slq.js code, we use column or columnName instead of column right now, see

https://github.com/strongloop/loopback-connector/blob/5ab9c9c76e32c3acb29c99ed0e55e839852a39d9/lib/sql.js#L375-L390

I think this database-specific property needs to be nested inside a database-specific property settings as can be seen in the tests:

https://github.com/strongloop/loopback-connector/blob/5ab9c9c76e32c3acb29c99ed0e55e839852a39d9/test/sql.test.js

    Customer = ds.createModel('customer',
      {
        name: {
          id: true,
          type: String,
          testdb: {
            column: 'NAME',
            dataType: 'VARCHAR',
            dataLength: 32,
          },
        }, vip: {
          type: Boolean,
          testdb: {
            column: 'VIP',
          },
        },
        address: String,
      },
      {testdb: {table: 'CUSTOMER'}});
  });

Could you please check if the following code works for you?

@model({
  name: 'DRIVER',   // This corrects the case-sensitive issue at the Table level.
})
export class Driver extends Entity {
  @property({
    type: 'number',
    id: true,
    db2: {
      column: 'DRIVER_ID',
    },
  })
  fooId?: number;
}

It would be great if you could contribute a patch to our base SQL connector to let it recognize name in addition to column and columnName settings. See Submitting a pull request in our docs to get started.

[mikeTmw]

Thank you for the quick response.

It appears that I did not dig quite deep enough into the stack to see the column() function. Setting that database-specific property and columnName did indeed work.

The confusion (on my part) was that the main documents on Model use the 'name' property on @ property and on @ model and since model fixed the problem at the table level I expected it to do the same at column level.

I am currently looking at a couple of different frameworks and this is one of the few (that I've found) that supports DB2 out of the box. I am not sure if I will get time to submit a patch but I will keep it in mind.

Thanks again.

[samarpanB]

Not sure if @elv1s is still working on his PR. But, since, this was an easy solution and we also solved it in our application, so I also generated a PR.
@bajtos see if that can be merged to resolve this issue.

[samarpanB]

@bajtos, @dhmlau I just rebased my PR. Please see if it can me merged. Actually, we kind of need this in one of our project. It will be good to have soon. Right now I need to use my forked repo for this. But I want to avoid that as being poor practice.

[dhmlau]

@samarpanB @elv1s, seems like the 2 PRs are the same?

[samarpanB]

Yes. If any one of them could be merged, it will be fine. I wasn’t sure if @elv1s was still working on his PR. So I just did mine and rebased it to make it current.

[elv1s]

go ahead with @samarpanB PR

[bajtos]

The problem will be solved in SQL connectors once we publish a new version of loopback-connector with https://github.com/strongloop/loopback-connector/pull/142.

What's remaining: fix NoSQL connectors like MongoDB, CouchDB, Cloudant, etc.

[dhmlau]

I've released [email protected].