How to insert an array into a field with format?

[Nithur-M]

I am using execute command to insert an object into my table with SET. For that, I am using mysql.format method.
When I send data like this: ["one", "two", "three"], the format method removes the array brackets. It causes errors.
check the manual that corresponds to your MySQL server version for the right syntax to use near ''eew', 'qwerty'

What is the workaround for this?

[edit]: sorry, the same error persist if I don't use format and use query instead of execute.

[sidorares]

Coulf you please paste an example of your code

[Nithur-M]

Here is the sample of the object I am sending:
object = {id: '1232321412', title: 'ertert', link: 'https://dget.com/fret', tags: ['sfef','dsds','sfsfs'], date: '32424000'};

Here is the code to save this object to my db:
let connection = await getConnection(); // query database const [rows, fields] = await connection.query('INSERT INTO TABLE SET ?', object);
This causes syntax errors. The console output shows me that array brackets in tags is removed while inserting into MySQL. Why?

[sidorares]

https://github.com/mysqljs/mysql#escaping-query-values

Objects are turned into key = 'val' pairs for each enumerable property on the object. If the property's value is a function, it is skipped; if the property's value is an object, toString() is called on it and the returned value is used.

what you see is a value ['sfef','dsds','sfsfs'] converted into a string

Which query do you want to build from your data? How you prefer tags to be stored? Do you have a separate table for them? This library does not try to be ORM and does not attempt to resolve any relational dependencies

[Nithur-M]

Which query do you want to build from your data?

Insert query. I want to store tags as an array.

[andrey-sidorov-iag]

[Nithur-M]

Yes, it is defined as JSON in the table.

[sidorares]

you can have custom serialization if you pass a parameter with toSqlString on it:

https://github.com/mysqljs/mysql#escaping-query-values

Objects that have a toSqlString method will have .toSqlString() called and the returned value is used as the raw SQL.

example:

function JSONColumnParameter(value) {
  return {
     toSqlString() => `'${JSON.stringify(value)}'`;
  }
}

let object = {id: '1232321412', title: 'ertert', link: 'https://dget.com/fret', tags: JSONColumnParameter(['sfef','dsds','sfsfs']), date: '32424000'};
query database const [rows, fields] = await connection.query('INSERT INTO TABLE SET ?', [object]);

Alternatively just JSON.stringify tags before passing object as a parameter to .query()

[Nithur-M]

Alternatively just JSON.stringify tags before passing object as a parameter to .query()

Yes, that's what I tried. And it works. Thanks for your help.