Transactions in node-sqlite3

Question

In node-sqlite3, if the db is currently in serialized mode, will the next statement wait before the callback of the previous statement finishes, or will the callback run at the same time as the next statement?

What's the best way to write a transaction using node-sqlite3? I've thought about these two approaches, but I'm not sure which one is correct, or even if they're both wrong.

// NEXT DB STATEMENT WAITS FOR CALLBACK TO COMPLETE?
db.serialize(() => {

    db.run('BEGIN');

    // statement 1
    db.run(
        sql1,
        params1,
        (err) => {
            if (err) {
                console.error(err);
                return db.serialize(db.run('ROLLBACK'));
            }                           
        }
    );

    // statement 2
    db.run(
        sql2,
        params2,
        (err) => {
            if (err) {
                console.error(err);
                return db.serialize(db.run('ROLLBACK'));
            }

            return db.serialize(db.run('COMMIT));                               
        }
    );  
});



// NEXT DB STATEMENT DOES NOT WAIT FOR CALLBACK TO COMPLETE?
db.serialize(() => {

    db.run('BEGIN');

    // statement 1
    db.run(
        sql1,
        params1,
        (err) => {
            if (err) {
                console.error(err);
                return db.serialize(db.run('ROLLBACK'));
            }

            db.serialize(() => {

                // statement 2
                db.run(
                    sql2,
                    params2,
                    (err) => {
                        if (err) {
                            console.error(err);
                            return db.serialize(db.run('ROLLBACK'));
                        }

                        return db.serialize(db.run('COMMIT));                               
                    }
                );
            });                             
        }
    );
});

Answer 1

I'm going out on a limb and say that db.serialize() is a convenience method that does not involve any magic. It should be possible to serialize a batch of statements by waiting until one finishes before sending the next.

This would also work for transactions, the only thing that must be guaranteed is that no other writes happen to the same db connection object while the statements are being run, to keep the transaction clean (as noted in the discussion thread to node-sqlite3 issue #304).

Chaining would be done by strictly calling the next statement in the callback of the previous one, unless the previous one has returned an error, at which point the execution should be stopped.

This is unwieldy when done by actually stacking callbacks in the source code. But if we promisify the Database#run method, we can use promises:

const sqlite3 = require('sqlite3');

sqlite3.Database.prototype.runAsync = function (sql, ...params) {
    return new Promise((resolve, reject) => {
        this.run(sql, params, function (err) {
            if (err) return reject(err);
            resolve(this);
        });
    });
};

We could have relied on util.promisify for the promisification, but this would result in the loss of one detail of the callback handling in Database#run (from the docs):

If execution was successful, the this object will contain two properties named lastID and changes which contain the value of the last inserted row ID and the number of rows affected by this query respectively.

Our custom variant captures the this object and returns it as the promise result instead.

With that out of the way, we can define a classic promise chain, kicking off with BEGIN, then chaining in any number of statements via Array#reduce, and eventually calling COMMIT on success or ROLLBACK on error:

sqlite3.Database.prototype.runBatchAsync = function (statements) {
    var results = [];
    var batch = ['BEGIN', ...statements, 'COMMIT'];
    return batch.reduce((chain, statement) => chain.then(result => {
        results.push(result);
        return db.runAsync(...[].concat(statement));
    }), Promise.resolve())
    .catch(err => db.runAsync('ROLLBACK').then(() => Promise.reject(err +
        ' in statement #' + results.length)))
    .then(() => results.slice(2));
};

As this builds the promise chain, it also builds an array of statement results that it returns when done (minus two items at the start, the first one being undefined from Promise.resolve(), the second one being the result of BEGIN).

Now we can easily pass several statements for serialized execution inside an implicit transaction. Each member of the batch may be either a stand-alone statement, or an array with statement and associated parameters (just as Database#run would expect it):

var statements = [
    "DROP TABLE IF EXISTS foo;",
    "CREATE TABLE foo (id INTEGER NOT NULL, name TEXT);",
    ["INSERT INTO foo (id, name) VALUES (?, ?);", 1, "First Foo"]
];

db.runBatchAsync(statements).then(results => {
    console.log("SUCCESS!")
    console.log(results);
}).catch(err => {
    console.error("BATCH FAILED: " + err);
});

which would log something like this:

SUCCESS!
[ { sql: 'DROP TABLE IF EXISTS foo;', lastID: 1, changes: 1 },
  { sql: 'CREATE TABLE foo (id INTEGER NOT NULL, name TEXT);',
    lastID: 1,
    changes: 1 },
  { sql: 'INSERT INTO foo (id, name) VALUES (?, ?);',
    lastID: 1,
    changes: 1 } ]

In case of an error, this would cause a roll back, and we would get back the error message from the DB engine, plus "in statement #X" where X refers to the statement position in the batch.