NodeJS sequelize auto generate models and run migrations SQL syntax error

Question

I am building a new NodeJS application with MySQL. I need to use the existing database schema. I have a mysql dump file that is loaded into the database (in a docker container). I am trying to generate models and migrations automatically and then run the migrations successfully. I am able to generate the models and migrations, however there is a SQL syntax error when running the generated migrations.

Here are the relevant versions:

Node10-alpine

"mysql": "^2.17.1",
"mysql2": "^1.6.5",
"sequelize": "^5.8.5",
"sequelize-auto": "^0.4.29",
"sequelize-auto-migrations": "^1.0.3"

I used the sequelize-auto module to generate the Models automatically. That works.

sequelize-auto -o "./models" -d sequelize_auto_test -h localhost -u username -p 5432 -x password -e mysql

I then attempted to use the sequelize-auto-migrations module to generate the Migrations and then run them automatically.

Generating the initial migration file works.

node ./node_modules/sequelize-auto-migrations/bin/makemigration --name <initial_migration_name>

However, when running the actual migration, there is a syntax error.

node ./node_modules/sequelize-auto-migrations/bin/runmigration

That works for many of the tables but then it runs into a syntax error.

     code: 'ER_PARSE_ERROR',
     errno: 1064,
     sqlState: '42000',
     sqlMessage:
      'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \') ENGINE=InnoDB\' at line 1',
     sql: 'CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;' },
  sql: 'CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;' }

Here is the relevant model osw.js (generated by the sequelize-auto module):

/* jshint indent: 2 */

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('osw', {
    OSWID: {
      type: DataTypes.INTEGER(10).UNSIGNED,
      allowNull: false,
      primaryKey: true
    },
    IdentificationID: {
      type: DataTypes.INTEGER(10).UNSIGNED,
      allowNull: true,
      references: {
        model: 'itemidentification',
        key: 'IdentificationID'
      }
    },
    ProposedHours: {
      type: DataTypes.DECIMAL,
      allowNull: true
    },
    WorkStartDate: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    WorkEndDate: {
      type: DataTypes.DATEONLY,
      allowNull: true
    },
    FormatID: {
      type: DataTypes.INTEGER(10).UNSIGNED,
      allowNull: true,
      references: {
        model: 'formats',
        key: 'FormatID'
      }
    },
    WorkLocationID: {
      type: DataTypes.INTEGER(10).UNSIGNED,
      allowNull: true
    }
  }, {
    tableName: 'osw'
  });
};

Here is the relevant part of the mysql dump file:

CREATE TABLE `OSW` (
  `OSWID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IdentificationID` int(10) unsigned DEFAULT NULL,
  `ProposedHours` decimal(10,2) DEFAULT NULL,
  `WorkStartDate` date DEFAULT NULL,
  `WorkEndDate` date DEFAULT NULL,
  `FormatID` int(10) unsigned DEFAULT NULL,
  `WorkLocationID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`OSWID`),
  KEY `OSW_FKIndex1` (`IdentificationID`),
  KEY `OSW_Format` (`FormatID`),
  CONSTRAINT `OSW_Format` FOREIGN KEY (`FormatID`) REFERENCES `formats` (`formatid`) ON DELETE SET NULL,
  CONSTRAINT `OSW_Ident` FOREIGN KEY (`IdentificationID`) REFERENCES `itemidentification` (`identificationid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1147 DEFAULT CHARSET=utf8 PACK_KEYS=0;

UPDATE: I think the issue might be related to the migration that was generated automatically. The migration file seems to be missing the column and field type definitions, so that might be why the SQL CREATE table command is missing the column names. Here is the relevant part of the migration file that was generated regarding the osw table:

var migrationCommands = [{
    {
        fn: "createTable",
        params: [
            "osw",
            {

            },
            {}
        ]
    }
];

Answer 1

• Yes, the SQL error is because there should be a list of columns between the parentheses.
• Yes, the migration file should contain columns instead of empty brackets, as will be shown by the correct file lower in this answer.
• The cause of the bad migration appears to involve the recent Sequelize version specified in your package.json. The rest of this answer explains the process.

---

Your sequelize-auto output looked normal, so I tried to reproduce just the migrations step, by:

1. Running sequelize init (not explicitly installed, NPM showed 4.44) in a new directory and pasting osw.js in ./models.
2. Installing matching versions of mysql2 and sequelize-auto-migrations, the bare minimum needed to call node ./node_modules/sequelize-auto-migrations/bin/makemigration --name osw
3. This produced migrations/1-osw.js:

'use strict';

var Sequelize = require('sequelize');

/**
 * Actions summary:
 *
 * createTable "osw", deps: [itemidentification, formats]
 *
 **/

var info = {
    "revision": 1,
    "name": "osw",
    "created": "2019-05-30T03:54:19.054Z",
    "comment": ""
};

var migrationCommands = [{
    fn: "createTable",
    params: [
        "osw",
        {
            "OSWID": {
                "type": Sequelize.INTEGER(10).UNSIGNED,
                "field": "OSWID",
                "primaryKey": true,
                "allowNull": false
            },
            "IdentificationID": {
                "type": Sequelize.INTEGER(10).UNSIGNED,
                "field": "IdentificationID",
                "references": {
                    "model": "itemidentification",
                    "key": "IdentificationID"
                },
                "allowNull": true
            },
            "ProposedHours": {
                "type": Sequelize.DECIMAL,
                "field": "ProposedHours",
                "allowNull": true
            },
            "WorkStartDate": {
                "type": Sequelize.DATEONLY,
                "field": "WorkStartDate",
                "allowNull": true
            },
            "WorkEndDate": {
                "type": Sequelize.DATEONLY,
                "field": "WorkEndDate",
                "allowNull": true
            },
            "FormatID": {
                "type": Sequelize.INTEGER(10).UNSIGNED,
                "field": "FormatID",
                "references": {
                    "model": "formats",
                    "key": "FormatID"
                },
                "allowNull": true
            },
            "WorkLocationID": {
                "type": Sequelize.INTEGER(10).UNSIGNED,
                "field": "WorkLocationID",
                "allowNull": true
            },
            "createdAt": {
                "type": Sequelize.DATE,
                "field": "createdAt",
                "allowNull": false
            },
            "updatedAt": {
                "type": Sequelize.DATE,
                "field": "updatedAt",
                "allowNull": false
            }
        },
        {}
    ]
}];

module.exports = {
    pos: 0,
    up: function(queryInterface, Sequelize)
    {
        var index = this.pos;
        return new Promise(function(resolve, reject) {
            function next() {
                if (index < migrationCommands.length)
                {
                    let command = migrationCommands[index];
                    console.log("[#"+index+"] execute: " + command.fn);
                    index++;
                    queryInterface[command.fn].apply(queryInterface, command.params).then(next, reject);
                }
                else
                    resolve();
            }
            next();
        });
    },
    info: info
};

This solves the immediate problem, but does not explain it. I cleaned the directory, initialized the package, installed all the dependencies, and tried to generate the migration again. That produced an empty migrationCommands variable like you showed above. Attempting to uninstall and reinstall various packages had no effect - I generated empty a dozen times. Uninstalling sequelize caused an error, so I started fresh with an empty directory and npm i -s mysql2 sequelize-auto-migrations; sequelize init. Generation from osw.js worked again.

npm showed that sequelize-auto-migrations was using sequelize 4.44 again. npm i sequelize installed 5.8.7, and generation immediately began failing. So sequelize-auto-migrations can only generate for your model when depending on an earlier version of sequelize. No idea what the root cause is.