Adding CURRENT_TIMESTAMP as a "DEFAULT" and "ON UPDATE" for createdAt, updatedAt on migrations #4896

dancollingridge · 2015-11-20T17:38:43Z

I am using migrations to create and update my MySQL DB schemas. I am using underscored versions of createdAt, updatedAt. Here is what I have in my migration file:

  created_at: {
    allowNull: false,
    type: Sequelize.DATE
  },
  updated_at: {
    allowNull: false,
    type: Sequelize.DATE
  }

When I run the migration, I would expect "DEFAULT" to be set to CURRENT_TIMESTAMP for "created_at" and "ON UPDATE" to be set to CURRENT_TIMESTAMP for "updated_at", but it doesn't happen.

So, I am trying to set them myself. I have tried adding:

defaultValue: Sequelize.NOW
updatedAt: Sequelize.NOW
defaultValue: Sequelize.fn('NOW')
updatedAt: Sequelize.fn('NOW')
defaultValue: 'CURRENT_TIMESTAMP'
updatedAt: 'CURRENT_TIMESTAMP'
default: Sequelize.NOW

...all to no avail. I can't find any way to get "DEFAULT CURRENT_TIMESTAMP" or "ON UPDATE CURRENT_TIMESTAMP" to run in the query.

I know that sequelize handles the create and update times automatically, but it would be nice to be able to also put these in the raw SQL to allow SQL to handle them (in the case of manually making edits to the database, etc.)

Thanks!

The text was updated successfully, but these errors were encountered:

mickhansen · 2015-11-21T11:15:04Z

defaultValue: sequelize.literal('CURRENT_TIMESTAMP) might work, not sure though.

dancollingridge · 2015-11-23T17:58:56Z

That worked beautifully! Thanks!

Still trying to figure out the "ON UPDATE CURRENT_TIMESTAMP" case, if you have any ideas.

mickhansen · 2015-11-23T18:11:20Z

Does that work with default value or is it an extra field attribute?

dancollingridge · 2015-11-23T20:07:42Z

It is an "Extra" field attribute.

mickhansen · 2015-11-23T20:33:07Z

Hmmm, then that is likely not possible with native sequelize at the moment.

dancollingridge · 2015-11-23T20:57:42Z

OK, thanks!

shun-tak · 2016-11-25T10:26:27Z

I tried following:

queryInterface.createTable(
    'user',
    {
        'id': {
            type: Sequelize.BIGINT,
            primaryKey: true,
            autoIncrement: true,
        },
        'name': {
            type: Sequelize.STRING,
        },
        'created_at': {
            type: Sequelize.DATE(3),
            defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3)'),
        },
        'updated_at': {
            type: Sequelize.DATE(3),
            defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'),
        },
    },
    {
        engine: 'InnoDB',
        charset: 'utf8mb4',
    }
);

and I got this table:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

My env is Sequelize [Node: 4.3.2, CLI: 2.4.0, ORM: 3.27.0, mysql: ^2.12.0]

Although it is closed :)

Here21 · 2017-09-08T02:40:31Z

@shun-tak Thanks man!

module.exports = function (sequelize, DataTypes) {
  const Model = sequelize.define('TUser', {
    createdAt: {
      type: DataTypes.DATE(3),
      defaultValue: sequelize.literal('CURRENT_TIMESTAMP(3)'),
      field: 'created_at',
    },
    updatedAt: {
      type: DataTypes.DATE(3),
      defaultValue: sequelize.literal('CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'),
      field: 'updated_at',
    },
  }, {
    tableName: 't_user',
    paranoid: true,
  });
  return Model
};

kartik115 · 2017-11-13T07:23:58Z

But how it will act while insertion. Sequelize orm showing error
INSERT INTO user (id,name,created_at,updated_at,user_role_id,company_id) VALUES (DEFAULT,'Kartikeya Mishra',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,1,1);

kartik115 · 2017-11-13T07:29:53Z

SequelizeDatabaseError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON UPDATE CURRENT_TIMESTAMP,1,1)

shun-tak · 2017-11-13T07:48:10Z

@kartik115 Just insert like this:

INSERT INTO user (id,name) VALUES (DEFAULT,'Alice');

or

INSERT INTO user (id,name,created_at,updated_at) VALUES (DEFAULT,'Alice',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);

Please see the MySQL manual.
https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

kartik115 · 2017-11-13T09:52:17Z

@shun-tak I got this error when I was using sequelize orm to create a new user. So it showing me syntax error. I have used
'updated_at': {
type: Sequelize.DATE(3),
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'),
},
So while creating new user its using the default value "ON UPDATE CURRENT_TIMESTAMP" and showing me error.

lvnar · 2017-11-13T19:47:24Z

Same error. Message:

String based operators are now deprecated. Please use Symbol based operators for better security.
SequelizeDatabaseError: syntax error at or near "ON".

