node.jspostgresqlexpresssequelize.jssequelize-cli

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


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")
  },
}

Solution

  • 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.