Any simple function to inserto a row into table from JS Object?

[jfoclpf]

Right now I use this cumbersome approach when I want to add a row whose data is in a JS Object

Adding a row to a table:

var db = mysql.createConnection(DBInfo)

var databaseObj = {val1: '1', name: 'John', age: 40}

var query = 'INSERT INTO my_table ('
var databaseKeys = Object.keys(databaseObj)
for (let i = 0; i < databaseKeys.length; i++) {
  query += databaseKeys[i] + (i !== databaseKeys.length - 1 ? ', ' : ')')
}
query += ' ' + 'VALUES('
for (let i = 0; i < databaseKeys.length; i++) {
  query += '\'' + databaseObj[databaseKeys[i]] + '\'' + (i !== databaseKeys.length - 1 ? ', ' : ')')
}

db.query(query, function (err, results, fields) {...

Is there any simpler or neater way to add a row into a table, where such row data is in a JS Object?

[dougwilson]

Check out the insert example in https://github.com/mysqljs/mysql#escaping-query-values

[jfoclpf]

@dougwilson I had previously carefully read those examples and they don't reply to my question. The function escape transforms an object, that is, "objects are turned into key = 'val' pairs for each enumerable property on the object."

So my object {val1: '1', name: 'John', age: 40} are converted into

`val1` = '1', `name` = 'John', `age` = 40

Now tell me how can I use that escaped string to execute INSERT into table ('val1', 'name', 'age') VALUES ('1', 'John', '40') ?

You never mention such example in the docs

[dougwilson]

This is the example if you want to take your data in a plain javascript object and insert it into a table:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
  if (error) throw error;
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

If it does not work for you, please can you demonstrate that and show what error you are getting?

[dougwilson]

Here is your original code written that way if it helps:

var db = mysql.createConnection(DBInfo)

var databaseObj = {val1: '1', name: 'John', age: 40}

var query = 'INSERT INTO my_table SET ?'

db.query(query, databaseObj, function (err, results, fields) {...

[jfoclpf]

WAU, thanks, that's because I was not ware of the instruction SET and I was not understanding it. I will try out. All the mysql examples I've seen to insert never mentioned SET and that's why I was not understanding you line

INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Thanks again

PS: I was missing this: https://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set

[jfoclpf]

Thanks again, it worked :)