Specify length and scale parameters of decimal (numeric) data type in waterline orm (sails js)

Question

How to specify decimal(length, scale) as a columnType in waterline orm for a currency column? I could find this question has been already asked. But the answer is not relevant to the question.

This is what the column attribute looks like. I'm using sails-Postgres.

attributes: { 
    price: {
        type: 'number',
        columnType: 'decimal(9,2)',
        columnName: 'PRICE'
       }
     }

In sails documents they have mentioned that

Column types are entirely database-dependent. Be sure that the columnType you select corresponds to a data type that is valid for your database! If you don’t specify a columnType, the adapter will choose one for you based on the attribute’s type.

But when I mentioned columnType: 'decimal(9,2)' (although it supports in Postgres) it does not properly work. When I retrieving data using blueprints it gives the price as a string.

{
    "price": "10.00",
}

I have read that these data types ­string, text, ­integer, float, date, ­dat­etime, ­boolean, ­binary, array, json, ­email are supported by waterline. In that case what would be the best data type to store a currency value? If we choose the float data type, how to store the price as decimal(9,2)?

Any help would be much appriciated. Thank you.

Answer 1

What I do in this case is to specify the definition like so:

 price: {
      type: 'number',
      defaultsTo: 34.99,
  },

Since I'm specifying a default price in here, Waterline will create a float4 column type for that value.

It is also worthy of note that I will be converting that amount to center(by multiplying by 100) so What the database will store will be 3499 I find it okay to store this way because payment providers will be needing a cent amount any ways.

On the conversion, I find it suitable to do in a beforeCreate Waterline lifecycle callback. So something like this.

beforeCreate: async function (valuesToSet, proceed) {
    // Compute amounts
    valuesToSet.price = sails.helpers.convertAmountToCent(valuesToSet.price)

    return proceed()
  },

Also, Waterline set the column type to float4 because it is inferring that from the defaultsTo value of 34.99