Advertisement

Sequelize raw query update array of objects as replacements

postgresql rawsql sequelize.js sql

I am using sequelize (postgres) and I need to properly escape a query like this:

`
       UPDATE "Pets"
       SET "name" = CASE LOWER("name")
         ${input.pets
            .map((pet) => `WHEN '${pet.name.toLowerCase()}' THEN '${pet.newName}'`)
            .join('n')}
          ELSE "name"
          END
        WHERE LOWER("name") IN(${input.pets
          .map((pet) => `'${pet.name.toLowerCase()}'`)
          .join(',')});
`

Sample input.pets:

[{ name: "rocky", newName: "leo" }]

Does anyone have an idea how to achieve this with replacements?

I have found a thread on github which suggested something like this:

let data = [ [ 252456, 1, 55, '0' ],
[ 357083, 1, 56, '0' ],
[ 316493, 1, 57, '0' ] ];

db.query(
  `INSERT INTO product (a, b) VALUES ${data.map(a => '(?)').join(',')};`,
  {
     replacements: data,
     type: Sequelize.QueryTypes.INSERT
  }
);

However, a 2d array is being used here not an array of objects. Is there a way to access individual properties from the array? When I try something like this

`
       UPDATE "Pets"
       SET "name" = CASE LOWER("name")
         ${input.pets
            .map((_pet) => `WHEN ? THEN ?`)
            .join('n')}
          ELSE "name"
          END
        WHERE LOWER("name") IN(${input.pets
          .map((_pet) => `?`)
          .join(',')});
`,
    { type: QueryTypes.UPDATE, replacements: input.pets },

The first ? turns out to be the whole object. Is there a way to access it’s properties? I also tried transforming input.pets into a 2d array but still couldn’t get it to work as in example with insert above.

In advance thanks for your time

Advertisement

Answer

    const names = input.pets.map((pet) => pet.name);
    const newNames = input.pets.map((pet) => pet.newName);

`
        UPDATE "Pets"
        SET "name" = CASE LOWER("name")
          ${names.map((_) => `WHEN LOWER(:names) THEN :newNames`).join('n')}
              ELSE "name"
              END
        WHERE LOWER("name") IN(${names.map((_) => `LOWER(:names)`).join(',')});
`,
      { replacements: { names, newNames } },

This works. In cases like this it’s better to work with simpler data structures. Another option I found is using sequelize.escape() built-in function, but it’s not documented so I decided not to

EDIT: After some testing, this works but for only one object in the input

If the input looks something like this:

[
        { name: "rocky", newName: "fafik" }
        { name: "asd", newName: "qwerty" }
]

Then in resut I get queries like this:

WHEN LOWER('rocky', 'asd') THEN 'fafik', 'qwerty'
WHEN LOWER('rocky', 'asd') THEN 'fafik', 'qwerty'

So it doesn’t loop over arrays. Still the problem remains, how to access individual properties, whether from array or an object?

EDIT2: FINAL ANSWER

sequelize.query(
  `
    UPDATE "Pets"
    SET "name" = CASE LOWER("name")
      ${input.pets.map(() => `WHEN ? THEN ?`).join('n')}
         ELSE "name"
         END
    WHERE LOWER("name") IN(?);
  `,
  {
    replacements: [
      ...input.pets.flatMap((x) => [x.name.toLocaleLowerCase(), x.newName]),
      input.pets.map((x) => x.name.toLocaleLowerCase()),
    ],
  },

Advertisement