Update Sequelize column with appending to the existing array on SQLite

Question

I have a model which looks like

import {Optional, Model, Sequelize, DataTypes } from 'sequelize';

/*This is the Product model used to save the data about products*/
export interface ProductAttributes {
    productId: number;
    title: string;
    userName: string;
    sellerReview: string;
}

export interface ProductCreationAttributes extends Optional<ProductAttributes, 'productId'> { }

export class Product extends Model<ProductAttributes, ProductCreationAttributes> implements ProductAttributes {
    productId!: number;
    title!: string;
    userName!: string;
    sellerReview: string;

    public static initialize(sequelize: Sequelize) {
        Product.init({
            productId: {
                type: DataTypes.INTEGER,
                autoIncrement: true,
                primaryKey: true,
            },
            title: {
                type: DataTypes.STRING,
                allowNull: false
            },
            userName: {
                type: DataTypes.STRING,
                allowNull: false
            },
            sellerReview: {
                type: DataTypes.STRING,
                get: function () {
                    return JSON.parse(this.getDataValue('sellerReview'));
                },
                set: function (val) {
                    return this.setDataValue('sellerReview', JSON.stringify(val));
                }
            }
        },
            {
                sequelize,
                tableName: 'products'
            }
        );
    }
}

I have a controller from where I will insert and update the values into the model as follows:

import express from 'express';
import { Router, Request, Response, RequestHandler } from 'express';
import { Product } from '../models/product.model';
import { Readable } from 'stream';
import { Sequelize, where } from 'sequelize';

/**
 * This method is to add new products/services to the products model.
 */
productController.post('/add',
    (req: Request, res: Response) => {
        Product.create(req.body)
        .then(product_added => res.send(product_added))
        .catch(err => res.status(500).send(err));
    });

/**
 * This method is to edit a product in the products model.
 */
productController.put('/edit/:id', (req: Request, res: Response) => {
    Product.findByPk(req.params.id)
        .then(found => {
            if (found != null) {
                found.update(req.body).then(() => {
                    res.status(200).send('Product updated successfully.');
                });
            } else {
                res.status(404).send('Product not found.');
            }
        })
        .catch(err => res.status(500).send(err));
    });
    productController.put('/addNewReview/:id', (req: Request, res: Response) => {
        Product.findByPk(req.params.id)
            .then(found => {
                if (found != null) {
                    found.update(
                        {sellerReview: Sequelize.literal( req.body.sellerReview)},
                        {where: {productId: req.params.id}}
                    ).then(() => {
                        res.status(200).send('Riview added successfully.');
                    });
                } else {
                    res.status(404).send('Product not found.');
                }
            })
            .catch(err => res.status(500).send(err));
        });
export const ProductController: Router = productController;

I wanted to update the row with a new review by concatenating it to the existing value of the 'sellerReview' column. For example: Existing column is ["review1", "review2", "review3"]. When I update it to add ["review4"], I wanted it to be ["review1", "review2", "review3", "review4"].

The input to create a new row is like:

{
"title": "product1",
"userName": "user1",
"sellerReview": ["review1", "review2", "review3"]

}

For updating,

{
"title": "product1",
"userName": "user1",
"sellerReview": ["review4"]

}

Using 'Sequelize.literal' doesn't work for me. Can someone help me with this?

Thanks in advance!!

@Anatoly It just rewrites the previous value of the column instead of concatenating. Like ["review4"] instead of ["review1", "review2", "review3", "review4"] — Harika Putta
– Harika Putta, Commented Nov 7, 2020 at 18:27
If Sqlite has something like CONCAT function then you can try it with sequelize.fn('CONCAT', sequelize.col('sellerReview'), req.body.sellerReview) — Anatoly
– Anatoly, Commented Nov 7, 2020 at 18:33
Oh, I didn't see that sellerReview has setter and getter — Anatoly
– Anatoly, Commented Nov 7, 2020 at 18:39
@Anatoly It is inserting this, {"sellerReview": {"fn": "CONCAT", "args": [ { "col": "sellerReview" }, [ "review4" ] ] }, — Harika Putta
– Harika Putta, Commented Nov 7, 2020 at 18:45

Harika Putta · Accepted Answer · 2020-11-09 06:35:39Z

2

Because you use setter and getter for sellerReview field you cannot use Sequelize.literal or Sequelize.fn.

So you can just take the existing value of sellerReview from found and combine it with a new value like this:

found.update({sellerReview: found.sellerReview.concat(req.body.sellerReview)}, { where: {productId: req.params.id}})

edited Nov 9, 2020 at 6:35

Harika Putta

631 silver badge10 bronze badges

answered Nov 7, 2020 at 18:44

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harika Putta Over a year ago

Thanks for your reply. The solution did not work for me. (It should be .concat I guess?). I changed it to .concat but it gave me a 500 error from the database. Can you please help me with this...

Harika Putta Over a year ago

@ Anatoly found.update({sellerReview: found.sellerReview.concat(req.body.sellerReview)}, { where: {productId: req.params.id}}) worked for me!! I think the problem is with JSON.parse. Thanks for the help!!

Collectives™ on Stack Overflow

Update Sequelize column with appending to the existing array on SQLite

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related