I've read about Sequelize operators on querying, but there's nothing abou 'ON'. :(

sshaplygin · 2018-04-23T08:26:00Z

@kartik115 using in sequelize version 3.21.0,
'updated_at': {
type: "TIMESTAMP"
}.
I don't know how, but it is working 🥁

amwill04 · 2018-06-12T15:41:11Z

Anyone coming here in 2018 @shun-tak method no longer works for sequelize v4 when you try to use update method.

queryInterface.createTable(
    'user',
    {
        'id': {
            type: Sequelize.BIGINT,
            primaryKey: true,
            autoIncrement: true,
        },
        'name': {
            type: Sequelize.STRING,
        },
        'created_at': {
            type: Sequelize.DATE(3),
            defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3)'),
        },
        'updated_at': {
            type: Sequelize.DATE(3),
            defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3)'),
        },
    },
    {
        engine: 'InnoDB',
        charset: 'utf8mb4',
    }
);

Basically remove the 'ON UPDATE CURRENT_TIMESTAMP(3)' otherwise this will result in SQL syntax error when you try to update. Like such:

UPDATE Users SET firstName='Bob', updatedAt=CURRENT_TIMESTAMP (3) ON UPDATE CURRENT_TIMESTAMP(3) WHERE id = 1

tomascharad · 2018-07-01T22:11:17Z

@amwill04, Does your solution updates the updatedAt attribute automatically? Or that should be model logic?

brentwashburne · 2018-07-31T23:00:36Z

In version 4.38.0, the file sequelize/lib/dialects/mysql/query-generator.js uses attribute.onUpdate at line 372, but that is inside a block at line 351: if (attribute.references) {. In other words, onUpdate is only used when there is a references attribute too. This is a bug, onUpdate should be allowed without a reference to another model.

cloudge · 2018-11-29T19:04:42Z

It works for me

queryInterface.createTable('User', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
      }
    });

sshaplygin · 2018-11-30T10:12:00Z

@cloudge please, tell about you sequelize version.
i am use v3

Picrrr · 2018-11-30T10:26:09Z

@shun-tak I got this error when I was using sequelize orm to create a new user. So it showing me syntax error. I have used
'updated_at': {
type: Sequelize.DATE(3),
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)'),
},
So while creating new user its using the default value "ON UPDATE CURRENT_TIMESTAMP" and showing me error.

With => "sequelize": "^4.38.0",

On Model :

module.exports = function (sequelize, DataTypes) {
  const Model = sequelize.define('User', {
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      field: 'created_at',
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: DataTypes.NOW,
      onUpdate : DataTypes.NOW,
      field: 'updated_at',
    },
  }, {
    tableName: 'user',
  });
  return Model
};

& On queryInterface for migration !

queryInterface.createTable('user', {
  createdAt: {
	allowNull: false,
	type: Sequelize.DATE,
	defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
  },
  updatedAt: {
	allowNull: false,
	type: Sequelize.DATE,
	defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
	onUpdate : Sequelize.literal('CURRENT_TIMESTAMP'),
  }
});

reticulan · 2019-04-15T14:25:57Z

All variants doesn't work correctly for me in postgres. They are INVOKED (WHY?!) and timestamp is setted to time of model's script execution. Not literal, not simple string 'now', nothing else doesn't work.

shakti-paktolus · 2020-05-13T12:06:29Z

defaultValue: sequelize.literal('CURRENT_TIMESTAMP) might work, not sure though.

Only solution which actually works when updated with both code and DB directly. Kudos.

krushndayshmookh · 2020-08-20T04:22:50Z

sequelize.literal('CURRENT_TIMESTAMP)

this solved my problem. thank you so much!

here's what i did earlier:

created_on: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: "CURRENT_TIMESTAMP(6)"
}

it was solved with:

created_on: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: sequelize.literal("CURRENT_TIMESTAMP(6)")
}

airglow923 · 2021-03-21T14:00:24Z

For those who are still confused, consider the code below:

First, use Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') as mentioned above:

const sample = (sequelize, Sequelize) =>
  sequelize.define("sample", {
    createdAt: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
    },
    updatedAt: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.literal(
        "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
      ),
    },
  });

Then, use COLUMN_NAME: Sequelize.literal("CURRENT_TIMESTAMP") to insert:

const Sample = sample(sequelize, Sequelize);

Sample.create({ updatedAt: Sequelize.literal("CURRENT_TIMESTAMP") }).then(
  (result) => {
    // do whatever
  }
);

sajjadjaved01 · 2021-04-27T18:23:15Z

works for now.

createdAt: {
        allowNull: false,
        type: 'TIMESTAMP',
      },

dishantdave123 · 2022-06-23T11:15:49Z

@kartik115 using in sequelize version 3.21.0, 'updated_at': { type: "TIMESTAMP" }. I don't know how, but it is working 🥁

ERROR: TIMESTAMP is not defined

