Better-sqlite3: Is there a way to prepare multiple statements into one statement object ?

[yw662]

I am somehow trying to simulate stored procedures by precompiling all the sqls of stored procedures before they are used.

However better-sqlite3 (as well as node-sqlite and node-sqlite3) allow only one sql statement per statement object, or it would be like this:

RangeError: The supplied SQL string contains more than one statement

(actually the rest 2 libs are even worse, they do not allow named parameters).

It means I need to compile the statements line by line into an array, and take case of transactions, since I am not sure how better-sqlite3 handles a single begin; or end; statement.

Any better way to handle multiple statements ?

p.s. at least not db.exec. It does not allow parameters.

[yw662]

I know sqlite3_prepare_v2 do one statement a time.
However, maybe the statement object can hold an array for all the compiled statements ? This is better than holding multiple statement objects in js code.

[JoshuaWise]

better-sqlite3 provides a .transaction() function for grouping multiple statements into a transaction. You still need to compile the statements individually, however.

const db = require('better-sqlite3')('my-database.db');

const statements = [
  'DELETE FROM table WHERE id = @id',
  'INSERT INTO table VALUES (@foo, @bar)',
  'UPDATE table SET foo = @foo, bar = @bar',
].map(sql => db.prepare(sql));

const myTransaction = db.transaction((data) => {
  for (const stmt of statements) {
    stmt.run(data);
  }
});

// Execute transaction
myTransaction({ id: 1, foo: 2, bar: 3 });

You could easily create a helper function to make this more automatic:

function compileTransaction(sqlArray) {
  const statements = sqlArray.map(sql => db.prepare(sql));
  return db.transaction((data = {}) => {
    let result;
    for (const stmt of statements) {
      if (stmt.reader) result = stmt.get(data);
      else stmt.run(data);
    }
    return result;
  });
}