Sequelize MySQL handling native reference errors in Queries (ManyToMany)

Question

I am new to MySQL and Sequelize and try to implement an error handling for a many to many relation with a reference error. I have the following relation Post > CategoryPost < Category relation. The migration of my Joining Table looks like this

module.exports = {
   up: async (queryInterface, Sequelize) => {

    await queryInterface.createTable("CategoryPost", {
      CategoryId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        references: {
          model: "categories",
          key: "id",
        },
      },
      PostId: { type: Sequelize.INTEGER, primaryKey: true },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
},

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

With the reference I try to prevent posts with non existing categories in my joining table. Which seems to work. Here is my CREATE POST Router incl. Controller.

router.post("/", async (req, res) => {
  let { userUuid, title, body, categories } = req.body;
  if (!categories) {
    return res.status(400).json({ msg: "Pls provide category" });
  }
  try {
    const user = await User.findOne({ where: { uuid: userUuid } });
    const post = await Post.create({
      userId: user.id,
      title,
      body,
    });
    await post.addCategories(categories);
    return res.json(post);
  } catch (err) {
    console.log(err);
    res.status(500).json({ error: "Something went wrong"});
  }
});

It works as expected. But if i try to insert post with non existing category I got a very ugly "native" mysql error, which I struggle to handle in a good manner.

My goal is to send a correct error msg "Please provide post with valid category", if there is a undefined category

The only thing i can think of is to check against Category.find(all). But it feels like double implementing the native db functionality of a foreign key and causes extra requests.

Sorry for my English, I hope the question became clear.

Answer 1

Your joining table should have its own primary key not related to neither 'Post' foreign key nor Category one. That's one common way how many-to-many table should work in general. Unfortunately Sequelize does not support composite primary keys so it's useless to indicate both CategoryId and PostId as a primary key (that definitely might be the second way).

And also you forgot to indicate PostId as a foreign key to posts.

await queryInterface.createTable("CategoryPost", {
      Id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      CategoryId: {
        type: Sequelize.INTEGER,
        references: {
          model: "categories",
          key: "id",
        },
      },
      PostId: { type: Sequelize.INTEGER,
        references: {
          model: "posts",
          key: "id",
        },
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
},