Does SQLite3 have prepared statements in Node.js?

Question

From the npm docs, only visible prepared statements are for insert. Does these prepared statement work for Select, update, and delete?

I tried for select, there isn't a .each function where the rows are called back. Anyone been able to do this or have links to resources, cause I can sure as hell unable to find any.

I have noticed the same thing when testing with mapbox/node-sqlite3 that I don't get data back when using prepared statements for SELECT, only INSERT. DELETE is working for me as a prepared statement but in my case my query isn't trying to return anything when I delete. — ABCD.ca, Nov 23 '17 at 19:06

score 32 · Accepted Answer · answered May 09 '15 at 13:47

32

According to the node-sqlite3 API documentation, you can use parameters in your SQL queries in several different ways:

// Directly in the function arguments.
db.run("UPDATE tbl SET name = ? WHERE id = ?", "bar", 2);

// As an array.
db.run("UPDATE tbl SET name = ? WHERE id = ?", [ "bar", 2 ]);

// As an object with named parameters.
db.run("UPDATE tbl SET name = $name WHERE id = $id", {
  $id: 2,
  $name: "bar"
});

answered May 09 '15 at 13:47

c.hill

3,127
25
31

3

Izc is asking about prepared statements that use SELECT, this shows a workaround but doesn't answer the question. – ABCD.ca Nov 23 '17 at 19:06

score 20 · Answer 2 · answered Mar 16 '18 at 19:58

Yes, prepared statements are supported.

With node-sqlite3:

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('data.db');

db.serialize(function() {

  var stmt = db.prepare("INSERT INTO users VALUES (?,?)");
  for (var i = 0; i < 10; i++) {
      stmt.run("user " + i, "email " + i);
  }
  stmt.finalize();

  stmt = db.prepare("SELECT * FROM users WHERE id=?");
  stmt.each(userId, function(err, row) {
      console.log(row.name, row.email);
  }, function(err, count) {
      stmt.finalize();
  });

});

With better-sqlite3:

var Database = require('better-sqlite3');
var db = new Database('foobar.db', options);

var stmt = db.prepare("INSERT INTO users VALUES (?,?)");
for (var i = 0; i < 10; i++) {
    stmt.run("user " + i, "email " + i);
}

var stmt = db.prepare('SELECT * FROM users WHERE id=?');
var row = stmt.get(userId);
console.log(row.name, row.email);

Does SQLite3 have prepared statements in Node.js?

2 Answers2

Linked

Related