sequelize.js TIMESTAMP not DATETIME

Question

sequelize.js TIMESTAMP not DATETIME

In my node.js app I have several models in which I want to define TIMESTAMP type columns, including the default timestamps created_at and updated_at.

According to sequelize.js' documentation, there is only a DATE data type. It creates DATETIME columns in MySQL.

Example:

var User = sequelize.define('User', {
... // columns
last_login: {
            type: DataTypes.DATE,
            allowNull: false
        },
...
}, { // options
        timestamps: true
});

Is it possible to generate TIMESTAMP columns instead?

mysql
node.js
datetime
timestamp
sequelize.js

Answer 1

What I did with sqLite is extended DataTypes with my custom sql logic for TIMESTAMP and it worked fine. I'm not 100% sure how the sql syntax should look for MySQL but my guess it's something similar to what I have. Look at example:

function (sequelize, DataTypes) {

    var util = require('util');
    var timestampSqlFunc = function () {
        var defaultSql = 'DATETIME DEFAULT CURRENT_TIMESTAMP';
        if (this._options && this._options.notNull) {
            defaultSql += ' NOT NULL';
        }
        if (this._options && this._options.onUpdate) {
            // onUpdate logic here:
        }
        return defaultSql;
    };
    DataTypes.TIMESTAMP = function (options) {
        this._options = options;
        var date = new DataTypes.DATE();
        date.toSql = timestampSqlFunc.bind(this);
        if (!(this instanceof DataTypes.DATE)) return date;
        DataTypes.DATE.apply(this, arguments);
    };
    util.inherits(DataTypes.TIMESTAMP, DataTypes.DATE);

    DataTypes.TIMESTAMP.prototype.toSql = timestampSqlFunc;

    var table = sequelize.define("table", {
        /* table fields */
        createdAt: DataTypes.TIMESTAMP,
        updatedAt: DataTypes.TIMESTAMP({ onUpdate: true, notNull: true })
    }, {
        timestamps: false
    });

};

All you need to do for MySQL is to change SQL type generation in timestampSqlFunc function, so for example defaultSql variable would be 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'

Answer 2

According to the Sequelize Documentation, you can set a defaultValue of Sequelize.NOW to create a timestamp field. This has the effect but relies on Sequelize to actually populate the timestamp. It does not create a "CURRENT_TIMESTAMP' attribute on the table.

var Foo = sequelize.define('Foo', {
    // default values for dates => current time
    myDate: { 
         type: Sequelize.DATE, 
         defaultValue: Sequelize.NOW 
    }
});

So, this does accomplish the end goal of having a timestamp field, but it is controlled through Sequelize and not through the actual database engine.

It also appears to work on databases that do not have a timestamp functionality, so that may be a benefit.

Reference URL: http://sequelize.readthedocs.org/en/latest/docs/models-definition/#definition

Answer 3

This depends on which version of MySQL you are using. Before MySQL 5.6.5(?) the only way to have more than one TIMESTAMP field in a table was to define the structure with default NULL values and manually insert them. ON UPDATE / CURRENT TIMESTAMP would not work.

If you're on MYSQL 5.6.5+ you should be able to define multiple DATETIME or TIMESTAMP fields with defaults / ON UPDATE CURRENT TIMESTAMP.

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html