How do i migrate sequelize in node js with 2 table referencing each other?

Question

How do I migrate sequelize with 2 table referencing each other? for example i have table roles & users, in table roles i have column created_by referencing to table users and in table users i have column roles_id referencing to roles, when i tried to run normally it says "relation roles does not exist"

Here is my code:

"use strict"

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // roles
    await queryInterface.createTable("roles", {
      id: {
        type: Sequelize.BIGINT,
        primaryKey: true,
        autoIncrement: true,
      },
      created_by: {
        type: Sequelize.BIGINT,
        allowNull: false,
        references: {
          model: "users",
          key: "id",
        },
        onDelete: "CASCADE",
        onUpdate: "CASCADE",
      },
    })

    await queryInterface.addIndex("roles", {
      fields: ["name"],
    })

    // users
    await queryInterface.createTable("users", {
      id: {
        type: Sequelize.BIGINT,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true,
      },
      roles_id: {
        type: Sequelize.BIGINT,
        allowNull: false,
        references: {
          model: "roles",
          key: "id",
          onDelete: "CASCADE",
          onUpdate: "CASCADE",
        },
      },
    })
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable("roles")
    await queryInterface.dropTable("users")
  },
}

JohnH · Answer 1 · 2023-04-25T21:57:16.083

Don't create mutually referential tables. The error relation roles does not exist is raised because the migration is attempting to create a foreign key reference to roles, but that table hasn't been created yet. The proper way to model the relation between users and roles is with an associative table. The following two table creation statements are functionally identical, choose either one (I don't use sequelize, so I'm presenting these as raw SQL):

CREATE TABLE users_roles(
  user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  role_id bigint NOT NULL REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT users_roles_uk UNIQUE (user_id, role_id)
);

CREATE TABLE users_roles(
  user_id bigint REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
  role_id bigint REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT user_roles_uk PRIMARY KEY (user_id, role_id)
);

These statements create a table that defines a many-to-many relationship between users and roles. The constraint, users_roles_uk, prevents redundant occurrences. To convert to a one-to-one relationship (which is the model the original migration would have created), add unique constraints to user_id and role_id.

So its better to seperate the role_id column in the table user to a new table? is this the best practice for it? is this solution only good for this case or its better in general everytime it has 2 table referencing each other? how about if i have 3 table referencing ex: in table user i have id_role and id_user_type both table have created_by column, should i make table user_roles and user_type? btw thank youu — Twigu, Apr 26 '23 at 13:15
There are several ways to model associations between users and roles. For one-to-one and one-to-many associations, it is often sufficient to pick one of the tables to have a reference to the other without resorting to a third table. The best approach to modeling a particular association depends on how the association will be used. — JohnH, Apr 28 '23 at 02:45

How do i migrate sequelize in node js with 2 table referencing each other?

1 Answers1