Insert values from object, using 'INSERT INTO table SET ?' ... ?

[mjcampagna]

Given something like this using MySQL ...

let movieInfo = {
	id: 123,
	title: 'Hit Movie'
};

pool.getConnection()
.then(conn => {

	// This works in MySQL
	conn.query('INSERT INTO movies SET ?', movieInfo, (err, res) => ...

... trying to do the equivalent thing using MariaDB. Meaning, the object Keys should be used as column names, and values as the values to be inserted. Is this possible, or am I missing something?

[rusher]

hmm, I didn't see that they have this possibility. replacing a '?' by a combination of values doesn't respect the usual prepared statement format. It seems an open door to issues/incomprehension.

After confirming that mysql works this way, i've found this issue: mysqljs/mysql#731.

Some queries like :
conn.query('update someTable set ? where id=?', [ {t1: 1, t2: 2}, 1])
would be better written in
conn.query('update someTable set t1=?, t2=? where id=?', [ 1, 2, 1])

Using object directly can help writing less code, but in this case, i kind of agree that this non-traditional way can lead to errors, even lead to injection if used the bad way.

I'll create an option to permit that kind of query to permit compatibility, but disabled by default

[mjcampagna]

Sounds good. Thanks!

[rusher]

done with 852ca61 in develop branch. It will be release in next version