This script is base for creating and applying sequelize schema migrations on the fly.

index.js

// models/index.js

import fs from 'fs';
import path from 'path';
import Sequelize from 'sequelize';
import { spawnSync } from 'child_process';
import migrate from 'sequelize-auto-migrations/lib/migrate';
import logger from '../utils/logger';

const models = {};
const basename = path.basename(__filename);

const host = process.env.DB_HOST || 'localhost';
const port = process.env.DB_PORT || 3306;
const username = process.env.DB_USER || 'root';
const password = process.env.DB_PASS || 'pass';
const database = process.env.DB_NAME || 'database';
const dialect = 'mysql';

const logging = msg => logger.debug(msg);
const operatorsAliases = false;
const setChangedBy = (model, options) => {
  if (options.userId) {
    model.changedBy = options.userId;
  }
};
const hooks = {
  beforeCreate: setChangedBy,
  beforeUpdate: setChangedBy,
  beforeSave: setChangedBy,
  beforeDestroy: setChangedBy,
  beforeBulkUpdate: (options) => {
    // update changedBy if userId is set
    if (options.userId) {
      options.attributes.changedBy = options.userId;
      options.fields.push('changedBy');
    }
  },
  beforeBulkDestroy: async (options) => {
    const { model } = options;
    // needs await so that update operations runs before destroy
    // empty update will trigger the beforeBulkUpdate hook
    await model.update({}, options);
  },
};
const dbOptions = {
  host, port, logging, dialect, seederStorage: 'sequelize', operatorsAliases, hooks,
};
const sequelize = new Sequelize(database, username, password, dbOptions);
const queryInterface = sequelize.getQueryInterface();

fs.readdirSync(__dirname)
  .filter(file => (file !== basename) && (file.slice(-3) === '.js'))
  .forEach((file) => {
    const model = sequelize.import(path.join(__dirname, file));
    models[model.name.charAt(0).toUpperCase() + model.name.slice(1)] = model;
  });

Object.keys(models).forEach((modelName) => {
  if (models[modelName].associate) {
    models[modelName].associate(models);
  }
});

// FIXME - cannot import in models when using the tool
const getOptions = opts => Object.assign({ paranoid: true }, opts);

const fixTypes = async () => {
  const migrationDir = path.resolve(__dirname, '../migrations');

  fs.readdirSync(migrationDir)
    .filter(file => (file.slice(-3) === '.js'))
    .forEach((file) => {
      const filePath = path.join(migrationDir, file);
      const data = fs.readFileSync(filePath, 'utf-8');

      const fixCode = [
        /* eslint-disable no-template-curly-in-string */
        "var Sequelize = require('sequelize');",
        "var logger = require('../utils/logger').default;",
        '',
        "var INTEGER = 'INTEGER';",
        "var DATETIME = 'DATETIME';",
        'var VARCHAR = ln => `VARCHAR(${ln})`;',
        'var TINYINT = ln => `TINYINT(${ln})`;',
        "var ENUM = (...values) => `ENUM(\"${values.join('\", \"')}\")`;",
        /* eslint-enable no-template-curly-in-string */
      ].join('\n');

      if (!data.includes(fixCode)) {
        const result = data
          .replace("var Sequelize = require('sequelize');", fixCode)
          .replace('pos: 0,', 'pos: 0, migrationCommands,')
          .replace('console.log', 'logger.debug');
        fs.writeFileSync(filePath, result, 'utf8');
        logger.debug(`Fixed ${filePath}`);
      }
    });
};

const migrationsDir = path.join(__dirname, '..', 'migrations');

// generate migrations
const generateMigrations = async () => {
  // current state
  const currentState = {
    tables: {},
  };

  // load last state
  let previousState = {
    revision: 0,
    version: 1,
    tables: {},
  };

  try {
    previousState = JSON.parse(fs.readFileSync(path.join(migrationsDir, '_current.json')));
  } catch (e) {
    // do nothing
  }

  if (process.env.NODE_ENV !== 'development') { // just return current revision
    logger.debug('No schema changes found');
    return previousState.revision;
  }

  // ugly bit :/
  const modelsClone = {};
  const sequelizeClone = new Sequelize(database, username, password, dbOptions);
  fs.readdirSync(__dirname)
    .filter(file => (file !== basename) && (file.slice(-3) === '.js'))
    .forEach((file) => {
      const model = sequelizeClone.import(path.join(__dirname, file));
      modelsClone[model.name.charAt(0).toUpperCase() + model.name.slice(1)] = model;
    });
  Object.keys(modelsClone).forEach((modelName) => {
    if (modelsClone[modelName].associate) {
      modelsClone[modelName].associate(modelsClone);
    }
  });

  currentState.tables = migrate.reverseModels(sequelizeClone, modelsClone);
  const actions = migrate.parseDifference(previousState.tables, currentState.tables);

  // sort actions
  migrate.sortActions(actions);

  const migration = migrate.getMigration(actions);

  const dev = process.env.NODE_ENV === 'development';
  if (migration.commandsUp.length === 0 || !dev) {
    if (!dev) {
      logger.error({
        prev: previousState.tables,
        cur: currentState.tables,
        actions,
      }, 'Migration creation not in development is not allowed');
    } else {
      logger.debug('No schema changes found');
    }
    return previousState.revision;
  }

  // log migration actions
  migration.consoleOut.forEach((v) => { logger.debug(`[Actions] ${v}`); });

  // if (options.preview) {
  //   logger.debug('Migration result:');
  //   logger.debug(beautify(`[ \n${migration.commandsUp.join(', \n')} \n];\n`));
  // }

  // backup _current file
  if (fs.existsSync(path.join(migrationsDir, '_current.json'))) {
    fs.writeFileSync(
      path.join(migrationsDir, '_current_bak.json'),
      fs.readFileSync(path.join(migrationsDir, '_current.json')),
    );
  }

  const dateObj = new Date();
  const revision = `${dateObj.getFullYear()}${[
    dateObj.getMonth() + 1,
    dateObj.getDate(),
    dateObj.getUTCHours(),
    dateObj.getUTCMinutes(),
    dateObj.getUTCSeconds(),
  ].map(bit => `0${bit}`.slice(-2)).join('')}`;

  currentState.revision = revision;
  fs.writeFileSync(path.join(migrationsDir, '_current.json'), JSON.stringify(currentState, null, 4));

  // write migration to file
  const info = migrate.writeMigration(revision, migration, migrationsDir, 'migration', 'auto generated');

  await fixTypes();

  logger.debug(`New migration [${revision}] has been saved to file '${info.filename}'`);
  return revision;
};

