Set optimistic locking as global option in node , sequelize

Question

I am trying to set up sequelize in my node project and for now I have

//sequelize init
const { DataTypes } = Sequelize;
const sequelize = new Sequelize({
  database: database,
  username: user,
  host: server, 
  password: password,
  dialect: 'mssql',
  dialectOptions: {
    options: {
      useUTC: true,
      dateFirst: 1,
    }
  },
  define:{
      timestamps:false,
      paranoid:false,
      freezeTableName: true
  }
});

//and my Model 
  const User= sequelize.define('User', {
    // attributes
    id: {
      field:'Id',
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true
    } ,
    startTime: {
      field:'startTime',
      type: Sequelize.DATE
    } 
  });

I try to setup version:true to enable Optimistic Locking I put it in model

  const Vessel = sequelize.define('FDMData', {
    // attributes
    id: {
      field:'vesselId',
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true
    } ,
    startTime: {
      field:'startTime',
      type: Sequelize.DATE
    } 
  },{
    version:true
   }
);

and I get Unhandled rejection SequelizeDatabaseError: Invalid column name 'version'.

I also tried to set it as global while init

const { DataTypes } = Sequelize;
const sequelize = new Sequelize({
  database: database,
  username: user,
  host: server, 
  password: password,
  dialect: 'mssql',
  dialectOptions: {
    options: {
      useUTC: true,
      dateFirst: 1,
    }
  },
  define:{
      timestamps:false,
      paranoid:false,
      freezeTableName: true,
      version: true
  }
});

and again, I get Unhandled rejection SequelizeDatabaseError: Invalid column name 'version'.

What am I missing? How can I fix this?

Thanks

Answer 1

When you set version: true and you are creating your database structure manually, sequelize expect to find a column named version on the table : so add a column version INTEGER NOT NULL DEFAULT 0 to your tables. You can also name the versioning column what ever you want, just passe a string version: "myVersionColumn"

If you let sequelize handle the creation of the DB structure, it generate a DDL for the FDMData table that look like

CREATE TABLE IF NOT EXISTS FDMData (
    vesselId INTEGER NOT NULL , 
    startTime DATETIME, 
    version INTEGER NOT NULL DEFAULT 0, 
    PRIMARY KEY (vesselId)
)

and your code work juste fine. For example

Vessel.sync().then(model=> {
// or sequelize.sync()
    console.log(model)
}).catch(error=> {
    console.log(error)
})

Answer 2

you are almost there, based on the docs, setting version to true or set it to whatever name you want do the trick

Enable optimistic locking. When enabled, sequelize will add a version count attribute to the model and throw an OptimisticLockingError error when stale instances are saved. Set to true or a string with the attribute name you want to use to enable.

however, just in the next section to optimistic locking -Database synchronization- it says

When starting a new project you won't have a database structure and using Sequelize you won't need to

meaning, sequelize doesn't depend on a sql structure already set in your database for this purpose if you sync your models after you define them except for the database definition, it will automatically create it for you including the version field, here is an example

const Sequelize = require('sequelize');
const config = {
  username: "root",
  password: "123",
  tableName: "test",
  options: {
      host: '127.0.0.1',
      dialect: 'mysql',
      pool: {
        max: 3,
        min: 1,
        acquire: 30000,
        idle: 10000
      },
      define: {
        timestamps:false,
        paranoid:false,
        freezeTableName: true,
        version: true
      }
    }
};
const sequelize = new Sequelize(config.tableName, config.username, config.password, config.options);

//and my Model 
  const User= sequelize.define('User', {
    // attributes
    id: {
      field:'Id',
      type: Sequelize.INTEGER,
      allowNull: false,
      primaryKey: true
    } ,
    startTime: {
      field:'startTime',
      type: Sequelize.DATE
    } 
  });

User.sync();

if you run this script you will see the following sql statements executed

Executing (default): CREATE TABLE IF NOT EXISTS User (Id INTEGER NOT NULL , startTime DATETIME, version INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (Id)) ENGINE=InnoDB; Executing (default): SHOW INDEX FROM User

however, if you don't want sequelize to sync your models, you have to explicitly have that field in your already established tables, but not explicitly defined in sequelize model as it will automatically know its there.