[mysql] How to auto generate migrations with Sequelize CLI from Sequelize models?

I have a set of Sequelize models. I want to use migrations, not DB Sync.

Sequelize CLI seems to be able to do this, according to this article: "When you use the CLI for the model generation, you will gain the migration scripts for free as well."

How to auto generate the migrations with Sequelize CLI from existing Sequelize models?

If you don't want to recreate your model from scratch, you can manually generate a migration file using the following CLI command:

sequelize migration:generate --name [name_of_your_migration]

This will generate a blank skeleton migration file. While it doesn't copy your model structure over to the file, I do find it easier and cleaner than regenerating everything. Note: make sure to run the command from the containing directory of your migrations directory; otherwise the CLI will generate a new migration dir for you

While it doesn't auto generate, one way to generate new migrations on a change to a model is: (assuming that you're using the stock sequelize-cli file structure where migrations, and models are on the same level)

1. (Same as Manuel Bieh's suggestion, but using a require instead of an import) In your migration file (if you don't have one, you can generate one by doing "sequelize migration:create") have the following code:

   'use strict';
   var models = require("../models/index.js")
   module.exports = {
     up: function(queryInterface, Sequelize) {
       return queryInterface.createTable(models.User.tableName, 
         models.User.attributes);
     },
     down: function(queryInterface, Sequelize) {
       return queryInterface.dropTable('Users');
     }
   };
   

2. Make a change to the User model.

3. Delete table from database.
4. Undo all migrations: sequelize db:migrate:undo:all
5. Re-migrate to have changes saved in db. sequelize db:migrate

I have recently tried the following approach which seems to work fine, although I am not 100% sure if there might be any side effects:

'use strict';

import * as models from "../../models";

module.exports = {

  up: function (queryInterface, Sequelize) {

    return queryInterface.createTable(models.Role.tableName, models.Role.attributes)
    .then(() => queryInterface.createTable(models.Team.tableName, models.Team.attributes))
    .then(() => queryInterface.createTable(models.User.tableName, models.User.attributes))

  },

  down: function (queryInterface, Sequelize) {
    ...
  }

};

When running the migration above using sequelize db:migrate, my console says:

Starting 'db:migrate'...
Finished 'db:migrate' after 91 ms
== 20160113121833-create-tables: migrating =======
== 20160113121833-create-tables: migrated (0.518s)

All the tables are there, everything (at least seems to) work as expected. Even all the associations are there if they are defined correctly.

You cannot create migration scripts for existing models.

Resources:

• Tutorial video on migrations.

If going the classic way, you'll have to recreate the models via the CLI:

sequelize model:create --name MyUser --attributes first_name:string,last_name:string,bio:text

It will generate these files:

models/myuser.js:

"use strict";_x000D_
module.exports = function(sequelize, DataTypes) {_x000D_
  var MyUser = sequelize.define("MyUser", {_x000D_
    first_name: DataTypes.STRING,_x000D_
    last_name: DataTypes.STRING,_x000D_
    bio: DataTypes.TEXT_x000D_
  }, {_x000D_
    classMethods: {_x000D_
      associate: function(models) {_x000D_
        // associations can be defined here_x000D_
      }_x000D_
    }_x000D_
  });_x000D_
  return MyUser;_x000D_
};

migrations/20150210104840-create-my-user.js:

"use strict";_x000D_
module.exports = {_x000D_
  up: function(migration, DataTypes, done) {_x000D_
    migration.createTable("MyUsers", {_x000D_
      id: {_x000D_
        allowNull: false,_x000D_
        autoIncrement: true,_x000D_
        primaryKey: true,_x000D_
        type: DataTypes.INTEGER_x000D_
      },_x000D_
      first_name: {_x000D_
        type: DataTypes.STRING_x000D_
      },_x000D_
      last_name: {_x000D_
        type: DataTypes.STRING_x000D_
      },_x000D_
      bio: {_x000D_
        type: DataTypes.TEXT_x000D_
      },_x000D_
      createdAt: {_x000D_
        allowNull: false,_x000D_
        type: DataTypes.DATE_x000D_
      },_x000D_
      updatedAt: {_x000D_
        allowNull: false,_x000D_
        type: DataTypes.DATE_x000D_
      }_x000D_
    }).done(done);_x000D_
  },_x000D_
  down: function(migration, DataTypes, done) {_x000D_
    migration.dropTable("MyUsers").done(done);_x000D_
  }_x000D_
};

