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

Question

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?

Lecompte

"this article" link is not working. :( – Chavaree

Keep an eye on github.com/sequelize/cli/issues/257 – Distrain

Answer 1

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

Webster

this is never talked about but needed when making migrations to add or remove columns especially if you want to have those update a production environment latter. – Pappas

You can run npx sequelize-cli migration:generate --name [name_of_your_migration] from root of your project. But before you do so, you need to tell sequelize-cli where to generate your migrations, sequelize-cli uses config called migrations-path for this. sequelize.org/master/manual/… – Dabble

This is not answering at all the question. It just generates a skeleton, there's no data in it, which is what OP is asking – Mcgrath

Thanks, this was really useful. Anyways, it'd be sweet we could do what the OP asks for. – Flanna

Answer 2

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.

Tramline

i keep getting "UNKNOWN_VALUE: Unknown value: mega-migrations" error. Why is this happening? – Zalea

Does this work with sequelize version 5.x.x? I know you have hinted it in the answer but I've been trying for hous to make it work but still doesn't? – Zalea

I have only tested this on Sequelize 5.x. and it works correctly for my team. – Tramline

I have it working but it won’t take the name option it keeps on throwing an error. I am using windows. Any idea hw to solve this? – Zalea

Oh. Just don't use the --name option then. It is optional. It will then create 01-noname.js... and you can manually rename this file. – Tramline

Thanks, that is a simple and effective solution. Will do that. – Zalea

I have just tested it. When I alter the column name. The migration file deletes the column and creates a new one therefore the data in that column is lost. Have you noticed that behavior? – Zalea

Yes. When altering column names, the diff engine doesn't know that it's the same column so you'll need to manually change the auto-generated migration to use renameColumn instead of removeColumn and addColumn. – Tramline

After hours of being stuck...Thank you! You are a gentleman and a scholar – Mcgrath

Hey! Thanks for this. Works well! :) Helped me a lot! I'm a newbie in node-express+Sequelize. – Syntactics

This worked for me on v6. The only point of failure was that Sequelize.NOW is not translated well and gives a syntax error on the migration. Other than that i had no problemas. Thanks! – Swetlana

I'm getting ERROR: Unexpected identifier. What should I do? – Wig

Answer 3

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";
module.exports = function(sequelize, DataTypes) {
  var MyUser = sequelize.define("MyUser", {
    first_name: DataTypes.STRING,
    last_name: DataTypes.STRING,
    bio: DataTypes.TEXT
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return MyUser;
};

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

"use strict";
module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable("MyUsers", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      first_name: {
        type: DataTypes.STRING
      },
      last_name: {
        type: DataTypes.STRING
      },
      bio: {
        type: DataTypes.TEXT
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    }).done(done);
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable("MyUsers").done(done);
  }
};

Mehta

Thanks @Dor I have few questions around the sync functinality using sequelize. As I understand sequelize-cli uses Umzug internally for doing all the migrations. The example you showed really helped me to get started but what is the procedure for Alter Table, suppose I would like to alter the MyUser model in sequelize and let sequelize cli create the migration script for me , how can sequelize cli create the migration script by going through all the model changes. – Interlock

Generally you need to separate between the sync ability, which is good for a new DB or for a demo, and between migrations. When you have a production system that you continually upgrade and don't want to lost the data, then migrations is probably your only option. Unfortunately, the CLI is only good for creating basic templates, it runs no special logic and does not scan your model. (I was disappointing of this as well.) You need to create the migrations to transform the schema/data, and you need to change the model to the represent the newest schema as if that's how it's always been. – Mehta

Thanks @Dor but it seems lot of work to write the migration script for the changes. I wish somehow the migration scripts could be avoided and sync can happen by default. – Interlock

So do I. However if you look at ActiveRecord and Entity Framework ORMs, this is the pattern they implement as well. – Mehta

sure but they also provide a feature where we could generate the DBML file (In case of Entity Framework) directly using the database first or model first approach. They allow a developer to make changes in the database and then on the press of a button we could get models from Entity Framework. – Interlock

@DorRotman Thanks for clarifying this. This is really disappointing as I, too, have been spoiled by Entity Framework... – Education

