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

README.md

Expo-sqlite - the right way

Why

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 but does not execute it.

Documentation

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 as first parameter and array of values to replace ? symbols 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 will wait for resolve of returned promise. This can be used to enable foreign keys, for example

• migrateFn Similar to prepareConnFn but for migration purposes (to prepare and update tables). It will receive separate Connection instance

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

Usage

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

...

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');
});

...

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

database.js

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();
    }
}

db.js

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;

m_0001.js

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)`
  );
}

This wrapper is exactly what I needed, I was struggling all day to get the callback based interface of expo-sqlite to work but with your wrapper everything was working in less than 10min. Thanks for sharing, expo-sqlite docs should mention this wrapper or actually just provide it because using the current websql is difficult to use with react. (Not to mention that websql is so poorly designed in the first place, e.g., not keeping the order of success and error callbacks the same between functions :(

I would like to ask, in the Database class, what is the point of awaiting on two instance variables, for example in execute():

async execute(sqlQuery, args = []) {
    await this._prepareConnectionPromise; // <-- ??
    await this._migrationPromise; // <-- ??
    return await this._connection.execute(sqlQuery, args);
}

what does this achieve?

I would like to ask, in the Database class, what is the point of awaiting on two instance variables, for example in execute():

async execute(sqlQuery, args = []) {
    await this._prepareConnectionPromise; // <-- ??
    await this._migrationPromise; // <-- ??
    return await this._connection.execute(sqlQuery, args);
}

what does this achieve?

It is a promise that have some operation that must be done before executing rhe queries.

@tasso85
As @AlenToma correctly said, we are waiting for async operations that must be finished before executing any queries

Oh yeah, that's it, I didn't consider those instance variables where promises, sorry for the obvious question!

This is awesome - thanks for this lib! ❤️ As a complete zero in JS I was breaking my head trying to work out the sequential transactions until I found this. Works like a charm - I had to iron out a few db locks (SQLITE_BUSY), but only because I moved to another screen in the app within one transaction, which attempted to open another.