If you want to create model along with migration use this command:-

sequelize model:create --name regions --attributes name:string,status:boolean --underscored

--underscored it is used to create column having underscore like:- created_at,updated_at or any other column having underscore and support user defined columns having underscore.

It's 2020 and many of these answers no longer apply to the Sequelize v4/v5/v6 ecosystem.

The one good answer says to use sequelize-auto-migrations, but probably is not prescriptive enough to use in your project. So here's a bit more color...

Setup

My team uses a fork of sequelize-auto-migrations because the original repo is has not been merged a few critical PRs. #56 #57 #58 #59

$ yarn add github:scimonster/sequelize-auto-migrations#a063aa6535a3f580623581bf866cef2d609531ba

Edit package.json:

"scripts": {
  ...
  "db:makemigrations": "./node_modules/sequelize-auto-migrations/bin/makemigration.js",
  ...
}

Process

Note: Make sure you’re using git (or some source control) and database backups so that you can undo these changes if something goes really bad.

1. Delete all old migrations if any exist.
2. Turn off .sync()
3. Create a mega-migration that migrates everything in your current models (yarn db:makemigrations --name "mega-migration").
4. Commit your 01-mega-migration.js and the _current.json that is generated.
5. if you've previously run .sync() or hand-written migrations, you need to “Fake” that mega-migration by inserting the name of it into your SequelizeMeta table. INSERT INTO SequelizeMeta Values ('01-mega-migration.js').
6. Now you should be able to use this as normal…
7. Make changes to your models (add/remove columns, change constraints)
8. Run $ yarn db:makemigrations --name whatever
9. Commit your 02-whatever.js migration and the changes to _current.json, and _current.bak.json.
10. Run your migration through the normal sequelize-cli: $ yarn sequelize db:migrate.
11. Repeat 7-10 as necessary

Known Gotchas

1. Renaming a column will turn into a pair of removeColumn and addColumn. This will lose data in production. You will need to modify the up and down actions to use renameColumn instead.

For those who confused how to use renameColumn, the snippet would look like this. (switch "column_name_before" and "column_name_after" for the rollbackCommands)

{
    fn: "renameColumn",
    params: [
        "table_name",
        "column_name_before",
        "column_name_after",
        {
            transaction: transaction
        }
    ]
}

2. If you have a lot of migrations, the down action may not perfectly remove items in an order consistent way.

3. The maintainer of this library does not actively check it. So if it doesn't work for you out of the box, you will need to find a different community fork or another solution.

As of 16/9/2020 most of these answers are not too much consistent any way! Try this new npm package

Sequelize-mig

It completed most known problems in sequelize-auto-migrations and its forks and its maintained and documented!

Its used in a way similar to the known one

Install:

npm install sequelize-mig -g / yarn global add sequelize-mig

then use it like this

sequelize-mig migration:make -n <migration name>

You can now use the npm package sequelize-auto-migrations to automatically generate a migrations file. https://www.npmjs.com/package/sequelize-auto-migrations

Using sequelize-cli, initialize your project with

sequelize init

Create your models and put them in your models folder.

Install sequelize-auto-migrations:

npm install sequelize-auto-migrations

Create an initial migration file with

node ./node_modules/sequelize-auto-migrations/bin/makemigration --name <initial_migration_name>

Run your migration:

node ./node_modules/sequelize-auto-migrations/bin/runmigration

You can also automatically generate your models from an existing database, but that is beyond the scope of the question.

I created a small working "migration file generator". It creates files which are working perfectly fine using sequelize db:migrate - even with foreign keys!

You can find it here: https://gist.github.com/manuelbieh/ae3b028286db10770c81

I tested it in an application with 12 different models covering:

• STRING, TEXT, ENUM, INTEGER, BOOLEAN, FLOAT as DataTypes

• Foreign key constraints (even reciprocal (user belongsTo team, team belongsTo user as owner))

• Indexes with name, method and unique properties

Another solution is to put data definition into a separate file.

The idea is to write data common for both model and migration into a separate file, then require it in both the migration and the model. Then in the model we can add validations, while the migration is already good to go.

In order to not clutter this post with tons of code i wrote a GitHub gist.

See it here: https://gist.github.com/igorvolnyi/f7989fc64006941a7d7a1a9d5e61be47