NodeJS Sequelize自动生成模型与迁移出现SQL语法错误

Problem Analysis #

Looking at your error logs and code snippets, the core issue is that sequelize-auto-migrations generated a migration command with an empty field definition for the osw table. This led to an invalid SQL statement (CREATE TABLE IF NOT EXISTS \osw` () ENGINE=InnoDB;), which throws the syntax error. This usually happens due to compatibility gaps between the old version of sequelize-auto-migrations(v1.0.3) andsequelize v5.x, or missing key attributes in the model generated by sequelize-auto`.

Solutions #

1. Manually Fix the Migration File (Quickest Fix)

Locate the generated migration file and fill in the complete field definitions, primary key, and foreign key constraints to match your original MySQL table structure:

var migrationCommands = [
  // Create osw table
  {
    fn: "createTable",
    params: [
      "osw",
      {
        OSWID: {
          type: Sequelize.INTEGER(10).UNSIGNED,
          allowNull: false,
          primaryKey: true,
          autoIncrement: true // Add AUTO_INCREMENT matching the original table
        },
        IdentificationID: {
          type: Sequelize.INTEGER(10).UNSIGNED,
          allowNull: true,
          references: {
            model: 'itemidentification',
            key: 'IdentificationID'
          }
        },
        ProposedHours: {
          type: Sequelize.DECIMAL(10,2), // Match original decimal(10,2) precision
          allowNull: true
        },
        WorkStartDate: {
          type: Sequelize.DATEONLY,
          allowNull: true
        },
        WorkEndDate: {
          type: Sequelize.DATEONLY,
          allowNull: true
        },
        FormatID: {
          type: Sequelize.INTEGER(10).UNSIGNED,
          allowNull: true,
          references: {
            model: 'formats',
            key: 'FormatID'
          }
        },
        WorkLocationID: {
          type: Sequelize.INTEGER(10).UNSIGNED,
          allowNull: true
        }
      },
      {
        engine: 'InnoDB',
        charset: 'utf8',
        packKeys: 0 // Match original table's PACK_KEYS=0 setting
      }
    ]
  },
  // Add foreign key constraints
  {
    fn: "addForeignKey",
    params: [
      "osw",
      "IdentificationID",
      "itemidentification",
      "IdentificationID",
      { onDelete: "CASCADE" }
    ]
  },
  {
    fn: "addForeignKey",
    params: [
      "osw",
      "FormatID",
      "formats",
      "FormatID",
      { onDelete: "SET NULL" }
    ]
  }
];

After making these changes, re-run the migration command and it should execute successfully.

2. Refine the Model and Regenerate Migrations

Your osw model is missing the autoIncrement attribute for the OSWID field, which might have prevented the migration tool from parsing the table structure correctly. First, update the model:

// osw model file
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('osw', {
    OSWID: {
      type: DataTypes.INTEGER(10).UNSIGNED,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true // Add this line to match original table
    },
    ProposedHours: {
      type: DataTypes.DECIMAL(10,2), // Update to match original precision
      allowNull: true
    },
    // Keep other fields unchanged
  }, { tableName: 'osw' });
};

Then re-run the migration generation command:

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

The migration tool should now correctly parse the model's field definitions.

3. Switch to a More Reliable Migration Tool (Long-Term Fix)

sequelize-auto-migrations v1.0.3 is outdated and has limited compatibility with sequelize v5.x. Consider switching to a more maintained tool:

• sequelize-cli: Generate migration templates manually and fill them in using your model and original table structure:

  npx sequelize-cli migration:generate --name init-osw-table
  

• umzug: A lightweight, flexible migration management library that works well with custom migration logic.

Additional Notes #

• Notice that your sequelize-auto command uses port 5432 (PostgreSQL's default), but you're using MySQL (default port 3306). While you mentioned model generation succeeded, double-check this to avoid future issues.
• If you need to migrate multiple tables, prioritize upgrading sequelize-auto and sequelize-auto-migrations to versions compatible with sequelize v5.x, or switch to a more modern tool entirely.

内容的提问来源于stack exchange，提问作者pengz