sqlite3 – insert – javascript object as values

What is the easiest solution to use a javascript object as values for a sqlite3 insert? The following code does not work.

const values = {
  name: 'John',
  age: 34,
  language: 'english'
};

db.run('INSERT INTO tablename VALUES (?)', values, (err) => {
  if (err) {
    console.log(err);
  } else {
    console.log('success');
  }
});

Answer

First of all you need to write the SQL correctly. To insert into the columns name, age, language, you need to write the SQL like this:

INSERT INTO tablename (name, age, language) VALUES (?, ?, ?)

And pass the values of the 3 columns as parameters.

db.run('INSERT INTO tablename (name, age, language) VALUES (?, ?, ?)', [values['name'], values['age'], values['language']]), (err) => { ... });

Or if the property names in the JavaScript object correspond directly to the column names, then you can generate the correct SQL string dynamically to have more flexibility:

const cols = Object.keys(values).join(", ");
const placeholders = Object.keys(values).fill('?').join(", ");
db.run('INSERT INTO tablename (' + cols + ') VALUES (' + placeholders + ')', Object.values(values)), (err) => { ... });