how should i store a price in mongoose?

Question

I'm using mongoose schemas for node.js along with express-validator (which has node-validator santiziations and validators).

What's a good way to store price for an item?

I currently have

var ItemSchema = new Schema({
    name            : { type: String, required: true, trim: true }
    , price             : Number
});

Price is optional, so I have:

  if ( req.body.price ) {
    req.sanitize('price').toFloat();
    req.assert('price', 'Enter a price (number only)').isFloat();
  }

express-validator gives me isNumeric (allows 0 padding), isDecimal, and isInt...I'd rather just convert to decimal and strip all characters, so I'm always inserting 42.00 into db.

I want to allow them to enter $42.00, $42, 42, 42.00 and just store 42.00. How can I accomplish this? and still validate that I'm seeing something resembling a number, for example if they enter 'abc' I want to throw an error back to the form using req.assert.

Also, I suppose currency will eventually become an issue...

Update, I found this post which says to store price as integer in cents, so 4200 https://dba.stackexchange.com/questions/15729/storing-prices-in-sqlite-what-data-type-to-use

I just need a way to convert 4200 to $42.00 when I call item.price and also sanitize and convert the input into 4200.

Answer 1

This is what I ended up doing...

I stored price as cents in database, so it is 4999 for 49.99 as described here: https://dba.stackexchange.com/questions/15729/storing-prices-in-sqlite-what-data-type-to-use

the getPrice will convert it back to readable format, so I can use item.price in my views w/o modifying it.

the setPrice converts it to cents.

model:

var ItemSchema = new Schema({
    name            : { type: String, required: true, trim: true }
    , price             : {type: Number, get: getPrice, set: setPrice }
});

function getPrice(num){
    return (num/100).toFixed(2);
}

function setPrice(num){
    return num*100;
}

I opted to only allow digits and decimal in price field, without $. So they can enter 49, 49.99, 49.00, but not 49.0 or $49

validation using regex:

if ( req.body.price ) {
    req.assert('price', 'Enter a price (numbers only)').regex(/^\d+(\.\d{2})?$/);
}

I wish there was a way to allow the $ because I think its a usability issue, just let the user enter it, but strip it off. I'm not sure how to do that and still validate that we have a price and not a bunch of letters for example.