"dependencies": {
"mysql2": "^2.3.3",
"sequelize": "^6.21.0",
"sequelize-cli": "^6.4.1"
}

siranweb · 2023-02-19T19:04:42Z

Just a quick note who also spent a lot of time as me to make updatedAt field work in PostgreSQL. ON UPDATE trigger works in MySQL, but not in PostgreSQL. Sequelize updates updatedAt field by itself. So, it's would be enough if you just write these lines in your migration:

updatedAt: {
      type: Sequelize.DATE,
      defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
    }

EmileIB · 2023-02-28T15:38:23Z

It works for me

queryInterface.createTable('User', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
      }
    });

what version are you using?

faheel · 2023-10-20T12:12:45Z

The suggestion by @miladabc in #5561 was the only one 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).

See #5561 (comment) for more details

dancollingridge closed this as completed Nov 23, 2015

RobinBuschmann mentioned this issue Feb 21, 2019

Allow full support of mysql timestamps sequelize/sequelize-typescript#544

Closed

rajat12a mentioned this issue May 12, 2019

onUpdate not working #10937

Closed

faheel mentioned this issue Oct 20, 2023

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

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Adding CURRENT_TIMESTAMP as a "DEFAULT" and "ON UPDATE" for createdAt, updatedAt on migrations #4896

Adding CURRENT_TIMESTAMP as a "DEFAULT" and "ON UPDATE" for createdAt, updatedAt on migrations #4896

dancollingridge commented Nov 20, 2015

mickhansen commented Nov 21, 2015

dancollingridge commented Nov 23, 2015

mickhansen commented Nov 23, 2015

dancollingridge commented Nov 23, 2015

mickhansen commented Nov 23, 2015

dancollingridge commented Nov 23, 2015

shun-tak commented Nov 25, 2016 •

edited

Here21 commented Sep 8, 2017 •

edited

kartik115 commented Nov 13, 2017

kartik115 commented Nov 13, 2017

shun-tak commented Nov 13, 2017

kartik115 commented Nov 13, 2017

lvnar commented Nov 13, 2017

sshaplygin commented Apr 23, 2018

amwill04 commented Jun 12, 2018 •

edited

tomascharad commented Jul 1, 2018 •

edited

brentwashburne commented Jul 31, 2018

cloudge commented Nov 29, 2018

sshaplygin commented Nov 30, 2018 •

edited

Picrrr commented Nov 30, 2018 •

edited

reticulan commented Apr 15, 2019

shakti-paktolus commented May 13, 2020

krushndayshmookh commented Aug 20, 2020

airglow923 commented Mar 21, 2021 •

edited

sajjadjaved01 commented Apr 27, 2021

dishantdave123 commented Jun 23, 2022

siranweb commented Feb 19, 2023 •

edited

EmileIB commented Feb 28, 2023

faheel commented Oct 20, 2023

Adding CURRENT_TIMESTAMP as a "DEFAULT" and "ON UPDATE" for createdAt, updatedAt on migrations #4896

Adding CURRENT_TIMESTAMP as a "DEFAULT" and "ON UPDATE" for createdAt, updatedAt on migrations #4896

Comments

dancollingridge commented Nov 20, 2015

mickhansen commented Nov 21, 2015

dancollingridge commented Nov 23, 2015

mickhansen commented Nov 23, 2015

dancollingridge commented Nov 23, 2015

mickhansen commented Nov 23, 2015

dancollingridge commented Nov 23, 2015

shun-tak commented Nov 25, 2016 • edited

Here21 commented Sep 8, 2017 • edited

kartik115 commented Nov 13, 2017

kartik115 commented Nov 13, 2017

shun-tak commented Nov 13, 2017

kartik115 commented Nov 13, 2017

lvnar commented Nov 13, 2017

sshaplygin commented Apr 23, 2018

amwill04 commented Jun 12, 2018 • edited

tomascharad commented Jul 1, 2018 • edited

brentwashburne commented Jul 31, 2018

cloudge commented Nov 29, 2018

sshaplygin commented Nov 30, 2018 • edited

Picrrr commented Nov 30, 2018 • edited

reticulan commented Apr 15, 2019

shakti-paktolus commented May 13, 2020

krushndayshmookh commented Aug 20, 2020

airglow923 commented Mar 21, 2021 • edited

sajjadjaved01 commented Apr 27, 2021

dishantdave123 commented Jun 23, 2022

siranweb commented Feb 19, 2023 • edited

EmileIB commented Feb 28, 2023

faheel commented Oct 20, 2023

shun-tak commented Nov 25, 2016 •

edited

Here21 commented Sep 8, 2017 •

edited

amwill04 commented Jun 12, 2018 •

edited

tomascharad commented Jul 1, 2018 •

edited

sshaplygin commented Nov 30, 2018 •

edited

Picrrr commented Nov 30, 2018 •

edited

airglow923 commented Mar 21, 2021 •

edited

siranweb commented Feb 19, 2023 •

edited