Sequelize can't create table but when I run the same in MySQL CLI it works

Question

I am using sequelize and have run into a weird error:

Executing (default): CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB;
Executing (default): CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.users' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)
Possibly unhandled SequelizeDatabaseError: ER_CANT_CREATE_TABLE: Can't create table 'crew.messages' (errno: 150)
    at module.exports.Query.formatError (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:160:16)
    at Query._callback (/home/ubuntu/public/server/node_modules/sequelize/lib/dialects/mysql/query.js:38:23)
    at Query.Sequence.end (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query.ErrorPacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/sequences/Query.js:94:8)
    at Protocol._parsePacket (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/home/ubuntu/public/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/home/ubuntu/public/server/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:746:14)

The weird part is, when I try executing those commands in MySQL CLI, it works perfectly:

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `groups` (`id` INTEGER NOT NULL auto_increment , `groupname` VARCHAR(255), `groupkey` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `salt` VARCHAR(255), `token` VARCHAR(255), `group_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `messages` (`id` INTEGER NOT NULL auto_increment , `message` VARCHAR(255), `group_id` INTEGER, `user_id` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Here is how I am defining the tables:

var dbconfig = {};
dbconfig.database = process.env.database || 'crew';
dbconfig.username = process.env.username || 'root';
dbconfig.password = process.env.password || '';
dbconfig.hostname = process.env.hostname || 'localhost';

var sequelize = new Sequelize(dbconfig.database, dbconfig.username, dbconfig.password, {
  host: dbconfig.hostname
});

var User = sequelize.define('users', {
  username: {
    type: Sequelize.STRING, 
    unique: true
  },
  password: Sequelize.STRING,
  salt: Sequelize.STRING,
  token: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  }
});

var Message = sequelize.define('message', {
  message: Sequelize.STRING,
  group_id: {
    type: Sequelize.INTEGER,
    references: 'groups',
    referencesKey: 'id'
  },
  user_id: {
    type: Sequelize.INTEGER,
    references: 'users',
    referencesKey: 'id'
  }
});

var Group = sequelize.define('groups', {
  groupname: Sequelize.STRING,
  groupkey: Sequelize.STRING
});


Group.sync({force: true});
User.sync({force: true});
Message.sync({force: true});

Answer 1

Figured it out!! Hopefully this will help others.

So basically the commands below are async operations, they get executed at different times, resulting in sequelize to fail, since the tables are dependent on other tables, order of execution matters.

Group.sync({force: true});
User.sync({force: true});
Message.sync({force: true});

Instead of doing the above, if you call sync() on sequelize instead, sequelize knows exactly what order the commands need to executed in.

sequelize.sync();