sails.js -postgresql returning string value instead of integer for bigint fields

Question

We are migrating a project from PHP to Node.js using Sails.js as the backend framework. We cannot modify our database and have to use the existing database for this project.

If I keep the migrate: "alter" for a newly created model, Sails by default will keep the id field as an integer.

However, for our existing database, id fields are mostly bigint. So I defined migrate: "safe" and proceeded with the model creation.

Now the problem I am facing is that when blueprint routes return the result, the id column value, which should be returned as a number, is being returned as a string instead. Here is an example:

[
  {
    "starttime": "07:00:00",
    "endtime": "14:00:00",
    "id": "1"
  },
  {
    "starttime": "14:00:00",
    "endtime": "22:00:00",
    "id": "2"
  },
  {
    "starttime": "22:00:00",
    "endtime": "07:00:00",
    "id": "3"
  }
]

How can I fix this issue?

Here is my model:

module.exports = {
  tableName: "timeslots",
  autoCreatedAt: false,
  autoUpdatedAt: false,
  attributes: {
    starttime: { type: "string", required: true },
    endtime: { type: "string", required: true }
  }
};

And here is the postgresql table definition

                                              Table "public.timeslots"
  Column   |  Type  |                       Modifiers                        | Storage  | Stats target | Description 
-----------+--------+--------------------------------------------------------+----------+--------------+-------------
 id        | bigint | not null default nextval('timeslots_id_seq'::regclass) | plain    |              | 
 starttime | text   | not null                                               | extended |              | 
 endtime   | text   | not null                                               | extended |              | 
Indexes:
    "idx_43504_primary" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "doctortimeslot" CONSTRAINT "doctortimeslot_ibfk_2" FOREIGN KEY (timeslot_id) REFERENCES timeslots(id) ON UPDATE CASCADE ON DELETE CASCADE

score 4 · Accepted Answer · answered Oct 03 '15 at 08:29

4

Waterline gets weird with datatypes it doesn't have built in. I think it defaults to strings when it's not sure what to do. This shouldn't really matter as JS will automatically coerce these values into numbers on your frontend.

However, if you need it to be a number the simplest solution would probably be to override the toJSON method in the model and have it force that value to an integer.

module.exports = {
  tableName: "timeslots",
  autoCreatedAt: false,
  autoUpdatedAt: false,
  attributes: {
    starttime: { type: "string", required: true },
    endtime: { type: "string", required: true },

    toJSON: function(){
      var obj = this.toObject();
      obj.id = parseInt(obj.id);
      return obj;
    }

  }
};

answered Oct 03 '15 at 08:29

Lenny

5,663
2
19
27

Perfect! overriding the toJSON method would be an ideal solution in my case since the API is to be consumed by native android and iOS applications. Thanks! – Mandeep Singh Oct 03 '15 at 09:19
By any means is it possible to override this method for all models without having to explicitly specify in each model ? – Mandeep Singh Oct 03 '15 at 09:21
Found it. Specified in the model.js file `attributes: { toJSON: function(){ var obj = this.toObject(); obj.id = parseInt(obj.id); return obj; } }` – Mandeep Singh Oct 03 '15 at 09:22
I'm not sure... If so you'd do it in `config/models.js`. I don't know if you can define attributes in there or not. – Lenny Oct 03 '15 at 09:23

score 0 · Answer 2 · answered Oct 29 '15 at 22:55

As an alternative you can use https://github.com/mirek/node-pg-safe-numbers which deals exactly with this problem by delegating unsafe handling to you (when number doesn't fit 2^53 javascript limit) - where you can return parsed value, string, null, throw an error or do something else.

In many cases you can use auto-parsing provided by the library and in the unsafe handler just return the original string value. Then in the code that is using numbers above 2^53 (ie. random big numbers) always cast to string and you'll be fine.

sails.js -postgresql returning string value instead of integer for bigint fields

2 Answers2