Sequelize.js: how to use migrations and sync

Question

Generating the "first migration"

In your case, the most reliable way is to do it almost manually. I would suggest to use sequelize-cli tool. The syntax is rather plain:

sequelize init
...
sequelize model:create --name User --attributes first_name:string,last_name:string,bio:text

This will create both model AND migration. Then, manually merge your existing models with generated with sequelize-cli, and do the same with migrations. After doing this, wipe database (if possible), and run

sequelize db:migrate

This will create schema will migrations. You should do this only once to switch to proper process of schema developments (without sync:force, but with authoritative migrations).

Later, when you need to change schema:

1. Create a migration: sequelize migration:create
2. Write up and down functions in your migration file
3. According to your changes in migration file, change your model manually
4. Run sequelize db:migrate

Running migrations on production

Obviously you can't ssh to production server and run migrations by hands. Use umzug, framework agnostic migration tool for Node.JS to perform pending migrations before app starts.

You can get a list of pending/not yet executed migrations like this:

umzug.pending().then(function (migrations) {
  // "migrations" will be an Array with the names of
  // pending migrations.
}); 

Then execute migrations (inside callback). The execute method is a general purpose function that runs for every specified migrations the respective function:

umzug.execute({
  migrations: ['some-id', 'some-other-id'],
  method: 'up'
}).then(function (migrations) {
  // "migrations" will be an Array of all executed/reverted migrations.
});

And my suggestion is to do it before app starts and tries to serve routes every time. Something like this:

umzug.pending().then(function(migrations) {
    // "migrations" will be an Array with the names of
    // pending migrations.
    umzug.execute({
        migrations: migrations,
        method: 'up'
    }).then(function(migrations) {
        // "migrations" will be an Array of all executed/reverted migrations.
        // start the server
        app.listen(3000);
        // do your stuff
    });
});

I can't try this right now, but at first look it should work.

UPD Apr. 2016

After a year, still useful, so sharing my current tips. For now, I'm installing sequelize-cli package as required live dependancy, and then modify NPM startup scripts in package.json like this:

...
"scripts": {
  "dev": "grunt && sequelize db:migrate && sequelize db:seed:all && node bin/www",
  "start": "sequelize db:migrate && sequelize db:seed:all && node bin/www"
},
...

The only thing I need to do on production server is npm start. This command will run all migrations, apply all seeders and start app server. No need to call umzug manually.

Just learning this myself, but I think I would recommend using migrations now so you get used to them. I've found the best thing for figuring out what goes in the migration is to look at the sql on the tables created by sequelize.sync() and then build the migrations from there.

migrations -c [migration name] 

Will create the template migration file in a migrations directory. You can then populate it with the fields you need created. This file will need to include createdAt/updatedAt, fields needed for associations, etc.

For initial table creation down should have:

migration.dropTable('MyTable');

But subsequent updates to the table structure can leave this out and just use alter table.

./node_modules/.bin/sequelize --migrate

An example create would look like:

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable(
        'MyTable',
        {
          id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
          },
          bigString: {type: DataTypes.TEXT, allowNull: false},
          MyOtherTableId: DataTypes.INTEGER,
          createdAt: {
            type: DataTypes.DATE
          },
          updatedAt: {
            type: DataTypes.DATE
          }
        });
    done();
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable('MyTable');
    done();
  }

To redo from start:

./node_modules/.bin/sequelize --migrate --undo
./node_modules/.bin/sequelize --migrate

I'm using coffee to run a seed file to populate the tables after:

coffee server/seed.coffee

This just has a create function in it that looks something like:

user = db.User.create
  username: 'bob'
  password: 'suruncle'
  email: '[email protected]'
.success (user) ->
  console.log 'added user'
  user_id = user.id
  myTable = [
    field1: 'womp'
    field2: 'rat'

    subModel: [
      field1: 'womp'
     ,
      field1: 'rat'
    ]
  ]

Remember to take your sync() out of index in your models or it will overwrite what the migrations and seed do.

Docs are at http://sequelize.readthedocs.org/en/latest/docs/migrations/ of course. But the basic answer is you have to add everything in yourself to specify the fields you need. It doesn't do it for you.

For development, there is now an option to sync the current tables by altering their structure. Using the latest version from the sequelize github repo, you can now run sync with the alter parameter.

Table.sync({alter: true})

A caveat from the docs:

Alters tables to fit models. Not recommended for production use. Deletes data in columns that were removed or had their type changed in the model.

I went through this post and similar questions, it didn't really answer it for me. Migrations are useful for spinning up local databases and for updating data in production

I asked the question here and answered it as well: Workflow for handling sequelize migrations and initialization?

TL-DR version for a greenfield project

1. Design your database schema as you traditionally would using pure SQL scripts or if you use a gui tool instead
2. When you finalize all your 95% of your db schema and your happy with it, go ahead and move it to sequelize by moving the entire .sql file over
3. Make your first migration. Run sequelize init:migrate in the whatever folder where your models are at
4. Make your first migrations file. Run sequelize migration:generate --name [name_of_your_migration]
5. In that migration file, put this code in there

("use strict");
/**
 * DROP SCHEMA public CASCADE; CREATE SCHEMA public
 * ^ there's a schema file with all the tables in there. it drops all of that, recreates
 */
const fs = require("fs");
const initialSqlScript = fs.readFileSync("./migrations/sql/Production001.sql", {
  encoding: "utf-8",
});
const db = require("../models");
module.exports = {
  up: () => db.sequelize.query(initialSqlScript),
  down: () =>
    db.sequelize.query(`DROP SCHEMA public CASCADE; CREATE SCHEMA public;
`),
};

with this general folder structure

1. Now your sequelize setup is synced with your initial database schema
2. when you want to edit your database schema, run this again sequelize migration:generate --name [name_of_your_migration]
3. Go ahead and make your modifications here on the up and down migration paths. These are your ALTER statements to change column names, DELETE, ADD columns etc
4. Run sequelize db:migrate
5. You want the models synced to the changes to your remote db, so what you can do now is npm install sequelize-auto.
6. This will read the the current database schema on your database and auto generate model files. Use a command similar to this sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u my_username -p 5432 -x my_password -e postgres found under https://github.com/sequelize/sequelize-auto

You can use git to see difflogs on your model, there should be only changes reflecting changes in the database model. As a side note, do not ever modify the models directly if you use sequelize auto, as this will generate them for you. Likewise, you no longer should modify your database schema directly with SQL files, granted this is an option as you can import those .sql files as well

Now your database schema is up to date, and you've officially moved over to sequelize database migrations only.

Everything is version controlled. This is the ideal workflow for database and backend developer

Now with the new sequelize migration is very simple.

This is a example what you can do.

    'use strict';

    var Promise = require('bluebird'),
        fs = require('fs');

    module.exports = {
        up: function (queryInterface, Sequelize) {

            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../initial-db.sql', 'utf-8');
                })
                .then(function (initialSchema) {
                    return queryInterface.sequelize.query(initialSchema);
                })
        },

        down: function (queryInterface, Sequelize) {
            return Promise
                .resolve()
                .then(function() {
                    return fs.readFileSync(__dirname + '/../drop-initial-db.sql', 'utf-8');
                })
                .then(function (dropSql) {
                    return queryInterface.sequelize.query(dropSql);
                });
        }
    };

Remember you have to set:

"dialectOptions": { "multipleStatements": true }

on database config.

Use version. Version of the application depends on the version of the database. If the new version requires an update of a database, create migration for it.

update: I decided to abandon the migration (KISS) and run script update_db (sync forse: false) when it is needed.