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

See original GitHub issue

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!

Issue Analytics

State:
Created 8 years ago
Comments:26 (3 by maintainers)

Top GitHub Comments

46reactions

shun-takcommented, Nov 13, 2017

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 😃

36reactions

mickhansencommented, Nov 21, 2015

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

Top Results From Across the Web

Add automatic createdAt and updatedAt timestamps in migration

In this table I want to add the timestamps createdAt and updatedAt that are automatically updated and my migrations works using literals ...

11.2.6 Automatic Initialization and Updating for TIMESTAMP ...

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).

Sequelize timestamps option and format explained

The timestamps option in Sequelize models allows you to add two time-related ... added to your model are createdAt and updatedAt attributes.

Not respecting DEFAULT CURRENT_TIMESTAMP(6).

Normally I use migrations (Phinx) to alter my DB. ... `addedAt` TIMESTAMP(6), `updatedAt` TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), ...

Why $table->timestamps() puts 'ON UPDATE ... - Laracasts

So why the migration code $table->timestamps() puts 'ON UPDATE CURRENT_TIMESTAMP' on the created_at column?