How to auto generate migrations with Sequelize CLI from 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
You cannot create migration scripts for existing models.
Resources:
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); }};
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.
- Delete all old migrations if any exist.
- Turn off
.sync()
- Create a mega-migration that migrates everything in your current models (
yarn db:makemigrations --name "mega-migration"
). - Commit your
01-mega-migration.js
and the_current.json
that is generated. - 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')
. - Now you should be able to use this as normal…
- Make changes to your models (add/remove columns, change constraints)
- Run
$ yarn db:makemigrations --name whatever
- Commit your
02-whatever.js
migration and the changes to_current.json
, and_current.bak.json
. - Run your migration through the normal sequelize-cli:
$ yarn sequelize db:migrate
. - Repeat 7-10 as necessary
Known Gotchas
- Renaming a column will turn into a pair of
removeColumn
andaddColumn
. This will lose data in production. You will need to modify the up and down actions to userenameColumn
instead.
For those who confused how to use
renameColumn
, the snippet would look like this. (switch "column_name_before" and "column_name_after" for therollbackCommands
)
{ fn: "renameColumn", params: [ "table_name", "column_name_before", "column_name_after", { transaction: transaction } ]}
If you have a lot of migrations, the down action may not perfectly remove items in an order consistent way.
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.