Create timestamps in a Sequelize migration

Question

I'm using SequelizeJS for my ORM.

I have a "Video" model. This model uses the "Videos" table.

How can I create a migration that includes timestamps? Do I need to define my own timestamp columns, or is there a shortcut?

In /migrations/123412341234-create-videos-table.js

'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    queryInterface.createTable(
      'Videos',
      {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        title: {
          type: Sequelize.STRING,
          allowNull: false,
          unique: true
        },
        author: {
          type: Sequelize.STRING,
          allowNull: false
        },
        videoUrl: {
          type: Sequelize.STRING,
        },
        coverUrl: {
          type: Sequelize.STRING,
        }
      }
    );
  },

  down: function (queryInterface, Sequelize) {
    queryInterface.dropTable('Videos');
  }
};

In /models/video.js

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('Video', {
    title: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    author: {
      type: DataTypes.STRING,
      allowNull: false
    },
    videoUrl: {
      type: DataTypes.STRING,
    },
    coverUrl: {
      type: DataTypes.STRING,
    }
  });
}

In /models/index.js (this is the default created by running $ sequelize init)

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(module.filename);
var env       = process.env.NODE_ENV || 'development';
var config    = require(__dirname + '/../config/config.json')[env];
var db        = {};

if (config.use_env_variable) {
  var sequelize = new Sequelize(process.env[config.use_env_variable]);
} else {
  var sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf('.') !== 0) && (file !== basename);
  })
  .forEach(function(file) {
    if (file.slice(-3) !== '.js') return;
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Answer 1

TLDR: sequelize 6, umzug, typescript, no hooks needed.

I prefer using Umzug with Sequelize. Umzug actually powers the sequelize-cli, but is more flexible and provides typescript support.

My migration file looks is provided below. You can see I manually define the createdAt field:

// src/db/migrations/10_visited_users.ts
import {DataTypes} from 'sequelize';
import {Migration, MigrationParams} from '../../types/db.migrations';

export const up: Migration = async ({context: queryInterface}: MigrationParams) => {
  await queryInterface.createTable('VisitedUsers', {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
      allowNull: false,
    },

    project: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    server: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    uid: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    level: {
      type: DataTypes.INTEGER,
    },

    name: {
      type: DataTypes.STRING,
    },

    country: {
      type: DataTypes.STRING,
    },

    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
    },
  });

  await queryInterface.addIndex('VisitedUsers', ['id'], {unique: true});

  await queryInterface.addIndex('VisitedUsers', ['createdAt'], {using: 'BTREE'});
};

export const down: Migration = async ({context: queryInterface}: MigrationParams) => {
  await queryInterface.dropTable('VisitedUsers');
};

module.exports = {up, down};

My model file listed below. Please notice how I manage createdAt and disable updatedAt since I dont need it (otherwise you will face an error on queries because by default sequelize expect both createdAt and updatedAd to be present):

// src/db/models/VisitedUsers.model.ts
import {DataTypes, Sequelize} from 'sequelize';
import {ModelDefined} from 'sequelize/types/model';
import {VisitedUserRecord} from '../../types/db';

const VisitedUsersModel = (sequelize: Sequelize): ModelDefined<
  VisitedUserRecord,
  VisitedUserRecord
> => {
  return sequelize.define('VisitedUsers', {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
      allowNull: false,
    },

    project: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    server: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    uid: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    level: {
      type: DataTypes.INTEGER,
    },

    name: {
      type: DataTypes.STRING,
    },

    country: {
      type: DataTypes.STRING,
    },

    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
      comment: 'Дата создания записи',
    },
  }, {
    updatedAt: false,
  });
};

export default VisitedUsersModel;

Attaching the types I used in migration files because I have struggled to make it work:

// src/types/db.migrations.d.ts
import {QueryInterface} from 'sequelize';
import {Umzug} from 'umzug';

type Migration = typeof Umzug.prototype._types.migration;
type MigrationParams = {context: QueryInterface};

export {Migration, MigrationParams};

Answer 2

When using migrations, the primary keys, updatedAt and createdAt fields are not created for you. Instead, you should make sure that you create these columns in your migration.

For the updatedAt and createdAt columns to update automatically, you can use hooks in the model.

I.e:

module.exports = function (sequelize, DataTypes) {

    var Person = sequelize.define('Person', {
        id: {
            type: DataTypes.INTEGER,
            primary: true,
            autoincrement: true
        },
        name: DataTypes.STRING,
        updatedAt: DataTypes.DATE,
        createdAt: DataTypes.DATE
    }, {
        hooks: {
           beforeCreate: function (person, options, fn) {
               person.createdAt = new Date();
               person.updatedAt = new Date();
               fn(null, person);
           },
           beforeUpdate: function (person, options, fn) {
               person.updatedAt = new Date();
               fn(null, person);
           }
       }
    });

    return Person;
}

^Going off the top of my head and not tested!