Table and column names are being forced to lower case

[paddyinpdx]

The connector is forcing table and column names to lower case in lines 610-635 of /lib/postgresql.js. I don't think it should doing this. Suggest removing toLowerCase() calls therein.

Please refer to https://groups.google.com/forum/#!topic/loopbackjs/sHmDCTqgzI0.

[jmls]

I've just hit this. The bug is not the lowercase, but that the postgres connector should be mapping the propertyname to the column name and doesn't seem to be doing so.

for example

{
 "name": "Shippers",
 "options": {
  "idInjection": false,
  "postgresql": {
   "schema": "public",
   "table": "shippers"
  }
 },
 "properties": {
  "shipperid": {
   "type": "Number",
   "required": true,
   "length": null,
   "precision": 16,
   "scale": 0,
   "id": 1,
   "postgresql": {
    "columnName": "ShipperID",
    "dataType": "smallint",
    "dataLength": null,
    "dataPrecision": 16,
    "dataScale": 0,
    "nullable": "NO"
   } [snipped]

when I try and access the data, I get the error

{
  "error": {
    "name": "error",
    "status": 500,
    "message": "column \"shipperid\" does not exist",
    "length": 101,
    "severity": "ERROR",
    "code": "42703",
    "position": "8",
    "file": "parse_relation.c",
    "line": "2892",
    "routine": "errorMissingColumn",
    "stack": "error: column \"shipperid\" does not exist\n    at Connection.parseE (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:534:11)\n    at Connection.parseMessage (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:361:17)\n    at Socket.<anonymous> (/usr/local/lib/node_modules/loopback-connector-postgresql/node_modules/pg/lib/connection.js:105:22)\n    at Socket.emit (events.js:107:17)\n    at readableAddChunk (_stream_readable.js:163:16)\n    at Socket.Readable.push (_stream_readable.js:126:10)\n    at TCP.onread (net.js:529:20)"
  }
}

so what should be happening is that the connector should map shipperid=>ShipperID as defined in the columnName options of postgres

[jmls]

ok, found where the problem is happening

in
loopback-connector/lib/sql.js, lines 78 there is this code

SqlConnector.prototype.column = function (model, property) {
  var name = this.getDataSource(model).columnName(model, property);
  var dbName = this.dbName;
  if (typeof dbName === 'function') {
    name = dbName(name);
  }
  return name;
};

I don't know the purpose of dbName(name), but name is returned correctly (shipperid->ShipperId) and then because dbName is a function, it gets called, and name is reset to shipperid and returned

this also happens in the idColumn function

[jmls]

bump - anyone got any comments ? This issue makes my postgres database unusable with strongloop

[lambrojos]

Same problem here

[tuomastanner]

Hi, I created a fork that fixes the issue at: https://github.com/W3GroupFinland/loopback-connector-postgresql

If you want, you can include it in your package.json by replacing the connector line with:
"loopback-connector-postgresql": "git://github.com/W3GroupFinland/loopback-connector-postgresql.git#master",

As I understand it, loopback converts all db column names to lowercase when accessing. Since the names are in mixed case, the db column is not found. I simply removed the lowercase conversion.

I also set the default datatype for Strings to TEXT without any arbitrary limits. This is based on: http://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying

[sjschubert]

Nice work @paddyinpdx/@tuomastanner. Any chance of a PR so the loopback folks have it on the radar?

[sjschubert]

I did a bit of an archeological expedition and found that this issue has been partially fixed in commit
d08dad7#diff-ac54005f9bbf753aa45ceebc4a032628, the table name is still being lowercased, although that seems to work with the naming scheme now. Maybe this issue should be closed?

[mirow]

No, the issue is not fixed. The poorly named method PostgreSQL.prototype.dbName() is called not just for table names, but also for column names.
https://github.com/strongloop/loopback-connector/blob/master/lib/sql.js#L161-L164

[raymondfeng]

Please note if a DB column name is provided (such as {myProp: {postgresql: {column: 'MyCol'}}), the casing will be preserved (MyCol). See https://github.com/strongloop/loopback-connector/blob/master/lib/sql.js#L153-L158. If no customization is made, we continue to enforce default cases for the column name. I would consider the issue has been fixed.

[tuomastanner]

In that case the behavior between databases is inconsistent, since loopback-connector-mssql preserves the case for column names. I personally see no upside to lowercasing the column names. It only causes inconsistency and is a source for potential bugs.

BTW if the models id field has a different column name from the field name, upserts an updates brake, since existence query is done with field name instead of column value.

[Sogl]

+1
I found the same problem with mysql loopback connector.

[Sogl]

@raymondfeng Any chance to merge? So much time has passed and this bug still exists and issue open.

[emresebat]

Thanks for the information @tuomastanner, I've also created a fork that used lowercase+underscore naming and updated my package.json.

By the way for me the correct way to include your forked repo is

"loopback-connector-postgresql": "git+https://:[email protected]/<USER_NAME>/<REPOSITORY_NAME>.git",

[marekful]

I ran into this problem when trying to use Model.find({include: 'some-relation'}) when Model's 'some-relation' is hasMany to the other model. @tuomastanner's fork didn't solve the problem (but it broke include for built-in models).

I found that the issue was only present when the relation is defined on both sides. Model1 hasMany to Model2 and Model2 belongsTo Model1. When I removed the relation from Model2, Model1.find with the include filter worked as expected.

[TangMonk]

same problem

[ssh24]

With respect to what @raymondfeng said, this is our default behaviour:

Please note if a DB column name is provided (such as {myProp: {postgresql: {column: 'MyCol'}}), the casing will be preserved (MyCol). If no customization is made, we continue to enforce default cases for the column name.

Closing this issue as resolved. Feel free to reopen if needed.

[gmarab]

Sorry @ssh24 and @raymondfeng, but it's not true: with the sql server connector the case of the column name is never preserved.
Even if the correct name is indicated in the customization.

[ssh24]

@gmarab Sorry there was a typo. It should be columnName not column.

    "info": {
      "type": "object",
      "postgresql": {
        "columnName": "Info",
        "dataType": "json"
      }
    }

[gmarab]

The name of the column is correct:

But the result is always wrong:

[ssh24]

What version of the connector are you using?

[gmarab]

The last: 3.0.0

[ssh24]

Sorry, just realized you are using mssql. This is the postgresql module. This might be an issue with mssql, do you mind creating an issue there and ping me?

[gmarab]

Sorry, but in the previous posts I saw that the connector for Mysql and MsSql was also mentioned. I thought the problem could be general.

[gmarab]

Hi @ssh24,

I really think the problem is general:
In loopback-conector \ sql.js, at line 1402 is made this assignment:
       data [p] = columnValue;

when I think the correct assignment is:
       data [columnName] = columnValue;

Or at least that works;)

[ssh24]

@gmarab It works for postgresql with the latest version of this connector module.

[ZooDoo4U]

I'm running with Postgres 9.6.5

Just curious what is the correct behavior for this? I'm kind of a newbie to Postgres in general, I get that it is case sensitive, many years doing c/c++ easy to live with if that is the way things are. The behavior I'm seeing seems broken. If one creates a table "MyTable" with column names "ColumnOne" why would Postgres if it actually is case sensitive ever alter the names? I can't think of any other tool or data format where case matters that anything just blindly lower cases stuff. How could one handle the case where you need to serialize in an Xml document where element become tables and attribute columns say for example?
<MyDatabase> <elem1 a="1"/> <Elem1 A="1"/> </MyDatabase>

How can one get this into a Postgres database where the db name is MyDatabase with two tables "elem1" and "Elem1"?
Create Database MyDatabase...
Create Table elem1( a int);
Create Table Elem1( A int);
Given my code is at the mercy of the xml author I have no control over the authored xml. Nothing else I've seen which is case sensitive plays with the names to cause bugs in user code…

How does one correctly handle serializing the sample xml above into a case sensitive database?

[ZooDoo4U]

Sorry for the noise, seems if name for each of the database, schema, table are quotes i.e.

create [database|schema|table] "CaseSensitiveName"

Postgres will honor the casing as expected, but one really has to wonder why this isn't the default behavior if in fact Postgres by default is case sensitive... Really feels broken in this regard...

[howientc]

I've found a simple solution to keeping mixed case that doesn't require modifying the connector source. I am running version 3.1.0 of loopback-connector-postgresql

Example: In a boot script (server/boot/z_my_script.js):

// First get my DataStore that runs on postgres:
const server = require('../server');
const ds = server.dataSources.myDataSource;

// override PostgreSQL.prototype.dbName in postgresql.js: 
ds.connector.dbName = name => name // just use the name as-is!

I also have a call to DataSource.discoverAndBuildModels which I used to generate a model for a database view. That, too, has issues with case that can be solved by explicitly passing nameMapper:null as below:

ds.discoverAndBuildModels(v.name, {visited: {}, associations: true, nameMapper: null})

(I also posted this to issue 116)