DEV Community

Maciek Chmura
Maciek Chmura

Posted on Edited on Originally published at maciek.cloud

Sequelize decimal type error

Sequelize is probably the most popular ORM for Express. It helped me to quickly start with a NodeJS server and a Postgres database in my current side project.
Unfortunately I encountered a strange issue when I wanted to introduce decimal numbers to one of my models.

Sequelize in version 5.21.3 has an error with decimal type.

My model looked like this:

module.exports = (sequelize, DataTypes) => {
  const incomeExpense = sequelize.define(
    'incomeExpense',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      value: DataTypes.DECIMAL(10, 2),
      description: DataTypes.STRING,
      date: DataTypes.DATEONLY,
      type: DataTypes.STRING
    },
    { freezeTableName: true }
  );
  incomeExpense.associate = function(db) {
    incomeExpense.belongsTo(db.stage);
  };
  return incomeExpense;
};

Data in Postgres:

id value description date type stageId
6 120.00 invoice 1 2019-11-11 income 3
7 120.33 invoice 2 2019-11-11 income 3

JSON response:

[
    {
        "id": 6,
        "value": "120.00",
        "description": "invoice 1",
        "date": "2019-11-11",
        "type": "income",
        "createdAt": "2019-11-10T23:00:00.000Z",
        "updatedAt": "2019-11-10T23:00:00.000Z",
        "stageId": 3
    },
    {
        "id": 7,
        "value": "120.33",
        "description": "invoice 2",
        "date": "2019-11-11",
        "type": "income",
        "createdAt": "2020-02-06T16:41:36.868Z",
        "updatedAt": "2020-02-06T16:41:36.868Z",
        "stageId": 3
    }
]

The returned value is a string type.

I thought that the conversion from a number to a string heppens somewhere in Node or React. As it turns out, it is the model itself.

Model after changing the value to DataTypes.FLOAT:

module.exports = (sequelize, DataTypes) => {
  const incomeExpense = sequelize.define(
    'incomeExpense',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      // value: DataTypes.DECIMAL(10, 2),
      value: DataTypes.FLOAT,
      description: DataTypes.STRING,
      date: DataTypes.DATEONLY,
      type: DataTypes.STRING
    },
    { freezeTableName: true }
  );
  incomeExpense.associate = function(db) {
    // associations can be defined here
    incomeExpense.belongsTo(db.stage);
  };
  return incomeExpense;
};

Postgres dropped the trailing zeros:

id value description date type stageId
6 120 invoice 1 2019-11-11 income 3
7 120.33 invoice 2 2019-11-11 income 3

And now the value in response is a number:

[
    {
        "id": 6,
        "value": 120,
        "description": "invoice 1",
        "date": "2019-11-11",
        "type": "income",
        "createdAt": "2019-11-10T23:00:00.000Z",
        "updatedAt": "2019-11-10T23:00:00.000Z",
        "stageId": 3
    },
    {
        "id": 7,
        "value": 120.33,
        "description": "invoice 2",
        "date": "2019-11-11",
        "type": "income",
        "createdAt": "2020-02-06T16:41:36.868Z",
        "updatedAt": "2020-02-06T16:41:36.868Z",
        "stageId": 3
    }
]

This issue is opened, and there is an ongoing discussion:
link

For now, I can't think of a better fix than Changing a DECIMAL to a FLOAT.

Top comments (0)