Hello, how can I specify relationships (class associations) within the cli ? Or, if I add them to the model, will them be valid ? – Mystery

and yet the video you are linking to does show he is able to create migrations from existing models (see when he typed stukko addMigration after 5:40). – Maihem

newbie to sequelize and confused, if I create a model the cli creates the migrations automatically, but if I create the model manually, does the CLI scan the model and generate the migrations in any way? also by default the model has no code for validating or associating, do I have to make changes to both the model and migrations for such things? can I add a foreign key to the model and run a magic command that generates the migration accordingly, can someone please clarify these things – Lyra

You shouldn't use drop to undo the migration. This does not match the purpose of migrating data. You will lost all your of your data if you undo a migration, which is not the purpose of migrations. – Munguia

Thanks @DorRotman - I really wish the "you are responsible for maintaining consistency between your migration files and model files" were explicitly addressed in the sequelize doc, as it's a HUGE detail – Debacle

Answer 4

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.

Untie

medium.com/riipen-engineering/… – Cooking

Note that this does not generate down migrations. – Goodill

It doesn't work for me either. It generates a migration file with my table names but that's it: no columns, no schema, nada. – Mcgrath

@CarlesAlcolea My guess would be there is something wrong with your models. Please post a separate question. – Untie

Answer 5

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>

Cerography

This tool is great. It works with your es6 models, it can detect renamed columns AND it can revert migrations! – Mullens

Still the way to go in 2023 (Sequelize v6). – Jesseniajessey

Answer 6

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

Mycah

Just to note for anyone that tries this script, it is strictly for mysql – Op

How do I run this? – Hoffer

Answer 7

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.

Cattle

Answer 8

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

Tireless

I think your code does something similar to models.sequelize.sync({force: true}) (just a little bit more complex). If you change the model there is no way to update your schema because the migration already run (that's why you do db:migrate:undo:all). Migrations should version your DB schema. It's a nice example (I learned few commands) but I wouldn't use it in production. – Modie

I agree, this takes away the power of migrations. What happens when the model code changes? The migration will have different behavior. Migrations should almost read like a git commit. It would be awesome to have a script that generated a migration for a specific point in time, and it could probably leverage what you're doing here. – Faircloth

You shouldn't use drop to undo the migration. This does not match the purpose of migrating data. You will lost all your of your user data if you undo a migration, which is not the purpose of migrations. – Munguia

Answer 9

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.

Mycah

I've modified my script above to generate static migration files for each model (in a ./tmp folder): gist.github.com/manuelbieh/606710b003b5fe448100 - as I already stated above: i have no idea if there are any negative sideeffects so use it with caution! – Mycah

What does your models directory look like? Are you still using the index.js script that sequelize recommends? – Brier

I get [SyntaxError: Unexpected reserved word] – Consols

WARNING: This is contrary to the whole migration model. If you'd want to just create the tables each time from the model, you could use the sync() function of Sequelize. However, it does not solve the problem of upgrading a production server that just needs a field added to a table. The only way to achieve this is by manually writing the migrations. Migrations rely on previous ones that have run historically. Having a single migration and rewriting it each time with different model - will simply not run, as the SequelizeMeta table indicates the migration has already run on that server before. – Mehta

Moreover, imagine this scenario: the create-tables migration creates all tables from the model, as they look when compiling or building the installation package. You deploy a server and run the migration during deployment. Later, you create a migration that only adds a field. You upgrade the server. Everything works. Then you need to install a new server. That server would run the create-tables migration that already contains the field, and then would run the next migration that only adds a field. The 2nd migration will fail as the field already exists. Conclusion: Migrations can never change. – Mehta

I agreed with @DorRotman. I think to initialize the database the first time, use sequelize.sync is the best, after the initialize (may be the app on prod for a while) then use the migration to add/modify columns, add more models etc. – Jamila

Where you store that file? – Dissatisfied

Answer 10

PaulMest's answer in this page was very usefull for me. I used 'sequelize-auto-migrations' but it did not detect my changes. I used 'sequelize-auto-migrations-v2' and this worked correctly for me. You can install it by:

npm install sequelize-auto-migrations-v2

And use it by:

node ./node_modules/sequelize-auto-migrations-v2/bin/makemigration

Matriculate

Answer 11

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

Gignac