why sequelize is trying to remove a foreign key that doesn't even exist?

Viewed 73

i'm brand new to Test driven development , i'm trying to play around and test some functions , but there's error arises in the sequelize.sync method , i will mark it below , i tried to remove the alter:true option and it worked but i don't know why this behavior occurs as the table is the same as the model and i dont even have the Users_ibfk_1 that he is talking about and claiming that i'm trying to remove it

i have 3 models: user.js

const { DataTypes } = require("sequelize");

module.exports = (sequelize) => {
  sequelize.define("User", {
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        is: /[a-zA-Z]+/,
        len: [2, 10],
      },
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false,
      validate: {
        is: /[a-zA-Z]+/,
        len: [2, 10],
      },
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        isEmail: { msg: "please provide valid email" },
      },
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  });
};

role.js:

const { DataTypes } = require("sequelize");

module.exports = (sequelize) => {
  sequelize.define("Role", {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    description: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
  });
};

and this is the association.js :

  const { User, Role, Task } = sequelize.models;

  Role.hasMany(User);
  User.belongsTo(Role);
  User.hasMany(Task);
  Task.belongsTo(User);
};

module.exports = { applyAssociation };

and in the index.js of sequelize folder i have this setup :

const Sequelize = require("sequelize");
const { applyAssociation } = require("./associations");
require("dotenv").config();

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USERNAME,
  process.env.DB_PASSWORD,
  {
    host: process.env.HOST,
    dialect: "mysql",
    logging: false,
  }
);

const modelDefiners = [
  require("./models/user"),
  require("./models/role"),
  require("./models/task"),
];

modelDefiners.map((modelDefiner) => {
  modelDefiner(sequelize);
});

const checkDatabaseConnection = async () => {
  try {
    await sequelize.authenticate();
    console.log("connected to the database successfully");
  } catch (error) {
    console.error(error);
  }
};

applyAssociation(sequelize);

const syncTableWithModel = async () => {
  try {
    await sequelize.sync({ alter: true }); ============>>>>>>>>>> this is the function that arises the error

    console.log("synced correctly with the table");
  } catch (error) {
    console.log(error);
  }
};
const initializeDatabase = async () => {
  await checkDatabaseConnection();
  await syncTableWithModel();
};

module.exports = { initializeDatabase, sequelize };

and this is my test file:

const { app, initializeDatabase } = require("./server");

beforeAll(async () => {
  await initializeDatabase();
});
describe("user API", () => {
  it("GET /user --> array of users", async () => {
    await request(app)
      .get("/api/v1/user/")
      .expect("Content-Type", /json/)
      .expect(200)
      .then((response) => {
        expect(response.body).toEqual(
          expect.objectContaining({
            status: "success",
            response: expect.any(String),
          })
        );
      });
  });
  // it("GET /task/id --> certain task", () => {});
  // it("POST /task --> create task", () => {});
});

and this is the error that arrises on the test but the code works really good without the test :

   Error: 
        at Query.run (/home/esraa/task/TodoApp/backend/node_modules/sequelize/src/dialects/mysql/query.js:46:25)
        at /home/esraa/task/TodoApp/backend/node_modules/sequelize/src/sequelize.js:641:28
        at processTicksAndRejections (node:internal/process/task_queues:96:5)
        at MySQLQueryInterface.removeConstraint (/home/esraa/task/TodoApp/backend/node_modules/sequelize/src/dialects/mysql/query-interface.js:86:12)
        at Function.sync (/home/esraa/task/TodoApp/backend/node_modules/sequelize/src/model.js:1402:17)
        at Sequelize.sync (/home/esraa/task/TodoApp/backend/node_modules/sequelize/src/sequelize.js:816:9)
        at syncTableWithModel (/home/esraa/task/TodoApp/backend/sequelize/index.js:39:5)
        at initializeDatabase (/home/esraa/task/TodoApp/backend/sequelize/index.js:47:3)
        at /home/esraa/task/TodoApp/backend/server.js:13:5 {
      name: 'SequelizeDatabaseError',
      parent: Error: Can't DROP 'Users_ibfk_1'; check that column/key exists
          at Packet.asError (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/packets/packet.js:728:17)
          at Query.execute (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/commands/command.js:29:26)
          at Connection.handlePacket (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:456:32)
          at PacketParser.onPacket (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:85:12)
          at PacketParser.executeStart (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/packet_parser.js:75:16)
          at Socket.<anonymous> (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:92:25)
          at Socket.emit (node:events:527:28)
          at addChunk (node:internal/streams/readable:315:12)
          at readableAddChunk (node:internal/streams/readable:289:9)
          at Socket.Readable.push (node:internal/streams/readable:228:10) {
        code: 'ER_CANT_DROP_FIELD_OR_KEY',
        errno: 1091,
        sqlState: '42000',
        sqlMessage: "Can't DROP 'Users_ibfk_1'; check that column/key exists",
        sql: 'ALTER TABLE `Users` DROP FOREIGN KEY `Users_ibfk_1`;',
        parameters: undefined
      },
      original: Error: Can't DROP 'Users_ibfk_1'; check that column/key exists
          at Packet.asError (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/packets/packet.js:728:17)
          at Query.execute (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/commands/command.js:29:26)
          at Connection.handlePacket (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:456:32)
          at PacketParser.onPacket (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:85:12)
          at PacketParser.executeStart (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/packet_parser.js:75:16)
          at Socket.<anonymous> (/home/esraa/task/TodoApp/backend/node_modules/mysql2/lib/connection.js:92:25)
          at Socket.emit (node:events:527:28)
          at addChunk (node:internal/streams/readable:315:12)
          at readableAddChunk (node:internal/streams/readable:289:9)
          at Socket.Readable.push (node:internal/streams/readable:228:10) {
        code: 'ER_CANT_DROP_FIELD_OR_KEY',
        errno: 1091,
        sqlState: '42000',
        sqlMessage: "Can't DROP 'Users_ibfk_1'; check that column/key exists",
        sql: 'ALTER TABLE `Users` DROP FOREIGN KEY `Users_ibfk_1`;',
        parameters: undefined
      },
      sql: 'ALTER TABLE `Users` DROP FOREIGN KEY `Users_ibfk_1`;',
      parameters: {}
    }

      at syncTableWithModel (sequelize/index.js:43:4)
1 Answers

I was having this issue as well.

For me, it was previously working and then stopped working on a subsequent sync. I went into my database and added the missing constraint, it was then able to drop it the next time sync was run.

Related