loopback:connector:postgresql error: relation "public.customer" does not exist

2,419 views
Skip to first unread message

Nolan Dubeau

unread,
Oct 27, 2014, 1:53:02 PM10/27/14
to loopb...@googlegroups.com
Hello,

I'm using the loopback_postgresql connector and I used Strong-Studio to generate the model for my Customer table. I did not change any of the properties besides indicating what field was the ID.

I ran the app with DEBUG on the connectors like so:  NODE_ENV=staging DEBUG=loopback:connector:* slc run, and when the connector is executed I see the following:

loopback:connector:postgresql SQL: SELECT "id", "customername", "customerkey", "website", "twitter", "created", "modified", "approved", "active", "measurement", "eula"  FROM "public"."customer"   ORDER BY "id" +10s

Note: FROM "public"."customer"

When I fire up Loopback explorer and do a call to get all customers I get the following error:

loopback:connector:postgresql error: relation "public.customer" does not exist
    at Connection.parseE (/Library/WebServer/Documents/myapp/node_modules/loopback-connector-postgresql/node_modules/pg.js/lib/connection.js:534:11)
    at Connection.parseMessage (/Library/WebServer/Documents/myapp/node_modules/loopback-connector-postgresql/node_modules/pg.js/lib/connection.js:361:17)
    at Socket.<anonymous> (/Library/WebServer/Documents/myapp/node_modules/loopback-connector-postgresql/node_modules/pg.js/lib/connection.js:105:22)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:764:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:426:10)
    at emitReadable (_stream_readable.js:422:5)
    at readableAddChunk (_stream_readable.js:165:9)
    at Socket.Readable.push (_stream_readable.js:127:10)

I do not have any relations defined in my model.  Does this error have something to do with the schema of my postgres database?

Here is my model definition:


