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');
}
});
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) => { ... });
Error: SQLITE_ERROR: table tablename has 4 columns but 1 values were supplied
INSERT INTO tablename (json) VALUES (?)json?name, age, language and values are 'John', 34, 'english'. I want to run INSERT INTO tablename (name, age, language) VALUES ('John', 34, 'english');Try ?,?,? and Obj.values()
code.
const values = {
name: 'John',
age: 34,
language: 'english'
};
paramString = "?";
for (var i = 0; i < Object.keys(values).length -1 ; i ++) paramString += ",?";
// db.run('INSERT INTO tablename VALUES ('+paramString + ')', Object.values(values));
console.log('INSERT INTO tablename VALUES ('+paramString + ')',Object.values(values));
INSERT INTO tablename (name age language) VALUES (John 34 english). This wont work.Error: SQLITE_ERROR: table tablename has 3 columns but 1 values were supplied.
... operator ...Object.values(values)console.log(...Object.values(values) prints John 34 english. The commas are missing.In case you're coming from React Native environment, inserting objects to SQLite works perfectly fine on iOS, and you even don't have to restructure the object.
However, for SQLite on Android behaves differently. Trying to insert an objects lags (at least my Android simulator) it so much, and there was no single response from the SQL transaction. In addition to that, I had to pass into the parameters values from the destructured object, because doing so in the SQL query, like in the accepted answer, didn't work for me.
It's a really strange behaviour, but I've also noticed the app has started working way faster on the Android simulator after I've removed inserting the object to the SQLite.
