Sequelize model insert with array datatypes (postgresql)

Question

Hi can anyone help me how to create sequelize model that can be inserted with array datatypes. My scenario is that I have 2 models (product & order), and what I want to do is that in order model I have order_item which will be array of product_id and refers to product table, so that I can get the rest of needed attributes.

**Note: I planned to use with raw queries

Here is my code:

Model Product

const { DataTypes } = require("sequelize");
const sequelize = require("../config");

const Product = sequelize.define(
 "products",
{
    id: {
      primaryKey: true,
      type: DataTypes.UUID,
      allowNull: false,
},
    p_image: {
    type: DataTypes.TEXT,
    allowNull: false,
},
    p_name: {
    type: DataTypes.STRING,
    allowNull: false,
},
    p_desc: {
    type: DataTypes.TEXT,
    allowNull: false,
},
    p_prize: {
    type: DataTypes.INTEGER,
    allowNull: false,
},
    p_size: {
    type: DataTypes.STRING,
    allowNull: false,
  },
 },
{
  freezeTable: true,
 }
  );
  module.exports = Product;

Model Order

const { DataTypes, Deferrable } = require("sequelize");
const sequelize = require("../config");
const Product = require("./model_product");

const Order = sequelize.define("orders", {
     id: {
        primaryKey: true,
        type: DataTypes.UUID,
        allowNull: false,
    },
    o_items: {
        type: DataTypes.ARRAY(DataTypes.UUID),
        allowNull: false,
        references: {
          model: Product,
          key: "id",
          deferrable: Deferrable.INITIALLY_IMMEDIATE,
        },
    },
    o_quantity: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    o_total_price: {
        type: DataTypes.INTEGER,
        allowNull: false,
    },
    });
module.exports = Order;

I planned to make the insert query like below:

  const sql = `INSERT INTO orders (id, o_items, o_quantity, o_total_price, "createdAt", "updatedAt")
    VALUES (?, ?, ?, ?, now(), now())
    ON CONFLICT (id) DO NOTHING
    RETURNING *;`;

When I start server npm start I received cannot be implemented:

CREATE TABLE IF NOT EXISTS "orders" ("id" UUID NOT NULL , "o_items" UUID[] NOT NULL REFERENCES "products" ("id") DEFERRABLE INITIALLY IMMEDIATE, "o_quantity" INTEGER NOT NULL, "o_total_price" INTEGER NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")); 
foreign key constraint "orders_o_items_fkey" cannot be implemented

EDIT

I checked and seemed its not possible to store array of foreign key, so I changed my model to m:n relations between product & order through order_items like below:

But still my question is how to insert array of products, let say ex: user in one order he can order 3 different products. Sorry if this question is stupid, but I'm new to this and anyone please help for this matter and also I wanted to do it with query because thats the requirement for the project. Thanks.

user_5718610 · Answer

See add(), $create() Something like so

const order = await this.orderModel.create(createOrderDto, options)
const orderItems = await order.$add('product', [productId1, productId2, productId3])

I think it work not only with id but with loaded entities

 const product1 = Product.findOne(1)
 const product2 = Product.findOne(2)
 const orderProduct = await order.$add('product', [product1, product2])

And to add new image to product for example

product!.$create('productsImage', image)),

Sequelize model insert with array datatypes (postgresql)

1 Answers