{
  "name": "Customer",
  "idInjection": false,
  "postgresql": {
    "schema": "public",
    "table": "Customer"
  },
  "properties": {
    "id": {
      "type": "Number",
      "id": true,
      "required": true,
      "length": null,
      "precision": 64,
      "scale": 0,
      "postgresql": {
        "columnName": "id",
        "dataType": "bigint",
        "dataLength": null,
        "dataPrecision": 64,
        "dataScale": 0,
        "nullable": "NO"
      }
    },
    "resellerid": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 64,
      "scale": 0,
      "postgresql": {
        "columnName": "resellerID",
        "dataType": "bigint",
        "dataLength": null,
        "dataPrecision": 64,
        "dataScale": 0,
        "nullable": "YES"
      }
    },
    "customername": {
      "type": "String",
      "required": false,
      "length": 75,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "customerName",
        "dataType": "character varying",
        "dataLength": 75,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },
    "customerkey": {
      "type": "String",
      "required": false,
      "length": 50,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "customerKey",
        "dataType": "character varying",
        "dataLength": 50,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "addressid": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "addressID",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "YES"
      }
    },
    "website": {
      "type": "String",
      "required": false,
      "length": 100,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "website",
        "dataType": "character varying",
        "dataLength": 100,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "twitter": {
      "type": "String",
      "required": false,
      "length": 25,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "twitter",
        "dataType": "character varying",
        "dataLength": 25,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "created": {
      "type": "String",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "created",
        "dataType": "timestamp without time zone",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },
    "modified": {
      "type": "String",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "modified",
        "dataType": "timestamp without time zone",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },
    "approved": {
      "type": "String",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "approved",
        "dataType": "timestamp without time zone",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "active": {
      "type": "Boolean",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "active",
        "dataType": "boolean",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },
    "brandingid": {
      "type": "Number",
      "required": false,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "brandingID",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "YES"
      }
    },
    "measurement": {
      "type": "String",
      "required": false,
      "length": 20,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "measurement",
        "dataType": "character varying",
        "dataLength": 20,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "eula": {
      "type": "String",
      "required": false,
      "length": 255,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "eula",
        "dataType": "character varying",
        "dataLength": 255,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": []
}


Thank you!  any help is appreciated.

Nolan

Raymond Feng

unread,
Oct 27, 2014, 2:31:25 PM10/27/14
to Nolan Dubeau, loopb...@googlegroups.com
Postgresql calls a table as relation :-(. Can you check if you have public.customer table? I expect public.Customer as it is the customized table name.

Thanks,

---
Raymond Feng
Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Nolan Dubeau

unread,
Oct 27, 2014, 2:53:05 PM10/27/14
to loopb...@googlegroups.com, nolan...@gmail.com
Hi Raymond,

Thanks for your reply.  I have public.Customer (with an uppercase C)

Nolan

Nolan Dubeau

unread,
Oct 27, 2014, 3:04:15 PM10/27/14
to loopb...@googlegroups.com
Hi Raymond,

I did a rename of the table to lowercase 'customer' and that error went away.

But...

The fields are camelCase in the database and the new error we receive is that 'resellerid' (lowercase) could not be found.

It appears that the connector is expecting table names and column names to all be lowercase, however the model config allows you to specify otherwise.  

Luckily we have the ability to change the case of the table and field names, but we would prefer not to have to do this.  Does this appear to be a bug in the connector?

Thanks.

Nolan

Patrick Barnes

unread,
Nov 18, 2014, 11:00:56 AM11/18/14
to loopb...@googlegroups.com
This is a problem for me, too, even when I specify the PG column name in the properties:

{
  "name": "SomeItem",
  "dataSource": "myitemsdb",
  "base": "PersistedModel",
  "idInjection": false,
  "public": true,
  "strict": true,
  "options": {
    "postgresql": {
      "table": "someItems"
    }
  },
  "properties": {
    "id": {
      "type": "number",
      "required": true,
      "generated": true,
      "id": true,
      "postgresql": {
        "columnName": "someItemId",
        "dataType": "integer",
        "dataLength": 32,
        "dataPrecision": null,
        "dataScale": 0,
        "nullable": "NO"
      }
    },
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": []
}

When I try to GET via the Explorer it throws this error: "column \"somestuffid\" does not exist

This is a showstopper, but I really want to use Loopback with PostgreSQL if possible. Can anyone offer a work-around? I've tried to troll the source code, but I can't see where lower case is being forced.

Felipe Figueroa

unread,
Nov 18, 2014, 11:14:02 AM11/18/14
to loopb...@googlegroups.com
What happend when you query your table from psql or pgadmin or whatever?

I believe that you should be able to get

SELECT someitemid FROM someitems;
SELECT someItemId FROM someItems;
SELECT SOMEITEMID FROM SOMEITEMS;

unless you created the table using double quotes for the column definitions

Patrick Barnes

unread,
Nov 18, 2014, 11:39:55 AM11/18/14
to loopb...@googlegroups.com
OK, I figured it out. I needed to remove the use of toLowerCase() in lines 610-635 of postgresql.js in the loopback-connector-postgresql module:

/*!
 * Convert to the Database name
 * @param {String} name The name
 * @returns {String} The converted name
 */
PostgreSQL.prototype.dbName = function (name) {
  if (!name) {
    return name;
  }
  // PostgreSQL default to lowercase names
  return name;
  //return name.toLowerCase();
};

/*!
 * Escape the name for PostgreSQL DB
 * @param {String} name The name
 * @returns {String} The escaped name
 */
PostgreSQL.prototype.escapeName = function (name) {
  if (!name) {
    return name;
  }
  return '"' + name.replace(/\./g, '"."') + '"';
  //return '"' + name.replace(/\./g, '"."').toLowerCase() + '"';
};

Everything now works. I'm tailing the PG log and I can see proper SQL generated, and results are returned via Explorer.

I'll open an issue in the GitHub repo.

Abdul Wahid

unread,
Jun 9, 2017, 2:52:16 AM6/9/17
to LoopbackJS
Hi Patrick Barnes,

I am still getting the same issue even I am using loopback 3.x and all latest library. 
Please let me know if you have merged the its related PR in its connector repo.

Thanks,
Abdul Wahid
Reply all
Reply to author
Forward
0 new messages