Thursday, 23 May 2019

NodeJS sequelize auto generate models and run migrations SQL syntax error

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 also ran the dump file through a SQL syntax checker and the syntax is valid. However I am still blocked by this error when attempting to run the migration.

Executing (default): CREATE TABLE IF NOT EXISTS `osw` () ENGINE=InnoDB;
{ SequelizeDatabaseError: 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
    at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:239:16)
    at Query.handler [as onResult] (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:46:23)
    at Query.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:30:14)
    at Connection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:449:32)
    at PacketParser.Connection.packetParser.p [as onPacket] (/usr/src/app/node_modules/mysql2/lib/connection.js:72:12)
    at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.Connection.stream.on.data (/usr/src/app/node_modules/mysql2/lib/connection.js:79:25)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
From previous event:
    at Query.run (/usr/src/app/node_modules/sequelize/lib/dialects/mysql/query.js:39:12)
    at runHooks.then (/usr/src/app/node_modules/sequelize/lib/sequelize.js:559:29)
From previous event:
    at Promise.try.then.connection (/usr/src/app/node_modules/sequelize/lib/sequelize.js:559:12)
From previous event:
    at Promise.resolve.retry (/usr/src/app/node_modules/sequelize/lib/sequelize.js:556:10)
    at /usr/src/app/node_modules/retry-as-promised/index.js:70:21
    at new Promise (<anonymous>)
    at retryAsPromised (/usr/src/app/node_modules/retry-as-promised/index.js:60:10)
    at Promise.try (/usr/src/app/node_modules/sequelize/lib/sequelize.js:543:30)
From previous event:
    at Sequelize.query (/usr/src/app/node_modules/sequelize/lib/sequelize.js:500:23)
    at promise.then (/usr/src/app/node_modules/sequelize/lib/query-interface.js:236:46)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
From previous event:
    at QueryInterface.createTable (/usr/src/app/node_modules/sequelize/lib/query-interface.js:236:20)
    at next (/usr/src/app/migrations/1-initial.js:538:48)
    at /usr/src/app/migrations/1-initial.js:543:13
    at new Promise (<anonymous>)
    at Object.up (/usr/src/app/migrations/1-initial.js:531:16)
    at Object.executeMigration (/usr/src/app/node_modules/sequelize-auto-migrations/lib/migrate.js:779:9)
    at /usr/src/app/node_modules/sequelize-auto-migrations/bin/runmigration.js:92:17
    at /usr/src/app/node_modules/async/dist/async.js:3110:16
    at replenish (/usr/src/app/node_modules/async/dist/async.js:1011:17)
    at /usr/src/app/node_modules/async/dist/async.js:1016:9
    at eachLimit$1 (/usr/src/app/node_modules/async/dist/async.js:3196:24)
    at Object.<anonymous> (/usr/src/app/node_modules/async/dist/async.js:1046:16)
    at Object.<anonymous> (/usr/src/app/node_modules/sequelize-auto-migrations/bin/runmigration.js:89:7)
    at Module._compile (internal/modules/cjs/loader.js:701:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:712:10)
    at Module.load (internal/modules/cjs/loader.js:600:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:539:12)
    at Function.Module._load (internal/modules/cjs/loader.js:531:3)
    at Function.Module.runMain (internal/modules/cjs/loader.js:754:12)
    at startup (internal/bootstrap/node.js:283:19)
    at bootstrapNodeJSCore (internal/bootstrap/node.js:622:3)
  name: 'SequelizeDatabaseError',



from NodeJS sequelize auto generate models and run migrations SQL syntax error

No comments:

Post a Comment