Creating a new instance that updates others, Sequelize, NodeJs, Express, Postgres-CodePudding

I'm using Nodejs, Express and Sequelize. I have a Postgres table that named Categories with a viewOrder field :

module.exports = (sequelize, DataTypes) => {
    const Category = sequelize.define(
        'Category',
        {
            name: DataTypes.STRING,
            viewOrder: {
                type: DataTypes.INTEGER,
                allowNull: false,
                unique: true
            }
        },
        {}
    );
    return Category;
};

Inserting a new record may bring problems with that, because, if the existing records have view orders of, say, [1, 2, 3, 4, 5], and the new record has an order view of 4, then the current 4 must be changed to 5, and the current 5 must be changed to 6

(I’m forcing this order views to be consecutive numbers, from the front-end side)

I guess that a simple Category.create won't suffice

Updating may also cause problems, if I want to change the record with viewOrder of 5, to a viewOrder of 3, then the records with view orders of 3 and 4, must also be changed, along with the one we are updating

How can I do that in Sequelize?

Thanks in advance,

Rafael

CodePudding user response：

It's an awful idea to rely on frontend to maintain unique values of viewOrder. The same goes for Sequelize. The most reliable way is to generate such unique values by PostgreSQL itself either creating a sequence (if you don't need to reuse generated values) or adding a trigger(s) on Category table.