Insert key value pairs from array into SQL query string

Question

I have this code with array:

const dataToset = [
    { key: 'key1', value: "some data" },
    { key: 'key2', value: 37 },
];

await db.query(`
    UPDATE some_table 
    SET ${dataToset.map(data => `${data.key} = ${data.value}`)}
    WHERE id ${id}`);

result query is next: UPDATE some_table SET key1=some data,key2=37 WHERE id=1 and it fails, because some data should be passed as string. How to get valid query: UPDATE some_table SET key1="some data",key2=37 WHERE id=1?

p.s. I am getting next error: syntax error near "data"

Isn't it WHERE id=1?

Yevhen Horbunkov
– Yevhen Horbunkov

2020-05-29 06:49:35 +00:00
Commented May 29, 2020 at 6:49 — Yevhen Horbunkov
– Yevhen Horbunkov, Commented May 29, 2020 at 6:49
nope, fixed it here.

Ted
– Ted

2020-05-29 07:01:10 +00:00
Commented May 29, 2020 at 7:01 — Ted
– Ted, Commented May 29, 2020 at 7:01

Yevhen Horbunkov · Accepted Answer · 2020-05-29 07:16:08Z

1

Aside from syntax error (it has to be WHERE id=1), I would do explicit array to string conversion, using .join(', ') and somewhat change your template string, paying attention that (only) string value should be enclosed into single quotes:

const dataToset = [
          { key: 'key1', value: "some data" },
          { key: 'key2', value: 37 },
      ],
      id = 1
      query = `
        UPDATE some_table
        SET ${
          dataToset
            .map(({key,value}) => 
              typeof value == 'string' ? 
              key+"='"+value+"'" : 
              key+'='+value)
            .join(', ')
        }
        WHERE id=${id}
      `

console.log(query)

.as-console-wrapper{min-height:100%;}

edited May 29, 2020 at 7:16

answered May 29, 2020 at 7:10

Yevhen Horbunkov

15.6k3 gold badges27 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Insert key value pairs from array into SQL query string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related