如何在Sequelize中配置DECIMAL类型以保留两位小数存储？

Hey there! I've run into this exact issue before with Sequelize and MySQL decimal columns—let's get it sorted out so you can store values like 12.26 correctly.

First, the Correct Model Configuration #

The error you saw with DataTypes.DECIMAL(10,2) is likely tied to your Sequelize version. Here are two valid approaches depending on which version you're using:

For Sequelize v6+ (Modern Versions)

You can directly pass precision and scale as arguments to DECIMAL—this should work without errors:

itemPrice: {
  type: DataTypes.DECIMAL(10, 2), // 10 total digits, 2 after the decimal point
  allowNull: false,
  field: 'itemPrice'
}

For Sequelize v5 or Earlier

Older versions require specifying precision and scale as separate properties in the column definition:

itemPrice: {
  type: DataTypes.DECIMAL,
  allowNull: false,
  field: 'itemPrice',
  precision: 10, // Total number of digits
  scale: 2       // Number of digits after the decimal point
}

Critical: Update Existing Table Structure #

If your table was already created with the default decimal(10,0) type, just modifying the model won't change the existing column. You need to create a migration to alter the column (avoid using sync({ force: true })—it will wipe your data!).

1. Generate a migration file:

npx sequelize-cli migration:generate --name update-itemPrice-decimal-type

2. Edit the migration file to modify the column:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Alter the column to use decimal(10,2)
    await queryInterface.changeColumn('your_table_name', 'itemPrice', {
      type: Sequelize.DECIMAL(10, 2),
      allowNull: false
    });
  },

  down: async (queryInterface, Sequelize) => {
    // Revert back to decimal(10,0) if needed
    await queryInterface.changeColumn('your_table_name', 'itemPrice', {
      type: Sequelize.DECIMAL(10, 0),
      allowNull: false
    });
  }
};

Replace your_table_name with the actual name of your table.

3. Run the migration to apply the change:

npx sequelize-cli db:migrate

Quick Check for Data Insertion #

When saving data, make sure you're passing a numeric value (not a string) to avoid unintended rounding:

// Correct: Pass a number
await YourModel.create({ itemPrice: 12.26 });

// Avoid: Passing a string (while it might work, it's less reliable)
// await YourModel.create({ itemPrice: '12.26' });

After following these steps, your itemPrice column should correctly store decimal values without rounding them off.

内容的提问来源于stack exchange，提问作者weeraa