Create a new relationship between two tables in an already deployed database - sequelize

Question

I'm not very familiar with sequelize, but currently I'm working with Node.js and Sequelize and I need to create a new association one to many between two tables. I know the code to generate the association:

school.hasMany(student,{ foreignKey: 'school_id', as : 'studentSchool', sourceKey: 'school_id'});
student.belongsTo(school, {foreignKey: 'school_id', targetKey : 'school_id', as: 'studentSchool'});

My problem is that the application has been deployed and in use for at least 2 years now. So there is a lot of data already. I don't know how to introduce this new association without corrupting the current data or without having to reconstruct the database.

Answer 1

You will need to create a migration for that. I am assuming you already use sequelize-cli (if you do not, install from npm)

In your terminal, run

npx sequelize-cli migration:generate --name added-association-to-school-and-student

This creates an empty migration file. Fill the file with the code below

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn("students", "school_id", {
      type: Sequelize.DataTypes.INTEGER,
        /*
          The defaultValue below was assigned because by default constraints are set to true.
          This means that all students must belong to a school
          If no school_id is specified, mysql sees that this does not follow the constraints and will opt to delete all records from the database.
          So assign a default value and after this, you can go ahead to manually assign the correct schools.
          ENSURE THAT THE DEFAULT VALUE OF school_id PROVIDED HAS A CORRESPONDING EXISITING RECORD IN THE school TABLE
        */
      defaultValue: 1, // or any other existing value. (This is very important!)
      references: {
        model: "schools",
        key: "school_id",
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn("students", "school_id");
  },
};

After creating the migration file, head over to your table definitions and add the associations for the respective tables

In student Table, add this association

school.hasMany(student,{ foreignKey: 'school_id', as : 'studentSchool', sourceKey: 'school_id'});

In the school Table, add this association

student.belongsTo(school, {foreignKey: 'school_id', targetKey : 'school_id', as: 'studentSchool'});

When this is done, run the migration file in your terminal

npx sequelize-cli db:migrate

ALSO, BACK THE DATA UP BEFORE DOING THIS (Just in case)