squeamish

Squeamish

A minimal wrapper around the node sqlite3 module to provide basic TypeScript and Promise (async/await) support. It also includes support for transactions and RxJS Observables. Of course, it works with plain old JavaScript too!

Transactions

Obviously with any SQLite connection you could create a transaction using the SQLite BEGIN syntax. However, if your code is running asynchronously, there's nothing there to stop other events causing statements to be executed within your transaction. Squeamish generates a logical database handle within the transaction for your queries to execute on, and locks the original handle until the transaction is closed. It also supports nested transactions using the SAVEPOINT syntax.

The upshot of this is that while a transaction t is open, promises on the outer database object (or outer tranasction) will not return. If you are expecting them to return before you progress to commit t, you will deadlock. It is safe to await on them from another function, however. So:

//WILL DEADLOCK:
async function f(db: Database) {
  const t = db.beginTransaction()

  //This next line will block:
  const items = await db.allAsync('SELECT…');
  await t.runAsync('INSERT…);

  //Because it won't return until this line is executed:
  await t.commit();
}

//WON'T DEADLOCK:
async function f1(db: Database) {
  const t = db.beginTransaction()
  await t.runAsync('INSERT…);
  await t.commit();
}

async function f2(db: Database) {
  return db.allAsync('SELECT *…');
}

async function f(db: Database) {
  f1(db);
  // f2() will probably try allAsync() while the transaction is open
  // it is safe to do so but will wait for it to close first.
  // If you don't want to wait, use multiple Database objects.
  const items = await f2(db);
}

Basic Usage

Promisification follows the convention of Bluebird.promisifyAll, so the API follows the standard sqlite3 module, with Async appended to method names and the final callback removed. Note that prepared statement usage is different, see the example below:

Example

import { Database, Statement } from 'squeamish';
    
async function testDB() {
  const db = new Database(':memory:');

  await db.execAsync('CREATE TABLE people (firstname TEXT, lastname TEXT);');

  await db.runAsync('INSERT INTO people VALUES ("Jeff", "Smith");');
  await db.runAsync('INSERT INTO people VALUES (?, ?);', ["Bart", "Simpson"]);
  await db.runAsync('INSERT INTO people VALUES (?, ?);', "Arthur", "Dent");

  const statement = await db.prepareAsync('SELECT * from people;');

  // Unlike the sqlite3 module, statements are passed to the db handle
  // (or transaction)
  let numRows = await db.eachAsync(statement, (err, row) => {
    console.log("Person is", row.firstname, row.lastname);
  });

  console.log("There were", numRows, "people");

  // Transactions:
  const t = await db.beginTransaction();
  try {
    // Use the tranaction like a DB connection
    await t.runAsync('INSERT INTO people VALUES ("Fred", "Flintstone");');

    // Note that await db.runAsync('...'); here would block while the transaction is open
    //
    // This means you would deadlock on await db.runAsync or other database
    // blocking function if the statement that closes the transaction is
    // further down the current function.  However, it is safe to await on db.*
    // if another function asynchronously closes the transaction (i.e. the case
    // where the transaction was opened async elsewhere).
    //
    // You are able to open additional Database() objects however and those
    // will not block. (Of course SQLite itself  may emit an Error if there is an
    // exclusive lock or you are not using WAL, for instance).

    // Nesting transactions:

    const t2 = await t.beginTransaction();
    try {
      await t2.runAsync('INSERT INTO people VALUES ("Betty", "Rubble");');

      numRows = await t2.eachAsync(statement, (err, row) => {
        console.log("Person is", row.firstname, row.lastname);
      });

      console.log("There are now", numRows, "people");

      await t2.commit();
    } catch(error) {

      console.error(error);
      await t2.rollback();
    }
    const insertStmt= await db.prepareAsync('INSERT INTO people VALUES (?, ?)');
    await insertStmt.bindAsync('Wilma', 'Flintstone');
    await t.runAsync(insertStmt);

    const finalCount = await t.getAsync('SELECT COUNT(*) AS count FROM people');
    console.log("There are now", finalCount.count, "people");

    await t.commit();
  } catch(error) {
    console.error(error);
    await t.rollback();
  }

  // RxJS 5 Observable support

  await db.select('SELECT firstname from people')
    .map(x => x.firstname)
    .map(name => "Hi, my name is " + name)
    .do(console.log)
    .toPromise();

}

testDB().then(() => {
  console.log("Finished");
}).catch( err => {
  console.error("Error:", err);
});