Defining a default ON UPDATE behavior for models, such as ON UPDATE CURRENT_TIMESTAMP

[brennanMKE]

I want tables created with the definitions below but I do not see how I can do that with Sequelize when these columns are automatically defined. This is also being done with a MySQL database.

The reason I want it defined in the database is because multiple apps use this database and not all are built with Sequelize. And I want to enforce these default values. Is there a way to have Sequelize create tables with the schema shown below? Or would an update after the tables are created be necessary to older these columns?

`createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

I am looking through the docs and I do not see how these columns would be created as I intend.

[brennanMKE]

I created a solution which is run after sync so that all tables are checked for the createdAt and updatedAt columns having missing defaults and altering those tables as needed. If the queries return 1 for the count it indicates the defaults are not set. Then the alter statements are used to set them.

Now other apps which use the same database without using Sequelize will benefit from these defaults.

I do not know if this kind of change would be accepted with the Sequelize code base. I found a couple of related issues in the past couple of years.

Queries

SELECT COUNT(*) AS `count`
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='experiment' 
AND `COLUMN_NAME` = 'createdAt' 
AND `COLUMN_DEFAULT` IS NULL
AND `TABLE_NAME`='users';

SELECT COUNT(*) AS `count`
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='experiment' 
AND `COLUMN_NAME` = 'updatedAt' 
AND `COLUMN_DEFAULT` IS NULL
AND `EXTRA` = ''
AND `TABLE_NAME`='users';

Alter Table

ALTER TABLE `users` MODIFY COLUMN `createdAt` DATETIME NOT NULL DEFAULT NOW();
ALTER TABLE `users` MODIFY COLUMN `updatedAt` DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW();

[mickhansen]

If defaultValue does not currently support sequelize.literal() it should probably be updated to do so.

[krmannix]

This would need to be changed in the way that a model is defined. One suggestion is

var user = sequelize.define('user', { /* attributes */ }, {
  timestamps: true,
  createdAt: {
    field: 'beginTime',
    defaultValue: sequelize.literal('NOW()'),
  },
});

but I believe that's a change that will break current functionality with insert/update queries (haven't run any tests yet). @mickhansen Is there another preferred way to add the option of a defaultValue or other options to the autogenerated timestamp fields, if this is something that should be supported?

[janmeier]

Adding createdAt as a field with a literal default value should work just fine

var user = sequelize.define('user', {
  createdAt: {
    type: DataTypes.DATE,
    field: 'beginTime',
    defaultValue: sequelize.literal('NOW()')
  }
}, {
  timestamps: true,

});

[Here21]

hi guys, if you need to set update on ON UPDATE CURRENT_TIMESTAMP, look below

module.exports = function (sequelize, DataTypes) {
  const Model = sequelize.define('TUser', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      unique: true,
      field: 'id',
    },
    'created_at': {
      type: DataTypes.DATE(3),
      defaultValue: sequelize.literal('CURRENT_TIMESTAMP(3)'),
    },
    'updated_at': {
      type: DataTypes.DATE(3),
      defaultValue: sequelize.literal('CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'),
    },
  }, {
    timestamps: true,
    tableName: 't_user',
    paranoid: true,
    underscored: true,
  });
  return Model
};

[mysuf]

@Here21 It inserts CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)') as value (default) and throws error as it is not valid DATETIME value. v4.38

[Here21]

@mysuf What's your MySQL version?

[mysuf]

@Here21 mysql-server 5.7.24

[Here21]

@mysuf
I haven't a clear answer, but you should ensure your database table created by the Sequelize, and check the property of the table where updated_at it is the correct value "is Timestamp, not DateTime"

if above you had checked and no problem, I guess the problem happens at the Sequelize version.

[ustun]

The following works for me as suggested above: This doesn't work properly. See below

    createdAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal('NOW()')
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.literal('NOW() ON UPDATE NOW()')
    }

However, I believe the defaults could be changed to this, so maybe this issue needs to be re-opened. Is the difficulty there to track the exact sql invocation for each database?

Edit: hmm, spoke too soon, it works when syncing, it fails as it did for @mysuf above at #5561 (comment)

The problem seems to be the following: Sequelize doesn't distinguish bt the defaultValue that should be provided when doing the table syncing vs the value that should be sent to db during inserts.

Should we reopen this issue?

[miladabc]

Meanwhile it is possible to solve this with the following:

createdAt: {
  type: 'DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)'
},
updatedAt: {
  type: 'DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'
}

I don't know if it's the correct way by the way.

[tcdeoliveira]

this worked for me

 createdAt: {
        allowNull: false,
        type: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
      },
 updatedAt: {
        allowNull: false,
        type: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
      }

[faheel]

I tried multiple solutions that were suggested in this thread and also in #4896, but what @miladabc suggested was the only thing that worked both in the app and the DB (i.e. no matter where the entry was inserted/updated, the right timestamps were always set).

Also wanted to add that if you are using Model.create() and also need the timestamps in its response, then you'll also need to add defaultValue as a JS function like this:

createdAt: {
    type: 'DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) NOT NULL',
    field: 'created_at',
    defaultValue: () => new Date(),
},
updatedAt: {
    type: 'DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) NOT NULL',
    field: 'updated_at',
    defaultValue: () => new Date(),
},

This way Sequelize will not inject it in type since it's a JS function, whereas if it's instead defined using Sequelize.literal() or Sequelize.fn() then it will get injected in the CREATE TABLE query that Sequelize generates on sync or during migrations, which would fail.