Wrapper for expo-sqlite with async/await, migrations and transactions support

Problem

With expo-sqlite it’s not possible to execute few depending statements inside single transaction – db.transaction does not work with async/promise and tx.executeSql just enqueues sql statement, not immediately executes it. For example, see issues: https://github.com/expo/expo/issues/1889, https://github.com/expo/expo/issues/3726. This forum thread explains a bit how db.transaction works: https://forums.expo.io/t/are-expo-sqlite-transactions-sync-or-async/7934/5

Solution

I created a wrapper around expo-sqlite that works the way you expect it. Usage and code below

Usage

import {Database} from "./database";
const db = new Database("main");

//Transaction example:

await db.transaction(async connection => {
  const res1 = await connection.execute('some sql query');
  await connection.execute('some another query depending on res1');
  await connection.execute('and another');
});

//Single statement without transaction:

await db.execute('some sql containing ?', ['values to replace ?']);

Database class has two methods – execute (to execute single statement without transaction) and transaction(cb) to execute few statements inside a transaction

execute method takes an SQL string (may contain question marks to be replaced) as first parameter and array of values (to replace correspondent question marks ?) as second parameter

transaction method takes an async function as parameter. Callback function receives an instance of Connection class which has execute method with signature as above

Constructor of Database class takes database name as first parameter and optional object as second. Available options:

prepareConnFn Async function to execute after connecting to database. Function receives a Connection instance. Execute and transaction methods of returned Database instance will wait for resolve of prepareConnFn. This can be used to enable foreign keys, for example

migrateFn is somewhat similar to prepareConnFn but for migration purposes (to prepare and update tables). It will receive it’s own Connection instance

See an example below for example of migration function and prepare function. You can omit them if not needed.

import {Database} from "./database";
import m_0001 from "./m_0001";

//When adding new migrations in already deployed app, append them to the end of array, do not re-arrange
//Do not modify migration after app version containing it is published
const migrations = [null, m_0001];
const migrationsTable = "_migrations";

const db = new Database("main", {
  prepareConnFn: async connection => {
    try {
      await connection.execute("PRAGMA foreign_keys = ON;");
    } catch (e) {
      console.log(e);
    }
  },
  migrateFn: async connection => {
    //Inside migration function you can use `connection.beginTransaction`, `connection.commitTransaction` and 
    //`connection.rollbackTransaction` methods to control transactions, as needed. In this example I simply 
    //run all migrations inside single transaction. Your needs might be different
    //Outside of migration use `transaction` method of `Database` class for transactions
    await connection.beginTransaction();
    try {
      await connection.execute(
        `create table if not exists ${migrationsTable} (version integer primary key, updatedAt text not null)`
      );
      const versions = (
          await connection.execute(`select * from ${migrationsTable}`)
      ).rows.map(({ version }) => version);
      const currentVersion = Math.max(0, ...versions);
      for (let i = currentVersion + 1; i < migrations.length; i++) {
        await migrations[i](connection);
        await connection.execute(`insert into ${migrationsTable} values (?, ?)`, [
          i,
          new Date().toISOString()
        ]);
        console.log(`Applied migration ${i}`)
      }
      await connection.commitTransaction();
    } catch (e) {
      await connection.rollbackTransaction();
      console.log(e);
    }    
  }
});

export default db;

Example of migration function (m_0001):

export default async function(connection) {
  await connection.execute(
    `create table users (
      id integer primary key autoincrement, 
      name text not null
    )`
  );
  await connection.execute(
    `create unique index uk_users_name on users (name)`
  );
}

Code of a wrapper

import * as SQLite from "expo-sqlite";

class Connection {
    constructor(databaseName) {
        this._db = SQLite.openDatabase(databaseName);
        this.transacting = false;
    }

    execute(sqlStatement, args = []) {
        return new Promise((resolve, reject) => {
            this._db.exec([{sql: sqlStatement, args}], false, (err, res) => {
                if (err) {
                    return reject(err);
                }
                
                if (res[0].error) {
                    return reject(res[0].error);
                }

                resolve(res[0]);
            });
        });
    }

    close() {
        this._db._db.close();
    }

    async beginTransaction() {
        await this.execute("begin transaction");
        this.transacting = true;
    }

    async commitTransaction() {
        await this.execute("commit");
        this.transacting = false;
    }

    async rollbackTransaction() {
        await this.execute("rollback");
        this.transacting = false;
    }
}

export class Database {
    constructor(name = "main", {prepareConnFn, migrateFn} = {}) {
        this._dbName = name;
        this._connection = new Connection(this._dbName);
        this._params = {prepareConnFn, migrateFn};

        this._prepareConnectionPromise =
            typeof this._params.prepareConnFn === "function"
                ? this._params.prepareConnFn(this._connection)
                : Promise.resolve();

        const performMigration = async () => {
            const connection = new Connection(this._dbName);
            await this._params.migrateFn(connection);
            connection.close();
        };

        this._migrationPromise =
            typeof this._params.migrateFn === "function"
                ? performMigration()
                : Promise.resolve();
    }

    async execute(sqlQuery, args = []) {
        await this._prepareConnectionPromise;
        await this._migrationPromise;

        return await this._connection.execute(sqlQuery, args);
    }

    async transaction(cb) {
        await this._prepareConnectionPromise;
        await this._migrationPromise;
        const connection = new Connection(this._dbName);
        if (typeof this._params.prepareConnFn === "function") {
            await this._params.prepareConnFn(connection);
        }
        try {
            await connection.beginTransaction();
            try {
                await cb(connection);
                await connection.commitTransaction();
            } catch (e) {
                await connection.rollbackTransaction();
                throw e;
            }
        } catch (e) {
            connection.close();
            throw e;
        }
        await connection.close();
    }

    close() {
        this._connection._db.close();
    }
}

See gist for all code:

https://gist.github.com/GendelfLugansk/db31d7742c4dbc3d6d768fa525474aff