const applyMigrations = (lastRevision) => {
  const migrationFiles = fs.readdirSync(migrationsDir)
    .filter(file => file.slice(-3) === '.js')
    // sort by revision
    .sort((a, b) => {
      const revA = parseInt(path.basename(a).split('-', 2)[0], 10);
      const revB = parseInt(path.basename(b).split('-', 2)[0], 10);
      if (revA < revB) return -1;
      if (revA > revB) return 1;
      return 0;
    })
    // remove all migrations before lastRevision
    .filter((file) => {
      const rev = parseInt(path.basename(file).split('-', 2)[0], 10);
      return (rev > lastRevision);
    });

  logger.debug('Migrations to execute:');
  migrationFiles.forEach(file => logger.debug(`\t${file}`));

  return new Promise(((resolve, reject) => {
    let filenameIndex = 0;
    let index = 0;
    function next() {
      const filename = migrationFiles[filenameIndex];
      if (!filename) {
        resolve();
        return true;
      }

      // eslint-disable-next-line
      const { migrationCommands, info } = require(path.join(migrationsDir, filename));
      const command = migrationCommands[index];
      if (command) {
        logger.debug(`[${info.revision} #${index}] execute: ${command.fn}`);
        index += 1;
        return queryInterface[command.fn](...command.params).then(next, reject);
      }
      filenameIndex += 1;
      index = 0;
      return next();
    }
    return next();
  }));
};

const upgradeDB = async () => {
  const { Migration } = models;
  let upgraded = false;

  // state of the schema
  const newMigrationString = await generateMigrations();
  const newMigration = +newMigrationString; // convert to number

  // the state of the database
  const res = await Migration.findOne({ attributes: ['migration'], order: [['createdAt', 'DESC']] })
    .catch((err) => {
      logger.debug({ err }, err.message);
    });
  const migration = (res && res.migration) || 0;

  if (migration > newMigration) {
    logger.error(`Migrations out of order: ${newMigration} in code, ${migration} in DB.`);
  } else if (migration < newMigration) {
    await applyMigrations(migration);
    upgraded = true;
  }

  // apply seeds
  const cmd = spawnSync(
    path.join(__dirname, '../../node_modules/.bin/babel-node'),
    [
      path.join(__dirname, '../../node_modules/.bin/sequelize'),
      '--config', path.join(__dirname, 'index.js'),
      '--seeders-path', path.join(__dirname, '..', 'seeders'),
      'db:seed:all',
    ],
    {
      env: { ...process.env, NODE_ENV: 'production' },
      cwd: path.join(__dirname, '../..'),
    },
  );
  if (cmd.error) {
    logger.error(cmd.error);
  } else {
    const seedLog = cmd.stdout.toString();
    const seedErr = cmd.stderr.toString();
    if (seedLog) logger.debug(`Sequelize seed: ${seedLog}`);
    if (seedErr) logger.error(`Sequelize seed error: ${seedErr}`);
  }

  if (upgraded) {
    await Migration.create({ migration: newMigration });
    logger.debug(`Upgraded database. New migrations state: ${newMigration} [+${newMigration - migration}].`);
  } else {
    logger.debug(`No DB upgrade needed. Migration state: ${migration}.`);
  }
};

module.exports = {
  ...models,
  sequelize,
  Sequelize,
  getOptions,
  upgradeDB,
  production: { // setup sequelize-cli (migrate) to run as prod
    username,
    password,
    database,
    host,
    dialect,
    port,
    operatorsAliases,
    seederStorage: 'sequelize',
  },
};

user.js

// models/user.js

export default (sequelize, DataTypes) => {
  const User = sequelize.define('user', {
    id: { type: DataTypes.STRING, allowNull: false, primaryKey: true },
    name: { type: DataTypes.STRING, defaultValue: '', allowNull: false },
  }, { paranoid: true });

  User.associate = (models) => {
    User.belongsTo(models.Team);
  };

  return User;
};