Node.js mysql many to many relationship

Question

I am trying to make for products and categories with many to many relationship between them with node.js and mysql without using Sequelize. Or should I definitely use it?

While connecting to DB created tables with relationships:

connection.connect((err) => {
    if (err) {
        return console.error("error: " + err.message);
    } else {
        console.log("Connected to the MySQL server.");
        let createProducts = `CREATE TABLE IF NOT EXISTS products(
                                product_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                                name VARCHAR (255) NOT NULL, 
                                description VARCHAR (255),
                                price DECIMAL (19,2),
                                sku VARCHAR (255),
                                image VARCHAR (255),
                                created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE now())`;
        let createCategories = `CREATE TABLE IF NOT EXISTS categories(
                                category_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                                name VARCHAR (255) NOT NULL, 
                                created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW())`;
        let createProductsCategories = `CREATE TABLE IF NOT EXISTS products_categories(
                                id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
                                product_id INT UNSIGNED NOT NULL,
                                category_id INT UNSIGNED NOT NULL,
                                FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE CASCADE,
                                FOREIGN KEY (category_id) REFERENCES categories (category_id) ON DELETE CASCADE,
                                created_at TIMESTAMP NOT NULL DEFAULT NOW(),
                                updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW())`;
        connection.query(createProducts, function (err, results, fields) {
            if (err) {
                console.log(err.message);
            }
        });
        connection.query(createCategories, function (err, results, fields) {
            if (err) {
                console.log(err.message);
            }
        });
        connection.query(createProductsCategories, function (err, results, fields) {
            if (err) {
                console.log(err.message);
            }
        });
    }
});

What else should I include in product and category model/controller that relation between them would be working? And data would be inserted into products_categories table while creating a product with category?

Product model:

const Categories = require("category.model");

// constructor
const Product = function (product) {
    this.name = product.name;
    this.description = product.description;
    this.price = product.price;
    this.sku = product.sku;
    this.image = product.image;
};

Product.create = (newProduct, result) => {
    mysql.query("INSERT INTO products SET ?", newProduct, (err, res) => {
        if (err) {
            result(err, null);
            return;
        }
        result(null, {id: res.insertId, ...newProduct});
    });
};

Product controller :

const Product = require("../models/product.model.js");

exports.create = (req, res) => {
    if (!req.body) {
        res.status(400).send({
            message: "Content can not be empty!"
        });
    }
    const product = new Product({
        name: req.body.name,
        description: req.body.description,
        price: req.body.price
    });
    Product.create(product, (err, data) => {
        if (err)
            res.status(500).send({
                message:
                    err.message || "Error occurred while creating the new Product."
            });
        else res.send(data);
    });
};

Product routes:

module.exports = app => {
    const products = require("../controllers/product.controller.js");
    app.post("/products", products.create);

Category model:

const Category = function (category) {
    this.name = category.name;
};

Category.create = (newCategory, result) => {
    mysql.query(`INSERT INTO categories SET ?`, newCategory, (err, res) => {
        if (err) {
            result(err, null);
            return;
        }
        result(null, {id: res.insertId, ...newCategory});
    });
};

Answer 1

Is it required to use Sequelize?

Short answer: No.

Long answer: Sequelize is an ORM which helps you to simplify different DB operations and save your time and energy. Which is why it is recommended to use ORM so that you can focus more on business logic rather than worrying about managing DB operations.

And also Sequelize is not the only ORM through which those things can be achieved.

Here, you can find the list of most popular ORMs.

How the relation between products and categories would work?

Your schema looks fine. Now if you do the insertion correctly it would get the job done.

While inserting, make sure that you follow these steps.

1. Insert into products
2. Insert into categories
3. Take product_id and category_id and insert them into product_categories.