sequelize: Sequelize returns a string for decimal types

This is an issue with mysql2 returning decimals as a string to preserve precision: https://github.com/sequelize/sequelize/issues/7465

If you are sure that your data won’t run into precision issues you can set dialectOptions: { decimalNumbers: true } when instantiating sequelize to get the previous behavior. Worked for my case.

I finally found a way and ended up doing this with the latest (4.37.6)

Sequelize.postgres.DECIMAL.parse = function (value) { return parseFloat(value); };

using setTypeParser() had no effect.

Since I know all my values are currency style decimals there hopefully won’t be a problem losing any precision. I wish there was a different type of DECIMAL (DECIMAL_2 or DECIMAL_INSECURE perhaps?)

Who thought that returning a number as a string was a great idea? “Lets break functionality for millions of projects in order to make a workaround for one random person who cares about the 25th digit after the decimal point.” Yep… ok then

Setting dialectOptions: { decimalNumbers: true } doesn’t seem to work for Postgres. Is this a MySQL2 only option?

Sequelize is getting more and more developer UNfriendly.

I’d rather just pay for a good library at this point. Is there one? Or is EVERYTHING in this realm open-SORES any more?

Come on, guys… this USED to work just fine. (sigh)

Postgres user here.

Why is this closed? the workarounds are less than ideal for postgres

Any fix for Postgres or is this still ongoing?

As a temporary fix, defining a custom setter also works. It isnt ideal by any means but it works for now!

'quantity' : { 'type' : DataTypes.DECIMAL(10, 2), 'allowNull': true, get() { return parseFloat(this.getDataValue('quantity')); } }

Can’t believe this issue is closed. There NEEDS to be an option to get plain decimal numbers also in postgres.

Why is this closed? the workarounds are less than ideal for postgres

No idea…

Suggested workaround

sequelize.define("user", {
  foo: {
    type: Sequelize.DECIMAL,
    get() {
      // Workaround until sequelize issue #8019 is fixed
      const value = this.getDataValue('foo');
      return value === null ? null : parseFloat(value);
    }
  }
});

If the above workaround is not enough for any of you, please let me know!

This is obviously a problem in Javascript, which doesn’t have a decimal type. People who care that much about the zillionth digit after the decimal point will anyway do the conversion to string on the db server end as a workaround. At some point, Decimal may make it into the javascript spec. Until then, lets please leave numbers as numbers and strings as strings.

A decimal is just a float, at the end of the day. Why in the world would it be coerced into a string? This change broke client code, and the work around requires the devs consuming this library to make changes to untold numbers of uses…? And for commenting about that, I get downvoted? That’s rich, guys. Here’s some friendly criticism; you broke others’ code with this change, and that is 100% not cool. 😦 So yeah, if it means my contract with the actual interface into this data and database abstraction layer remains unchanged, yeah, I’d rather PAY for someone to publish this layer and have actual accountability for breaking my code. Downvoted for THAT stance? That’s pretty sad. 😦

What you said about numeric amounts is exactly what I mean 😃 you should not use float or double to store numeric amounts, but the JavaScript number type is a double (both the SQL double, float, and real, as well as the JS number type are IEEE 754 floating point numbers).

You definitely should use either integers or decimals for that. But representing decimals using the JS number type will lead to the same imprecision issues as if you stored them as doubles in the database.

JavaScript doesn’t have a decimal type (yet), so we return them as strings to prevent any loss of precision. You can then use libraries like bigdecimal to handle them

Similarly, not all bigints values can be represented by the JS number type. Which is why for now they’re returned as strings too. Now that JS does support a bigint type (1n), we’re planning on returning them as js bigints instead (in the next major release)

Of course you’re free to use the JS number type, which is why we’d like to add a flag you can turn on. But we’ll make it opt-in instead of the default 😃

@chornbe You’re not helping. Your frustration is understandable, but berating devs of a free library isn’t productive. If you’re so hot to throw money at the problem, hire a dev to commit some improvements to this library.

In the mean time, there’s another reported issue that addressed this problem for me under 4.x/postgres here: https://github.com/sequelize/sequelize/issues/9074#issuecomment-423271834

This fix didn’t work in v5.x/postgres, so after some digging I ended up with a fix that’s currently working here: https://github.com/sequelize/sequelize/issues/9074#issuecomment-524914844

