Request: Migrations

[andrewl913]

Who thinks that adding migration functionality to sequelize would help devs? I find myself having to sync({force:true}) to constantly update my tables and their respective rows. Anyone else agree?

[rnemec]

Yes, that would be good. Now, considering this is a generator, when I ran into a need of migrations, I just added them to my project - folder with migrations, a runner in sqldb (using Umzug) as well as modified package.json (to run them locally as well as 'prestart' npm target at my AWS deployment)

Andrew, if you are interested in taking my changes and making a PR (possibly after cleaning my code, perhaps adding to the grunt/gulp, ...) I can pass the changes. Probably the main work would be to switch from real files to generator templates, make it conditional (only when sequelize-ing), etc.

[andrewl913]

@rnemec Yeah that would be a great idea! Trying to find free time to between school and work will be challenging. I can take a look at your changes, but won't make any significant contributions until late August possibly. And yeah just branching off the sequelize prompts will be the best route.

A few concerns:

• Should it migrations be a default option?
• Will the CLI to run migrations:
  1. be installed by yeoman?
  2. be integrated as a sub-generator?
  3. added as a npm script with args?
  4. have the user manually install the CLI?

[clintonmedbery]

@rnemec or anyone else have an example of getting the migrations to work after generating the stack? Thank you in advance.

[rnemec]

I am guilty as charged of all the crimes of promises without resolve() ;) So let me at least write down some details of my changes - perhaps somebody would turn them into a PR.

1. Add 'umzug' as dependency to package.json.

"umzug": "^1.11.0"

2. Add migrate.js to the server/sqldb folder (if you put it elsewhere, make sure you change the require('.')). If somebody wants to correct the use of process.exit(), please do so.

'use strict';

// Set default node environment to development
var env = process.env.NODE_ENV = (process.env.NODE_ENV || 'development');

if (env === 'development' || env === 'test') {
  // Register the Babel require hook
  require('babel-core/register');
}

var sequelizeInstance = require('.').sequelize;
var Umzug = require('umzug');
var umzug = new Umzug({
  // The storage.
  // Possible values: 'json', 'sequelize', an argument for `require()`, including absolute paths
  storage: 'sequelize',

  // The options for the storage.
  // Check the available storages for further details.
  storageOptions: {
    sequelize: sequelizeInstance
  },

  // The logging function.
  // A function that gets executed everytime migrations start and have ended.
  logging: console.log,

  migrations: {
    params: [sequelizeInstance.getQueryInterface(), sequelizeInstance.constructor],
    path: 'server/migrations'
  }
});

var donePromise;
if (process.argv[2] === 'down') {
  donePromise = umzug.down().then(function (migrations) {
    console.log("DOWN-ed migrations:");
    migrations.forEach((mig) => {console.log(mig.file);});
  });
} else if (process.argv[2] === 'markDone') {
  donePromise = umzug.pending()
  .then((migrations) => {
    console.log("MARKED-DONE migrations:");
    return Promise.all(migrations.map((mig) => {
      return umzug.storage.logMigration(mig.file)
      .then(() => {console.log('OK  : '+mig.file);})
      .catch((err) => {console.error('FAIL: '+mig.file);})
    }))
  })
} else {
  donePromise = umzug.up().then(function (migrations) {
    console.log("UP-ed migrations:");
    migrations.forEach((mig) => {console.log(mig.file);});
  });
}

donePromise
  .then(() => { process.exit();})
  .catch((err) => {
    console.error(err);
    process.exit(1);
  });

3. Add folder server/migrations folder.

4. Generate migrations from command-line in the project root. I don't have any generator or Grunt/Gulp wrapper for this step, not sure it's worth it (you may want to pass arguments, etc).

sequelize migration:create --migrations-path server/migrations --name add_superduper_column --underscored --config server/config/environment/development.js

5. Edit your generated migration file (use normal sequelize logic and don't forget to return the promise). Note the use of Sequelize for type constants.

'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.addColumn(
        'Mice', 'superduper', Sequelize.INTEGER)
      .then(() => queryInterface.sequelize.query(
        'UPDATE Mice SET superduper = 42 '));
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.removeColumn('Mice', 'superduper');
  }
};

6. Add couple of scripts to package.json. For me, the important one is "prestart" since that is executed by AWS Beanstalk when deploying new version. (Yes, I deploy to EBS). These scripts may need a little love, see further below. (I know - couple means 2 not 3. My excuse: non-Engrish speaker.)

  "scripts": {
    "sync-migrations": "node server/sqldb/migrate markDone",
    "migrate": "node server/sqldb/migrate",
    "prestart": "node server/sqldb/migrate",
    "start": "node server",
    "test": "grunt test",
    "update-webdriver": "node node_modules/protractor/bin/webdriver-manager update"
  },

7. Run the migrations from bash command-line. This step would be nice to have in the Grunt/Gulp file - please, anybody, add your comment with the snippet (or a PR once this is in). It would also help the poor Windows users.

npm run-script migrate -- up
NODE_ENV=test npm run-script migrate -- up

8. To undo the last migration, run this. I didn't bother undoing multiple. Just hit it couple of times if needed. Also, the migrations are stored in the SequelizeMeta table. If a migration got screwed-up (and one of them will, I guarantee), sometimes it is easier to just undo the chage (and screw-up) manually and remove the migration file name from the table. Otherwise, here is the down script:

npm run-script migrate -- down

9. For discussion: I also modified my seeding logic so in dev mode it doesn't recreate the DB. The consequence is that I need to run the migrations in dev mode. Now, when I eventually need to wipe and recreate the DB, I need to mark any migrations as done - so I have created additional migration mode markDone (see the migrate.js code) to mark any outstanding migration as done. This may be generalized (if needed) into a Grunt/Gulp task that recreates a DB (dev or test) and marks all migrations as done. Currently:

npm run-script migrate -- markDone

That's all I can think of, hope it helps at least one Fullstack-er.

[clintonmedbery]

Wow! Thanks! Going to try this tonight.

[rnemec]

Looking fwd to hearing how it works for you. If you have anything to improve, please, share as well so we can help Andrew with this.

[clintonmedbery]

Think I got it working good inside my docker container. I followed your example (thanks!) and had some problems but got it figured out. For one, use Sequelize instead of DataTypes as I did. Easy fix. I also used gulp shell to run the migrate command.

gulp.task('serve', cb => {
    runSequence(
        [
            'clean:tmp',
            // 'lint:scripts',
            'inject',
            'copy:fonts:dev',
            'env:all'
        ],
        'webpack:dev',
        ['start:server'],
        ['migrate', 'start:client'],
        'watch',
        cb
    );
});

gulp.task('migrate', shell.task([
    'echo MIGRATION STATION!', 'npm run-script migrate -- up'
]))

[rnemec]

Clinton, thanks for the confirmation! (I was afraid something was left out)

Yes, using Sequelize for types is the easiest way, most compatible with the generated migrations.

In your Gulp file, you may want to eventually optimize the migrations and run them as a plain node task, not npm or shell tasks (note, that the npm script just calls node server/sqldb/migrate). I'm no gulp expert but it feels like easy change with significant improvement. Anyone from audience? Anyone? (Bueller? Bueller?)

Also, if you have different databases for dev vs tests, you may want to have tasks like migrate:dev, migrate:tests and migrate. Note that running the migrations multiple times on the same database is pretty fast (second run does nothing), at least in small/medium projects.

Richard

[jspenc72]

Can this be added? https://github.com/db-migrate/node-db-migrate