Better-sqlite3: How to use transactions

[JoshuaWise]

UPDATE

Transactions have been reworked in version 5.0.0, and they are now well-documented here.

ORIGINAL POST

In response to https://github.com/JoshuaWise/better-sqlite3/issues/48#issuecomment-316179745

You can use db.transaction() like this:

var transaction = db.transaction([
  'INSERT INTO movies (title, rating) VALUES (@title, @rating)',
  'INSERT INTO cast_members (name, movie) VALUES (@actorName, @title)'
]);
transaction.run({ title: 'Taxi Driver', rating: 100, actorName: 'Robert De Niro' });

If you need to run javascript code in your transaction, you must manage the transaction manually. This is easier if you create a nice utility like this:

var begin = db.prepare('BEGIN');
var commit = db.prepare('COMMIT');
var rollback = db.prepare('ROLLBACK');

// Higher order function - returns a function that always runs in a transaction
function asTransaction(func) {
  return function (...args) {
    begin.run();
    try {
      func(...args);
      commit.run();
    } finally {
      if (db.inTransaction) rollback.run();
    }
  };
}

Then you can run arbitrary transactions like this:

var insertMovie = db.prepare('INSERT INTO movies (title, rating) VALUES (@title, @rating)');
var insertActor = db.prepare('INSERT INTO cast_members (name, movie) VALUES (@actorName, @title)');

var insertMovieAndActor = asTransaction(function (data) {
  insertMovie.run(data);
  // run arbitrary javascript code
  insertActor.run(data);
});

insertMovieAndActor({ title: 'Taxi Driver', rating: 100, actorName: 'Robert De Niro' });

[Custardcs]

thank you bud for all the help :D

[souravsaraf]

I have a question about Transactions. (I hope this is the right place to ask)

Scenario : Suppose my sqlArray contains 100 insert statements. the 41st row contains an error in the data. Now, we can have 3 cases :

1. Inserts 0 rows at the end. Basically Rollback on error.
2. Inserts 40 rows and exits on error.
3. Inserts 99 rows and ignores the error.

The current method of using db.transaction() achieves case 1. Is there any way to achieve the other 2 cases by probably supplying some arguments to db.transaction() or similar.

P.S : I think it can be achieved by using try-catch , begin , commit , and db.prepare / db.exec combinations. But, is it possible via db.transactions().

Thanks

[Custardcs]

How we experience any issue at the moment is if there is an issue at the moment with an insert the whole dB locks up.. users have to close the application and re open.. haven’t found a way around it yet.

[JoshuaWise]

@souravsaraf Currently no. In order to achieve the effect you want, you'll need to manage the transaction yourself with BEGIN, COMMIT, etc.

[JoshuaWise]

@Custardcs that sounds like a bug, can you open an issue describing it?

[dimitry-ishenko]

May I suggest adding begin/commit/rollback helper methods to the Database object?
So that you can do e.g.:

const sqlite = require('better-sqlite3');
var db = new sqlite('foo.sqlite');

db.begin();
try {
    // doing stuff
    db.commit();
} finally {
    if(db.inTransaction) db.rollback();
}

[dimitry-ishenko]

I ended up writing a tiny sqlite3.js wrapper like this:

'use strict';

const Database = require('better-sqlite3');

Database.prototype.begin    = function() { this.prepare('BEGIN'   ).run(); }
Database.prototype.commit   = function() { this.prepare('COMMIT'  ).run(); }
Database.prototype.rollback = function() { this.prepare('ROLLBACK').run(); }

module.exports = Database;

[JoshuaWise]

@dimitry-ishenko I don't want to add such methods to the library because there are many different kinds of "begin" and "commit" statements (such as BEGIN IMMEDIATE etc.).

As for your wrapper, you can improve performance by caching the prepared statements.

'use strict';

const Database = require('better-sqlite3');

const begin = this.prepare('BEGIN');
const commit = this.prepare('COMMIT');
const rollback = this.prepare('ROLLBACK');

Database.prototype.begin = function() { return begin.run(); }
Database.prototype.commit = function() { return commit.run(); }
Database.prototype.rollback = function() { return rollback.run(); }

module.exports = Database;

[dimitry-ishenko]

@JoshuaWise thank you for your suggestion and thanks for being performance oriented :smile:

But wait... I don't have access to this in here:

const begin = this.prepare('BEGIN');
const commit = this.prepare('COMMIT');
const rollback = this.prepare('ROLLBACK');

So this won't work... :disappointed: Do I even need to prepare then? I think I can just exec directly.

[JoshuaWise]

@dimitry-ishenko Sorry, I jumped the gun there. You'll need to do that with a constructed database object, like this:

'use strict';

const Database = require('better-sqlite3');

function makeDatabase(...args) {
  const db = new Database(...args);
  const begin = db.prepare('BEGIN');
  const commit = db.prepare('COMMIT');
  const rollback = db.prepare('ROLLBACK');
  db.begin = () => begin.run();
  db.commit = () => commit.run();
  db.rollback = () => rollback.run();
  return db;
}

module.exports = makeDatabase;

[ronburk]

First post in this thread really needs to be in wiki. I knew I had seen it, but then had a devil of a time finding it when I wanted it again.

[pke]

I wonder, @JoshuaWise is there any reason you wrote your code like this:

try {
  func(...args);
  commit.run();
} finally {
  if (db.inTransaction) rollback.run();
}

instead of

try {
  func(...args);
  commit.run();
} catch (error) {
  if (db.inTransaction) rollback.run();
  throw error
}

[JoshuaWise]

@pke Nope, either way is fine.

[JoshuaWise]

Transactions have been reworked in version 5.0.0, and they are now well-documented here.