Sequelize migration error 150 in one to many relationship

Question

I cloned my web app under development from Windows into an Ubuntu machine. This app has a modular structure with a Node JS Server, a MYSQL DB and a client with React.

I am using Sequelize CLI for db controllers.

When I create my db and run migrations in Ubuntu I get

ERROR: Can't create table icsi_app_development.phases (errno: 150 "Foreign key constraint is incorrectly formed")

This is working fine in Windows with the same depedencies:

Sequelize CLI [Node: 14.17.2, CLI: 6.2.0, ORM: 6.6.2]

Any idea on this association error? Thanks a lot!

This is my create-phase.js

'use strict';
module.exports = {
  up: async (queryInterface, DataTypes) => {
    await queryInterface.createTable('phases', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      uuid: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
      },
      projectId: {
        type: DataTypes.INTEGER,
        references: {model: "Projects", key: "id"},
        onDelete: "CASCADE"
      },
      name: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      },
      description: {
        type: DataTypes.STRING,
        allowNull: false
      },
      isActive: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: true
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    });
  },
  down: async (queryInterface, DataTypes) => {
    await queryInterface.dropTable('phases');
  }
};

This is my create-project.js

'use strict';
module.exports = {
  up: async (queryInterface, DataTypes) => {
    await queryInterface.createTable('projects', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER
      },
      uuid: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
      },
      nume: {
        type: DataTypes.STRING,
        allowNull: false
      },
      cod_identificare: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      },
      departament: {
        type: DataTypes.STRING,
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
      }
    });
  },
  down: async (queryInterface, DataTypes) => {
    await queryInterface.dropTable('projects');
  }
};

This is my project model

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Project extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
    // toJSON(){
    //   return {...this.get(), id: undefined}
    // }
  };
  Project.init({
    nume: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: {msg: "Proiectul trebuie sa aiba un nume"},
        notEmpty: {msg: "Proiectul trebuie sa aiba un nume"}
      }
    },
    cod_identificare: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        notNull: {msg: "Proiectul trebuie sa prezinte un cod de identificare unic"},
        notEmpty: {msg: "Proiectul trebuie sa prezinte un cod de identificare unic"}
      }
    },
    departament: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notNull: {msg: "Proiectul trebuie sa fie atribuit unui departament"},
        notEmpty: {msg: "Proiectul trebuie sa fie atribuit unui departament"}
      }
    },
    uuid: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4
    }
  }, 
  {
    sequelize,
    tableName: "projects",
    modelName: 'Project',
  });

  Project.associate = (models) => {
    Project.hasMany(models.Phase, {
      foreignKey: 'projectId',
      as: "phases"
    });

    Project.belongsToMany(models.User, {
      foreignKey: 'projectId',
      through: 'users_projects',
      as: "users"
    });
  }

  return Project;
};

And this is my phase model

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Phase extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  Phase.init({
    projectId: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        notEmpty: {msg: "Faza proiectului trebuie sa prezinte un titlu"},
        notNull: {msg: "Faza proiectului trebuie sa prezinte un titlu"}
      }
    },
    description: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        notEmpty: {msg: "Faza proiectului trebuie sa prezinte o descriere"},
        notNull: {msg: "Faza proiectului trebuie sa prezinte o descriere"}
      }
    },
    isActive: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      defaultValue: true
    },
    uuid: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4
    }
  }, {
    sequelize,
    tableName: "phases",
    modelName: 'Phase',
  });

  Phase.associate = (models) => {
    Phase.belongsTo(models.Project, {
      foreignKey: 'projectId',
      as: "projects"
    });

    Phase.hasMany(models.File, {
      foreignKey: "phaseId",
      as: "files"
    });
  }

  return Phase;
};

Answer 1

There are two things that jump out to me as possibilities for the error.

1. If you are running the migration to create the Phases table before the one for the Project table, I believe that you will have this problem. You'd be creating a projectId foreign key before the software has a reference to the Projects table and its id column.

2. Capitalization is typically not an issue in SQL, but it's possible that Sequelize has trouble with the different capitalizations of "projects" in the createTable method of the Projects migration and "Projects" in the model property of the projectId.references in the Phases migration.