YMMV - I haven’t had time to test it substantially.

How do you guys solve this issue? (postgres) a) using float instead of decimal for currency is not correct b) the custom getter can reduce the performance c) using parseFloat for each math operation makes the code dirty

I have just updated to the latest version of sequelize and started having the same issue. It’s returning strings for my decimal data types.

if anyone uses sequelize-typescript and postgreSQL this is useful https://www.npmjs.com/package/sequelize-typescript#accessors

I resolve like this

@Column
get price(): number {
  return parseFloat(`${this.getDataValue('price')}`);
}

set price(value: number) {
  this.setDataValue('price', value);
}

I hope this helped anyone

Keep in mind that number is not capable of safely representing DECIMAL or BIGINT (which is why we won’t enable this by default). Another way of fixing your issue is to use DataTypes.INTEGER and DataTypes.DOUBLE instead.

I’m collecting data regarding how to support choosing how a type is represented in JS.

This is what it currently looks like: https://gist.github.com/ephys/50a6a05be7322c64645be716fea6186a#support-for-alternative-js-types

if anyone uses sequelize-typescript and postgreSQL this is useful https://www.npmjs.com/package/sequelize-typescript#accessors

I resolve like this

@Column
get price(): number {
  return parseFloat(`${this.getDataValue('price')}`);
}

set price(value: number) {
  this.setDataValue('price', value);
}

I hope this helped anyone

Care, add a check for null value

  @Column(DataType.DECIMAL(10, 2))
  get price(): number {
    const value = this.getDataValue('price');
    return value === null ? null : parseFloat(value);
  }

  set price(value: number) {
    this.setDataValue('price', value);
  }

@papb just letting you know, when you use {raw: true} the getter function you mentioned is no longer work.

Sequelize version: 5.21.6

Here is a SSCCE for this issue:

const user = sequelize.define("user", { foo: Sequelize.DECIMAL });
(async () => {
  await sequelize.sync({ force: true });
  await user.create({ foo: 12.34 });
  console.log(typeof (await user.findOne()).foo); // string
})();

Fixed for me too. Suggest this workaround is added to here http://docs.sequelizejs.com/manual/tutorial/upgrade-to-v4.html

Hi @ephys thanks for comment on this and all the effort you put on this awesome project. as far as I can see there is a lot of confusion over this topic, but here my comments: your suggestion of use DOUBLE instead of DECIMAL works pretty well, sequelize returns the data type as expected (number), this works while you don’t need a fixed decimal LENGTH.

for example this approach DataTypes.DOUBLE(18,4) PostgresSQL does not support ‘DOUBLE PRECISION’ with LENGTH, there is where the DECIMAL data type becomes important.

There is multiple discussions on stack over flow where encourage people not to use FLOAT for financial operations, instead of that they suggest the usage of DECIMAL data types.

Maybe all of us who ended up here in this issue trying to bring support to DECIMAL data type on sequelize, have a wrong design of the software or perhaps we have misunderstanding of programming data structures.

Anyway, thank you again @ephys for taking the time and document all those workarounds and POCs, even to answer all those issue comments

@papb I’m not sure that solution fits our need.

• Is the underlying field in the DB a string? If so that means a lot of problems when trying to do aggregates or math
• Do I need to add this logic to every INTEGER/DECIMAL field I’m concerned about? I like the granularity and explicitness, but it also feels like a lot of boilerplate and at its root a very unexpected behavior/opinion for sequelize to default to without clear and obvious overrides.

BTW - I hadn’t seen the acronym for SSCCE before - thanks for helping me learn something new! =)

I also had to put it after my models are included (so basically final step). I’m using Sequelize version 4.26.

const env = process.env;
const modelPath = path.resolve(__dirname, "models");
const sequelize = new Sequelize({
  name: env.POSTGRES_DB,
  host: env.POSTGRES_HOST,
  dialect: "postgres",
  username: env.POSTGRES_USER,
  password: env.POSTGRES_PASSWORD
});
sequelize.addModels([modelPath]);
pg.types.setTypeParser(1700, parseFloat);

@deejbee The solution I found:

import * as pg from "pg";
// setup your Sequelize instance
pg.types.setTypeParser(1700, parseFloat);

1700 corresponds to the Decimal type. Using parseFloat like this is only safe if you know your numbers will be within the valid float range.