Can ? placeholders be used in an INSERT with sqlite3?


#1

Question

can ? placeholders be used in an INSERT with sqlite3?

Answer

Yes, they can. We just need to be careful with how we phrase the insertion, because when using ? as a placeholder, the VALUES will take place in sequential order. For example, regularly we could make this kind of insert:

db.run("INSERT INTO Questions_Table( question, user ) VALUES ('can ? placeholders be used in an INSERT with sqlite3?', 'Axel')");

/*or you can also regularly write:*/

let learner = {
  question: 'can ? placeholders be used in an INSERT with sqlite3?',
  user: 'Axel'
 }
db.run(`INSERT INTO Questions_Table(question, user) VALUES VALUES (`${learner.question}`, `${learner.name}`)`)

but by securing it with ? placeholders, will also use db.run's capacity to take a second argument which is an array of values, and each value will be assigned in sequential order, for example if we have a table of baby names and we want to insert one or more each name inserted will take the place of the values placeholder. ie.:

db.run("INSERT INTO baby_names(name) VALUES(?)", ['Johnny']);

in this case we specify that there will be one value, what about multiple values?

db.run("INSERT INTO uncommon_baby_names(name) VALUES(?),(?),(?)", ['Demitri', 'Axel', 'Rocket']);

Lastly, we can also have multiple columns and using an array of arrays:

db.run("INSERT INTO Rentals(type, price) VALUES (?,?), (?, ?), (?,?)", [['House', 3000], ['Apartment', 1200], ['Loft', 5000] ]);