Transaction API about node-sqlite3

[koistya]

If this library is used with ES2015+ async/await (via sqlite), how can you make sure that some extraneous queries are not being injected into the transaction?

  await db.run('BEGIN');
  try {
    await db.exec(sql);
    await db.run('COMMIT');
  } catch (err) {
    await db.run('ROLLBACK');
    throw err;
  }

[Pita]

Yes, that would be awesome +1

[kkaefer]

@Pita: Please note that transactions are already possible with node-sqlite3, there's just no specific interface for it. Nothing stops you from running db.run("BEGIN"); /* some queries inside the transaction */ db.run("COMMIT"). You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

[Pita]

I know that it's possible. I'm already doing it like this. But it's a ugly way cause I have to build a huge sql string manually.

var sql = "BEGIN TRANSACTION;\n";
  for(var i in bulk)
  {
    if(bulk[i].type == "set")
    {
      sql+="REPLACE INTO store VALUES (" + escape(bulk[i].key) + ", " + escape(bulk[i].value) + ");\n";
    }
    else if(bulk[i].type == "remove")
    {
      sql+="DELETE FROM store WHERE key = " + escape(bulk[i].key) + ";\n";
    }
  }
  sql += "END TRANSACTION;";

   this.db.exec(sql, function(err){ ...

[kkaefer]

You don't have to build a single string; you can just use the regular APIs between db.run("BEGIN") and db.run("COMMIT")

[miccolis]

I'd also really love to see this API implemented. I'm refactoring https://github.com/developmentseed/couch-sqlite to work with long lived connections and it would be great to use this, rather than a pool of one connection.

/cc @gundersen

[kkaefer]

2 eggrolls.

You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

What's the best way to do this? Is it sufficient to put the transaction inside db.serialize? Will that block other non-db things like http server events?

[JoshuaWise]

You only have to pay attention that no other code holding the database handle injects queries inside the transaction that shouldn't be there.

What's the best way to do this? Is it sufficient to put the transaction inside db.serialize? Will that block other non-db things like http server events?

I'd like an answer to ghost's question, please. It's not clear in the docs.

[bpasero]

I would like to know under which condition a ROLLBACK should be issues? It is not clear to me if this is done automatically by the engine. In other words, I would assume that upon any failure, the entire transaction is being rolled back to the state before. Otherwise, what would be the motivation to use a transaction in the first place?

[daniellockyer]

Clsoing in favor of #304