Transaction API

[kkaefer]

// Flat interface.
var trx = db.begin();
trx.on('commit', function() {});
trx.on('rollback', function() {});


trx.rollback();
trx.commit(); // No implicit commit when calling db.begin();

// Creates a new stack
db.transaction(function(trx) {
    trx == this
    // implicit begin

    // transaction objects are like database objects:

    trx.run("CREATE TABLE foo (id INT PRIMARY KEY, bar)");
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "first text");
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "second text"); // will fail
    trx.run("INSERT INTO foo VALUES(?, ?)", 1, "third text", function(err) {
        if (err) {
            // Do nothing and the error will be ignored

            // See below for more explanation
            trx.clear();
            trx.rollback();
            trx.commit();
        }
    });

    trx.on('rollback', function() {
        // This is emitted after the transaction was rolled back.
    });

    trx.on('commit', function() {
        // This is emitted after the transaction was committed successfully.
    });

    trx.on('error', function() {
        // This will be called when a statement execution doesn't have a callback
        // and no error handlers.

        // removes all further statements from the stack. You can now add more
        // statements to the stack. the automatic commit on empty stack persists.
        // Does not remove the error handler, you have to do that manually.
        trx.clear();

        // removes all further statements from the stack and does a rollback of the
        // entire transaction.
        trx.rollback();

        // removes all further statements from the stack and commits the transaction
        trx.commit();
    });

    // when no error handler is attached and an error bubbles up to the transaction's
    // error event, the transaction is rolled back and the rollback event is emitted.

    // implicit commit when the stack is empty.
});

[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.

[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;
  }

[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